question

muk avatar image
muk asked

my query is taking like 10 minutes

[link text][1] Hello all, can someone help me I have performance issues with this query: SELECT t1.ID, t1.Prefix, t1.[First Name], t1.[Middle Name], t1.[Last Name], t3.SUFFIX, t3.PERSON_STATUS, t3.GENDER, t3.DECEASED_DATE, t3.MARITAL_STATUS, t1.[Company Name], t1.[Address Line 1], t1.[Address Line 2], t1.[Address Line 3], t1.City, t1.State, t1.Zip, t1.[Address Type], t1.Email, t1.Cell, t1.Home, t3.SPOUSE AS [Spouse ID], t3.BIRTH_DATE, t1.[Life Largest Amount], t1.[Largest Amount Date], t1.[Life Smallest Amount], t1.[Smallest Amount Date], t1.[Last Amount], t1.[Last Amount Date], t1.[First Amount], t1.[First Amount Date], t2.[Sum Of GF] AS [Gift Amount], t2.[Sum Of NP] AS [Pledge Amount], t2.[Sum Of PP] AS [Pledge Payment Amount], t2.[Sum Soft Credit] AS [Soft Gift Amt], t2.[SUM SOFT PP], t2.[SUM SOFT NP], t2.[Sum Of GF] + t2.[Sum Soft Credit] + t2.[Sum Of PP] AS [Lifetime Giving] FROM (SELECT PV.ID AS ID, PV.Prefix AS Prefix, PV.[First Name] AS [First Name], PV.[Middle Name] AS [Middle Name], PV.[Last Name] AS [Last Name], PV.[Company Name] AS [Company Name], PV.[Address Line 1] AS [Address Line 1], PV.[Address Line 2] AS [Address Line 2], PV.[Address Line 3] AS [Address Line 3], PV.City AS City, PV.[State] AS State, PV.Zip AS Zip, PV.[Address Type] AS [Address Type], PV.[Preferred Email] AS Email, PV.Cell AS Cell, PV.Home AS Home, C.[LIFE LARGEST AMOUNT] AS [Life Largest Amount], C.[LARGEST AMOUNT DATE] AS [Largest Amount Date], C.[LIFE SMALLEST AMOUNT] AS [Life Smallest Amount], C.[SMALLEST AMOUNT DATE] AS [Smallest Amount Date], C.[LAST AMOUNT] AS [Last Amount], C.[LAST AMOUNT DATE] AS [Last Amount Date], C.[FIRST AMOUNT] AS [First Amount], C.[FIRST AMOUNT DATE] AS [First Amount Date] FROM PREFERRED_MAIL_VIEW PV CROSS APPLY (SELECT MAX(CASE WHEN L = 1 THEN AMT ELSE NULL END) [LIFE LARGEST AMOUNT], MAX(CASE WHEN L = 1 THEN CONTRIB_DATE ELSE NULL END) [LARGEST AMOUNT DATE], MAX(CASE WHEN S = 1 THEN AMT ELSE NULL END) [LIFE SMALLEST AMOUNT], MAX(CASE WHEN S = 1 THEN CONTRIB_DATE ELSE NULL END) [SMALLEST AMOUNT DATE], MAX(CASE WHEN D = 1 THEN AMT ELSE NULL END) [LAST AMOUNT], MAX(CASE WHEN D = 1 THEN CONTRIB_DATE ELSE NULL END) [LAST AMOUNT DATE], MAX(CASE WHEN A = 1 THEN AMT ELSE NULL END) [FIRST AMOUNT], MAX(CASE WHEN A = 1 THEN CONTRIB_DATE ELSE NULL END) [FIRST AMOUNT DATE] FROM (SELECT COALESCE (NULLIF (CDD.CONDES_HARD_CREDIT_AMT, 0), CDD.CONDES_SOFT_CREDIT_AMT, 0) AMT, C.CONTRIB_DATE, ROW_NUMBER() OVER (ORDER BY COALESCE (NULLIF (CDD.CONDES_HARD_CREDIT_AMT, 0), CDD.CONDES_SOFT_CREDIT_AMT, 0) DESC) L, ROW_NUMBER() OVER (ORDER BY COALESCE (NULLIF (CDD.CONDES_HARD_CREDIT_AMT, 0), CDD.CONDES_SOFT_CREDIT_AMT, 0) ASC) S, ROW_NUMBER() OVER (ORDER BY CONTRIB_DATE DESC) D, ROW_NUMBER() OVER (ORDER BY CONTRIB_DATE ASC) A, CONTRIB_TYPE FROM CONTRIB_DONOR CD INNER JOIN CONTRIB_DONOR_DESIG CDD ON CD.CONTRIB_DONOR_ID = CDD.CONDES_CONTRIB_DONOR INNER JOIN CONTRIBUTION C ON CDD.CONDES_CONTRIBUTION = C.CONTRIBUTION_ID WHERE CD.COND_DONOR = PV.ID AND (C.CONTRIB_TYPE IN ('GF', 'PP'))) T) C) AS t1, (SELECT PV.ID, C.[SUM SOFT NP] AS [SUM SOFT NP], C.[SUM SOFT PP] AS [SUM SOFT PP], case when C.[SUM OF GIFTS] is not null then C.[SUM OF GIFTS] else 0 end AS [Sum Of GF], case when C.[SUM PAID TO DATE] is not null then C.[SUM PAID TO DATE] else 0 end AS [Sum Of PP], C.[SUM NP GIFTS] AS [Sum Of NP], case when C.[Sum Soft Credit] IS not null then C.[Sum Soft Credit] else 0 end AS [Sum Soft Credit] FROM PREFERRED_MAIL_VIEW PV CROSS APPLY (SELECT SUM(CASE WHEN CONTRIB_TYPE = 'GF' THEN AMT ELSE 0 END) [SUM OF GIFTS], SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN AMT ELSE 0 END) [SUM PAID TO DATE], SUM(CASE WHEN CONTRIB_TYPE = 'NP' THEN AMT ELSE 0 END) [SUM NP GIFTS], SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN ammt ELSE 0 END) [SUM SOFT PP], SUM(CASE WHEN CONTRIB_TYPE = 'NP' THEN ammt ELSE 0 END) [SUM SOFT NP], SUM(ammt) [Sum Soft Credit] FROM (SELECT NULLIF (CDD.CONDES_HARD_CREDIT_AMT, 0) AMT, CDD.CONDES_SOFT_CREDIT_AMT ammt, C.CONTRIB_DATE, CONTRIB_TYPE FROM CONTRIB_DONOR CD INNER JOIN CONTRIB_DONOR_DESIG CDD ON CD.CONTRIB_DONOR_ID = CDD.CONDES_CONTRIB_DONOR INNER JOIN CONTRIBUTION C ON CDD.CONDES_CONTRIBUTION = C.CONTRIBUTION_ID WHERE CD.COND_DONOR = PV.ID) T) C) AS t2, (SELECT p.ID, p.SUFFIX, p.SPOUSE, p.PREFERRED_RESIDENCE, p.PERSON_STATUS, p.BIRTH_DATE, p.GENDER, p.DECEASED_DATE, p.MARITAL_STATUS FROM PERSON p) AS t3 WHERE t1.ID = t2.ID AND t2.ID = t3.ID; when I execute this it is suppose to return over 30,000 records but it takes like 10 minutes to execute this so it is impossible to run a report. What can I do to make this faster? Thanks! [1]: /storage/temp/297-exec.zip Here is the PREFERRED_MAIL_VIEW definition: CREATE VIEW [dbo].[PREFERRED_MAIL_VIEW] AS Select PERSON.ID, PERSON.PREFIX As Prefix, PERSON.FIRST_NAME As 'First Name', PERSON.MIDDLE_NAME As 'Middle Name', PERSON.LAST_NAME As 'Last Name', PERSON.PREFERRED_NAME As 'Company Name', Max(Case When ADDRESS_LS.POS = 1 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As 'Address Line 1', Max(Case When ADDRESS_LS.POS = 2 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As 'Address Line 2', Max(Case When ADDRESS_LS.POS = 3 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As 'Address Line 3', ADDRESS.CITY As City, ADDRESS.STATE As State, ADDRESS.ZIP As Zip, PSEASON.ADDR_TYPE As 'Address Type', PEOPLE_EMAIL.PERSON_EMAIL_ADDRESSES As [Preferred Email], Max(Case When (PERPHONE.POS = 1 And PERPHONE.PERSONAL_PHONE_TYPE = 'CELL') Then PERPHONE.PERSONAL_PHONE_NUMBER Else (Case When (PERPHONE.POS = 2 And PERPHONE.PERSONAL_PHONE_TYPE = 'CELL') Then PERPHONE.PERSONAL_PHONE_NUMBER End) End) As 'Cell', Max(Case When (PERPHONE.POS = 1 And PERPHONE.PERSONAL_PHONE_TYPE = 'HOM') Then PERPHONE.PERSONAL_PHONE_NUMBER Else (Case When (PERPHONE.POS = 2 And PERPHONE.PERSONAL_PHONE_TYPE = 'HOM') Then PERPHONE.PERSONAL_PHONE_NUMBER End) End) As 'Home' From PERSON Inner Join ADDRESS On PERSON.PREFERRED_ADDRESS = ADDRESS.ADDRESS_ID Inner Join ADDRESS_LS On ADDRESS_LS.ADDRESS_ID = ADDRESS.ADDRESS_ID Inner Join PSEASON On PSEASON.ID = PERSON.ID And PSEASON.PERSON_ADDRESSES = ADDRESS.ADDRESS_ID Full Join PEOPLE_EMAIL On PEOPLE_EMAIL.ID = PERSON.ID Left Join PERPHONE On PERPHONE.ID = PERSON.ID Where (PEOPLE_EMAIL.POS = 1) Or (PEOPLE_EMAIL.POS Is Null) Group By PERSON.ID, PERSON.PREFIX, PERSON.FIRST_NAME, PERSON.MIDDLE_NAME, PERSON.LAST_NAME, PERSON.PREFERRED_NAME, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIP, PSEASON.ADDR_TYPE, PEOPLE_EMAIL.PERSON_EMAIL_ADDRESSES Having PERSON.ID Is Not Null
t-sqlqueryperformance-tuning
exec.zip (25.1 KiB)
7 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.

JohnM avatar image JohnM commented ·
Can you post the execution plan associated with this query? That should help to narrow things down.
2 Likes 2 ·
WilliamD avatar image WilliamD commented ·
If you could also supply us with a little sample data and table creation scripts along with an expected outcome, that'd be a bonus.
2 Likes 2 ·
muk avatar image muk commented ·
@JohnM Should I post the xml of the execution plan? It will not let me attach this because the file is too big. Should I copy-paste this into the window instead? @WilliamD I am driving off of a view that uses several tables in that view (Preferred_Mail_View) Do you want me to send the table creation scripts for all the tables that feed into that view as well? My expected outcome is over 30,000 records, would a small extract suffice?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
How big is the execution plan file? You can zip it and post that instead if it is too big (512kb is the limit)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
The view definition scripts may be useful as well as the base tables - they may be referenced in the execution plan.
0 Likes 0 ·
Show more comments

1 Answer

·
JohnM avatar image
JohnM answered
Just a couple of thoughts and keep in mind that I'm not a tuning expert by any means. Can you narrow down the results coming from '`CONTRIB_DONOR_DESIG`'? You are currently doing two inner joins to it and with it being currently 332 million rows, without the where clause for that specific table you're bring the whole thing back. Twice. The two hash joins for these joins eat 80% (80.6)of your total query cost. Given this, 10 minutes probably isn't too far off the mark in my opinion. The execution plan suggested two nonclustered indexes for that the same table. They might help you, however keep in mind that those suggested missing indexes are based on various numbers since the last time SQL Server was restarted. So, if you restarted your server yesterday, those suggestions might be worthless, but nevertheless something to at least investigate. In the other tables, just about everything is an index scan versus an index seek. In looking at the query, you might be able to adjust those by modifying their respective where clauses. Given the large cost of the hash joins, I would bet you'd see an improvement in performance by focusing on that issue first versus the small indexes. Hope this helps!!
2 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.

JohnM avatar image JohnM commented ·
Also, just was thinking that you also might want to look at your indexes for fragmentation and also check to see how old the statistics are on the tables. Either one of those two issues might also help to improve the performance.
0 Likes 0 ·
muk avatar image muk commented ·
Thanks so much John I tweeked the statistics and that seemed to do the trick!!!!
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.