Are there any alternatives to using CROSS APPLY

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.