Post reply

queries running faster after cached in the query plan

  • September 10, 2012 at 3:53 am

    #393143

    Hi

    Probly a quick on to answer

    Is it the more times you run a query the faster it gets? or is the query plan cached once after the first run and so the query speed should be fairly consitent from the 2nd run onwards?

    this probably could have been worded better.

    Thanks in advance

  • September 10, 2012 at 4:18 am

    This was removed by the editor as SPAM

  • September 10, 2012 at 4:29 am

    #1535597

    thanks for clearing that up 🙂

  • September 10, 2012 at 4:31 am

    #1535599

    The latter, assuming that the query continues to process the same number of rows each time.

    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

  • September 10, 2012 at 4:36 am

    #1535602

    that is unlikely

    rows are added continually through out the day to several of the tables involved in the query in question

  • September 10, 2012 at 4:39 am

    #1535605

    just to clarify

    will the query plan only be cached when the data in the query is consitent? so each time the data changes the query needs to recache?

  • September 10, 2012 at 4:40 am

    #1535607

    No. Only when the data changes are sufficient to trigger a statistics update, as stats updates invalidate cached plans.

    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

  • September 10, 2012 at 4:40 am

    #1535608

    It depends. You save on compile time after the first run (which is relatively small for most queries), but the majority of variation in CPU/IO time is more likely to be due to whether the pages to satisfy the query are in the buffer pool or not, which will depend on the amount of memory, the frequency that query is executed, other workloads on the system and the runtime parameters.

    Elapsed time can obviously be much more variable based on other load on the server, concurrency,locking etc.

Viewing 8 posts - 1 through 7 (of 7 total)

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