September 1, 2015 at 2:26 pm
Hi. We recently upgraded our ERP and went from using SQL Server 2008 to 2014. In addition, we upgraded our hardware (faster, better everything). Suddenly all of the queries we have embedded in various .NET programs are running much, much slower. The query below went from 2 seconds to 5 minutes and 27 seconds. On top of that, I am a total newb, know very little about this stuff, inherited all of these queries and am now responsible for speeding things up.
Does anybody have any idea what might have happened to slow things down so much? Can anybody give me pointers (at the most basic level) on how to rewrite queries like the one below to make them faster? Many thanks in advance!
Declare @BuyerName as nvarchar(50);
Declare @StartDate as date;
Declare @EndDate as date;
Set @BuyerName = '' + '%';
Set @StartDate = '01/01/2014';
Set @EndDate = '12/31/2014';
select
V.VendorID
,V.Name
,PA.Name as Buyer
,(RD.OurQty*RD.OurUnitCost) as RelValue
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateProm
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateDue
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END )as NumOfLateProm
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END ) as NumOfLateDue
,(RD.FailedQty*RD.OurUnitCost*(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END)) as RejectValue
,(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END )AS InspectionFailed
from
RcvDtl RD
join PORel POR on RD.PONum = POR.PONum and RD.POLine = POR.POLine and RD.PORelNum = POR.PORelNum
join Vendor V on RD.VendorNum = V.VendorNum
join POHeader POH on POR.PONum = POH.PONum
join Erp.PurAgent PA on PA.BuyerID = POH.BuyerID
left outer join PartTran PT on PT.PONum = RD.PONum and PT.POLine = RD.POLine and PT.PORelNum = RD.PORelNum and PT.PackSlip = RD.PackSlip and PT.PackLine = RD.PackLine and PT.TranType = 'INS-DMR'
left outer join Ice.UD14 on UD14.Key1 = PT.DMRNum and UD14.Key5 = '1'
where
dbo.AGM_isdatenull(RD.ReceiptDate) = 0 and dbo.AGM_isdatenull(POR.DueDate) = 0 and cast(RD.ReceiptDate as date) <> cast( GETDATE() as date)
and RD.InspectionPending = 0 and RD.ReceiptDate >= @StartDate and RD.ReceiptDate <= @EndDate
and 1 = CASE WHEN @BuyerName = '%' THEN 1 ELSE CASE WHEN PA.name like @buyername THEN 1 ELSE 0 END END
September 1, 2015 at 2:41 pm
bwhiteside (9/1/2015)
Hi. We recently upgraded our ERP and went from using SQL Server 2008 to 2014. In addition, we upgraded our hardware (faster, better everything). Suddenly all of the queries we have embedded in various .NET programs are running much, much slower. The query below went from 2 seconds to 5 minutes and 27 seconds. On top of that, I am a total newb, know very little about this stuff, inherited all of these queries and am now responsible for speeding things up.Does anybody have any idea what might have happened to slow things down so much? Can anybody give me pointers (at the most basic level) on how to rewrite queries like the one below to make them faster? Many thanks in advance!
Declare @BuyerName as nvarchar(50);
Declare @StartDate as date;
Declare @EndDate as date;
Set @BuyerName = '' + '%';
Set @StartDate = '01/01/2014';
Set @EndDate = '12/31/2014';
select
V.VendorID
,V.Name
,PA.Name as Buyer
,(RD.OurQty*RD.OurUnitCost) as RelValue
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateProm
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateDue
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END )as NumOfLateProm
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END ) as NumOfLateDue
,(RD.FailedQty*RD.OurUnitCost*(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END)) as RejectValue
,(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END )AS InspectionFailed
from
RcvDtl RD
join PORel POR on RD.PONum = POR.PONum and RD.POLine = POR.POLine and RD.PORelNum = POR.PORelNum
join Vendor V on RD.VendorNum = V.VendorNum
join POHeader POH on POR.PONum = POH.PONum
join Erp.PurAgent PA on PA.BuyerID = POH.BuyerID
left outer join PartTran PT on PT.PONum = RD.PONum and PT.POLine = RD.POLine and PT.PORelNum = RD.PORelNum and PT.PackSlip = RD.PackSlip and PT.PackLine = RD.PackLine and PT.TranType = 'INS-DMR'
left outer join Ice.UD14 on UD14.Key1 = PT.DMRNum and UD14.Key5 = '1'
where
dbo.AGM_isdatenull(RD.ReceiptDate) = 0 and dbo.AGM_isdatenull(POR.DueDate) = 0 and cast(RD.ReceiptDate as date) <> cast( GETDATE() as date)
and RD.InspectionPending = 0 and RD.ReceiptDate >= @StartDate and RD.ReceiptDate <= @EndDate
and 1 = CASE WHEN @BuyerName = '%' THEN 1 ELSE CASE WHEN PA.name like @buyername THEN 1 ELSE 0 END END
First of all, did you update your statistics after upgrading?
The query itself has a number of low hanging fruits to improve performance. You have a scalar function (dbo.AGM_isdatenull) in the where clause twice. It is also called sporadically throughout this entire query. What does that scalar function do and can you remove it? Scalar functions are notoriously poor performers.
I suspect that the number of selects could be greatly reduced. When there are subqueries as a column value there is a good indication that some improvements can be made here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 2, 2015 at 5:26 am
Assuming you changed the compatibility setting on your database so that it's now at 120, you're using the new cardinality estimator that comes with SQL Server 2014.
Queries that were marginal in prior versions of SQL Server, and as was pointed out above, there are a lot of tuning opportunities in that query, can react quite badly to the new cardinality estimator (which, is actually much better than the old one). Your best bet, tune the queries. Second best bet, identify the queries that are having problems and use traceflags to put their behavior back to the old cardinality estimator. Last resort, change the compatibility level of your database so that it doesn't use the new cardinality estimator.
Read more about the estimator, get the trace flags.
I'd tune the queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2015 at 11:10 am
Hi,
We've been discussing this in a different area of the forum and the issue has been resolved so I wanted to come post the solution here in case anyone stumbles upon this thread and has a similar problem.
The trick is to try to putting limiting conditionals in the joins rather than the where clause when possible. In doing so I got this query, which was taking more than five minutes to run, down to zero seconds. To confirm that the result set is the same for both queries, I took the results from both, put them each in their own Excel worksheet, compared the worksheets and they were exactly the same. Its amazing that both queries (zero seconds vs. 5 minutes & 22 seconds) return the exact same dataset, but one takes sooooo much longer. Just goes to show how important it is to write your queries properly.
Here is the new query if anyone wants to compare the two:
Declare @BuyerName as nvarchar(50);
Declare @StartDate as date;
Declare @EndDate as date;
Set @BuyerName = '' + '%';
Set @StartDate = '01/01/2014';
Set @EndDate = '12/31/2014';
select
T.VendorID
,T.Name
,COUNT(T.name) as NumofRel
,SUM(T.relvalue) as RelValue
,cast(cast((COUNT(T.name) -SUM(T.NumOfLateDue))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as OnTimeScoreD
,cast(cast((COUNT(T.name) -SUM(T.NumOfLateProm))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as OnTimeScoreP
,SUM(T.NumOfLateDue) as NumofLateDue
,SUM(T.NumOfLateProm) as NumofLateProm
,AVG(T.DaysLateDue) as AvgDaysLateDue
,AVG(T.DaysLateProm) as AvgDaysLateProm
,cast(cast((COUNT(T.name) -SUM(T.InspectionFailed))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as QualityScore
,sum(T.InspectionFailed) as NumOfRejects
,SUM(T.RejectValue) as RejectValue
from (
select
V.VendorID
,V.Name
,PA.Name as Buyer
,(RD.OurQty*RD.OurUnitCost) as RelValue
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateProm
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateDue
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END )as NumOfLateProm
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END ) as NumOfLateDue
,(RD.FailedQty*RD.OurUnitCost*(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END)) as RejectValue
,(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END )AS InspectionFailed
from
Vendor V
join RcvDtl RD on RD.VendorNum = V.VendorNum and RD.ReceiptDate is not null and cast(RD.ReceiptDate as date) <> cast( GETDATE() as date) and RD.InspectionPending = 0 and RD.ReceiptDate >= @StartDate and RD.ReceiptDate <= @EndDate
join PORel POR on RD.PONum = POR.PONum and RD.POLine = POR.POLine and RD.PORelNum = POR.PORelNum and POR.DueDate is not null
join POHeader POH on POR.PONum = POH.PONum
join Erp.PurAgent PA on PA.BuyerID = POH.BuyerID
left outer join PartTran PT on PT.PONum = RD.PONum and PT.POLine = RD.POLine and PT.PORelNum = RD.PORelNum and PT.PackSlip = RD.PackSlip and PT.PackLine = RD.PackLine and PT.TranType = 'INS-DMR'
left outer join Ice.UD14 on UD14.Key1 = PT.DMRNum and UD14.Key5 = '1'
where 1 = CASE WHEN @BuyerName = '%' THEN 1 ELSE CASE WHEN PA.name like @buyername THEN 1 ELSE 0 END END
) as T
group by T.VendorID, T.Name
order by T.VendorID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply