x

my query is taking like 10 minutes

link text

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!

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

exec.zip (25.7 kB)
more ▼

asked Jul 11, 2012 at 02:43 PM in Default

avatar image

muk
440 33 35 40

Can you post the execution plan associated with this query? That should help to narrow things down.

Jul 11, 2012 at 02:49 PM JohnM

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.

Jul 11, 2012 at 03:06 PM WilliamD

@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?

Jul 11, 2012 at 03:19 PM muk

How big is the execution plan file? You can zip it and post that instead if it is too big (512kb is the limit)

Jul 11, 2012 at 03:58 PM Kev Riley ♦♦

The view definition scripts may be useful as well as the base tables - they may be referenced in the execution plan.

Jul 11, 2012 at 04:00 PM Kev Riley ♦♦
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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!!

more ▼

answered Jul 11, 2012 at 07:23 PM

avatar image

JohnM
14.2k 3 7 14

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.

Jul 11, 2012 at 08:13 PM JohnM

Thanks so much John I tweeked the statistics and that seemed to do the trick!!!!

Jul 11, 2012 at 08:14 PM muk
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1090
x451
x75

asked: Jul 11, 2012 at 02:43 PM

Seen: 1733 times

Last Updated: Jul 11, 2012 at 08:14 PM

Copyright 2017 Redgate Software. Privacy Policy