Select Statement takes too long

1

Hi,

I have the following Insert Statement that is taking a long long time and is still running (past 4 hours).. I run it last night and this morning I received the error message;

There is insufficient memory available in the buffer pool

So, I restarted the server again to clear any cache and re-run the query.. Still running
N/B - the server has 2 TB of space so - space is not an issue.

The insert statement is;

INSERT INTO dbo.EmisDiary(DiaryGuidDigest,PatientID,ConsultationID,StaffID,DiaryEntryDate, MedicalCodeID, Duration)
SELECT DiaryGuidDigest,PatientID,ConsultationID,StaffID,DiaryEntryDate, MedicalCodeID, Duration
FROM dbo.EmisDiaryExtraction WITH (tablock)
WHERE MedicalCodeID IS NOT NULL

The query itself (dbo.EmisDiaryExtraction) is;

SELECT    EMISJun15Btch1.dbo.Diary.DiaryGuidDigest, dbo.EmisPatient.PatientID, dbo.EmisConsultation.ConsultationID, dbo.EmisStaff.StaffID, 
           EMISJun15Btch1.dbo.Diary.EffectiveDateTime AS DiaryEntryDate, dbo.CPRDLkupMedical.MedicalCodeID, EMISJun15Btch1.dbo.Diary.DurationTerm AS Duration
FROM       EMISJun15Btch1.dbo.Diary WITH (tablock) INNER JOIN
           dbo.EmisPatient ON EMISJun15Btch1.dbo.Diary.PatientGuidDigest = dbo.EmisPatient.PatientGuidDigest INNER JOIN
           dbo.EmisStaff ON EMISJun15Btch1.dbo.Diary.AuthorisingUserRoleGuidDigest = dbo.EmisStaff.StaffGuidDigest LEFT OUTER JOIN
           dbo.CPRDLkupMedical ON (EMISJun15Btch1.dbo.Diary.SnomedCTConceptId = dbo.CPRDLkupMedical.Snomed OR
           EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL AND EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL) AND 
           ISNULL(CASE WHEN LEFT(EMISJun15Btch1.dbo.Diary.EmisCode, 7) = 'EMISATT' THEN 'EMISATT' 
                       WHEN LEFT(EMISJun15Btch1.dbo.Diary.EmisCode, 5) = 'PCSDT' THEN 'PCSDT'          
           ELSE EMISJun15Btch1.dbo.Diary.EmisCode END, N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS = ISNULL(dbo.CPRDLkupMedical.Emiscode,
           N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS AND (EMISJun15Btch1.dbo.Diary.SnomedCTDescriptionId = dbo.CPRDLkupMedical.SnomedDescription OR
           EMISJun15Btch1.dbo.Diary.SnomedCTDescriptionId IS NULL AND dbo.CPRDLkupMedical.SnomedDescription IS NULL) AND ISNULL(EMISJun15Btch1.dbo.Diary.Term, 
           N'(novalue)') = ISNULL(dbo.CPRDLkupMedical.Term, N'(novalue)') AND 
           ISNULL(CASE WHEN LEFT(EMISJun15Btch1.dbo.Diary.ReadCode, 5) = 'PCSDT' THEN 'PCSDT' 
                       WHEN LEFT(EMISJun15Btch1.dbo.Diary.ReadCode, 7) = 'EMISATT' THEN 'EMISATT'
           ELSE EMISJun15Btch1.dbo.Diary.ReadCode END, N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS = ISNULL(dbo.CPRDLkupMedical.Readcode,
           N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS LEFT OUTER JOIN
           dbo.EmisConsultation ON EMISJun15Btch1.dbo.Diary.ConsultationGuidDigest = dbo.EmisConsultation.ConsultationGuidDigest

Please could someone look into this for me.

Thank you so much

Regards

2

These will be killing your query:

ON ISNULL(xxx, N'(novalue)') = ISNULL(yyy, N'(novalue)')

and

ON ISNULL(CASE WHEN LEFT(EMISJun15Btch1.dbo.Diary.EmisCode, 7) = 'EMISATT' THEN 'EMISATT' 
			WHEN LEFT(EMISJun15Btch1.dbo.Diary.EmisCode, 5) = 'PCSDT' THEN 'PCSDT'          
			ELSE EMISJun15Btch1.dbo.Diary.EmisCode 
			END, N'(novalue)')
			= ISNULL(dbo.CPRDLkupMedical.Emiscode, N'(novalue)')

Both the use of ISNULL, as above, and the CASE and the LEFT. This type of constructions prevent the use of any indexes which could be used to improve query performance.

I suspect that this is also slow

 ON 
(
	    EMISJun15Btch1.dbo.Diary.SnomedCTConceptId = dbo.CPRDLkupMedical.Snomed
	 OR EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL 
	AND EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL
)

are you confident that the precedence of AND and OR is correct for the result you are looking for from the code?

This bit:

	 OR EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL 
	AND EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL

looks to me to be goofy ...

Where we mix AND and OR we ALWAYS use parenthesis - even if the operator precedence will give us what we want, so that the outcome of expression is unambiguous and obvious to anyone else looking at the code in the future.

I find this dubious too:

	LEFT OUTER JOIN dbo.CPRDLkupMedical
		 ON 
		(
			    EMISJun15Btch1.dbo.Diary.SnomedCTConceptId = dbo.CPRDLkupMedical.Snomed
			 OR EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL 
			AND EMISJun15Btch1.dbo.Diary.SnomedCTConceptId IS NULL
		)

The first test involves the JOINed dbo.CPRDLkupMedical table, fair enough.

The other two tests don't and may well result in a Cartesian join.

Given that CPRDLkupMedical uses an OUTER JOIN and is only required for one column in the SELECT it might improve performance to move that part to an OUTER APPLY. Perhaps the same for dbo.EmisConsultation.

But personally my approach would be more likely to change the structure of the data so that JOINs were more easily made and didn't have issues with NULL/Non-null and so on.

Do you really have EmisCode columns in both EMISJun15Btch1.dbo.Diary and dbo.CPRDLkupMedical.Emiscode tables that use different Collations? If so I would want to question how that came about. (You don't need "COLLATE SQL_Latin1_General_CP1_CS_AS" on BOTH sides of the "=" sign, but that's just semantics, might make the code a bit easier to read though.