Query performance before execution plan is cached, runs fine afterwards

• sam.alexander

  Ten Centuries

  Points: 1135

  More actions

  May 7, 2010 at 8:27 am

  #220769

  Hello,

  I'm running into latency on several larger queries where when there's no cached execution plan the query at first run can take minutes to run, but subsequent runs take seconds. I've been running tests and this is definitely due to the execution plan not being cached when it first runs.

  The database is SQL 2000, so I know my options are limited... but is there anyway to force the plan to stay cached? I've viewed the actual execution plans on several of these queries and they're about as efficient as can be, but the problem is the time it takes the SQL Parser to create the cached plan at first run.

  Any suggestions? Several of these queries run for many records, so one workaround is to run the query twice ... once on only one row to cache the plan then again for all rows. This works, but it creates an extra step that just seems clunky.

  Thanks for any suggestions and take care,

  Sam

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  May 7, 2010 at 8:37 am

  #1164151

  Assuming that is it just the parsing / optimization phase that is taking the time , then try giving SQLServer ALL the information you can.

  ie

  if you are joining a to b to c

  then also explicity state that a = c as well.

  You may want to reorganise the query and use forceplan, or split it into two using a temp table to stage some data.

  ---

  
  Clear Sky SQL
  My Blog[/url]