August 7, 2015 at 10:11 am
Hello all,
I have an odd issue and wanted to see if anyone had any experience with this or have any ideas where to look...
code samples follow...
I have a sproc that takes in a date parameter. If I call this sproc once, it runs in a reasonable amount of time, typically between 2 and 4 seconds among our various servers. But if I put the call into a loop and change the parameter, the sproc starts taking about a minute.
For example, this call takes about 2 seconds on my local dev db:
EXEC dbo.ProcessData @DateToProcess = '8/1/2015';
I can make 5 calls to this and it takes about 8 seconds:
EXEC dbo.ProcessData @DateToProcess = '8/1/2015';
EXEC dbo.ProcessData @DateToProcess = '8/2/2015';
EXEC dbo.ProcessData @DateToProcess = '8/3/2015';
EXEC dbo.ProcessData @DateToProcess = '8/4/2015';
EXEC dbo.ProcessData @DateToProcess = '8/5/2015';
But if I put the parameter to pass in into a variable, it starts to crawl:
DECLARE @DateStart DATETIME, @DateEnd DATETIME;
SELECT @DateStart = '8/1/2015', @DateEnd = '8/5/2015';
WHILE ( @DateStart <= @DateEnd )
BEGIN
EXEC dbo.ProcessData @DateToProcess = @DateStart;
SET @DateStart = DATEADD ( DD, 1, @DateStart );
END
This loop takes almost 5 minutes. I put output statements in to see what it's doing and it shows that each iteration takes almost a minute.
I have data that I can call with this sproc that goes back about 3 months. I have the loop do all three monhts and it takes about an hour and a half with the output statements showing it's taking about a minute per execution.
I can select about any 5 days (e.g. setting the start date and end date to about any 5 consecutive days) and it's the same, so it's not just a set range, but any range.
we have quite a few servers (mainly sql 2008 and a few 2005) and it happens on some and not on others. I've looked at the machines and nothing is hammering them (e.g. memory, cpu and such are not pegged on the ones running slow).
I can take backups of db's and restore to my local db, and ones that run quickly on the server run quickly on my local machine, and ones that run slowly on the server runs slowly on my db, so the problem follows the db.
I've cleared the sproc cache with freeproccache as well to no avail.
I can take the code from the sproc and put it into the loop and it runs fine (quickly).
If anyone has any suggestions, I would appreciate them.
Thanks,
Dan
August 7, 2015 at 10:34 am
That's odd behavior. Have you looked at the execution plans for both calls, the hard coded value and the variable? I'd be curious if there was a difference. I wouldn't think there would be. Once you pass the variable value to the stored procedure, the stored proc should compile as a parameter using parameter sniffing, regardless of how you called it, variable or not.
Also, variable or not, are you passing exactly the same value initially? If not, it could just be bad parameter sniffing at work.
"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
August 7, 2015 at 10:58 am
I haven't looked at the execution plans just yet, but yes, the exact same dates are being used for both when I test them the sql above was just run in two different windows. The first time it runs, it actually processes the data. if it is run for the same date again, there's nothing to do, so it runs in just a fraction of a second. In the hard coded script, the second time it runs, it takes about a second for all 5 dates, but in the loop with the variable, it's 5 minutes no matter if it's the first time or the 10th time it runs...
I've also tried starting with a variety of dates as the starting variable, all have the same effect.
Thanks,
Dan
August 7, 2015 at 11:56 am
It sure sounds like something related to parameter sniffing. Possibly it's data conversions? You're showing passing a string in one and a datetime in the one with the variable. What's the actual data type being used? Maybe you're getting a conversion somewhere? For example, if I run this code:
CREATE PROC dbo.MyProc
(@MyParameter VARCHAR(50))
AS
SELECT @MyParameter;
GO
DECLARE @MyVariable DATETIME = GETDATE();
EXEC dbo.MyProc
@MyParameter = '8/7/15'; -- varchar(50)
EXEC dbo.MyProc
@MyParameter = @MyVariable;
The results look like this:
8/7/15
Aug 7 2015 1:53PM
The difference between those two could certainly cause issues depending on what indexes you have in place and what the data types are.
"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
August 10, 2015 at 6:36 am
The parameter to the sproc is a datetime, so the hard coded strings would be the ones getting converted to a datetime, but those are the ones running quickly. In other words, if I were to correct the script, I would correct the one that runs fast and explicitly convert the string to a date.
August 10, 2015 at 7:44 am
Back to comparing the execution plans to understand where the differences occur.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply