Use of local vars vs parameters in a stored procedure query 1

[unclejimbob]

I am new to MSSQL 2005 and I've 'solved' the problem, but just wondered if anyone else has come across a similar issue. I have a stored procedure which has a static SQL statement that contains a clause this...

SELECT
pn1.given_name,
pn1.family_name,
per1.date_of_birth
FROM
dbo.person per1
INNER JOIN dbo.person_name pn1 ON per1.party_id = pn1.party_id AND pn1.preferred_name_flag = 1
WHERE
pn1.given_name IS NOT NULL AND
pn1.family_name IS NOT NULL AND
per1.date_of_birth IS NOT NULL
AND
(
per1.create_date BETWEEN @start_date AND @end_date
OR
per1.last_update_date BETWEEN @start_date AND @end_date
)
GROUP BY
pn1.given_name,
pn1.family_name,
per1.date_of_birth
HAVING COUNT(*) > 1

It just identifies duplicates and stuffs them into a probe table...

Now I know it fails at this statement because I've tested it

If I pass parameters thus...

CREATE PROC dbo.myreport
(
@selection_criteria_type_id INT=1,
@start_date DATETIME=NULL,
@end_date DATETIME=NULL
)
AS
....

the statement executes and never comes back.


However if I assign the parameters to local variables and then use them like this:

CREATE PROC dbo.up_rpt_040
(
@selection_criteria_type_id INT=1,
@start_date_rpt DATETIME=NULL,
@end_date_rpt DATETIME=NULL
)
AS
...

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @start_date = @start_date_rpt
SET @end_date = @end_date_rpt
...

then the proc executes in about 5-10 seconds.

My local SQL Server guru says that it may have something to do with stored proc query plans, but I find it a real puzzler.

Can anyone shed some light as to why it is that the SQL statement seems to care a lot about where the values for the predicate come from ?

ujb

 

[gmmastros]

>> My local SQL Server guru says that it may have something to do with stored proc query plans, but I find it a real puzzler.

Sounds like you have a pretty good SQL Server guru handy. This phenomonon is called Parameter Sniffing.

Here's one article that explains it (there are many others).

http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom