Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simplify Large Queries with Temporary Tables, Table Variables and CTEs


Simplify Large Queries with Temporary Tables, Table Variables and CTEs

Author
Message
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1094
Comments posted to this topic are about the item Simplify Large Queries with Temporary Tables, Table Variables and CTEs
Josh Ashwood
Josh Ashwood
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 535
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!
Patibandla
Patibandla
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 178
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.
Andy Siantonas
Andy Siantonas
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 81
I'd echo the above point: would be very interesting to see some analysis of each technique performs.
Joffrey
Joffrey
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 111
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47241 Visits: 44377
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, MVP, M.Sc (Comp Sci)
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


ben.reese
ben.reese
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
amarlahon
amarlahon
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 132
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.
EdSwiedler
EdSwiedler
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 94
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.
George H.
George H.
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 118
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search