SQL Server Performance Forum – Threads Archive

Temp Tables Performance

I read somewhere that Temp tables – if you take aside the time taken to create them – are far faster than permanent tables for inserts, updates, and deletes. This is because transactions aren’t logged on the temp tables so speed increase apparently should be 3 to 4 times faster. Yesterday, I tested this with side by side insert comparisons on a permanent table vs a temporary table – inserting about 10,000 rows. Using Query Analyser and the Execution Plan viewer, the two inserts came out at taking 50% of the total time each. What’s going on here? Are temp tables faster or not? Or is there a problem with using the Execution Plan for such kind of analysis? I used Show Execution plan, rather than Estimated Execution plan as I wanted exact actual cost to the db. Cheers guys,
Dave.

---

This can be a hard question to answer as there are a LOT of variables. If your db recovery mode is set to SIMPLE, there won’t be any difference at all. Also, if your regular tables sit on a better disk subsystem then your tempdb, it’s very possible they’ll actually be faster. For 10k rows or less, table variables will generally be faster because they are tables in memory. Make sense? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Did you create tables in temdb or auxiliary tables?
I mean: Create table A or Create table #A Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Yeah, I should have specified exactly I guess, but the answer is that all the other factors are equal as far as I can tell – recovery mode is FULL, all DBs and Files are on one disk – this is my own dev box. The select statement in my test brings back just over 10K records. (It will be different in yours depending on how many objects are on your server) Anyway, the execution plan comes out 50% for the insert to speedtest and 50% to #speedtest. I think the problem is probably with the Execution Plan – it only shows ‘Cost’ rather than time taken, and probably the Cost algorithm doesn’t pick up on the idea of temp tables being quicker. What do you think? Here’s the test: alter database northwind
set recovery full create table #speedtest
(testvalue varchar(100)) create table speedtest
(testvalue varchar(100))
insert into #speedtest
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects insert into speedtest
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects
union all
select [name] from master.dbo.sysobjects

---

Well, I tested inserting data into permanent tables vs. temporary table. I didn’t have any other variables like different disk subsystems for tables and so on. I did notice a difference when I tried to insert 300,000 records in those tables. It was not 3 or 4 times faster though but yes, inserts in temporary tables were approximately 2 times faster than the permanent tables. This was true for tables created in tempdb or in any other database with # sign. Dave, when you look at the execution plan costs, it shows 50% each because it generates the same query plan for both the inserts. However, if you do SET STATISTICS IO ON before running them, you will notice a significant difference in I/Os for permanent tables and temporary tables. If you run the queries separately, you will even notice difference in execution time.
HTH
-Rajeev Lahoty

---

And I forgot to mention that the database was in FULL recovery mode where I had my permanent table. Thanks

---

Also, Execution Plan (From SQL Analyzer) can see # tables. So I supose you are looking execution plan from select no from create table #speedtest. And, may be, there is some differences between both executions plans. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Well – the execution plans are exactly identical, but I just thought that the INSERT part of the execution plan would have some kind of real cost associated with it. Apparently not. I added the statistics option like Rajeev suggested and actually found *no* difference in IO for my test. I did additionally set the option – set statistics time on and this showed the difference in time finally – managed to run a test that showed Perm tables comign out at 850ms and temp tables at 750ms – this was an INSERT test for about 40K rows. These rows would have been all cached in memory as I used the master db sysobjects table for the data source so the difference *should be* reflecting just the additional speed temp tables provide. I had also added a table variable to my test this time and it completed the test in 600ms. I really thought the differences would have been much bigger. 850 -> 750 -> 600 Perm -> Temp -> Table Variable
1 -> 12% faster -> 25% faster Anyone else got any facts about speed that are based on more accurate tests/ Cheers guys,
Dave.

---

Luis – I’m not sure I understand you here. It is my understanding that the Execution Plan doesn’t care about DDL statements so, for example, any CREATE TABLE statements will not show up. It can see the tables that are created though, but there was zero difference between the two execution plans used for temp vs perm tables and the cost was exactly the same too. The statistics option that Rajeev pointed out has helped, but I still thought the Insert part of the Execution Plan should take things into account such as Temp table or table variable, or permanent table, or whatever, and even things like the cost of accessing that table because of where it’s filegroup is located but this doesn’t appear to happen. Maybe I’ll send an email to sqlwish. There’s one example in particular where it should have a real cost associated with INSERT – occassionally (very bad) queries will cause the Execution Plan to use temp tables and pump stuff in there. How does the optimisation engine know when to switch to using temp tables if it doesn’t know the real cost of using them? Cheers,
Dave.

---

I have another question related to this topic – When are Global Temp tables cleaned? I understand local temp tables will be any point after the scope has closed, but scope never ends for global temp tables so do they have to be deleted manually? Dave.

---

Dave you are right. I was wrong when I said you would observe difference in I/Os. I will run more tests on this with bigger datasets and will let you all know results of my tests. Thanks

---

i do not believe that tempdb, ie, temp tables is not logged,
i believe that the tempdb is in simple recovery mode,
that would imply in certain insert ops in the tempdb may be bulk logged,
so if that were compared to a full-recovery db, it would be faster,
the ops cited are not bulk logged,

---

Well the ops cited were certainly faster, and consistently faster, when using tempdb. Their file groups are definitely on the same disk and I even tried turning the op around so that insert to #speedtest was first followed by insert to speedtest but temp table still faster. I mean it’s only 10 – 15 % but that’s still quite a big deal. Is it possible that temp tables are written to memory first or something, and maybe lazy written to disk at a later date? I know I read somewhere about these temp tables not being logged and thus being quicker but turns out like you say, they are logged, and yet they are still quicker. Any ideas why? Dave.

---

Global temporary tables are automatically dropped.
The table is dropped when the connection that created the temp table closes, AND any SQL statements (on other connections) which are currently processing and referencing the table have completed. Note that the granularity for this is statement level, not batch !

---

]]>