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
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.
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.
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.
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
JOIN to those tables and make it part of the JOIN criteria is one option.
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??
17 People are following this question.
