By now, you have probably heard of CTEs
And you may have even heard them referred to as Inline Views. Really, an Inline View can be any type of derived table. It’s very easy to illustrate when one may turn into a performance problem with CTEs, if you aren’t careful.
A lot of people think that when you call a CTE, the results are somehow persisted in a magical happy place and the underlying query just hangs back admiring the output as it sails into the upper deck.
Take the following example, which serves no real purpose.
|
1 2 3 4 |
WITH t1 AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 ) SELECT * FROM t1; |
It has a perfectly reasonable execution plan, and will lead a happy life.
I am from the future
Now, let’s join that to itself.
|
1 2 3 4 5 6 |
WITH t1 AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 ) SELECT * FROM t1 JOIN t1 AS t2 ON t2.Id = t1.Id; |
Doesn’t get much easier than that. But what happened with the plan?
Huh. That’s a whole other index operation. So just like when you join to a view, the view has to be executed and returned. In fact, if you keep throwing joins that reference the original CTE, you’ll keep getting more index operations.
|
1 2 3 4 5 6 7 8 |
WITH t1 AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 ) SELECT * FROM t1 JOIN t1 AS t2 ON t2.Id = t1.Id JOIN t1 AS t3 ON t3.Id = t1.Id; |
Did someone say they wanted another index operation? Because I thought I heard that.
To sum things up, CTEs are a great base
From which you can reference and filter on items in the select list that you otherwise wouldn’t be able to (think windowing functions), but every time you reference a CTE, they get executed. The fewer times you have to hit a larger base set, and the fewer reads you do, the better. If you find yourself referencing CTEs more than once or twice, you should consider a temp or persisted table instead, with the proper indexes.
10 Comments. Leave new
What are the odds? A blog post on CTEs the same day as I write a blog post on CTEs…
http://sqlblog.com/blogs/adam_machanic/archive/2015/04/07/re-inventing-the-recursive-cte.aspx
Yours is a little more detailed, I suppose 🙂
Between these two I’ll probably put the post I’ve been working on about CTE’s on the back burner for a bit….
Thank You Thank You Thank You for wiriting this. I have heard so many people exclaim that CTE’s are the solution for all queries. In my experice and my co wokrers experence it seesm there is a very narrow window in which they are helpful and generally a Temp Table or a table varaible is a much better solution
It appears to be CTE week. Great post Erik!
The trick is to have small result sets returned by the CTEs, which means that an ideal situation is where you can put some nice, restrictive parameters/criteria into the CTE portion of a query.
CTEs have been described as “sugar for syntax” because they are great for making complex queries readable and understandable. When appropriate I have written deeply nested CTEs with multiple CTE references that performed just great, so I am tired of hearing about how we have to avoid CTEs. Sometimes you have to fall back on temp tables or table variables when the CTE row counts get too high. Fine. Use the right tool for the job!
Thanks for listening.
Great post Erik. I loved CTE’s because it helped to make your code more “read-able”. But the performance issues (not assigning the proper amount of RAM, and the one you describe) has made me switch to using tables I call “IMP”. I tend to dislike temp tables because that gets sent to tempdb, and we all love to visit that place…lol. Also given you code temp tables may not follow unless you make them global.
This is an awesome post. I spend quite a bit of time rewriting CTE-based queries to use temp tables instead.
While I do get the argument that they make code more readable, temp tables do the same thing for me.
I heart CTEs but have indeed had them do alternately wonderful and harrowing things to my execution plans.
I may be alone in this but there have been a few times that I’ve come into a procedure, created a one-row CTE for the fifty or so seemingly arbitrary parameters to live in, and cross joined the bulk of the query to that rather than the parameters. Referring to the CTE columns seems a lot more readable to me, and something about a big list of variable declarations in SQL rubs me up the wrong way.
I’ve never seen a performance drop from doing this. In fact many times I see performance improvements, but that’s usually because it turns out that about two-thirds of those variables weren’t referenced anywhere and the original assignment queries were junk.
> Kendra says: It drives some people nuts if you preface your CTE with a semi-colon to make sure the prior statement was properly terminated. But I won’t judge you if that’s what you like to do.
Aaaaaaaaaaaaaaaaaaaaaaaaaa no don’t do that!
It’s even so easy to do properly these days with SQL Prompt’s automatic “insert semicolons” feature (and other tools probably have it too).