question

[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