@PARAM vs @LOCAL_VAR performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

@PARAM vs @LOCAL_VAR performance

Hi, I’ve found that I can get massivly varying performance in a stored procedure depending upon whether a particualr select uses a parameter being passed in to the stored procedure or if it first copies it to a local variable and uses that. eg/ DECLARE PROCEDURE A
@X AS BINARY(16)
BEGIN
SELECT … FROM … WHERE col = @X …vs… DECLARE PROCEDURE A
@p_X BINARY(16)
BEGIN
DECLARE @X BINARY(16)
SET @X = @p_X
SELECT … FROM … WHERE col = @X There’s nothing obvious from the execution plans that would cause this but I’m seeing a difference in performance of about 400% between these two chunks of SQL. Depending upon whether I have an index on a second table JOINed in the SQL (but NOT keyed by the value being passed in) the effect can be reversed – it becomes quicker to use the parameter directly rather than copying it to the local variable. Anyone know why the use of the parameter or the use of a locally declared variable (of the same type) sould make such a significant difference in the execution time please? Note: This has only been observed with a BINARY(16). Thanks, Confused of Dudley, UK.
This has to do with parameter sniffing. Without the value in a local variable sql server does not know the value when compiling the plan. When assigned to a local variable, it knows the value. See:
http://sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=716
Thankyou Argyle! (It’s nice to know that it wasn’t just the server going out of it’s way to give me a hard time.) Is there any steadfast rule to follow please? As I mentioned I have used the parameter version with a covering index to remove a bookmark lookup in another part of the query and this is faster than assigning to a local variable first. Without the covering index the query runs a lot slower when using the parameter but faster when using the local variable. The problem I have is that the product I work on has literally hundreds of stored procedures and I’d like to be able to look at something and identify which would be the best way to pass the keying information into the SQL statements within the stored procedures. Is there a list like… * If you are doing A, B or C use the parameter as it is.
* If you are doing E, F or G put the parameter in a local varaible prior to use. …? (Please!) Thankyou once again. Mark.
Well it depends. If you are going to let sql server compile the plan with a known value you want this value to be typical of the queries run. But the thing is a query plan for value X might not be good for value Y. It depends on the distibution of the values in your tables. If the index usage varies a lot for different values in the same stored procedure you might want to use the WITH RECOMPILE option when creating the procedure. This will generate a new plan each time, but of course with some overhead. On the other hand each individual query will get an optimal plan.<br /><br />You could also create a master stored procedure that will call sub procedures that are grouped according to queries that are similair. Then each sub procedure will get it’s own plan.<br /><br />Test, test, test <img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />
Ah… in this particular case the parameter making this effect apparent is a GUID held in a BINARY(16). We use heaps of the things! I will have a play with the ‘WITH RECOMPILE’ and see what happens. Thanks.
]]>