June 17, 2008 at 11:36 am
Hi,
My sp runs 2 min+ but the Query Anayzer finishes it in 7 sec. it's a monthly sales forecast report with actual data from another table substituted for past months. This is the sp.
CREATE PROCEDURE usp_Sales_Forecast
(
@vp varchar(25),
@SalesType char(15),
@Year as char(4),
@submission varchar(25),
@Grouping varchar(25)
)
AS
SET NOCOUNT ON
Select CASE WHEN @Grouping = 'Naspid' THEN a.Naspid
WHEN @Grouping = 'Director' THEN a.Director
WHEN @Grouping = 'Branch' THEN a.Branch
WHEN @Grouping = 'Branchname' THEN a.Branchname ELSE a.VP END [Grouping],
SUM(CASE WHEN Datediff(dd, '1/31/' + @Year ,Getdate()) < 10 THEN a.Jan ELSE isnull(b.Jan,0) END) AS Jan,
SUM(CASE WHEN Datediff(dd, '2/28/' + @Year ,Getdate()) < 10 THEN a.Feb ELSE isnull(b.Feb,0) END) AS Feb,
SUM(CASE WHEN Datediff(dd, '3/31/' + @Year ,Getdate()) < 10 THEN a.Mar ELSE isnull(b.Mar,0) END) AS Mar,
SUM(CASE WHEN Datediff(dd, '4/30/' + @Year ,Getdate()) < 10 THEN a.Apr ELSE isnull(b.Apr,0) END) AS Apr,
SUM(CASE WHEN Datediff(dd, '5/31/' + @Year ,Getdate()) < 10 THEN a.May ELSE isnull(b.May,0) END) AS May,
SUM(CASE WHEN Datediff(dd, '6/30/' + @Year ,Getdate()) < 10 THEN a.Jun ELSE isnull(b.Jun,0) END) AS Jun,
SUM(CASE WHEN Datediff(dd, '7/31/' + @Year ,Getdate()) < 10 THEN a.Jul ELSE isnull(b.Jul,0) END) AS Jul,
SUM(CASE WHEN Datediff(dd, '8/31/' + @Year ,Getdate()) < 10 THEN a.Aug ELSE isnull(b.Aug,0) END) AS Aug,
SUM(CASE WHEN Datediff(dd, '9/30/' + @Year ,Getdate()) < 10 THEN a.Sep ELSE isnull(b.Sep,0) END) AS Sep,
SUM(CASE WHEN Datediff(dd,'10/31/' + @Year ,Getdate()) < 10 THEN a.Oct ELSE isnull(b.Oct,0) END) AS Oct,
SUM(CASE WHEN Datediff(dd,'11/30/' + @Year ,Getdate()) < 10 THEN a.Nov ELSE isnull(b.Nov,0) END) AS Nov,
SUM(CASE WHEN Datediff(dd,'12/31/' + @Year ,Getdate()) < 10 THEN a.Dec ELSE isnull(b.Dec,0) END) AS Dec
From
(Select * From vSales
Where Submission = @submission
And VP=@VP And SalesType = @SalesType) a
Left Join
vSPA_Naspid b
On a.Naspid=b.naspid
And b.SalesType = @SalesType
And b.Year=Cast(@Year as int)
And b.VP=@VP
GROUP BY CASE WHEN @Grouping = 'Naspid' THEN a.Naspid
WHEN @Grouping = 'Director' THEN a.Director
WHEN @Grouping = 'Branch' THEN a.Branch
WHEN @Grouping = 'Branchname' THEN a.Branchname ELSE a.VP END
GO
======
This takes 7 mins. But when I declare and set the parameters,
Declare @vp varchar(25)
Declare @Year as char(4)
Declare @submission varchar(25)
Declare @SalesType varchar(5)
Declare @Grouping varchar(25)
Set @vp='CHESTER'
Set @year='2008'
Set @submission ='WK24 2008'
Set @SalesType='MRV'
Set @Grouping = 'Director'
and run it through the Analyzer it only takes 7 sec. I was expecting the sp to run at least as fast. Are there problems with the way I set it.
Thanks,
Wendell
June 17, 2008 at 12:09 pm
Without looking at the execution plans, I would have to guess that you get very different plans depending on the parameters you pass in. Since stored procedures cache execution plans, the first time you run the procedure will determine the plan it is going to use and subsequent runs with different parameters will probably perform very poorly.
You could test this by adding the RECOMPILE keyword to the procedure. If the performance is ok this way, it could be an option to leave it like this. However, you may want to re-write it in a way that it can cache plans appropriately - especially if the procedure is run repeatedly.
June 17, 2008 at 1:19 pm
Thanks for the quick response. The sp ran about the same using the WITH RECOMPILE option. I compared the plans and found that the Table scan was 97% of the cost through both the sp and Query Analyzer. But, the row count was 245, 000 thru the QA and over 24,000,000 for the sp. Both were run using the same parameters. Can you think of any reason why they should execute so differently?
Wendell
June 17, 2008 at 1:23 pm
It could also be parameter sniffing. One thing I have done that seemed to help some of my stored procedures is to declare local variables inside the stored procedure that I then set to the values passed in through the stored procedures parameters.
Also, but perhaps not as important, I would explicitly define the columns returned in the derived table from the view vSales instead of using the *. You should only return the columns you need, not all the columns. It also will help with readability of the code.
😎
June 17, 2008 at 1:28 pm
If you can, clear your procedure cache. In the procedure, add an OPTIMIZE FOR and specify the variable values you are using when running through Management Studio. The procedure may be picking up bad statistics or optimizing in a fashion that it expects variable differences. You are right at the problem, you will need to keep picking away a bit to get the optimizer to pick a good plan.
I suspect the overall issue is your conditional grouping. Just looking at it it feels like a rather bad idea. I can understand the optimizer having trouble with something like that - it needs to do a completely different sort and group operation depending on the result of your CASE statement. You really have a fixed number of grouping possibilities. It will be more source code to manage, but you may want to break the procedure into one sub stored procedure per grouping possibility and call the appropriate sub procedure based on the variables.
June 17, 2008 at 1:42 pm
You should definitely look at parameter sniffing like has been suggested. The other suggestions are also excellent for overall tuning, but parameter sniffing helps explain why the SP makes bad choices and the straight query doesn't....
----------------------------------------------------------------------------------
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?
June 17, 2008 at 1:46 pm
Sorry, should have looked at the plan closer. The QA query executed once and the sp executed 101 times. A clustered index seek and scan executed 101 times for both. But the row count of the clusterd index scan of the sp was 10201 while 101 for the QA query.
Wendell
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply