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

T-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.