Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rangi
Starting Member
18 Posts |
 Posted - 2002-12-04 : 08:42:07
|
| Do temporary tables slow down stored procedures? I am hoping the answer is no because I am planning on storing a results from five tables in temporary tables so I can then run queries on these (smaller) tables.I'm assuming the actual process of creating and removing five tables doesn't take the server hardly any time at all, but as I've been unable to test this, does anybody happen to know?Thanks,Rangi |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-04 : 09:01:15
|
| Creating a table, temp or permanent, really doesn't take any time at all. Filling it with data and/or updating it does. A temp table can be slower if the server gets a lot of users hitting it simultaneously, and the procedure(s) create a lot of temp tables. Tempdb can get saturated with I/O activity. You'd have to test your procedure under a heavy load to see if it's unacceptable. Generally speaking though, temp tables don't slow anything down otherwise. I use them all the time and never had any major performance problems.If you're using SQL Server 2000, you can also use table variables. These exist in RAM and can be much faster, but make sure you test different approaches. You can't put indexes on a table variable, but you can index a temp table. Doing so can make temp tables faster, depending on the work you're doing.Edited by - robvolk on 12/04/2002 09:02:16 |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-04 : 09:18:39
|
| Creating the temp table takes time and uses resources.Usually you wouldn't save subsets of tables in temp tables but save the results of a join.The advantage of a tem table is that you are accessing a smaller structure and it is more likely to all be in memory when accessed. Also it makes processing easier to understand and more flexible.Try to avoid them for things like internet applications where you are expecting hundreds of threads to be executing - this could cause tempdb to block as tables are created and destroyed - use table variables instead where possible.For client server applications you shouldn't find a problem with performance.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 12/04/2002 09:21:04 |
|
|
|
Rangi
Starting Member
18 Posts |
Posted - 2002-12-04 : 09:51:51
|
| robvolk and nr,Thank you both for your excellent advise. I didn't know about table variables but I have now read a bit about them and they sound extremely useful and just what I need.Thanks again for taking the time to help,Rangi |
|
|
|
|
|
|
|
|
Do temporary tables slow down stored procedures?