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.
The below query is part of my stored procedure.. i have not pasted the full of the query which have more left joins and columns for each respective join table..
If Object_Id ('CAPRIS_Application_DB.DWH.DimProjectHeader') is not null
Drop table [dwh].[DimProjectHeader]
SELECT skm.[SID],
skm.[SalesProjectHeaderGUID]
,COALESCE(ph.[TrainingType], '') as [TrainingType]
,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)