1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] Using multiple Cross Joins with JSON seems to slow my query right down and never...

Discussão em 'Outras Linguagens' iniciado por Stack, Agosto 16, 2021.

  1. Stack

    Stack Membro Participativo

    I have the following JSON:

    [
    {
    "UserDetails": [
    {
    "UserName": "User1",
    "UserDateOfBirth": "06/11/89",
    "UserID": "12345",
    "NotesDay1": [
    {
    "NoteID": "a287fcc4-4b3e-4b63-ac61-dc063c53894d",
    "AnswerType": 1,
    "RemedialText": null,
    "Details": null,
    "UserLocation": 0,
    "DateDone": "2021-08-06T00:19:14.673+00:00"
    },
    {
    "NoteID": "4a48385a-f73a-44ff-bde0-a7de6aa3d965",
    "AnswerType": 1,
    "RemedialText": null,
    "Details": null,
    "UserLocation": 0,
    "DateDone": "2021-08-06T02:19:59.373+00:00"
    },
    {
    "NoteID": "ddb6bc52-634f-4960-b753-619b18ecd1fa",
    "AnswerType": 1,
    "RemedialText": null,
    "Details": null,
    "UserLocation": 0,
    "DateDone": "2021-08-06T04:14:50.214+00:00"
    }
    ]
    },
    {
    "UserName": "User2",
    "UserDateOfBirth": "05/08/99",
    "UserID": "23456",
    "NotesDay1": [],
    "NotesDay2": [
    {
    "NoteID": "62cf5478-0705-4cb5-8a18-97c5335de606",
    "AnswerType": 1,
    "RemedialText": null,
    "Details": null,
    "UserLocation": 0,
    "DateDone": "2021-08-07T01:00:48.071+00:00"
    },
    {
    "NoteID": "7f864ef4-4624-4b2e-a40e-bfa77a59e0b0",
    "AnswerType": 1,
    "RemedialText": null,
    "Details": null,
    "UserLocation": 0,
    "DateDone": "2021-08-07T01:00:48.071+00:00"
    },
    {
    "NoteID": "db1a0af0-6274-4237-a379-77c4c54f2973",
    "AnswerType": 1,
    "RemedialText": null,
    "Details": null,
    "UserLocation": 0,
    "DateDone": "2021-08-07T06:28:02.962+00:00"
    },
    {
    "NoteID": "2ae6b923-e852-4d0b-96e3-194c58eec28f",
    "AnswerType": 1,
    "RemedialText": null,
    "Details": null,
    "UserLocation": 0,
    "DateDone": "2021-08-07T06:28:02.962+00:00"
    }
    ],
    "NotesDay3": [],
    "NotesDay4": [],
    "NotesDay5": [],
    "NotesDay6": [],
    "NotesDay7": []
    }
    ]
    }
    ]


    My current SQL query to import this is set up as below:

    SELECT j2.UserID, j3.NoteID1, j4.NoteID2, j5.NoteID3, j6.NoteID4, j7.NoteID5, j8.NoteID6, j9.NoteID7
    INTO [UserDayNotes]
    FROM OPENJSON(@JSON)
    WITH
    (
    UserDetails nvarchar(max) '$.UserDetails' as JSON
    ) j1

    CROSS APPLY OPENJSON(j1.UserDetails) WITH
    (
    UserID nvarchar(100) '$.UserID',
    NotesDay1 nvarchar(max) '$.NotesDay1' as JSON,
    NotesDay2 nvarchar(max) '$.NotesDay2' as JSON,
    NotesDay3 nvarchar(max) '$.NotesDay3' as JSON,
    NotesDay4 nvarchar(max) '$.NotesDay4' as JSON,
    NotesDay5 nvarchar(max) '$.NotesDay5' as JSON,
    NotesDay6 nvarchar(max) '$.NotesDay6' as JSON,
    NotesDay7 nvarchar(max) '$.NotesDay7' as JSON
    ) j2

    CROSS APPLY OPENJSON(j2.NotesDay1) WITH
    (
    NoteID1 nvarchar(100) '$.NoteID'
    ) j3

    CROSS APPLY OPENJSON(j2.CareNotesDay2) WITH
    (
    NoteID2 nvarchar(100) '$.NoteID'
    ) j4

    CROSS APPLY OPENJSON(j2.CareNotesDay3) WITH
    (
    NoteID3 nvarchar(100) '$.NoteID'
    ) j5

    CROSS APPLY OPENJSON(j2.CareNotesDay4) WITH
    (
    NoteID4 nvarchar(100) '$.NoteID'
    ) j6

    CROSS APPLY OPENJSON(j2.CareNotesDay5) WITH
    (
    NoteID5 nvarchar(100) '$.NoteID'
    ) j7

    CROSS APPLY OPENJSON(j2.CareNotesDay6) WITH
    (
    NoteID6 nvarchar(100) '$.NoteID'
    ) j8

    CROSS APPLY OPENJSON(j2.CareNotesDay7) WITH
    (
    NoteID3 nvarchar(100) '$.NoteID'
    ) j9


    If I run the query as it is, then it runs forever and ever and never completes. If I comment out all but j2.UserID, j3.NoteID1 and j4.NoteID2, then the query runs more or less instantly. I'm assuming this is something to do with the number of cross apply items I have, but my experience and knowledge of this side of SQL is not great, and I could really use some help.

    How do I get to the point where I have the following data layout:

    UserID NotesDay1 NotesDay2 NotesDay3 NotesDay4 NotesDay 5 NotesDay 6 NotesDay7
    User1 12345 23456 34567 45678 56789
    User2 54321 65432 76543 87654

    (Good lord, that table took some doing!)

    Thanks in advance.

    Continue reading...

Compartilhe esta Página