Local Variable performance

Question

Local Variable performance

karthik M

One Orange Chip

Points: 29139
More actions
June 13, 2007 at 2:20 am

#117433

Hi ,
In my recent procedure , I am using lot of local variables to store intermediate values.
will local variables affect query performance ?

karthik

Viewing 4 posts - 1 through 3 (of 3 total)

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

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 1

The simple answer is yes they will affect query performance, but without knowing more specifically the data model, indexes, what type of local variable (table, scalar) etc I can not really answer if it will be better or worse.

Depending on how much the local variables change / are used from run to run you may want them passed to the procedure.

I would suggest running some different tests over your datasets as well as expected datasets over time.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

The actual overhead of the variables is essentially negligible, even for hundreds of them I would bet. They are RAM and CPU constructs, and thus operate in the micro- and nano-second time frames. The BIG POINT is what are you putting IN those variables. If you are doing tons of iterative SELECTs from tables, then your sproc will be poorly performing but due to the I/O (milli-second time frame), not the variables. If this is the case, look for ways to combine your processing logic in to better SET-based processing.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

As a previous post inferred... do you have cursors and/or WHILE loops? That'll be the performance killer... you might want to post your code with a description of what it does and maybe even some table info and sample data... you'd be surprised what the folks on these forums can erg out performance-wise...

--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)