Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Temporary variables in SQL Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 1:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:43 AM
Points: 112, Visits: 227
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.


Post #1045736
Posted Tuesday, January 11, 2011 2:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 40,204, Visits: 36,605
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 2008, MVP
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

Post #1045757
Posted Tuesday, January 11, 2011 3:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:43 AM
Points: 112, Visits: 227
Thanks Gail.I'll go through it and get back to you if I have any issues.Again Thanks a lot..
Post #1045775
Posted Tuesday, January 11, 2011 8:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:43 AM
Points: 112, Visits: 227
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.
Post #1046241
Posted Tuesday, January 11, 2011 10:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 40,204, Visits: 36,605
It doesn't. No use or benefit whatsoever.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1046259
Posted Wednesday, January 12, 2011 4:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:43 AM
Points: 112, Visits: 227
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.
Post #1046386
Posted Wednesday, January 12, 2011 5:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 40,204, Visits: 36,605
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 2008, MVP
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

Post #1046403
Posted Wednesday, January 12, 2011 7:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1046468
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse