Post reply

Help with SQL query execution plan

  • November 1, 2010 at 9:12 am

    #240533

    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

    #1244884

    Please post table definitions, index definitions and execution plan as per http://qa.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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply