asked
Help to Optimize a Frequently Ran Query
Recently we have determined that a report being ran against our production database is causing the tempdb to inflate and take up a lot of space on the server. The query was written long before I started working here and I know that it really needs some major tuning. Can anyone help me optimize this? I don't even know where to begin its such a beast. SELECT CONTRIBUTION.CONTRIBUTION_ID AS Contribution, CASE WHEN
PREFERRED_MAIL_VIEW.ID IS NOT NULL THEN
PREFERRED_MAIL_VIEW.ID ELSE
PERSON.ID END AS [Colleague ID], CASE WHEN PREFERRED_MAIL_VIEW.Prefix IS NOT NULL THEN PREFERRED_MAIL_VIEW.Prefix ELSE PERSON.PREFIX END AS Prefix, CASE WHEN PREFERRED_MAIL_VIEW.[First Name] IS NOT NULL THEN PREFERRED_MAIL_VIEW.[First Name] ELSE PERSON.FIRST_NAME END AS [First Name], CASE WHEN PREFERRED_MAIL_VIEW.[Middle Name] IS NOT NULL THEN PREFERRED_MAIL_VIEW.[Middle Name] ELSE PERSON.MIDDLE_NAME END AS [Middle Name], CASE WHEN PREFERRED_MAIL_VIEW.[Last Name] IS NOT NULL THEN PREFERRED_MAIL_VIEW.[Last Name] ELSE PERSON.LAST_NAME END AS [Last Name], PERSON.SUFFIX AS Suffix, PREFERRED_MAIL_VIEW.[Company Name], PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Cell, PREFERRED_MAIL_VIEW.Home, PREFERRED_MAIL_VIEW.[Address Type], PREFERRED_MAIL_VIEW.[Preferred Email], CONTRIBUTION.CONTRIB_TYPE AS Type, CONTRIBUTION.CONTRIB_PLEDGE AS [RD or Pledge ID], CONTRIB_DONOR_DESIG.CONDES_HARD_CREDIT_AMT AS [Hard Credit Amount], CONTRIB_DONOR_DESIG.CONDES_SOFT_CREDIT_AMT AS [Soft Credit Amount], CONTRIB_DONOR_DESIG.CONDES_MATCH_CREDIT_AMT AS [Match Credit Amount], DESIGNATION.DESIG_LONG_DESC AS Designation, CONTRIB_DONOR_DESIG.CONDES_CAMPAIGN AS Campaign, MAX(CASE WHEN PERSON_LS.POS = 1 AND PERSON_LS.REUNION_CLASS IS NOT NULL THEN PERSON_LS.REUNION_CLASS END) AS [Reunion Class 1], MAX(CASE WHEN PERSON_LS.POS = 2 AND PERSON_LS.REUNION_CLASS IS NOT NULL THEN PERSON_LS.REUNION_CLASS END) AS [Reunion Class 2], MAX(CASE WHEN PERSON_LS.POS = 3 AND PERSON_LS.REUNION_CLASS IS NOT NULL THEN PERSON_LS.REUNION_CLASS END) AS [Reunion Class 3], CONVERT(varchar(200), CONTRIBUTION.CONTRIB_COMMENTS) AS Comments, CASE WHEN Query4.GIFT_SOURCE = MAX(CASE WHEN PERSON_LS.POS = 1 AND PERSON_LS.PERSON_MULTIPLE_SOURCES IS NOT NULL THEN PERSON_LS.PERSON_MULTIPLE_SOURCES END) THEN '' ELSE 'YES' END AS DIFF_SOURCE, Query4.GIFT_SOURCE, MAX(CASE WHEN PERSON_LS.POS = 1 AND PERSON_LS.PERSON_MULTIPLE_SOURCES IS NOT NULL THEN PERSON_LS.PERSON_MULTIPLE_SOURCES END) AS [Source 1], MAX(CASE WHEN PERSON_LS.POS = 2 AND PERSON_LS.PERSON_MULTIPLE_SOURCES IS NOT NULL THEN PERSON_LS.PERSON_MULTIPLE_SOURCES END) AS [Source 2], MAX(CASE WHEN PERSON_LS.POS = 3 AND PERSON_LS.PERSON_MULTIPLE_SOURCES IS NOT NULL THEN PERSON_LS.PERSON_MULTIPLE_SOURCES END) AS [Source 3], MAX(CASE WHEN PERSON_LS.POS = 4 AND PERSON_LS.PERSON_MULTIPLE_SOURCES IS NOT NULL THEN PERSON_LS.PERSON_MULTIPLE_SOURCES END) AS [Source 4], CONTRIBUTION.CONTRIB_SESSION AS [Session Number], DESIGNATION.DESIGNATION_ID, CONTRIBUTION.CONTRIB_DONOR_DATE, DESIGNATION.DESIG_PURPOSE, DESIGNATION.DESIG_GIVING_AREA, CONTRIBUTION_LS.CONTRIBUTION_ID, CONTRIBUTION_LS.CONTRIB_DONORS, CONTRIBUTION_LS.CONTRIB_SOLICITORS, PERSON1.LAST_NAME AS SOLICITORS_LAST_NAME, PERSON1.FIRST_NAME AS SOLICITORS_FIRST_NAME, PREFERRED_MAIL_VIEW.[Mail Rules 1], PREFERRED_MAIL_VIEW.[Mail Rules 2], PREFERRED_MAIL_VIEW.[Mail Rules 3] FROM CONTRIBUTION LEFT OUTER JOIN CONTRIBUTION_LS ON CONTRIBUTION_LS.CONTRIBUTION_ID = CONTRIBUTION.CONTRIBUTION_ID FULL OUTER JOIN CONTRIB_DONOR_DESIG ON CONTRIBUTION_LS.CONTRIB_DONOR_DESIGS = CONTRIB_DONOR_DESIG.CONTRIB_DONOR_DESIG_ID INNER JOIN DESIGNATION ON CONTRIB_DONOR_DESIG.CONDES_DESIGNATION = DESIGNATION.DESIGNATION_ID INNER JOIN CONSTITUENT_LS ON CONTRIB_DONOR_DESIG.CONDES_CONTRIB_DONOR = CONSTITUENT_LS.CN_CONTRIB_DONORS FULL OUTER JOIN PREFERRED_MAIL_VIEW ON CONSTITUENT_LS.CONSTITUENT_ID =
PREFERRED_MAIL_VIEW.ID FULL OUTER JOIN PERPHONE ON
PERPHONE.ID =
PREFERRED_MAIL_VIEW.ID INNER JOIN PERSON ON CONSTITUENT_LS.CONSTITUENT_ID =
PERSON.ID INNER JOIN PERSON_LS ON
PERSON_LS.ID =
PERSON.ID FULL JOIN ADR_PHONES ON ADR_PHONES.ADDRESS_ID =
PREFERRED_MAIL_VIEW.ID LEFT JOIN (SELECT CONTRIB_DONOR.CONTRIB_DONOR_ID, CONTRIB_DONOR.COND_DONOR, CONTRIB_DONOR_LS.COND_SOURCES AS GIFT_SOURCE, CONTRIB_DONOR_LS.POS, CONTRIB_DONOR.COND_CONTRIBUTION FROM CONTRIB_DONOR INNER JOIN CONTRIB_DONOR_LS ON CONTRIB_DONOR_LS.CONTRIB_DONOR_ID = CONTRIB_DONOR.CONTRIB_DONOR_ID WHERE CONTRIB_DONOR_LS.POS = 1) Query4 ON CONTRIBUTION.CONTRIBUTION_ID = Query4.COND_CONTRIBUTION AND
PERSON.ID = Query4.COND_DONOR LEFT JOIN PERSON PERSON1 ON CONTRIBUTION_LS.CONTRIB_SOLICITORS =
PERSON1.ID WHERE CONTRIBUTION.CONTRIBUTION_ID IS NOT NULL GROUP BY CONTRIBUTION.CONTRIBUTION_ID, CASE WHEN
PREFERRED_MAIL_VIEW.ID IS NOT NULL THEN
PREFERRED_MAIL_VIEW.ID ELSE
PERSON.ID END, CASE WHEN PREFERRED_MAIL_VIEW.Prefix IS NOT NULL THEN PREFERRED_MAIL_VIEW.Prefix ELSE PERSON.PREFIX END, CASE WHEN PREFERRED_MAIL_VIEW.[First Name] IS NOT NULL THEN PREFERRED_MAIL_VIEW.[First Name] ELSE PERSON.FIRST_NAME END, CASE WHEN PREFERRED_MAIL_VIEW.[Middle Name] IS NOT NULL THEN PREFERRED_MAIL_VIEW.[Middle Name] ELSE PERSON.MIDDLE_NAME END, CASE WHEN PREFERRED_MAIL_VIEW.[Last Name] IS NOT NULL THEN PREFERRED_MAIL_VIEW.[Last Name] ELSE PERSON.LAST_NAME END, PERSON.SUFFIX, PREFERRED_MAIL_VIEW.[Company Name], PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Cell, PREFERRED_MAIL_VIEW.Home, PREFERRED_MAIL_VIEW.[Address Type], PREFERRED_MAIL_VIEW.[Preferred Email], CONTRIBUTION.CONTRIB_TYPE, CONTRIBUTION.CONTRIB_PLEDGE, CONTRIB_DONOR_DESIG.CONDES_HARD_CREDIT_AMT, CONTRIB_DONOR_DESIG.CONDES_SOFT_CREDIT_AMT, CONTRIB_DONOR_DESIG.CONDES_MATCH_CREDIT_AMT, DESIGNATION.DESIG_LONG_DESC, CONTRIB_DONOR_DESIG.CONDES_CAMPAIGN, CONVERT(varchar(200), CONTRIBUTION.CONTRIB_COMMENTS), Query4.GIFT_SOURCE, CONTRIBUTION.CONTRIB_SESSION, DESIGNATION.DESIGNATION_ID, CONTRIBUTION.CONTRIB_DONOR_DATE, DESIGNATION.DESIG_PURPOSE, DESIGNATION.DESIG_GIVING_AREA, CONTRIBUTION_LS.CONTRIBUTION_ID, CONTRIBUTION_LS.CONTRIB_DONORS, CONTRIBUTION_LS.CONTRIB_SOLICITORS, PERSON1.LAST_NAME, PERSON1.FIRST_NAME, PREFERRED_MAIL_VIEW.[Mail Rules 1], PREFERRED_MAIL_VIEW.[Mail Rules 2], PREFERRED_MAIL_VIEW.[Mail Rules 3] ORDER BY [Colleague ID], Contribution
First, get the execution plan to understand exactly what the optimizer is doing with this query. Second, I'd strongly recommend a complete rewrite. Break it down. Start from the one table and slowly rebuild it. Make sure your code is consistent, ensure all objects referenced have owners established. Ensure that you're using the code consistently throughout. Then, really explore the use of the FULL JOIN syntax. Check the execution plans as you rebuild the query. Avoid JOIN operations to views. You may need some indexes. Be sure your statistics are up to date and, possibly, with a FULL SCAN.
Provided query is using lots of cause that create performance issue such as Group by , Order by , Full Outer Join , Max and Etc . If I would you I will start with compilation execution plan you can also use include actual execution plan or estimated execution plan to find out if you can narrow some of Scan , HASH issue. also notice that there are lots of View join as well in query . make sure if you are not repeating same table that you are using in view. Good luck ..
+1: Execution plan - this will tell you where the time is being spent
Ok so I was able to optimize it a bit by removing the MAX and CASE statements. But here is my question. I got it from 45 seconds to 9 seconds, I can take out the GROUP BY since I removed all the aggregate statements but when I remove it, the query takes 11 seconds to run. Why would it take longer when I remove the group by? TIA. @H_G_H
please try to use Option (recompile) to get better timing . Option (recompile) create new execution plan every time you execute script. It is quite helpful when you perform testing and troubleshooting query like you provided. regarding to Group by and order by , performance on query is depend on how you have index set up on table which you are using in group by and order by condition . Hope this help
And recompiles force the optimizer to create a new plan each time a query is run. For a query of this complexity, that's asking a lot of your server. If it's run frequently, it would probably benefit more from having a good plan in cache and reusing it. I'm not comfortable with this advice until we see that we have bad parameter sniffing in play or something else that's likely to need a new plan. Since this is not ad hoc T-SQL, having a stable plan is probably better advice.
Look at the properties of the scan. You can determine if the query is filtering there or elsewhere in the query. You may need to adjust your indexes. But it could also be related to the GrOUP BY or the WHERE condition looking for NULL values.
Do we really need ORDER BY Clause here ? It brings SORT Operator, which is very costly operator FULL OUTER JOIN is very costly. It is right + left outer join combined. Do we need to apply GROUP BY on these many number of columns ? See the actual execution for missing indexes
@Grant Fitchey: ![alt text][1] [1]: /storage/temp/2031-untitled.png
Indications are, that index isn't helping the data access then. It could be because of the WHERE clause, or the JOIN criteria. I'd still go with the rebuild option.
17 People are following this question.
