#table vs @table which is better
-
maybe i am late but i just read that using a table var instead of a #temp table is better.. has anyone had a exp. with this. i have a about 800 lines of procs where we have lots of #temp tables and i was thinking of looking to recode for @table datatype..
-
I would say that it all depends on the data you have. Personally I'm still using temp tables for a lot of my work. I do this because I typically have millions of records to work with and I feel that if there are multiple users running the process at the same time that will use too much ram on the server. For the situations that don't require multiple users and I feel that the memory usage won't be too large I use table variables.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
thanks.. i was wondering about that.. i do have some report_table's that get updated on a regualar basis and i think that it may fit there. as fars as users now i only have 25 max.. so thats not a issue now...
-
Hello,
Is this a local varaible that has been declared to receive the data or does this work as the temporary table that can store all the results sets from a local query?
Can you provide some examples of how this works?
Thanks,
Johnny
quote:
I would say that it all depends on the data you have. Personally I'm still using temp tables for a lot of my work. I do this because I typically have millions of records to work with and I feel that if there are multiple users running the process at the same time that will use too much ram on the server. For the situations that don't require multiple users and I feel that the memory usage won't be too large I use table variables.Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
-
BOL says:
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements.
table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.
table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
Assignment operation between table variables is not supported. In addition, because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks.
-
If you are using temp table inside a stored procedure there is an advantage using a @table instead of #table. The stored procedure will spared with the recompilation each time you execute. The execution plan is cached and performance will improve. As far as the IO - there is no difference between them. However there are few limitation of the DML on each type, like you can not perform a SELECT INTO on a @table.
-
quote:
If you are using temp table inside a stored procedure there is an advantage using a @table instead of #table. The stored procedure will spared with the recompilation each time you execute.
You can avoid this issue by creating the #table outside of the stored proc. We create the table(s) in the middle tier, and since the SPs operate on the same connection (you need to standardize the connection among the different objects) it works smoothly. One key advantage is the modularity this can provide.
-
quote:
...I would say that it all depends on the data you have. Personally I'm still using temp tables for a lot of my work. I do this because I typically have millions of records to work with and I feel that if there are multiple users running the process at the same time that will use too much ram on the server...
How will using temp tables decrease the amount of RAM used on the server, especially for multiple users? The temp tables are just being created in RAM/VM for tempdb and are using just the same IO and extent spools to pull the data for the temp table from the original table than if you were just doing a SELECT from the original table. More than likely, you are going to be pulling from pre-spooled extents anyway, so the RAM won't be an issue unless you are pinning tables...
--
Am I missing something here?
-
Be careful here. Table vars do not work exactly the same as temp vars. For example, you cannot use exec as source when inserting into table var.
That is, if you try this...
declare @tmpUsers TABLE(spid int...blah, blah, blah)
INSERT INTO @tmpUsers EXEC SP_WHO
then you get the following error message...
Server: Msg 197, Level 15, State 1, Line 5
EXECUTE cannot be used as a source when inserting into a table variable.
HTH
Billy
quote:
maybe i am late but i just read that using a table var instead of a #temp table is better.. has anyone had a exp. with this. i have a about 800 lines of procs where we have lots of #temp tables and i was thinking of looking to recode for @table datatype..
-
Any good references on the nitty gritty differences between #temp tables and variable tables
-
Another nit-pick detail that contributes to improvement of the performance is that a table variable requires less locking and logging resources as the transactions involving table variable last only for the duration of an update.
-
quote:
How will using temp tables decrease the amount of RAM used on the server, especially for multiple users? The temp tables are just being created in RAM/VM for tempdb and are using just the same IO and extent spools to pull the data for the temp table from the original table than if you were just doing a SELECT from the original table. More than likely, you are going to be pulling from pre-spooled extents anyway, so the RAM won't be an issue unless you are pinning tables...--
Am I missing something here?
A colleague pointed out that my post was confusing, so I will clarify. I meant to simply say that, given the choice of using temporary tables / table variables, or no temporary tables / table variables at all, the least amount of RAM used will be with not using the temp storage area at all...
If you can do without the temp tables, than do without. Temp tables can help simplify some processes, but often at the expense of high RAM and disk usage. While I'm aware that temp tables (#table...) are not created in RAM, per se, they ARE created from the same spooled data pages that exist in RAM that would be used if the temp table weren't created at all. tempdb's main advatage is that it's DML commands are only half-logged, meaning inserts and updates are faster because only the portion of the transaction's rollback is needed to be logged. SELECTs aren't any faster from tempdb than any other db, given similar disk/drive locations. My colleague has a good general rule of thumb: less than 10K rows, use a table variable, more than 10K, use a temp table. And I would add, don't use temp tables unless you are absolutely sure you need them.
--
Here is a good link on the issue:
http://www.sql-server-performance.com/jg_derived_tables.asp
--
Thanks,
Jay
ps..thanks to RonKyle for pointing this out...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply