<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Stan Kulp
»
Simplify Large Queries with Temporary Tables,...
40 posts, Page 1 of 4
1
2
3
4
»
»»
Simplify Large Queries with Temporary Tables, Table Variables and CTEs
Rate Topic
Display Mode
Topic Options
Author
Message
Stan Kulp-439977
Stan Kulp-439977
Posted Monday, August 08, 2011 10:06 PM
SSC Journeyman
Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 79,
Visits: 601
Comments posted to this topic are about the item
Simplify Large Queries with Temporary Tables, Table Variables and CTEs
Post #1156511
Josh Ashwood
Josh Ashwood
Posted Tuesday, August 09, 2011 1:30 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, May 20, 2013 5:52 PM
Points: 30,
Visits: 448
Nice article, and definitely on topic for real database developers...
However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ?
An 'It depends' answer would be ok, if expanded on!
Post #1156552
Patibandla
Patibandla
Posted Tuesday, August 09, 2011 1:37 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 2:17 AM
Points: 35,
Visits: 143
A very good article , it really enhances the performance just by using the identity columns in #Temp and ##Temp tables rather to using order by while inserting the rows , specifically when you nedd to loop through this data at some later point in the session.
Post #1156555
Andy Siantonas
Andy Siantonas
Posted Tuesday, August 09, 2011 2:29 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 12:20 PM
Points: 14,
Visits: 76
I'd echo the above point: would be very interesting to see some analysis of each technique performs.
Post #1156584
Joffrey
Joffrey
Posted Tuesday, August 09, 2011 2:58 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 29, 2013 4:15 AM
Points: 3,
Visits: 62
I use CTE queries a lot when building datasources for my reports. I prefer them over #temp tables and @tables (because there is more work involved creating those)
Once you go slightly overboard with CTEs you'll notice that technique isn't working for a specific problem. A CTE can be running fast, you make a slight change and it runs really really bad. That's where I usually start looking at a CTE > insert that set into a #temp table and finish it off with a SELECT or another CTE against the #temp table.
Those occasions are rare though.
Nice article, cheers.
Post #1156600
GilaMonster
GilaMonster
Posted Tuesday, August 09, 2011 3:20 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742,
Visits: 30,021
Josh Ashwood (8/9/2011)
However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ?
It depends. Really.
It'll vary with the size and complexity of what you're doing. Simpler queries will probably be better kept in one or with CTEs (if it makes things easier to read). More complex ones may benefit from being split up and run in bits.
The major difference that you must realise is that temp tables and table variables are both physical structures - they both store the data in an intermediate state. CTEs are not tables, they're just named subqueries, so they're not splitting out the processing, doing some now some later. It's still a single query.
Table variables, because of no statistics, can often be problematic on larger row counts (where anything over a couple hundred is probably large)
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1156618
ben.reese
ben.reese
Posted Tuesday, August 09, 2011 4:02 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, March 18, 2013 5:14 AM
Points: 15,
Visits: 47
My comment in agreement with what GilaMonster has said, is to be very carful do do proper performance and scale testing if you decide to use table variables, they can perform very badly on large data sets.
As an annecdotal example, I have inherited a number of very large and complex queries that use table variables, some of these were taking more than half an hour to run. Doing nothing to them other than changing the table variables to temporary table gets them returning in 30 seconds or so.
Post #1156644
amarlahon
amarlahon
Posted Tuesday, August 09, 2011 4:05 AM
Forum Newbie
Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 1,
Visits: 38
The article is really nice, but its very important to consider the performance before using a table variables, temp tables, or CTE's.
In terms of performance I think we should avoid using a temp table both in a stand alone query or in a SP, as the SQL optimizer can't reuse the execution plan for a query/sp with a temp table. It creates a new execution plan each time we reexecute the query/SP. In case of a large SP using a code block with a temp table is very much prone to face a performance drawback because the SQL optimizer is unable to reuse the execution plan each time we reexecute the SP with changed input parameters.
However we use tablevariable or CTE for the same requirement which doesn't hamper the performance.
Post #1156646
EdSwiedler
EdSwiedler
Posted Tuesday, August 09, 2011 6:02 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, March 12, 2013 9:16 AM
Points: 18,
Visits: 71
One thing that I have found that can cause a temporary table or table variable to perform better than a CTE is that you can create indexes on them. This will help when joining them to other tables in later processing. No, I have not actually captured statistics on this, but have noticed a perceived performance boost. As with everything SQL Server, this will all be impacted by other tasks running at the same time.
I will say that I have found that a series of CTE's, each building on the last, does better than the massive join's that I have encountered.
Post #1156742
George H.
George H.
Posted Tuesday, August 09, 2011 6:06 AM
Valued Member
Group: General Forum Members
Last Login: Saturday, May 04, 2013 5:21 AM
Points: 52,
Visits: 117
ben.reese (8/9/2011)
My comment in agreement with what GilaMonster has said, is to be very carful do do proper performance and scale testing if you decide to use table variables, they can perform very badly on large data sets.
As an annecdotal example, I have inherited a number of very large and complex queries that use table variables, some of these were taking more than half an hour to run. Doing nothing to them other than changing the table variables to temporary table gets them returning in 30 seconds or so.
I agree with ben.reese and GilaMonster...I also have run into situations where doing nothing more than moving sub-queries into CTEs or temp tables has increased performance drastically. You definitely need to test each situation as it comes up and just keep in mind that you have several options available.
Post #1156746
« Prev Topic
|
Next Topic »
40 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
