November 1, 2010 at 9:12 am
I have a SQL statement that fetches data about absences in a date range to display in a chart.
In most cases the SQL does not take too long to run (approx 1 second - not ideal but for now I'm ok with that).
But for one particular date range the statement takes over 6 mins
This code takes around 1 second to return 38 rows
select absencepersonid,absencedetailstart,absencedetailhours
from absences join absencedetail on absenceid=absencedetailabsenceid
where absencepersonid in (1,2,3,4)
and absencedetailstart>='08/Sep/2010'
and absencedetailstart<'06/Oct/2010'
and absenceCodeID in (select AbsCodeID from AbsCodes where AbsCodeChartCode)
and absencedetailabsenceid not in (select AbsenceID from processedabsences)
Change the dates to '15/Sep/2010' and '13/Oct/2010' and it takes over 6 mins to return 23 rows.
If I run both queries through SSMS and show execution plans each query is using a completely different plan. And interestingly the first query takes 58% of total execution cost relative to batch???
Any help with helping me understand what is causing this huge difference (and the anomalous execution cost percentages) would be greatly appreciated.
Thanks
Stuart
November 1, 2010 at 9:27 am
Please post table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Those execution costs are estimates. There are lots of things that would make them wrong, bad statistics being one of the primary suspects.
After saving the execution plans, try an UPDATE STATISTICS <tablename> WITH FULLSCAN on both tables involved and see if it fixes things.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply