January 15, 2008 at 9:25 am
Hello:
Interestingly enough, I haven't come across this before. I have a SQL stored procedure which takes four parameters; periodstartdate (datetime), periodenddate (end time), hsgradyearstart (int), hsgradyearend (int)
[dbo].[CalculateActivityTotal]
-- Add the parameters for the stored procedure here
@periodstartdate datetime = '2007-01-01',
@periodenddate datetime = '2007-01-08',
@hsgradyearstart int = 1900,
@hsgradyearend int = 2007
AS...
If I run the stored procedure and pass the parameters using EXEC or
sp_executesql "CalculateActivityTotal '2008-01-04 12:00:00', '2008-01-11 12:00:00', 1900, 2008"
the stored proc takes well over ten minutes to run (it does a bunch of aggregation). If I modify the stored procedure to take no parameters, however, and I hardcode the dates in the stored proc using declare and set then it runs in 13 seconds. What could be causing my problem and how I can I go about resolving this? I need to pass the parameters via reporting server. Thanks!
January 15, 2008 at 10:10 am
Try looking up "Parameter sniffing"
You might have a shot at getting it to behave better with this simple change:
create procedure [dbo].[CalculateActivityTotal] (
@periodstartdate datetime = '2007-01-01',
@periodenddate datetime = '2007-01-08',
@hsgradyearstart int = 1900,
@hsgradyearend int = 2007)
AS
Declare @MyLocalStartDate datetime
declare @MyLocalEnddate datetime
declare @MyLocalYearStart int
declare @MyLocalYearEnd int
--set the local var's to the passed params
select @MyLocalStartDate =@periodstartdate ,
@MyLocalEnddate=@periodenddate,
@MyLocalYearStart =@hsgradyearstart,
@MyLocalYearEnd=@hsgradyearend
....
Then use the MyLocal variables in your procedure. This should help with the execution planning
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 12:33 pm
In addition to what Matt Miller is suggesting, you might verify that the data types of the parameters is the same as the data types of the columns. If you're getting an implicit conversion it might prevent the indexes from being used and you'll see table scans in the execution plan instead the seeks that you probably get when you're using the correct data types.
However, it's much more likely to be the first solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2008 at 12:58 pm
Tagging along with the two of them have already stated, you could also use PLAN Hints
OPTION (OPTIMIZE FOR (@parameter = ))
-Roy
January 15, 2008 at 2:21 pm
Thanks for the tips. By putting the local variables in, it did seem to fix the problem quite a bit. For the most part it only takes around 2 minutes to run, which still isn't as good as the 13 seconds, but is better than the 10+ minutes. I'm not sure if the optimize syntax would work because, in theory, I'm allowing a wide range of date variable to be passed in (as I thought optimize was for a most commonly used value). Please remind me, do I use profiler to view the execution plan for my sp?
January 15, 2008 at 2:23 pm
It's one of the option in Query analyzer or SSMS.
In QA - Ctrl+K
in SSMS - Ctrl-M
That will do the ACTUAL execution plan, so you then have to run the SP to get the execution plan. It will be an extra tab in the results area.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply