Are there any alternatives to using CROSS APPLY

Simon Evans 81 Reputation points
2021-02-11T11:07:08.993+00:00

Are there any alternatives to using CROSS APPLY to below, performance isn't great on a large dataset

SELECT A.LOCAL_PATIENT_IDENTIFIER
       ,DATEADD(DAY,1,A.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE) AS GAP_START
       ,COALESCE(DATEADD(DAY,-1,Next_Record.NEXT_START_DATE),'4712-12-31') AS GAP_END
FROM #CDO_MPI_GP_HISTORY_SE2 A
OUTER APPLY
        (     SELECT TOP 1 B.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE AS NEXT_START_DATE
              FROM #CDO_MPI_GP_HISTORY_SE2 B
              WHERE B.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE > A.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE AND B.ORGANISATION_CODE LIKE 'YD%'
                     AND A.LOCAL_PATIENT_IDENTIFIER = B.LOCAL_PATIENT_IDENTIFIER
              ORDER BY B.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE
       ) Next_Record
WHERE  A.ORGANISATION_CODE LIKE 'YD%'  
       AND COALESCE(Next_Record.NEXT_START_DATE,'4712-12-31')<>DATEADD(DAY,1,A.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE)
ORDER BY LOCAL_PATIENT_IDENTIFIER, GAP_START
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-12T06:05:02.933+00:00

    Hi @Simon Evans ,

    Welcome to Microsoft Q&A!

    Please also refer below example:

    DROP TABLE IF EXISTS #CDO_MPI_GP_HISTORY_SE2  
      
    CREATE TABLE #CDO_MPI_GP_HISTORY_SE2  
    (LOCAL_PATIENT_IDENTIFIER INT,  
    GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE DATE,  
    GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE DATE,  
    ORGANISATION_CODE VARCHAR(100))  
      
    INSERT INTO #CDO_MPI_GP_HISTORY_SE2 VALUES  
    (1,'2021-01-01','2021-01-03','YD1'),  
    (1,'2021-01-06','2021-01-09','YD2'),  
    (1,'2021-01-10','2021-01-15','YD2'),  
    (1,'2021-01-20','2021-01-25','YD3'),  
    (2,'2021-01-08','2021-01-11','YD1'),  
    (2,'2021-01-15','2021-01-20','YD2'),  
    (2,'2021-01-23','2021-01-29','YD3');  
      
    WITH CTE AS (  
    SELECT * ,LEAD(GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE)   
    OVER (PARTITION BY LOCAL_PATIENT_IDENTIFIER ORDER BY GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE) NEXT_START_DATE  
    FROM #CDO_MPI_GP_HISTORY_SE2  
    WHERE ORGANISATION_CODE LIKE 'YD%')  
    SELECT LOCAL_PATIENT_IDENTIFIER  
           ,DATEADD(DAY,1,GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE) AS GAP_START  
           ,COALESCE(DATEADD(DAY,-1,NEXT_START_DATE),'4712-12-31') AS GAP_END  
    FROM CTE  
    WHERE COALESCE(NEXT_START_DATE,'4712-12-31')<>DATEADD(DAY,1,GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE)  
    

    Output:

    LOCAL_PATIENT_IDENTIFIER	GAP_START	GAP_END  
    1	2021-01-04	2021-01-05  
    1	2021-01-16	2021-01-19  
    1	2021-01-26	4712-12-31  
    2	2021-01-12	2021-01-14  
    2	2021-01-21	2021-01-22  
    2	2021-01-30	4712-12-31  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-02-11T14:28:34.667+00:00

    Depending on the version you are using, you can use LEAD instead to find the next value.

    https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15

    0 comments No comments

  2. Erland Sommarskog 103.5K Reputation points MVP
    2021-02-11T22:43:35.413+00:00

    Elaborating on Tom's suggestion, here is a query that uses LEAD:

    SELECT A.LOCAL_PATIENT_IDENTIFIER     ,DATEADD(DAY,1,A.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE) AS GAP_START
           ,DATEADD(DAY, -1, 
               LEAD(GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE, 1, '4800-01-01') 
                   OVER (PARTITION BY LOCAL_PATIENT_IDENTIFIER ORDER BY GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_START_DATE))  
             AS GAP_END
    FROM #CDO_MPI_GP_HISTORY_SE2 A
    WHERE  A.ORGANISATION_CODE LIKE 'YD%'  
           AND COALESCE(Next_Record.NEXT_START_DATE,'4712-12-31')<>DATEADD(DAY,1,A.GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE)
    ORDER BY LOCAL_PATIENT_IDENTIFIER, GAP_START
    

    You will need SQL 2012 or later.


  3. Jeffrey Williams 1,891 Reputation points
    2021-02-12T01:25:45.377+00:00

    I think a big problem with your query is the WHERE clause. The COALESCE for the next start date - and using DATEADD on GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE is going to force a table scan instead of using any indexes.

    Is there any reason to check for NULL not being equal to the next GENERAL_MEDICAL_PRACTITIONER_ASSOCIATION_END_DATE?

    A quick look without seeing any sample data, this looks like a gaps & islands type problem. If that is the case, there may be a much better approach - but it depends on the data and how you are grouping that data.