Temporary variables in SQL

Question

Post reply

Temporary variables in SQL

tharucse

SSChasing Mays

Points: 640
More actions
January 11, 2011 at 1:48 am

#157385

I found that some people are using temporary variables with stored procedures just to increase the performance of the query.No particular need for temp variables there.It's like this.If we want to insert some values in a table then
Create PROCEDURE MyStoredProcedure
(
@param1 INT OUTPUT,
@param2 varchar(40)
)
As
DECLARE @temp1 INT
SET @temp1=@param1
DECLARE @temp2 varchar(40)
SET @temp2 =@param2
Insert into Table_1(Field1,Field2)values(@temp1,@param2)
I just could not imagine how this will improve the performance.For me it seems in the other way.Can anyone please help me to understand this?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's used when you run into problems with parameter sniffing.

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tharucse SSChasing Mays Points: 640 More actions · Answer 2

Thanks Gail.I'll go through it and get back to you if I have any issues.Again Thanks a lot..

tharucse SSChasing Mays Points: 640 More actions · Answer 3

In search queries when data distribution is skewed parameter sniffing can be a problem and local variables can be used to solve it.But I really don't understand how this comes into play when we do inserts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It doesn't. No use or benefit whatsoever.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tharucse SSChasing Mays Points: 640 More actions · Answer 5

Thanks Gail.I just asked one person who has this practice of using temporary variables.They say that they keep it as a standard way to write sql procedures even there is no use in insert queries Because can not expect all the developers to think in an intelligent way before write queries when it comes to a particular query that may have performance issue because of parameter sniffing.:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

tharucse (1/12/2011)
Thanks Gail.I just asked one person who has this practice of using temporary variables.They say that they keep it as a standard way to write sql procedures even there is no use in insert queries Because can not expect all the developers to think in an intelligent way before write queries when it comes to a particular query that may have performance issue because of parameter sniffing.:-)

That's a bad practice. The use of variables for parameter sniffing problems is a specific solution to a specific problem. By using variables everywhere you'll be hindering the optimiser and generally preventing it from finding good execution plans.

Use variables for the parameters only when there really is a parameter sniffing problem and investigation has shown that it is an appropriate solution (rather than rewriting the query or specific query hints). It should not be a standard development practice

http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

Also, table variables don't necessarily make things any faster than a Temp Table. They both start out in memory... they both write to disk (TempDB) when they get bigger than memory wants to hold.

Table Variables also make it (IMHO) more difficult to troubleshoot a problem in the code because they don't persist after the run is complete in SSMS. That means you have to run the code from the beginning every time or write some code to do some skips. You don't need to do that with Temp Tables because they persist during the session..

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)