Stored Procedure(s) slow on initial execution

Question

Group, I am still learning SQL, and I am running into a new problem. I have a couple stored procedures that are slow on their initial execution/connection. For example, I have a stored procedure (sp) that I use in an application to look up prices for a product. The first time I run the sp in the morning it may take 20-40 seconds to execute. After that it only takes 1-2 seconds... We also run daily updates on tables used by the sp(s) and after an update the cache is cleared and again it will take 20-40 seconds for the initial run and then 1-2 seconds after.

Is there any way around this? Not sure if I should add something to the Daily update to maybe fire my sp after the update (which could get messy) or if I can add something to my sp that tells it to not clear cache (which could cause space issues). I don't know what to do, everything works great after the initial execution...

Any suggestions are greatly appreciated.

Answer 1

The likely reason you're seeing the difference in speed is due to caching. Once you've run a SProc the execution plan goes into cache and it's much faster. What we did in our environment was to run our more used SProcs as a scheduled task around 7:30am so that they would be "warmed up" by 8am for our users when they started their workdays.

Answer 2

There are two potential reasons for this.

1. First, the first time any stored proc runs it must be compiled, and this takes time. The compiled plan is then (depending on vendor) cached so thgat subsequent executions do not have to recompile it. Even later, if it has not been executed in some time, the compiled plan in cache may have been overwritten and need to be re-compiled again.

2. Secondly, (again for most vendors), when you run any query, tjhe data pages needed to execute the query are read. But the query processor "reads" them from cache. Only if the data pages not in cache does the processor go to the disk. So, for the first time it needs the data ins ome time, it generally wil have t ogo to disk for the data, but subsequent executuions that need those same data pages will get them from in-memory cache. As disk I/O is orders of magnitude slower than RAM I/O, this can cause a very significant difference in query execution times.