August 22, 2017 at 4:25 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.
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)
August 22, 2017 at 4:31 am
Duplicate of topic, where discussion already taking place: https://www.sqlservercentral.com/Forums/1892957/Query-optimization-Select-Into-Alter-Table-ADD-CONSTRAINT-PRIMARY-KEY-ID-ASC
Also, your prior topic is in the SQL 2005 forum, and this in in the 2012 forum. If you aren't using 2005, I would suggest posting in your original topic and letting users know that you are on a different version to the forum you posted in. 2005 and 2012 are very different in available functionalities.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 4:55 am
Thom A - Tuesday, August 22, 2017 4:31 AMDuplicate of topic, where discussion already taking place: https://www.sqlservercentral.com/Forums/1892957/Query-optimization-Select-Into-Alter-Table-ADD-CONSTRAINT-PRIMARY-KEY-ID-ASCAlso, your prior topic is in the SQL 2005 forum, and this in in the 2012 forum. If you aren't using 2005, I would suggest posting in your original topic and letting users know that you are on a different version to the forum you posted in. 2005 and 2012 are very different in available functionalities.
ok thanks. i will do the same
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply