September 7, 2005 at 6:43 pm
Hi,
I understand that the first time we execute a stored procedure, an execution plan is generated and cached, and this execution plan is then used for future requests.
Two arguments of the stored procedure I have are @StartDate and @EndDate. Currently, the maximum difference between @StartDate and @EndDate is 1 day. After I create the stored procedure, if I first run with parameters @StartDate = '7/1/05 00:00' and @EndDate = '7/1/05 23:59' in Query Analyzer, this executes in 2 seconds. The execution plan for this gets cached and now if I call the stored proc with @StartDate = '7/1/05 06:00' and @EndDate = '7/1/05 07:59', it takes only about 1 second.
But if I do the opposite, i.e. re-create the stored procedure and run the 2 hour query first, in Query Analyzer, it generates a bad execution plan and the 2 hour query takes 9 seconds to execute. Now if I run the full day query with this execution plan, it takes more than a minute to complete.
Is there some way in which I can force the first execution plan to be used even when the 2 hour query is called first?
Bala.
September 7, 2005 at 10:14 pm
Create a local copy of all the parameters used in the query, copy the parameters' value to the local variables and use those variables in the query.
You can search for parameter sniffing on this site for more info on the subject.
September 8, 2005 at 7:24 am
If the value of query parameters are in big ranges, you can force the SP to recompile each time when it's called.
CREATE PROCEDURE usp_xxx
WITH RECOMPLIE
AS
...
Though recompile is said "expensive", actually it just takes milliseconds. You can always get the best plan for each query.
September 8, 2005 at 7:34 am
Actually it's "WITH RECOMPILE"
September 8, 2005 at 2:23 pm
The slowness WAS due to Parameter Sniffing. I tried some of the techniques to overcome that and it solved the problem. Thanks for your suggestions.
September 8, 2005 at 2:28 pm
Which one solved your problem?
September 8, 2005 at 5:17 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply