August 22, 2017 at 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)
August 22, 2017 at 2:33 am
What is your question? Remember we can't see your screen and we don't know anything about your data. We'll need the actual execution plan as a minimum here to help you, please.
John
August 22, 2017 at 2:49 am
John Mitchell-245523 - Tuesday, August 22, 2017 2:33 AMWhat is your question? Remember we can't see your screen and we don't know anything about your data. We'll need the actual execution plan as a minimum here to help you, please.John
hi John,
The above 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
August 22, 2017 at 3:09 am
Shanmuga Raj - Tuesday, August 22, 2017 2:14 AMI 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 KEYI 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
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.
August 22, 2017 at 3:17 am
DesNorton - Tuesday, August 22, 2017 3:09 AMShanmuga Raj - Tuesday, August 22, 2017 2:14 AMI 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 KEYI 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.
The query is part of my stored procedure.. i have not pasted the part of the query.. i just want to know how to optimize the query
August 22, 2017 at 4:42 am
Shanmuga Raj - Tuesday, August 22, 2017 3:17 AMThe query is part of my stored procedure.. i have not pasted the part of the query.. i just want to know how to optimize the query
We can't help optimise a query we can't see the data for, or run ourselves. At the very least, like John mentioned, a execution plan is needed. The easiest way to obtain this and upload it would be to do the following:
You may want to obfuscate your query plan, if it contains personal/sensitive information.
Also, you duplicated this topic in the 2012 forum. Which version of SQL Server are you using?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 4:56 am
let me rephase my query ;
i am using sql server 2012
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 5:25 am
Please post the full query, its execution plan and the definition of the tables it references and the indexes on those tables.
We can't tune a query from seeing part of its text only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2017 at 7:34 am
Also, did you do any analysis while the code was running for that 1 hour? What if someone had a lock on a table and you simply waited for THAT to complete? What if your query did a bunch of physical IOs and your IO system took 10000 SECONDS to get the IO into RAM?
Use sp_whoisactive to find the first issue and differential file IO stall analysis for the other. I would add in differential wait stats analysis too. The latter two are where you take a snapshot of those two things, wait for a while (I often use 3 minutes), take another snapshot and diff the two and divide by time. That gives you "something that could be causing a problem right now"/millisecond.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply