February 23, 2011 at 3:02 pm
I am working on a proc which works most off a temp table. We are seeing performance issues with that procedure. I see in the code ( its not my code:-P) that a temp table (#) is cleared and later in the proc it does some inserts,updates,deletes, joins..etc.I think if use a dynamic table (@) instead of temp table(#) i will see some performance improvement because temp table (#) is in temp db and dynamic table (@) is in memory. Any advice? Only limitation i think i cant create an index on dynamic table (@)
February 23, 2011 at 3:14 pm
this is one of the misconceptions of sqlserver temp objects.
- http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-myths-debunked-part-1/
- http://www.scarydba.com/2009/10/13/table-variables-are-only-in-memory-fact-or-myth/
There is another forum thread with the same question:
http://www.sqlservercentral.com/Forums/Topic923309-146-1.aspx
and of course there is an article at SSC:
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 23, 2011 at 3:24 pm
I would look to tune to batch instead of worrying about the a temp table v table variable. They can both spill into tempdb, depending on size.
I might look to post the code and/or exec plan and get some advice on tuning the SQL.
February 23, 2011 at 3:30 pm
ALZDBA is correct, both temp tables and tables variables needs to be stored in tempdb under some circumstances. However, there are some differences in relation to performance to when it comes to table variables vs temporary tables, e.g. operations on table variables are not logged, statistics are not created on them, stored procedures using them could be precompiled (while sps with temp tables cannot) etc.
What are the actual performance problems you are experiencing? Most probably it's related to the query itself rather than using temporary tables.
Andreas Goldman
February 23, 2011 at 3:44 pm
If you want to, you can just post the execution plan and we'll see what we can come up with 🙂
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 23, 2011 at 9:52 pm
Andreas Goldman (2/23/2011)
operations on table variables are not logged
Myth, not true
http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
stored procedures using them could be precompiled (while sps with temp tables cannot) etc.
Stored procedures are never pre-compiled. If you mean that using table variables allows plan reuse, kinda partially true, but temp table does not automatically cause a recompile any more.
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
February 24, 2011 at 1:11 pm
For a small number of rows you won't see any signifiacnt performance diference, but as the rowcount goes up you will find temp (#) tables perform better than table variables (@).
One reason is the optimiser sees the table variable as a variable and assumes it can only have 1 row, so optimisation for things like joins is based on this. For a few rows it isn't a problem, but over about 100 rows you start seeing the difference.
Another issue to consider is the indexing limitation on table variables vs Temp tables. Table variables can use primary key constraints and unique constraints to ad indexes, but you can't have a simple, non-clustered index. Something I've done often is define a temp table in a stored proc, load data in it, then add the indexes I want to improve performance.
You may want to look at thi site: http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
February 25, 2011 at 8:01 am
thanks everyone. I will get back here with some updates. Thanks
February 25, 2011 at 8:11 am
If you are doing DML statements, it is normally quite handing to have a PRIMARY KEY declared on the TEMP(#) table
February 25, 2011 at 8:47 am
For a small number of rows you won't see any signifiacnt performance diference,
That is absolutely not true in all cases. Take a table where 50% of the rows have a key (such as customerid) that is a single value. All the remaining rows are onesies and twosies. Now create a table variable and put a single value in it and join that table to the big customerid table. The optimizer will invariably choose an index seen and bookmark lookup nested loop plan, regardless of if the single value is for the 50%-total-rows customerid or a onesie because you cannot have column STATISTIC on the value (unless it is PK). Temp table CAN have stats, and you will get table scan/hash for 50% value (optimal plan) AND get the nested loop seek join for onesie/twosie value.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2011 at 8:49 am
grahamc (2/25/2011)
If you are doing DML statements, it is normally quite handing to have a PRIMARY KEY declared on the TEMP(#) table
I disagree with this. I cannot count the number of times I have REMOVED PKs from temp tables at clients because they serve no purpose in the subsequent usage of the temp table but have TREMENDOUS overhead when they are defaulted because they are CLUSTERED.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2011 at 12:36 pm
Related to this topic, I've tried to find an appropriate time to use table variables and I still haven't found a case where a normal table is not significantly faster with the proper indexes. So, does anyone have an example where a table variable actually performs better than an indexed table?
February 25, 2011 at 1:29 pm
SpectralGhost (2/25/2011)
Related to this topic, I've tried to find an appropriate time to use table variables and I still haven't found a case where a normal table is not significantly faster with the proper indexes. So, does anyone have an example where a table variable actually performs better than an indexed table?
I like that they can be used as an input parameter in procs in SQL 2008. Very useful for passing results of things like multi-selects.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2011 at 1:46 pm
SpectralGhost (2/25/2011)
Related to this topic, I've tried to find an appropriate time to use table variables and I still haven't found a case where a normal table is not significantly faster with the proper indexes. So, does anyone have an example where a table variable actually performs better than an indexed table?
Well, performance and usage of table variables are different issues. There are some places where you can only use table variables (table-valued functions, parameters to stored procedures).
If you look at the SSC article that ALZDBA linked to (http://www.sqlservercentral.com/articles/Temporary+Tables/66720/[/url]), in the discussion is someone who gives a specific example of a routine that starts off utilizing table variables, and mid-way through shifts over to using temporary tables. It all comes down to: test both for each situation, and pick the appropriate one for your use.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply