Stored Procedures 1

[JoshBN]

I have a stored procedure that when I run it for the first each day, it takes 2 minutes to finish executing. After my first run each day of the stored procedure, it only takes around 5-6 seconds to run it. Does anyone have any ideas why this is or what I can do to speed up my first run of the day? Thank you for any help.

 

[bperry]

I can think of two possible reasons:

(1) Most likely: After the first run , the data pages that the query needs to read are already cached in memory, so that in subsequent runs it can read the data directly from memory without going back to the disk. (On a busy server with not enough memory, during the day those data pages might get flushed from cache, but i guess that's not happening on your system.)

(2) Less important: After the first run, the execution plan for the SP is already in memory, and SQL doesn't have to re-develop the plan for subsequent runs (i.e. it will already know how to carry out the query). But normally, query plans don't take so long to prepare that it would account for the difference you are seeing.

So, there's probably not much to do re: speeding up the first run. If it makes sense, you could perhaps have the SP automatically run when the system starts, or maybe have a scheduled job run the jos early before the users get at it. But often that's not really practical: it is very situation-specific.

If anyone else can shed more light on this, they'll probably post their comments here.

bperry

 

[JoshBN]

Thanks for the help. This makes sense. I added some more indexes to my tables and now the stored Procedure is much faster the first run. Thanks again.