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.
Best Answer
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.
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.
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.
