T-SQL Tuesday #018 – CTEs – They’re not always good for you

LogoT-SQL Tuesday is a monthly event on the second Tuesday of each month; various SQL Server bloggers write about a specific topic.  In this case, the subject, chosen by Bob Pusateri (blog | twitter), is CTEs or “Common Table Expressions”.  Bob seems to think that we can get bonus points for mentioning that CTE is also the abbreviation for “Coefficient of Thermal Expansion”, but as this doesn’t fit with the theme of my post, I won’t do that.  Oh wait…

Oh look – I’ve already done a CTE post for T-SQL Tuesday 016, where I used a CTE to show the rate of growth of the database backups…  No?  Am I not allowed to repost?  Okay, try this.

Background information

CTEs can be thought of as one-shot Views or temporary tables, valid only for the duration of the query that follows their definition.

I have a query that I use to generate reports on database file activity – useful for planning I/O stuff.  This query involves at its heart a view that contains a CTE; this view is called by a second view several times in order to calculate sums, averages and maxima and so on for the number of I/O operations and the amount of data being transferred – useful information capacity planning for the storage system.

Yes, and…?

However, the problem with writing queries like that is that what starts out as a relatively simple query plan

suddenly becomes a lot more long-winded

See that repeating group on the right?  That’s the CTE being generated 8 times.  This, obviously, has a knock-on effect on the execution time of the report that’s built upon that view.  Here’s the execution plan for the report query itself:

Not that much more complex than the view itself, but it all hurts helps.

So, what is a DBA to do?  In this case, it was easy.  Re-architect the query run by the report.  Use the view (and other CTEs within the query), but use them to populate temporary tables.  This, in my case, took the report run-time down from over 2 hours to under 40 seconds, 60% of which is building a temporary table version of that mahoosive CTE.  I can live with that, and the messier execution plan.

CTEs do have their place, even in this situation – they made development of that report enormously easier; however, due to their nature of being executed every time they are referenced within a query, they do have their drawbacks.

Advertisements
This entry was posted in SQLServerPedia Syndication, T-SQL Tuesday and tagged , , . Bookmark the permalink.

5 Responses to T-SQL Tuesday #018 – CTEs – They’re not always good for you

  1. Bob Pusateri says:

    Thanks so much for the great T-SQL Tuesday post! It’s always good to see examples of how things can quickly become sub-optimal and what can be done to bring them back in line.

    • thomasrushton says:

      Hi Bob – Thanks for swinging by, and thanks for hosting this month’s T-SQL Tuesday.

      On a point of information – it took less time to fix the query than it took for the query to run while writing this post!

  2. Hello mate

    I was glad you posted this… I was going to do a second post on ‘5 things I should have mentioned in my first post’ – then I could only think of 3. And one of them was the repeated materialisation problem… And you covered that pretty well here 🙂

    M

  3. Pingback: T-SQL Tuesday #18 Wrapup | Bob Pusateri - The Outer Join

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s