Sargable condition slower than non-sargable...why?
-
Please help me understand this performance issue... I'm running a somewhat complex query with 5 table joins. I have experienced a dramatic performance slowdown on this query over the past 2 weeks.
Slow (16 minutes for 4500 rows) sargable query includes where condition of:
((Shifts.dtShiftDate>dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1)
Fast (3 seconds for 4500 rows) non-sargable query includes:
((Shifts.dtShiftDate Between dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1 and getdate()-1)
'Between' vs '>' is the basic difference in the two queries. Excel is generating the queries using the first method in one pivot table and the second method in another. I captured this with Profiler. I don't know how to control the behavior there, but I feel that it is something with in the db server. I say this because both queries take an equal amount of time in my test system with a copy of all the data. The production server is not under a heavy load, and has an overkill of ram with plenty free (3gb).
There has not been a dramatic increase of data, it has been a slow/steady increase in data over the past 2 years.
I have indexes on the proper fields in my tables. Profiler did not have any suggested improvements on the full query.
I have always run the basic optimizations jobs from the SQL Maintenance Plans nightly. Today I ran Update Statistics on all of the tables involved which didn't help at all. I've tried reorganizing the indexes both with and without adjusting free space.
There is probably (I am hoping) some simple easy explanation to this. If you can help me understand this please don't hesitate to throw out some ideas!
-Aaron
-
Just curious, why don't you have AND Shifts.dtShiftdate < GETDATE()-1 in the first query when you have it in the 2nd?
-
Good point David. I missed that and after trying the clause I found the query is fast when adding the seemingly trivial AND shiftdate < GETDATE. So this had nothing to do with being sargable.
This added condition adds about 15 records to the query yet it takes its execution time from 16 minutes down to 3 seconds. Any ideas on how this could be? Here is the essential psuedocode for the where clauses
Extremely Slow:
where shiftdate > today - 5 months
Fast
where shiftdate > today - 5 months AND shiftdate < today
There is no index on shiftdate. Any ideas are appreciated.
-
Hi Aaron. SQL Server is configured to use a physical pool of memory on the server, and it will allocate the majority of this memory pool to hold data pages that have been read, along with the compiled execution plans for all previously-run Transact-SQL statements. It is this dynamic pool of memory that is being referred to by the data cache. The second time the query will run faster when it was the first time. Can you run the same query (where shiftdate > today - 5 months ) twice and see how long it will take to finished.
G.B.
-
To remove the data-caching effect of the results of 1 test run influencing another test run....investigate and execute both DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.
These will ensure the proper independance of test-run timings.
-
With all do respect I don't think this has anything to do with caching. I say this because I have run the slow query back to back many times and it always takes 15-16 minutes. The fast query always takes 2-4 seconds. It is uncanny. Running the fast query before the slow one or vise-versa has no affect.
Also, my test server which has less ram but the same data and OS/patch/sql version runs fast in both queries. I'm stumped.
-
in general caching will affect test run timings...and doing as advised with the dbcc commands will be more accurate....
the advice given was to totallly eliminate it as a factor.
can you post the execution plans?....it will point to the real source of the problem....and thus maybe a solution.
-
No malice intended in my prior statement. Caching may be taking the perf of the fast query from 4 seconds down to 2 seconds and the slow from 16 minutes to 15 minutes, but there seems to be no correlation between the two queries affecting each other.
I think adding this trivial AND condition is somehow changing the execution plan, so you are absolutely right in requesting this. I just don't know how to post it effectively in the forum. Is there a way to export the cost and other info association of the execution plan without the gui form? Here are the plans using set showplan off. I'm still trying to figure out how to post the execution plans from query analyser to the forum(running it is easy on screen).
---------------------------------------------------------------------
SLOW QUERY
StmtText -------------------------------
SELECT
Shifts.dtShiftDate,
Shifts.chMachineID,
Shifts.siShiftNo,
Shifts.vchOperation,
Parts.vchPlatform,
Parts.vchOpening,
Parts.vchPlatform+' '+vchopening,
rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),
Shifts.i
(1 row(s) affected)
StmtText
-------------------------------
|--Compute Scalar(DEFINE
[Expr1043]=[Parts].[vchPlatform]+' '+[Parts].[vchOpening], [Expr1044]=rtrim([Parts].[vchDescription])+' - '+rtrim(ltrim(Convert([Parts].[chPartID]))), [Expr1045]=[Shifts].[intPressCycles]*Convert([Operations].[bitIncludeInMachin|--Compute Scalar(DEFINE
[Expr1027]=isnull([Shifts].[intTimeDown], 0), [Expr1028]=isnull([Shifts].[intScrap], 0), [Expr1029]=isnull([Shifts].[intRings], 0), [Expr1032]=[Expr1032], [Expr1040]=If (Convert([Operations].[bitIncludeInMachineHrs])=1) the|--Nested Loops(Inner Join, OUTER REFERENCES
[Shifts].[chPartID], [Shifts].[dtShiftDate]) WITH PREFETCH)|--Nested Loops(Inner Join, OUTER REFERENCES
[Shifts].[chPartID], [Shifts].[chMachineID]))| |--Nested Loops(Inner Join, OUTER REFERENCES
[Shifts].[chPartID]))| | |--Sort(ORDER BY
[Shifts].[dtShiftDate] ASC, [Shifts].[chMachineID] ASC))| | | |--Nested Loops(Inner Join, OUTER REFERENCES
[Operations].[vchOperation]))| | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Operations].[PK__Operations__76969D2E] AS [Operations]))| | | |--Nested Loops(Inner Join, WHERE
IsFalseOrNull(Convert([Operations].[bitIncludeInMachineHrs])=1))OUTER REFERENCES[Shifts].[chShiftID]))| | | |--Nested Loops(Left Outer Join, OUTER REFERENCES
[Shifts].[chShiftID]))| | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES
[Shifts].[vchOperation]))| | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES
[Shifts].[chPartID], [Shifts].[dtShiftDate]) WITH PREFETCH)| | | | | | |--Hash Match(Right Outer Join, HASH
[Union1021])=([Shifts].[intShiftCoordinator]), RESIDUAL[Union1021]=[Shifts].[intShiftCoordinator]))| | | | | | | |--Sort(DISTINCT ORDER BY
[Union1018] ASC, [Union1019] ASC, [Union1020] ASC, [Union1021] ASC, [Union1022] ASC, [Union1023] ASC))| | | | | | | | |--Concatenation
| | | | | | | | |--Compute Scalar(DEFINE
[Expr1012]=rtrim([Employees].[FirstName])+' '+[Employees].[LastName], [Expr1013]=Convert([Employees].[Emp #])+replicate('*', 18-len(Convert([Employees].[Emp #| | | | | | | | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Employees].[PK_Employees]), WHEREConvert([Employees].[Terminated])1))| | | | | | | | |--Compute Scalar(DEFINE
[Expr1016]=rtrim([IntroAssociates].[FName])+' '+[IntroAssociates].[LName], [Expr1017]=Convert([IntroAssociates].[TempID])+replicate('*', 18-len(Convert([Intro| | | | | | | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[IntroAssociates].[PK_IntroAssociates]), WHERE[IntroAssociates].[Status]='Interim'))| | | | | | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Shifts].[PK__Shifts__0425A276]), WHERE([Operations].[vchOperation]=[Shifts].[vchOperation] AND Convert([Shifts].[dtShiftDate])>dateadd(month,| | | | | | |--Clustered Index Seek(OBJECT
[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616]), SEEK[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dtMonthYear]=dateadd(day, -datepar| | | | | |--Clustered Index Seek(OBJECT
[ShopFloor].[dbo].[Operations].[PK__Operations__76969D2E]), SEEK[Operations].[vchOperation]=[Shifts].[vchOperation]) ORDERED FORWARD)| | | | |--Hash Match(Cache, HASH
[Shifts].[chShiftID]), RESIDUAL[Shifts].[chShiftID]=[Shifts].[chShiftID]))| | | | |--Compute Scalar(DEFINE
[Expr1032]=If ([Expr1093]=0) then NULL else [Expr1094]))| | | | |--Stream Aggregate(DEFINE
[Expr1093]=COUNT_BIG(datediff(minute, [OperatorLogins].[dtLogin], [OperatorLogins].[dtLogout])), [Expr1094]=SUM(datediff(minute, [OperatorLogins].[dtLogin], [OperatorLogin| | | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[OperatorLogins].[PK_OperatorLogins]), WHERE[OperatorLogins].[dtLogout]NULL AND [OperatorLogins].[chShiftID]=[Shifts].[chShiftID]))| | | |--Hash Match(Cache, HASH
[Shifts].[chShiftID]), RESIDUAL[Shifts].[chShiftID]=[Shifts].[chShiftID]))| | | |--Stream Aggregate(DEFINE
[Expr1037]=MAX([OperatorLogins].[dtLogout]), [Expr1038]=MIN([OperatorLogins].[dtLogin])))| | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[OperatorLogins].[PK_OperatorLogins]), WHERE[OperatorLogins].[chShiftID]=[Shifts].[chShiftID]))| | |--Clustered Index Seek(OBJECT
[ShopFloor].[dbo].[Parts].[PK__Parts__7D78A4E7] AS [Parts]), SEEK[Parts].[chPartID]=[Shifts].[chPartID]) ORDERED FORWARD)| |--Clustered Index Seek(OBJECT
[ShopFloor].[dbo].[PartMachineAvailability].[PK_PartMachineAvailability] AS [PartMachineAvailability]), SEEK[PartMachineAvailability].[chPartID]=[Shifts].[chPartID] AND [PartMachineAvailability].[chMa|--Clustered Index Seek(OBJECT
[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616] AS [PartMetrics]), SEEK[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dtMonthYear]=[Shifts].[dtShiftDate]-Convert(datepart(day, (32 row(s) affected)
---------------------------------------------------------------------
FAST QUERY
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
Shifts.dtShiftDate,
Shifts.chMachineID,
Shifts.siShiftNo,
Shifts.vchOperation,
Parts.vchPlatform,
Parts.vchOpening,
Parts.vchPlatform+' '+vchopening,
rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),
Shifts.i
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE
[Expr1043]=[Parts].[vchPlatform]+' '+[Parts].[vchOpening], [Expr1044]=rtrim([Parts].[vchDescription])+' - '+rtrim(ltrim(Convert([Parts].[chPartID]))), [Expr1045]=[Shifts].[intPressCycles]*Convert([Operations].[bitIncludeInMachin|--Sort(ORDER BY
[Shifts].[dtShiftDate] ASC, [Shifts].[chMachineID] ASC))|--Compute Scalar(DEFINE
[Expr1027]=isnull([Shifts].[intTimeDown], 0), [Expr1028]=isnull([Shifts].[intScrap], 0), [Expr1029]=isnull([Shifts].[intRings], 0), [Expr1032]=[Expr1032], [Expr1040]=If (Convert([Operations].[bitIncludeInMachineHrs])=1|--Nested Loops(Inner Join, OUTER REFERENCES
[Shifts].[chPartID], [Shifts].[dtShiftDate]) WITH PREFETCH)|--Hash Match(Inner Join, HASH
[PartMachineAvailability].[chPartID], [PartMachineAvailability].[chMachineID])=([Shifts].[chPartID], [Shifts].[chMachineID]), RESIDUAL[PartMachineAvailability].[chPartID]=[Shifts].[chPartID] AND [Part| |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[PartMachineAvailability].[PK_PartMachineAvailability] AS [PartMachineAvailability]))| |--Hash Match(Inner Join, HASH
[Parts].[chPartID])=([Shifts].[chPartID]), RESIDUAL[Parts].[chPartID]=[Shifts].[chPartID]))| |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Parts].[PK__Parts__7D78A4E7] AS [Parts]))| |--Hash Match(Inner Join, HASH
[Operations].[vchOperation])=([Shifts].[vchOperation]), RESIDUAL[Operations].[vchOperation]=[Shifts].[vchOperation]))| |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Operations].[PK__Operations__76969D2E] AS [Operations]))| |--Nested Loops(Inner Join, WHERE
IsFalseOrNull(Convert([Operations].[bitIncludeInMachineHrs])=1))OUTER REFERENCES[Shifts].[chShiftID]))| |--Hash Match(Right Outer Join, HASH
[OperatorLogins].[chShiftID])=([Shifts].[chShiftID]), RESIDUAL[OperatorLogins].[chShiftID]=[Shifts].[chShiftID]))| | |--Compute Scalar(DEFINE
[Expr1032]=If ([Expr1095]=0) then NULL else [Expr1096]))| | | |--Hash Match(Aggregate, HASH
[OperatorLogins].[chShiftID]), RESIDUAL[OperatorLogins].[chShiftID]=[OperatorLogins].[chShiftID]) DEFINE[Expr1095]=COUNT_BIG(datediff(minute, [OperatorLogins].[dtLogin],| | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[OperatorLogins].[PK_OperatorLogins]), WHERE[OperatorLogins].[dtLogout]NULL))| | |--Hash Match(Right Outer Join, HASH
[Operations].[vchOperation])=([Shifts].[vchOperation]), RESIDUAL[Operations].[vchOperation]=[Shifts].[vchOperation]))| | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Operations].[PK__Operations__76969D2E]))| | |--Hash Match(Right Outer Join, HASH
[PartMetrics].[chPartID], [PartMetrics].[dtMonthYear])=([Shifts].[chPartID], [Expr1093]), RESIDUAL[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dt| | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616]))| | |--Compute Scalar(DEFINE
[Expr1093]=dateadd(day, -datepart(day, Convert([Shifts].[dtShiftDate]))+1, [Shifts].[dtShiftDate])))| | |--Hash Match(Right Outer Join, HASH
[Union1021])=([Shifts].[intShiftCoordinator]), RESIDUAL[Union1021]=[Shifts].[intShiftCoordinator]))| | |--Sort(DISTINCT ORDER BY
[Union1018] ASC, [Union1019] ASC, [Union1020] ASC, [Union1021] ASC, [Union1022] ASC, [Union1023] ASC))| | | |--Concatenation
| | | |--Compute Scalar(DEFINE
[Expr1012]=rtrim([Employees].[FirstName])+' '+[Employees].[LastName], [Expr1013]=Convert([Employees].[Emp #])+replicate('*', 18-len(Convert([Employees].[| | | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Employees].[PK_Employees]), WHEREConvert([Employees].[Terminated])1))| | | |--Compute Scalar(DEFINE
[Expr1016]=rtrim([IntroAssociates].[FName])+' '+[IntroAssociates].[LName], [Expr1017]=Convert([IntroAssociates].[TempID])+replicate('*', 18-len(Convert([| | | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[IntroAssociates].[PK_IntroAssociates]), WHERE[IntroAssociates].[Status]='Interim'))| | |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[Shifts].[PK__Shifts__0425A276]), WHERE(Convert([Shifts].[dtShiftDate])>dateadd(month, -5, getdate()-Convert(datepart(day, getdate()))+'J| |--Hash Match(Cache, HASH
[Shifts].[chShiftID]), RESIDUAL[Shifts].[chShiftID]=[Shifts].[chShiftID]))| |--Stream Aggregate(DEFINE
[Expr1037]=MAX([OperatorLogins].[dtLogout]), [Expr1038]=MIN([OperatorLogins].[dtLogin])))| |--Index Spool(SEEK
[OperatorLogins].[chShiftID]=[Shifts].[chShiftID]))| |--Clustered Index Scan(OBJECT
[ShopFloor].[dbo].[OperatorLogins].[PK_OperatorLogins]))|--Clustered Index Seek(OBJECT
[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616] AS [PartMetrics]), SEEK[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dtMonthYear]=[Shifts].[dtShiftDate]-Convert(datepart((33 row(s) affected)
-
How fitting to have sad faces all over my post, thats how I feel. Is there a way to shut that behavior off? 🙂
-
Also, any clues on why execution would be faster on my test box? Utilization is low on both boxes (prd is dual proc with 3gb ram and 10% avg utilization).
The 16 minute query runs in 11 seconds on my test box.
-
you can compare test + live setups...using s/w like sqlcompare from redgate.com.....and/or db/ghost....
that will deal with any 'inside sql' differences....other differences could be at disk/network level.
also index hints....might work to force the slow query to be a fast query.
re the query plans...can you identify the differences in the plans? i'm too busy (unusually
) to work my way through that much detail -
I haven't been able to gleen good information out of the execution plan because I don't understand it. When I see the costs in the gui plan I can understand that.
The execution plan of the slow running query showed 70 percent was taken up by 3 of the clustered index scans on 2 joined tables (out of 5 total). another 20 % was taken by a clustered index seek. The rest were small 0%, 1% or 2% costs.
I'm trying the demo from RedGate, thanks so much Andrew for your time.
-A
-
lateral thinking here...
why not simplify the "dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1)" construct to a pre-determined variable....and at least take the inclusion of the formula "dateadd" out of the performance equation.
-
That is a very good idea. What I'm haven't told you is that the slow query was generated from an Excel pivot table via odbc. My user knows that the query is fast on my test box and slow on my prod (using same data sets). I've given him the modified query to resolve the speed issue temporarily, but resolving the difference between the servers has to be my end solution. I'm hoping this RedGate you suggested can help me find out what is different.
I was thinking of running a dif on the two text execution plans but I don't know if I would understand that. That will be my next approach after RedGate. The execution plans on test and production should be identical, if not then I'm on to something. If they are identical then I will go crazy.
-
your problem was more interesting than my work.....
I can tell you the exec-plans are different....print them out and compare line by line....you'll see they diverge quite fast.
i'd be interested to see if the exec-plans (of both queries) from the test box...(which are always fast???)...differ in any way....either between themselves...or compared to the prod versions.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply