A tale of two where clauses...
-
I have two where causes that cause extreme differences of execution time in my query. My question is what are potential causes this behavior? The rows returned are the same by the two queries, there are no shift dates > today.
Extremely Slow (15 minutes):
where shiftdate > (today - 5 months)
Extremely Fast (3 seconds):
where shiftdate > (today - 5 months) AND shiftdate <= today
This is just psuedo code but you get the idea. The table has been reindexed adjusting the free space and statistics have been updated. I've tried this with and without a non-clustered index on shiftdate which causes no change in performance between the two queries.Any ideas are greatly appreciated.
-
Hi,
There could be many rows for shiftdate > today in the table. The first query will process those rows also, where as the second one filters them. So processing time is less
Harsha
HArsha -
Run them in Query analyser with the execution plan enabled, see where the difference lies
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Try this
where shiftdate > (today - 5 months )
--Jeswanth
--------------------------------
-
The problem is not this simple. Notice I have 'shiftdate <= today' in the query, that should eliminate no records.
-
As mentioned above your select is filtered more in the second clause. My thumb rule when i write sql is that i put the clause that limits my select most at the bottom of the query. SQL-92 has a rule that queries are executed from the bottom and up.
So the first query selects a time frame FROM (today-5 months) TO (anything after that)
the second query selects a time frame FROM (today-5 months) TO (less or equal today)
-
Yes, the second query is more restrictive theoretically, but why would a condition that changes no logic (pulls the same number of rows), change the query execution speed by multiple orders of magnitude? This is driving me crazy!
-
Well this is a matter of how the execution is planned on the server.
I suggest to do an analysis of that execution plan for those 2 queries.
Im guessing there are 2 different plans for those queries, since the first is (theoretical ) a bigger select than the second. But logical we see that its not an issue.
-
Aaron,
1) How many rows does this query return
where shiftdate <= today ?
2) How do you get this (today - 5 months)? Can you give us more details.
Igor
-
1) As I said at the start, the records returned by both queries are equivalent (same records, same count). The count is about 4,500 out of a table that has about 24,000. There are joins to other tables but I'm trying to narrow the problem down to its simplest part.
2) Using DateAdd(mm,-5, GetDATE()). I just thought today - 5 months is easier to understand for most people.
Thanks for your attention.
-
Can you post ALL joins and where clauses where you are using this colums (shiftdate) for both cases.
-
It seems likee you have this condition in your 'slow' query:
WHERE([Operations].[vchOperation]=[Shifts].[vchOperation] AND Convert([Shifts].[dtShiftDate])>dateadd(month, ... ,
but do not have it in your 'fast' query.
-
Absolutely!!
Slow
----------------------------------
SELECT
Shifts.dtShiftDate,
Shifts.chMachineID,
Shifts.siShiftNo,
Shifts.vchOperation,
Parts.vchPlatform,
Parts.vchOpening,
Parts.vchPlatform+' '+vchopening,
rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),
Shifts.intPressCycles,
Shifts.intPressCycles*bitIncludeInMachineHrs,
intPressCycles*bitIncludeInMachineHrs*bitIncludePressCycles,
Shifts.intTimeDown,
Shifts.intScrap, Shifts.intRings,
60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs*bitIncludePressCycles,
60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs,
60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs*bitIncludePressCycles,
60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,
ltrim(datepart(month,dtshiftdate))+' - '+rtrim(datepart(year,dtshiftdate)),
Shifts.OperatorTimeMins,
Shifts.MachineHrs*bitIncludeInMachineHrs,
(Shifts.intPressCycles*bitIncludeInMachineHrs-intRings)*(1-ftyieldgoal/100),
(intPressCycles*bitIncludeInMachineHrs-intRings)*(1-PartMetrics.ftYieldBud/100),
MachineHrs*(1-PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,
PartMetrics.ftCrewSizeBud,
PartMetrics.intCycleTimeBud,
Parts.intCycleTimeGoal,
PartMetrics.ftYieldBud,
Parts.ftYieldGoal,
PartMetrics.ftUptimeBud,
Parts.ftUptimeGoal,
Parts.ftCrewSizeGoal,
ftCrewSizeBud*MachineHrs*bitIncludeInMachineHrs,
ftCrewSizeGoal*MachineHrs*bitIncludeInMachineHrs,
MachineHrs*(1-ftUptimeGoal/100)*bitIncludeInMachineHrs,
Shifts.dtShiftDate-datepart(w,dtShiftDate-1)+1,
PartMachineAvailability.bitIncludePressCycles
FROM
ShopFloor.dbo.Operations Operations,
ShopFloor.dbo.PartMachineAvailability PartMachineAvailability,
ShopFloor.dbo.PartMetrics PartMetrics, ShopFloor.dbo.Parts Parts,
ShopFloor.dbo.vShifts Shifts
WHERE
Parts.chPartID = Shifts.chPartID AND
PartMetrics.chPartID = Parts.chPartID AND
Operations.vchOperation = Shifts.vchOperation AND
PartMachineAvailability.chMachineID = Shifts.chMachineID AND
PartMachineAvailability.chPartID = Shifts.chPartID AND
((Shifts.dtShiftDate>dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1) AND
(PartMetrics.dtMonthYear=dtshiftdate-datepart(day,dtShiftDate)+1) AND
(Shifts.vchOperation'DEVELOPMENT'))
ORDER BY Shifts.dtShiftDate, Shifts.chMachineID
====================================================
fast
-----------------------
SELECT
Shifts.dtShiftDate,
Shifts.chMachineID,
Shifts.siShiftNo,
Shifts.vchOperation,
Parts.vchPlatform,
Parts.vchOpening,
Parts.vchPlatform+' '+vchopening,
rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),
Shifts.intPressCycles,
Shifts.intPressCycles*bitIncludeInMachineHrs,
intPressCycles*bitIncludeInMachineHrs*bitIncludePressCycles,
Shifts.intTimeDown,
Shifts.intScrap, Shifts.intRings,
60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs*bitIncludePressCycles,
60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs,
60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs*bitIncludePressCycles,
60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,
ltrim(datepart(month,dtshiftdate))+' - '+rtrim(datepart(year,dtshiftdate)),
Shifts.OperatorTimeMins,
Shifts.MachineHrs*bitIncludeInMachineHrs,
(Shifts.intPressCycles*bitIncludeInMachineHrs-intRings)*(1-ftyieldgoal/100),
(intPressCycles*bitIncludeInMachineHrs-intRings)*(1-PartMetrics.ftYieldBud/100),
MachineHrs*(1-PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,
PartMetrics.ftCrewSizeBud,
PartMetrics.intCycleTimeBud,
Parts.intCycleTimeGoal,
PartMetrics.ftYieldBud,
Parts.ftYieldGoal,
PartMetrics.ftUptimeBud,
Parts.ftUptimeGoal,
Parts.ftCrewSizeGoal,
ftCrewSizeBud*MachineHrs*bitIncludeInMachineHrs,
ftCrewSizeGoal*MachineHrs*bitIncludeInMachineHrs,
MachineHrs*(1-ftUptimeGoal/100)*bitIncludeInMachineHrs,
Shifts.dtShiftDate-datepart(w,dtShiftDate-1)+1,
PartMachineAvailability.bitIncludePressCycles
FROM
ShopFloor.dbo.Operations Operations,
ShopFloor.dbo.PartMachineAvailability PartMachineAvailability,
ShopFloor.dbo.PartMetrics PartMetrics, ShopFloor.dbo.Parts Parts,
ShopFloor.dbo.vShifts Shifts
WHERE
Parts.chPartID = Shifts.chPartID AND
PartMetrics.chPartID = Parts.chPartID AND
Operations.vchOperation = Shifts.vchOperation AND
PartMachineAvailability.chMachineID = Shifts.chMachineID AND
PartMachineAvailability.chPartID = Shifts.chPartID AND
((Shifts.dtShiftDate>dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1) AND
difference is here:
-=> dtShiftDate <= GETDATE() AND <=-
(PartMetrics.dtMonthYear=dtshiftdate-datepart(day,dtShiftDate)+1) AND
(Shifts.vchOperation'DEVELOPMENT'))
ORDER BY Shifts.dtShiftDate, Shifts.chMachineID
-
Can you try to run these query with INNER JOINs :
'Slow'
SELECT ...
FROM ShopFloor.dbo.Operations Operations
INNER JOIN ShopFloor.dbo.vShifts Shifts ON Operations.vchOperation = Shifts.vchOperation AND Shifts.vchOperation<>'DEVELOPMENT'
INNER JOIN ShopFloor.dbo.Parts Parts ON Parts.chPartID = Shifts.chPartID
INNER JOIN ShopFloor.dbo.PartMetrics PartMetrics ON PartMetrics.chPartID = Shifts.chPartID AND (PartMetrics.dtMonthYear=dtshiftdate-datepart(day,dtShiftDate)+1)
INNER JOIN ShopFloor.dbo.PartMachineAvailability PartMachineAvailability
ON (PartMachineAvailability.chMachineID = Shifts.chMachineID AND PartMachineAvailability.chPartID = Shifts.chPartID)
WHERE (Shifts.dtShiftDate>dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1)
ORDER BY Shifts.dtShiftDate, Shifts.chMachineID
'Fast'
SELECT ...
FROM ShopFloor.dbo.Operations Operations
INNER JOIN ShopFloor.dbo.vShifts Shifts ON Operations.vchOperation = Shifts.vchOperation AND Shifts.vchOperation<>'DEVELOPMENT'
INNER JOIN ShopFloor.dbo.Parts Parts ON Parts.chPartID = Shifts.chPartID
INNER JOIN ShopFloor.dbo.PartMetrics PartMetrics ON PartMetrics.chPartID = Shifts.chPartID AND (PartMetrics.dtMonthYear=dtshiftdate-datepart(day,dtShiftDate)+1)
INNER JOIN ShopFloor.dbo.PartMachineAvailability PartMachineAvailability
ON (PartMachineAvailability.chMachineID = Shifts.chMachineID AND PartMachineAvailability.chPartID = Shifts.chPartID)
WHERE ( (Shifts.dtShiftDate>dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1) AND Shifts.dtShiftDate <= GETDATE() 
ORDER BY Shifts.dtShiftDate, Shifts.chMachineID
and tell us timing now.
-
The slow query becomes fast by converting the joins to INNER JOINS, and returns the same rows. Any ideas why? I feel there must be something different in my dev/prod servers to make the execution plan different between the two queries.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply