DesNorton - Tuesday, August 22, 2017 3:09 AM

Shanmuga Raj - Tuesday, August 22, 2017 2:14 AM

I need help to Query optimization.
Query is part of StoredProcedure which executes for more than one hour.
I need to optimize the query in such a way the action is done faster.
StoredProcedure does the below action.
1. Drop Table
2. Create Table with data from various joins using "Into Table"
3.Alter Table Add CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY

I am mentioning the steps followed since the query will have lot of columns and tables..Below are the steps followed with few columns and joins used. 

  If Object_Id ('CAPRIS_Application_DB.DWH.DimProjectHeader') is not null
Drop table [dwh].[DimProjectHeader]

SELECT skm.[SID],
     skm.[SalesProjectHeaderGUID]
     ,COALESCE(sph.LongDescription,sph.MediumDescription,sph.ShortDescription,spho.LongDescription
     ,spho.LongDescription,spho.MediumDescription,spho.ShortDescription,N'#N/A') AS SalesProjectHeaderGUIDTextEN
  ,COALESCE(ph.[TrainingType], '') as [TrainingType]
     ,COALESCE(tt.ShortDescription,N'#N/A') AS TrainingTypeTextEN
  ,COALESCE(ph.[LostToCompetitor], '') as [LostToCompetitor]
     ,COALESCE(CASE WHEN ph.salesstagesince > 0 then
             CONVERT(varchar(10),CONVERT (datetime,convert(char(8),ph.salesstagesince)) , 104)
        ELSE
             CAST(ph.salesstagesince as char(1))-- '0'
        END ,'') as SalesStageSince
     ,ph.[_AuditModifiedDate]
     
into [dwh].[DimProjectHeader]

FROM [cdw].[DimProjectHeaderSKMap] SKM
left join [cdw].[DimProjectHeader] PH on skm.sid = ph.sid 
LEFT OUTER JOIN [cdw].[RefOEMText] om ON om.OEM=ph.[OEMLevel1]
AND om.textID =304
LEFT OUTER JOIN [cdw].[RefDomainValueText] rfp ON rfp.LowerValue=ph.[ReasonforProductProzessUpgrade]
    and rfp.Domain= 'ZADTEL00004Z'
    and rfp.LanguageKey='E'
LEFT OUTER JOIN (SELECT DISTINCT SalesProjectHeaderGUID,SalesProjectHeaderNumber FROM cdw.DimProjectHeader) refph
    ON ph.ReferenceNumber = refph.SalesProjectHeaderNumber
     

GO

ALTER TABLE [dwh].[DimProjectHeader]
ADD CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY ([SID] ASC)


This query does not appear to be complete

• Your field [SalesProjectHeaderGUIDTextEN] references aliases [sph] and [spho] which do not exist in the query
• Your field [TrainingTypeTextEN] references alias [tt] which does not exist in the query

From a performance perspective, you have 2 LEFT JOINs which do not get used anywhere in the query.  They are just wasting resources while they go on a wild goose chase for data that you don't need/want.