October 22, 2011 at 12:08 pm
sqlguy-736318 (10/22/2011)
Thanks Ninja - I assumed that table variables would always have better performance than temp tables but based on your emphatic NOOOO it sounds like you have a lot of experience with this....
Like most things in SQL it depends. For a good idea of which will perform faster under which conditions read this:
October 22, 2011 at 12:26 pm
bitbucket-25253 (10/22/2011)
sqlguy-736318 (10/22/2011)
Thanks Ninja - I assumed that table variables would always have better performance than temp tables but based on your emphatic NOOOO it sounds like you have a lot of experience with this....Like most things in SQL it depends. For a good idea of which will perform faster under which conditions read this:
Here's a generalisation.
The more & more accurate info you give to the optimizer, the better chance you'll have to get a good plan. More importantly the least chance you'll have to get a terrible plan.
A farily good plan, instead of a great plan, might make 1-2 users wait half a second which is not good but won't annoy anyone too much. A terrible plan can go as far as killing the server for everbody for minutes at a time. I've also seen a few plans force reboots or major tempdb problems (this has other solution, but when you have non-dba admins, those solutions will be used)
Either object might give a suboptimal plan. But all in all it's way more likely with a table variable. Hence my choice to always default to #tbl when starting new work... untill I need a @tbl because #tbl cannot do the job.
Of course there's a big gray zone in between where both objects will do the job just fine. However bad performance is extremely easy to avoid here if you make the wiser choice early in the process.
1 illustation I like is comparing this to the chances of getting struck by lightning.
Out of the millions of strikes daily your chances of getting hit are pretty much 0.
However, during a big storm (higher, hybrid load on a server), I give you the choice to either come inside my house or go out in the field, in metal body armor too fly a kite, also with metal string.
There's still only 1 million lightning strikes during the storm... but I'll still stay inside rather than take my chances outside.
October 22, 2011 at 12:37 pm
sqlguy-736318 (10/22/2011)
Thanks for your replies everybody. Sounds like table variables should be used instead of local temp tables. Table variables seem to have a smaller footprint and local temp tables seem like the old way of doing things.
And they perform absolutely abysmally in many cases.
One question about temp tables though - does the additional info at the end of the temp table name in tempdb represent the session ID of the machine that created the temp table? Is that ID uniquely tied to the machine?
It's a random hash.
For example, if I restarted my machine and tried to recreate the same temp table the next day then would SQL server say that the table already existed?
No, because as soon as the connection closes the temp table is dropped automatically.
That's the key here. A temp table is automatically dropped when the session that created it closes or the procedure it was created in ends. That's why there's no need to check that it exists before creating it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 12:38 pm
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.
Exactly the same with temp tables, and temp tables don't suffer from the costing errors and subsequent poor execution plan that table variables do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 12:40 pm
sqlguy-736318 (10/22/2011)
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.Thanks accidental - table variables do seem to be the best for adhoc queries as opposed to online oltp queries because the SQL seems cleaner and more centralized.
As long as 'clean and centralised' is more important than 'performs adequately', go right ahead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 12:49 pm
sqlguy-736318 (10/22/2011)
Thanks Ninja - I assumed that table variables would always have better performance than temp tables but based on your emphatic NOOOO it sounds like you have a lot of experience with this....
Try it for yourself. Run this code in TempDB
if (OBJECT_ID('Test1') is not null)
DROP TABLE Test1
go
CREATE TABLE Test1 (ID int)
DECLARE @i int
SET @i = 0
SET NOCOUNT ON
WHILE @i < 20000
BEGIN
INSERT INTO Test1 (ID) Values (@i)
SET @i = @i + 1
END
--2. Query using temp table
DECLARE @Start DATETIME
SET @Start = (SELECT GETDATE())
CREATE TABLE #Tmp1(ID int)
INSERT INTO #Tmp1(ID)
SELECT ID FROM Test1
SELECT * FROM Test1
WHERE ID NOT IN (SELECT ID FROM #Tmp1)
SELECT DATEDIFF(ms,@Start,GETDATE())
--Record the time to run - on my machine 643 milliseconds
DROP TABLE #Tmp1
-- Now run this T-SQL
--1. Query using table variable
DECLARE @Start DATETIME
SET @Start = (SELECT GETDATE())
DECLARE @Tmp1 TABLE (ID int)
INSERT INTO @Tmp1(ID)
SELECT ID FROM Test1
SELECT * FROM Test1
WHERE ID NOT IN (SELECT ID FROM @Tmp1)
SELECT DATEDIFF(ms,@Start,GETDATE())
--Time to run on my machine 70,020 yes 70 thousand and 20 milliseconds
October 22, 2011 at 1:26 pm
... and that's with ONLY 1 table and ONLY 20K rows.
Try a 10 tables inner / outer join, optional filters and 20 M rows.
That performance's going to suck BIG TIME. Assuming the query actually finishes.
And I'm still only talking about a rather small / medium db.
I get those numbers on a 50 GB db. Imagine on a 50 TB system.
October 22, 2011 at 2:50 pm
sqlguy-736318 (10/22/2011)
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.Thanks accidental - table variables do seem to be the best for adhoc queries as opposed to online oltp queries because the SQL seems cleaner and more centralized.
Gosh... I see a lot of folks chimed in on this particular pair of posts and I hate to beat a dead horse, but nothing could be further from the truth in either of the posts above.
There's no need to drop Temp Tables in a stored procedure either at the beginning or the end. Once the stored procedure that created the Temp Table goes out of scope, the Temp Table will automatically drop. A Temp Table does not exists until it's created so there's no real need to do a conditional drop at the beginning of the sproc, either. Most people leave a conditional drop in just for the next person who has to troubleshoot the sproc.
An "image" of what the Temp Table looked like will remain for about the same time as the execution plan does so that if someone runs the proc again, it doesn't have to work as hard. That's not true with Table Variables.
Because of the potential problems with Table Variables appearing in the execution plan as only having a single row, table variables are hardly ever the right thing to do for ad hoc queries. Considering that SELECT/INTO is so easy to use for the creation of Temp Tables, I'd have to say that Temp Tables are actually easier to use for ad hoc queries. Once the session closes, the Temp Tables will also drop so there's really no need for a DROP Table even in ad hoc queries.
The two posts I quoted above are pretty typical of the misconcenptions surrounding Temp Tables and Table Variables. For a much more complete picture of both, please see the following article. I strongly recommend it to anyone who ever works with Temp Tables and Table Variables... even people who have a fair bit of experience in SQL Server. It's one of the best and most complete articles on the subject to data.
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply