VERY slow stored procedure performance on datetime parameter

GeneTekA

A relatively straight-forward stored procedure is working fine until one day it becomes incredibly slow. We look into it assuming it to be an index related issue, but it's not. If we run the SELECT on its own it runs in 0-2 seconds. That same SELECT within a sproc is taking 1:52 consistently. I began hard-coding the parameter values into the SELECT to find out which one was causing the problem and got to the datetime parameters. If those were hard-coded performance was great, but if I used the passed parameters it took close to two minutes.

So I then declared local variables within the sproc and set the values of those variables to equal the passed parameter values. Both the parameters and the variables are being explicitly declared as datetime. When doing this the procedure runs in 0-2 seconds!!!

It would seem to be some sort of problem with interpretation of the parameters, but even if I set the passed parameters values to a cast datetime of the passed value it still won't work. It only works fast when using the local variables.

This is not the case for other sprocs within the project (of which numerous ones have datetime parameters). And it's not a problem with a cached execution plan related to the sproc either - I've been able to recreate this in a brand new procedure.

This is the 3rd time this has happened. The first time I thought it was some odd fluke. The second time was an annoyance. Now it's becoming an issue. When will the other sprocs start behaving as this one did What is causing it Obviously if this starts randomly happening with other sprocs and they are changing execution times form 0-2 to 1:50+ then we're going to have a real serious production problem!

Also, some background on the platform... the 3 different times this has happened have been in different environments - the first time was on Windows 2000 and SQL 2000, and the last two times has been on Windows 2003 and SQL 2005 (but on different servers). In other words, based on what I'm seeing this is not a server configuration issue.

Any ideas anyone

eralper

Hi,

If you use two date parameters which forms a date range, and if the first call of the SP is run with a small date range then the SP is compiled to use an execution plan which is suitable for those parameters.

And after the compilation, if you give parameters that form a huge date range, that may be slow to get the results with the compiled execution plan.

You may create the SP with recompile hint for such situations.

But this causes the SP to be recompiled everytime it is called.

Do you think this may be a reason

CREATE PROC spname

{

parameter list

}

WITH RECOMPILE

AS

.....

Eralper

http://www.kodyaz.com

Dinakar

Are you using any functions on the datecolumn in your WHERE clause Does the column have any index Posting the query helps.

Dhericean

A couple of suggestions:

Use SQL Profiler to capture the Plans for the different styles of invocation. The Showplan All and Showplan XML events are under the Performance group. This should let you see any difference in the plans between the fast and slow versions.
Try rearranging the procedure arguments to see whether it is the datetime parameters or the parameters at position 11 or whatever that are the problem. It may be that there is some strange boundary effect at the point these parameters are in the list.

Were there any changes to the procedure or the tables around the time the problem arose

Also are you saying that if you have a local variable and assign the parameter value to that and use the variable it is okay , but if you use the parameter directly then it is slow If this is the true it seems to suggest a difference between their storage/access. I'm not sure what we can do about this, though in this case you have at least found a workround with the variable.

Umachandar Jayachandran - MS

This is a plan related problem. See the link below for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

It has information on how parameter sniffing works, how plans are generated, how parameter values are used in the plans etc.

GeneTekA

eralper - I had already tried running the sproc with the recompile option each time. It does help improve performance and would, depending on the parameters, run in as little as 14 seconds. However, that's still far from the 0-2 of the normal SQL. As for the date range specified, I'm honestly not clear on how the range of dates would effect execution plan, but in this particular case it isn't relevant because the date range was only a day. Every time the query has been run the range is between 1-7 days. Thanks for the input.

Chris

GeneTekA

I was using a function on the where clause at one point but took it out in the initial troubleshooting processes expecting that this was the issue. There are indexes in place - this was another obvious area I had looked at, but if it was an index issue should it not be affecting the same SELECT statement when I'm running it outside the sproc

Thanks, Chris

GeneTekA

Thanks for the suggestions - I will try these out now.

There weren't any changes in the procedure or tables around the time this problem was identified. This is a production database so all changes are tracked closely.

I will post the procedures here in a moment so you can see the differences.

Cheers, Chris

GeneTekA

Here is the procedure that uses local variables and assigns the values based on the passed parameters.

ALTER PROCEDURE [dbo].[MySproc_LocalVar]

@StartDate DATETIME

, @EndDate DATETIME

AS

DECLARE @CompanyID int

DECLARE @SiteID int

DECLARE @StartDtm datetime

DECLARE @EndDtm datetime

SET @CompanyID = 1000

SET @SiteID = 1007

SET @StartDtm = @StartDate --'3/19/2007'

SET @EndDtm = @EndDate --'3/20/2007'

SELECT [Order].OrderID, OrderDetail.SiteID, Account.SectionID, [Section].SectionName, Account.AccountCode,

ISNULL(Account.FirstName,'') AS FirstName, ISNULL(Account.MiddleName,'') AS MiddleName, ISNULL(Account.LastName,'') AS LastName, SUM(OrderDetail.TotalPrice) AS Total

FROM [Order] INNER JOIN

OrderDetail ON [Order].OrderID = OrderDetail.OrderID INNER JOIN

OrderStatusDate ON [Order].OrderID = OrderStatusDate.OrderID INNER JOIN

Account ON [Order].ReceiverAccountID = Account.AccountID INNER JOIN

[Section] ON [Account].SectionID = [Section].SectionID INNER JOIN

Site ON Account.SiteID = Site.SiteID AND [Section].SiteID = Site.SiteID INNER JOIN

Product ON OrderDetail.ProductID = Product.ProductID INNER JOIN

ProductLanguage ON Product.ProductID = ProductLanguage.ProductID

WHERE ([Order].CompanyID = @CompanyID) AND (OrderDetail.SiteID = @SiteID) AND (OrderStatusDate.OrderStatusID = 301) AND ([Order].InsertDtm >= @StartDtm) AND ([Order].InsertDtm <= @EndDtm) AND

(Product.ProductTypeID <> 101) AND OrderStatusDate.OrderStatusDtm = (SELECT MAX(OrderStatusDtm) FROM OrderStatusDate WHERE OrderID = [Order].OrderID)

GROUP BY [Order].OrderID, OrderDetail.SiteID, Account.SectionID, [Section].SectionName, Account.AccountCode, ISNULL(Account.FirstName,''), ISNULL(Account.MiddleName,''), ISNULL(Account.LastName,'')

ORDER BY Account.SectionID, [Order].OrderID, Account.AccountCode

GeneTekA

Here is the original procedure that is intended to use the passed paramters. As you can see, I did try setting the value of the passed parameters to a cast at one point but since commented that out. Between these various attempts I have used the RECOMPILE switch to get a clean execution. Also, for the record I realize there are other inefficiences in this query. This is from an older portion of the application that has since been optimized but the optimized process will only work for new installs of the application. I have removed out various parts of this procedure that I suspected of causing the performance lag and the last component remaining has always been the date filter.

ALTER PROCEDURE [dbo].[MySproc_PassedVar]

@StartDtm DATETIME

, @EndDtm DATETIME

AS

DECLARE @CompanyID int

DECLARE @SiteID int

SET @CompanyID = 1000

SET @SiteID = 1007

--SELECT @StartDtm = CAST(@StartDtm AS datetime)

--SELECT @EndDtm = CAST(@EndDtm AS datetime)