Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

queries running faster after cached in the query plan Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 3:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 280, Visits: 560
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
Post #1356621
Posted Monday, September 10, 2012 4:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:19 PM
Points: 3,694, Visits: 4,828
When a query is run for the first time, the plan needs to be generated and cached, taking a bit of time.
Thereafter any execution will use the existing cache, provided it has not been flushed. Thus the next execution will be faster than the first, but roughly the same for each execution thereafter


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1356630
Posted Monday, September 10, 2012 4:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 280, Visits: 560
thanks for clearing that up :)
Post #1356632
Posted Monday, September 10, 2012 4:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 41,570, Visits: 34,495
The latter, assuming that the query continues to process the same number of rows each time.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1356634
Posted Monday, September 10, 2012 4:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 280, Visits: 560
that is unlikely

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

Post #1356638
Posted Monday, September 10, 2012 4:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 280, Visits: 560
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?
Post #1356642
Posted Monday, September 10, 2012 4:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 41,570, Visits: 34,495
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 2008, MVP
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

Post #1356644
Posted Monday, September 10, 2012 4:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 1,207, Visits: 9,351
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.
Post #1356645
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse