May 2, 2003 at 3:58 pm
We are doing a lot of updating and computation on this temp table inside a stored proc. Can this temp table (#table) be pinned and what are the downsides to it ?
thanks
-sk
May 2, 2003 at 5:49 pm
I think using a table variable instead of a temp table will help you:
DECLARE @t1 TABLE( c1 int, c2 varchar(10) )
now @t1 is a local table variable. Memory for it is allocated as long as the variable is in scope. The only disadvantage is that it consumes memory. Of course, it is much faster then a temp table (in tempdb).
I do not think you can alter a table variable, create indexes on it, etc. But you can include constraints in the DECLARE statement.
Edited by - mromm on 05/02/2003 6:32:42 PM
May 5, 2003 at 9:05 am
quote:
I think using a table variable instead of a temp table will help you:DECLARE @t1 TABLE( c1 int, c2 varchar(10) )
now @t1 is a local table variable. Memory for it is allocated as long as the variable is in scope. The only disadvantage is that it consumes memory. Of course, it is much faster then a temp table (in tempdb).
I do not think you can alter a table variable, create indexes on it, etc. But you can include constraints in the DECLARE statement.
Edited by - mromm on 05/02/2003 6:32:42 PM
thanks, an option worth looking into
-sk
May 5, 2003 at 3:34 pm
I've recently converted a bunch of procs from temp tables to table vars, and have seen performance improvements of up to 10 times. You can create a primary key in order to get indexing on the table var. If you don't have a unique column, you can always create an identity column and use that as the second (third,etc.) column in a compound primary key.
-Dan
Edited by - dj_meier on 05/05/2003 3:36:07 PM
-Dan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply