| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-01-06 : 11:09:45
|
| There are occasional posts that speak about the repeated create / destroy of temp tables causing some kind of database clutter.How bad a problem is this? Is there an administrative tool that will clean up the mess?Sam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 12:16:57
|
| Could you post some links to these posts? The database clutter would exist in tempdb anyway though. No, there aren't any tools to clean up the mess.Tara |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-01-06 : 12:30:03
|
| Hi Tara,I can't put my finger on any, and searching "temporary table" doesn't help much (too many posts).I take it from your response that there's really not much to be concerned about? I don't need to be concerned that my db performance will suffer if I increase the use of temp tables in my stored procedures?Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 12:33:28
|
| Well, yes you do need to be concerned but not do to database clutter. You need to watch the memory utilization.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-01-06 : 13:01:01
|
| Memory - based surprised me and I did a search. I found Graz's article on temporary tables which confirms the memory-based suggestion.I also found a great yak page in his article.I'm sure I read a post in the last couple of months which held a comment (of surprise) noting that local tables where held in the same "area" as temporary tables. I took this to mean the same area of the database. My dim recollection was that there wasn't any net gain using a local table variable instead of a temporary table.Any thoughts about that?Thanks again,Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 14:34:51
|
| From sql-server-performance.com:SQL Server 2000 offers a new data type called "table." Its main purpose is for the temporary storage of a set of rows. A variable, of type "table," behaves as if it is a local variable. And like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared. In most cases, a table variable can be used like a normal table. SELECTs, INSERTs, UPDATEs, and DELETEs can all be made against a table variable. For best performance, if you need a temporary table in your Transact-SQL code, try to use a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them much faster. In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000] Added 8-7-2001 Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-01-06 : 14:46:34
|
| Sounds definitive. I suppose judgement needs to be applied here. It wouldn't make sense to use memory to hold a table if the amount of data were huge.In my case, my data is generally around 30,000 rows, but I'm trying to design for 10x that figure.Sam |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-01-06 : 15:01:46
|
| Graz's blog post at http://weblogs.sqlteam.com/billg/posts/641.aspx would seem to dispute the claim that table variables are not created in the tempdb.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-01-06 : 15:29:18
|
| Sounds like the key benefit of a local table is that there are fewer opportunities to compete for locks, and fewer opportunities to indicate recompilation is needed.Neither of which are problems for me. I need temp tables to implement datagrid paging. The life of the temp table is quite small, so either should do fine.Any thoughts about Temp Table vs Local Variable Table for paging recordsets in stored procedures?Sam |
|
|
|
|
Temp Tables