I have a stored procedure with a list of about 50 variables of different types repeated about 8 times as part of different groups (declaration, initialization, loading, calculations, result, e.t.c.).
In order to avoid duplication I want to use temp tables instead (not table variable, which does not bring advantages that I seek - inferred type).
I've read that temp tables may start as "in memory table" and then are spilled to disk as they grow depending on amount of memory and many other conditions.
My question is - if I use temp table to store and manipulate one record with 50 fields, will it be much slower than using 50 variables ?
I would not use a temp #Table unless I need to store temporary results for multiple rows. Our code uses lots of variables in some stored procedures. The ability to initialize during declaration helps reduce clutter.
Temp #Tables have some interesting side effects with regards to query compilation. If your stored procedure calls any child procedures, and queries in the child procs refer to this #Table, then these queries will be recompiled upon every execution.
Also, note that if you modify the temp #Table schema in any way, then SQL Server will not be able to cache the table definition. You'll be incurring query recompilation penalties in every query that refers to the table. Also, SQL Server will hammer various system tables as it continually creates and drops the table metadata.
On the other hand, if you don't call child procs, and you don't change the #Table schema, it might perform OK.
But stylistically, it does not make sense to me to add another join to a query just to get a variable for use in a WHERE clause. In other words, I'd rather see a lot of this:
declare @id
select @id = ...
select tbl.id, ...
from tbl
inner join tbl2 ...
where tbl.id = @id
Instead of this:
create table #VarTbl (...)
insert into #VarTbl (...) select ...
select tbl.id, ...
from tbl
inner join tbl2 ...
cross join #VariableTable
where tbl.id = VarTbl_ID
Another thought: can you break apart the stored procedure into logical groups of operations? That might help readability. It can also help reduce query recompilations. If one child proc needs to be recompiled, this will not affect the parent proc or other child procs.