This is part of a bigger selection, but I have stripped it down to the essential question :
Compare this two SQL queries - the first works with a constant, the second with a variable, both have the same value (lets say 180). The one with the constant displays result immediately (e.g. within milliseconds), the one with the variable takes a few seconds to yield the same result.
Where is the catch ?
Query 1:
SELECT *
FROM table
WHERE field > 180
Query 2:
DECLARE @V INT
SET @v = 180
SELECT *
FROM table
WHERE field > @v
The catch lies in parameter sniffing. From the article mentioned:
"Parameter sniffing occurs when a parameterized query uses cached cardinality estimates to make query plan decisions. The problem occurs when the first execution has atypical parameter values. For each subsequent execution the optimizer is going to assume the estimates are good even though the estimates may be way off. For example, say you have a stored procedure that returns all id values between 1 and 1000. If the stored procedure is executed with this large range of parameter values, the optimizer is going to cache these atypical values, which indirectly causes the optimizer to under estimate cardinality. The problem is a typical execution may only return a few rows. This “sniffing” can cause queries to scan a table oppose to seek because the optimizer is assuming inaccurate cardinality estimates."
Well - I have been asked to elaborate on the solution (and I see also that "tips on writing great answers") - let me try to explain it - although I can explain only the effect - for understanding the background one has to plunge oneself into the mentioned articles.
I faced the problem of making a snapshot of certain data every three minutes.
The (very simplified) query was
SELECT *
FROM table
WHERE TimeStamp > DateAdd(ss,-180,GetDate())
Worked perfect - put it in a function :
CREATE FUNCTION GetSnapshot (@ss int) RETURNS TABLE
AS
RETURN
SELECT *
FROM Table
WHERE TimeStamp > DateAdd(ss,-@ss,GetDate())
This also worked perfect as long as I called it with constanst e.g.
SELECT *
FROM GetSnapshot(180)
Now I wanted to parameterize further because 180 seconds wan't fit all purposes.
Now here the problem began :
DECLARE @v int
SET @v = 180
SELECT *
FROM GetSnapshot(@v)
runs almost 10 seconds whereas the direct call with 180 takes milliseconds
I have also to mention that the same effect goes with simple tables - the fact that I called a function did not influence the result. No matter what I tried - ten seconds.
Now before completely despair I turned to the experts here in stackoverflow with the question in the title. I know a lot about parameter passing in programming languages - but nothing about it in SQL. In a PL if you pass by value the compiler generates code to make a local copy of the actual value at run-time and passes it to the called function like a constant - whereas pass by reference passes the language construction "as is" and than the called procedure can "call" this parameter again and again - be it a variable or a function call or whatever. Therefore I had the impression that the SQL compiler makes for constants call by value and for variables call by reference - which would require multiple evaluation by the called procedure and explain the long run-time on a resultset of a few ten thousand records.
In the mentioned article Erland explains it more or less the way I did :
(Start quote)
- A constant is a constant, and when a query includes a constant, SQL Server can use the value of the constant with full trust, and even take such shortcuts to not access a table at all, if it can infer from constraints that no rows will be returned.
- For a parameter, SQL Server does not know the run-time value, but it "sniffs" the input value when compiling the query.
- For a local variable, SQL Server has no idea at all of the run-time value, and applies standard assumptions. (Which the assumptions are depends on the operator and what can be deduced from the presence of unique indexes.)
(End quote)
He than elaborates further about Parameter Sniffing and Execution Plans which I'm not ashamed to admit that I did not understand nothing (-:) - but all in all it resembles the by value / be reference concept of programming languages.
Now how do I force the SQL Server to "call by value" ?
Fortunately there was the hint of David to the Demystifying SQL Server : SQL Server Parameter Sniffing article which gives the solution which I praised : pack the complete "by reference" call with a sp_executesql wrapper - in this case the outer call will be still "by reference" but since the parameter resolving is done on the wrapper level the inner call can be done "by value"
and we are back to the milliseconds response times.
Use it like this to make the trick :
exec sp_executesql
N'SELECT * FROM GetSnapshot(@v)',
N'@v int',
@v=180
That's all - sorry for the late response but I was very busy the last days ...
Meiki
