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