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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Stored Procedures 1
- Thread starter JoshBN
- Start date
-
1
- #2
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
(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
- Thread starter
- #3
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question