How to pass sql function parameter by value

Question

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

Thanks Remus - this is an excellent pointer ! However - I feel a bit bulldozered by the vast amount of SQL details - but there is also a link to another excellent article — Meiki1964
– Meiki1964, Commented Jul 1, 2012 at 13:56

David Brabant · Accepted Answer · 2012-07-02 15:15:40Z

2

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."

edited Jul 2, 2012 at 15:15

answered Jul 1, 2012 at 10:28

David Brabant

44k16 gold badges93 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marcel N. Over a year ago

very interesting, although I never run into it

Meiki1964 Over a year ago

Unbelievable - put the same select into executesql and it goes back to milliseconds: exec sp_executesql N'SELECT * FROM table WHERE field > @v', N'@v int', @v=180 - Thank you David !

Kev Over a year ago

Whilst this may theoretically answer the question, we would like you to include the essential parts of the linked article in your answer, and provide the link for reference. Failing to do that leaves the answer at risk from link rot.

Community · Accepted Answer · 2017-05-23 11:48:12Z

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

Collectives™ on Stack Overflow

How to pass sql function parameter by value

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related