problem with sp parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

problem with sp parameters

Author

Topic

ptramontani
Starting Member

1 Post

Posted - 2010-10-07 : 06:32:48

Hi all,
I found a very strange problem that seems to be a mssql bug.
I have a stored procedure with some parameters.
One of these params is a dateTime.
The procedure uses parameters in the where clause of a select statement
IE:
CREATE PROCEDURE myProc (
@amount decimal, @customer nvarchar, @dueDate datetime
) AS
select * from table
where
amount >= @amount and
customer = @customer and
dueDate <= @dueDate
return 0
go

The sp was very fast but some days ago I modified it simply adding some comments and I applied it against the db using the "alter procedure" statement.
Then the procedure became very slow and I found that the select statement took about 60 seconds every time the procedure was launched. (the same select lauched with SQL Query Analizer ends in some millisecond)

I found that the problem was caused by the @dueDate parameter and I found this solution:
I introduced a local variable in the sp (@localDueDate)
I set its value = the @dueDate param value at the beginning of the sp
Then I use the local variable in the where clause
-> now the sp is fast again and the select statement ends in some millisecond as the direct launch in Query Analizer

CREATE PROCEDURE myProc (
@amount decimal, @customer nvarchar, @dueDate datetime
) AS
declare @localDueDate datetime
set @localDueDate = @dueDate
select * from table
where
amount >= @amount and
customer = @customer and
dueDate <= @localDueDate
return 0
go

Someone found a problem like this?
Did you know which kind of problem is and how can I avoid it?

Thanks
Paolo Tramontani

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-07 : 06:38:47

It's called parameter sniffing. In my experience it's generally quite rare but when it bites you it bites hard. Rule of thumb is always to declare local variables and assign them from the params.

Just do a google search for it "sql parameter sniffing" and start reading.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources