x

Is it better (faster) to use variables, or store values in a temp table or table var?

I have some UDFs that I am trying to clean up that use a series of IF statements to return the status of something in real time.

These functions all start off by declaring 15-20 variables (varchars, ints), selecting values into them, then cycles through the IF statements.

Would there be a benefit to storing the values in a temp table or table var versus creating, populating and referencing all of these variables?

Update

The UDFs currently in existence are actually scalar functions built by another developer, no longer with the project. The variables are populated from a single select with an average of 5 joins. I have inherited about 10 of these functions. Some reports hit two or more of them, and some times the same function is hit once in the select statement and again in the where clause.

more ▼

asked Oct 21, 2009 at 03:11 PM in Default

Rob Allen gravatar image

Rob Allen
34 4 4 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Without seeing your code, I think multiple variables is probably better.

I think the key for performance is the part when you are "selecting values into them". Fewer queries is obviously better. If you want to set multiple variables with a single select, you can insert the values into a table variable as you suggested, or you can just include multiple assignments in the SELECT clause.

SELECT @FirstVar = first_col, @SecondVar = second_col
FROM MyReferenceTable

For code readability, I would think that multiple variables are better. Also, if the values are stored inside a table variable, then your IF statements would have to query that table each time.

more ▼

answered Oct 21, 2009 at 04:03 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Good point. Unfortunately, the code is 200+ lines and isn't commented so adding it wouldn't have changed your answer. Thanks.
Oct 21, 2009 at 04:08 PM Rob Allen
This is a good answer, particularly the part pointing out you should select multiple values into multiple variables at once.
Oct 21, 2009 at 05:06 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

Also, Starting with sql server 2008 you can declare and set a value to any variable, like:

declare @a int = 0, @b int = 1, @c int = 2

Have you though about splitting your functions and call them from your main, making more specialized funcions/procedures instead of those big monsters of 200+ lines is often a great idea. This way each function/procedure could require less variables.

more ▼

answered Oct 21, 2009 at 04:27 PM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left

Without seeing the code, I'd go with variables. Temporary tables or table variables carry quite a bit of overhead to allocate space for them, load them, in the case of temporary tables, create and maintain statistics, again in the case of temporary tables, deal with recompiles, and with table variables deal with the fact that if you're putting more than a few rows in them, they lead to serious performance bottlenecks.

These UDF's you're creating, they don't access data through all these various IF statements do they? If so, you're possibly looking at some serious performance issues due to recompiles. Are they multi-statement table valued UDF's? These are extremely dangerous for performance since they work off the mechanism of table variables, with no statistics, each one is treated as if it were a single row by the optimizer.

more ▼

answered Oct 21, 2009 at 08:11 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57
x33
x22

asked: Oct 21, 2009 at 03:11 PM

Seen: 2528 times

Last Updated: Oct 22, 2009 at 10:21 AM