September 4, 2012 at 10:20 am
Hi,
I'm analyzing an existing app from a client that uses a lot a SP that has inserts on a temporary table to process the data.
Are temporary tables better than table variables or, even better, use a "permanent" table with a SessionID column and insert the data on that table? With a "permanent" table I could have the necessary indexes created so it could perform better...
The table hasn't more than 100 rows... but using a temp table has a delay of creating and dropping the table, and I don't know how table variables really work...
Thanks,
Pedro
September 4, 2012 at 10:53 am
PiMané (9/4/2012)
Hi,I'm analyzing an existing app from a client that uses a lot a SP that has inserts on a temporary table to process the data.
Are temporary tables better than table variables or, even better, use a "permanent" table with a SessionID column and insert the data on that table? With a "permanent" table I could have the necessary indexes created so it could perform better...
The table hasn't more than 100 rows... but using a temp table has a delay of creating and dropping the table, and I don't know how table variables really work...
Thanks,
Pedro
There is no cut and dried "A is better than B" here. It all depends on your implementation, amount of data, etc etc etc...
With only 100 rows it probably isn't going to make a lot of difference and indexes would be incredibly minimal with that little data. You should check BOL for table variables, it will explain it far better than a forum post. There have been thousands of comparisons about which is the "preferred" and the answer is always the same "it depends".
With such a small amount of data I would think that a temp table or table variable would be easier to work with than a permanent table. The persistent table brings along its own baggage you have to deal with (concurrency, removing temp data, etc). Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2012 at 10:59 am
Thanks,
Also the "permanent" table has the overhead of deleting the SessionID data after isn't necessary, and the temp table or table variable "self destroys" :).
Thanks again, I'll check the BOL...
Pedro
September 4, 2012 at 3:21 pm
September 4, 2012 at 3:27 pm
As said earlier, I would say there is no definite right answer. Just wanted to share the issue I recently faced. We were using table variables for staging purposes before we finally loaded data into physical table. when we started, the amount of records were minimal, but it grew over time and reached over 100k and then we started having memory overflow /not responding issue on the server. Modifying all the table variables to temp tables solved the thing. In your case, table variable might be appropriate.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply