question

muk avatar image
muk asked

Tuning SQL Query - Running too long

Hello! I have a query that does exactly what I need it to but I know there has to a better way to write this because it runs for 17 minutes. The problem is in the where logic because when I take the where out, it runs in less than a minute. Can anyone help me rewrite the where? SELECT DISTINCT A.ID , A.[Last Name] , A.[First Name] , A.[Middle Name] , A.[Reunion Class 1] FROM ( SELECT PREFERRED_MAIL_VIEW.ID , PREFERRED_MAIL_VIEW.[Last Name] , PREFERRED_MAIL_VIEW.[First Name] , PREFERRED_MAIL_VIEW.[Middle Name] , PREFERRED_MAIL_VIEW.[Reunion Class 1] , cndmsl0.AS400ID , cndmsl0.J1NMID , cndmsl0.J1LOID , cndmsl0.J1GRYR , cndmsl0.J1COLG , appcl.AS400ID AS AS400ID1 , appcl.NMID11 , appcl.MAJ111 , appcl.MAJ211 , appcl.MAJ11C , appcl.MAJ21C , appcl.CLL111 , appcl.CLL211 , appcl.CLL311 , appcl.CLL411 , appcl.CLL11C , appcl.CLL21C , appcl.CLL31C , appcl.CLL41C , appcl.CL1D11 , appcl.CL2D11 , appcl.CL3D11 , appcl.CL4D11 , appcl.CL1D1C , appcl.CL2D1C , appcl.CL3D1C , appcl.CL4D1C , appcl.CL1M11 , appcl.CL2M11 , appcl.CL3M11 , appcl.CL4M11 , appcl.CL1M1C , appcl.CL2M1C , appcl.CL3M1C , appcl.CL4M1C , appcl.CL1G11 , appcl.CL2G11 , appcl.CL3G11 , appcl.CL4G11 , appcl.CCG111 , appcl.CGP211 , appcl.CGP311 , appcl.CGP411 , appcl.CRD111 , appcl.CRD211 , appcl.CRD311 , appcl.CRD411 , appcl.CLT111 , appcl.CLT211 , appcl.CLT311 , appcl.CLT411 , adorhsl0.AS400ID AS AS400ID2 , adorhsl0.JCNMID , adorhsl0.JCXVAS , adorhsl0.JCOIAD , adorhsl0.JCOCAD , adorhsl0.JCOTAD , adorhsl0.JCLORG , adorhsl0.JCDEGR , adorhsl0.JCYRGD , adorhsl0.JCGPA , adotel0.AS400ID AS AS400ID3 , adotel0.AWNMID , adotel0.AWXVAS , adotel0.AWOIAD , adotel0.AWOCAD , adotel0.AWOTAD , adotel0.AWHOAA , adotel0.AWHOAC , orgtrl0.AS400ID AS AS400ID4 , orgtrl0.ASNMID , orgtrl0.ASXVAS , orgtrl0.ASORID , orgtrl0.ASYNAW , orgtrl0.ASCOAA , orgtrl0.ASCPAA FROM PREFERRED_MAIL_VIEW LEFT JOIN STUDENTS ON PREFERRED_MAIL_VIEW.ID = STUDENTS.STUDENTS_ID LEFT JOIN ( SELECT CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END AS COLL_ID , LTRIM(as400_cndmsl0.J1NMID) AS AS400ID , as400_cndmsl0.* FROM as400_cndmsl0 LEFT JOIN PERSON_ALT ON LTRIM(as400_cndmsl0.J1NMID) = PERSON_ALT.PERSON_ALT_IDS LEFT JOIN PERSON ON as400_cndmsl0.J1NMID = SUBSTRING(PERSON.SSN, 1, 3) + SUBSTRING(PERSON.SSN, 5, 2) + SUBSTRING(PERSON.SSN, 8, 4) WHERE CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END IS NOT NULL ) cndmsl0 ON PREFERRED_MAIL_VIEW.ID = cndmsl0.COLL_ID LEFT JOIN ( SELECT CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END AS COLL_ID , LTRIM(as400_appcl.NMID11) AS AS400ID , as400_appcl.* FROM as400_appcl LEFT JOIN PERSON_ALT ON LTRIM(as400_appcl.NMID11) = PERSON_ALT.PERSON_ALT_IDS LEFT JOIN PERSON ON as400_appcl.NMID11 = SUBSTRING(PERSON.SSN, 1, 3) + SUBSTRING(PERSON.SSN, 5, 2) + SUBSTRING(PERSON.SSN, 8, 4) WHERE CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END IS NOT NULL ) appcl ON PREFERRED_MAIL_VIEW.ID = appcl.COLL_ID LEFT JOIN ( SELECT CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END AS COLL_ID , LTRIM(as400_adorhsl0.JCNMID) AS AS400ID , as400_adorhsl0.* FROM as400_adorhsl0 LEFT JOIN PERSON_ALT ON LTRIM(as400_adorhsl0.JCNMID) = PERSON_ALT.PERSON_ALT_IDS LEFT JOIN PERSON ON as400_adorhsl0.JCNMID = SUBSTRING(PERSON.SSN, 1, 3) + SUBSTRING(PERSON.SSN, 5, 2) + SUBSTRING(PERSON.SSN, 8, 4) WHERE CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END IS NOT NULL ) adorhsl0 ON PREFERRED_MAIL_VIEW.ID = adorhsl0.COLL_ID LEFT JOIN ( SELECT CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END AS COLL_ID , LTRIM(as400_adotel0.AWNMID) AS AS400ID , as400_adotel0.* FROM as400_adotel0 LEFT JOIN PERSON_ALT ON LTRIM(as400_adotel0.AWNMID) = PERSON_ALT.PERSON_ALT_IDS LEFT JOIN PERSON ON as400_adotel0.AWNMID = SUBSTRING(PERSON.SSN, 1, 3) + SUBSTRING(PERSON.SSN, 5, 2) + SUBSTRING(PERSON.SSN, 8, 4) WHERE CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END IS NOT NULL ) adotel0 ON PREFERRED_MAIL_VIEW.ID = adotel0.COLL_ID LEFT JOIN ( SELECT CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END AS COLL_ID , LTRIM(as400_orgtrl0.ASNMID) AS AS400ID , as400_orgtrl0.* FROM as400_orgtrl0 LEFT JOIN PERSON_ALT ON LTRIM(as400_orgtrl0.ASNMID) = PERSON_ALT.PERSON_ALT_IDS LEFT JOIN PERSON ON as400_orgtrl0.ASNMID = SUBSTRING(PERSON.SSN, 1, 3) + SUBSTRING(PERSON.SSN, 5, 2) + SUBSTRING(PERSON.SSN, 8, 4) WHERE CASE WHEN PERSON_ALT.ID IS NOT NULL THEN PERSON_ALT.ID ELSE PERSON.ID END IS NOT NULL ) orgtrl0 ON PREFERRED_MAIL_VIEW.ID = orgtrl0.COLL_ID WHERE PREFERRED_MAIL_VIEW.[Reunion Class 1] IS NOT NULL AND ( cndmsl0.J1LOID IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR appcl.CLL11C IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR appcl.CLL21C IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR appcl.CLL31C IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR appcl.CLL41C IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR adorhsl0.JCOIAD IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR adorhsl0.JCOCAD IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR orgtrl0.ASORID IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR orgtrl0.ASYNAW IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR adotel0.AWOIAD IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) OR adotel0.AWOCAD IN ( SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' ) ) ) AS A
t-sqlquerysql servertuningquery-tuning
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
bernard avatar image
bernard answered
the first thing i would do is to insert this into a temp table: SELECT INSTITUTIONS_LS.INST_IDS_IDX into temp FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' as this is a query you run a few times there is no reason to do do it multiply times afterwards mayb think about index but again depends what else you do with this tables.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
You've got a lot of common code smells in there. All those functions in the JOIN criteria and WHERE clauses: LTRIM(as400_cndmsl0.J1NMID) You need to eliminate those first of all. I suspect pretty strongly if you look at the execution plan you'll see that the "Reason for Early Termination" is Timeout. That means there are so many tables and so much stuff going on the optimizer just can't get a good plan. Do you really need to have that OR statement with the sub-select repeated over and over again? CASE statements in the WHERE clause are also going to murder performance. I'd start with all that.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

west007 avatar image
west007 answered
first, in the where clause is allways the same set used, instead of where it could be one left join with or connectors. second, that is really monster sql, try to redesign the hole thing
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
JOIN to those tables and make it part of the JOIN criteria is one option.
1 Like 1 ·
muk avatar image muk commented ·
Thank you @Grant Fritchey @bernard and @west007, is there anyway you can possibly give me a sample sql of how to rewrite this in a more efficient way? I know that I say SELECT INSTITUTIONS_LS.INST_IDS_IDX FROM INSTITUTIONS_LS WHERE INSTITUTIONS_LS.INSTITUTIONS_ID = '0001867' multiple times but what I am doing is putting this into a report where the user enters the parameter and I need to see if this parameter exists on multiple tables. How can I check for INSTITUTIONS_ID in multiplpe different tables without an Or statement??
0 Likes 0 ·
muk avatar image muk commented ·
Thank you very much @GrantFritchey I will try it and post my resulting performance time.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.