SSS SSS - 8 days ago 4
SQL Question

How to make this slow nested SQL Case Query faster

I have a table of 'Contacts' that includes fields like Name, Surname, DOB etc.

I am trying to create an SQL View of this which also includes a field called 'ProspectiveNews'. This field will essentially contain one of 4 classification codes (or blank) depending on a match in criteria from another View.

A contact can have numerous records in this other View with different YearLevels.

In this other View the contacts ID could appear in one of two columns: [ContactID] or [ContactSpouseID] where another column, ReferenceTable <> 'Enquiry'

Then there are some records where ReferenceTable = 'Enquiry' where the ContactID column contains their EnquiryID instead so it needs to use that.

Hence the 3 checks for each classification code.

Here is the code though it takes about 2min to run. That's the problem - it is too slow. What could I do to make this quicker?

Code summary:

IF Contact has a record(s) from another View that is(are) only YearLevel 0 THEN 'K'

IF Contact has a record(s) from another View that is(are) only YearLevel 1-6 THEN '1-6'

IF Contact has a record(s) form another View that is(are) YearLevel 7-12 THEN '7-12'

IF Contact has records that are in YearLevels:

0 AND 1-6

OR

0 AND 7-12

OR

1-6 AND 7-12

THEN 'General'

Code:



SELECT

C.ID as ContactID,
ESC.ID as EnquiryID,

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry')
AND NOT EXISTS (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 0 AND ReferenceTable <> 'Enquiry' AND [ContactID] = C.ID)
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry')
AND NOT EXISTS (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 0 AND ReferenceTable <> 'Enquiry' AND [ContactSpouseID] = C.ID)
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel = 0 AND ReferenceTable = 'Enquiry')
AND NOT EXISTS (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 0 AND ReferenceTable = 'Enquiry' AND [ContactID] = C.ID)
)
THEN 'K'

ELSE

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 0 AND YearLevel < 7 AND ReferenceTable <> 'Enquiry')
AND NOT EXISTS (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry' AND [ContactID] = C.ID)
AND NOT EXISTS (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 6 AND ReferenceTable <> 'Enquiry' AND [ContactID] = C.ID)
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 0 AND YearLevel < 7 AND ReferenceTable <> 'Enquiry')
AND NOT EXISTS (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry' AND [ContactSpouseID] = C.ID)
AND NOT EXISTS (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 6 AND ReferenceTable <> 'Enquiry' AND [ContactSpouseID] = C.ID)
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 0 AND YearLevel < 7 AND ReferenceTable = 'Enquiry')
AND NOT EXISTS (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel = 0 AND ReferenceTable = 'Enquiry' AND [ContactID] = C.ID)
AND NOT EXISTS (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 6 AND ReferenceTable = 'Enquiry' AND [ContactID] = C.ID)
)

THEN '1-6'

ELSE

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 6 AND YearLevel < 13 AND ReferenceTable <> 'Enquiry')
AND NOT EXISTS (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel < 7 AND ReferenceTable <> 'Enquiry' AND [ContactID] = C.ID)
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 6 AND YearLevel < 13 AND ReferenceTable <> 'Enquiry')
AND NOT EXISTS (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel < 7 AND ReferenceTable <> 'Enquiry' AND [ContactSpouseID] = C.ID)
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel > 6 AND YearLevel < 13 AND ReferenceTable = 'Enquiry')
AND NOT EXISTS (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND YearLevel < 7 AND ReferenceTable = 'Enquiry' AND [ContactID] = C.ID)
)
THEN '7-6'

ELSE

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND ReferenceTable <> 'Enquiry')
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND ReferenceTable <> 'Enquiry')
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactID] FROM [Table] WHERE [EnrolDate] > '2016-12-31' AND ReferenceTable = 'Enquiry')
)

THEN 'General'

ELSE '' END END

END
END AS ProspectiveNews




SSS SSS
Answer

@UV

Thank you: The slow performance is due to the fact you are selecting from [Table] too many times. instead, join a shortlist of [table], maybe aggregated, and use case using the joined fields. If I'll have time I'll write an example later. if you need you join to refer the main table you may want to use OUTER APPLY there are good posts from Itzik Ben-Gan about it or look here: sqlbits.com/Sessions/Event14/… – UV