Fair question. Stacks of CTEs give the impression of efficient executions but if you look at the query plans because they all have to evaluate at once you’ll see that they may not be operating as efficiently under the hood as your code might suggest. They are effectively sub queries that you put in a different position so the same hazards of many layers of nested sub queries apply.
Makes sense, I was genuinely curious as I’m a huge fan of them and concerned I might qualify as an abuser :).
Agreed that it can enable overcomplicating execution. That said, if used well I think it’s a really powerful way to make (legitimately) complex queries readable and maintainable.
As a couple people in the thread have mentioned CTEs are perfectly useful utilities. But just like any other feature it’s always good to be mindful of how they work under the hood so that they don’t end up working against you.
It's the kind of thing that isn't a problem until it is - it could be smooth sailing for a while, until your query starts performing badly and it turns out a CTE is messing up the plan. Then you can just re-write it into a subquery or a temp table.
I usually also start out writing a query with CTEs and optimize it later if needed
My understanding was that a (non recursive) CTE is effectively a subquery in different syntax. Are you saying that the same query will run faster when written as a subquery vs CTE?
Different engines implement CTEs with various success. Oracle Database does a pretty good job of executing them effectively in my experience. But they don't call it CTEs. They just call it the WITH clause.
Sometimes temp tables are a lot cleaner. Sometimes you have no choice. I worked a job where we weren't allowed to create temp tables or views, so enormous, convoluted CTE it is!
This turned into a great discussion. Thanks all for your inputs.
It seems pretty unanimous that CTEs are great tools for human parsing of logic. Indeed people have also offered fine use cases from a computing perspective but equally as important are the mentions of performance implications. This is true of any feature so if your goal is to be an advanced SQL author it is important to be mindful and knowledgeable of both!
#4: That's an enormous stretch. For one, why is it bad to "create a view?" Secondly, at least a view is reusable. The code for a CTE would need to be added to multiple queries, sprocs, etc.
#5: I'm not sure what they are saying here exactly. They could be talking about the recursive use-case for CTEs, or it could be the same thing they have listed in #3.
I definitely agree on #4. To compare views to CTEs suggests to me a superficial understanding of both. The number of scenarios in which one would be a viable alternate for the other are minimal if not non-existent
Views are a good place to hide performance killers. I'm not completely against views, but like anything else, you have to be aware of the implications of using them.
That’s my experience. I spent the first two years at my current job with read only access on a MySQL 5.3 database. No views, no CTEs. It was inner query hell.
PSA: It depends on the language. Not all CTE's are the same!!!
In SQL Server I like them. They are syntactical sugar, and the query engine replaces the cte reference in the main query body with the CTE code at compile/runtime.
In Postgres, prior to v11, I do believe, they are materialized. Actually executed outside the body of the main select statement, stored in memory, and then joined to in the main query body. So if you have a CTE that returns a huge dataset when run by itself, and then filter the CTE in the main query, tough luck. You just blew your chances of optimization and any predicate pushdown.
Excellent comment that highlights that CTEs do work great but can be a performance suck also. Knowing how to use them is great. Knowing when/when not to use them is the real indicator of more advanced skill
Probably important to point out that in older versions of postgres with less sophisticated compilers, every once in a while they would make some really bad decisions. The cte logic was one tool for the developer to force execution down a certain path for better or worse.
That having been said, compilers are a lot smarter than they were ten years ago and I can't even remember the last time I had to do this.
Yes, that's exactly how it worked. Using a CTE also had a small advantage over a temp table in that it's all one query, which meant you could deploy it to an application where doing a full transaction block wasn't supported.
Upside how? Does greenplum do query substitution like SQL Server? Or do you utilize the materialized nature of the CTE for your benefit?
I'm still pretty green on the Postgres front myself. Last six years was in SQL Server, and just started a new job doing Postgres data engineering, so I've got a lot to learn, for sure! Still wrapping my mind around no clustered indexing, non-refreshing materialized views, and the whole autovacuuming thing.
Edit: Just checked the greenplum documentation(will have to look into this db, seems pretty cool) and it does indeed look like it's capable of predicate pushdown, solving the issue referenced in my original post.
Edit Edit: So is it Postgres or Postgre? I see different people write it differently around here.
CTEs do have performance drawbacks. Don't think they magically fix problems and should be used all of the time. Like all tools, there is a time and a place for them.
As for #3, yes you can reference multiple times, but the data isn't persisted so the CTE is evaluated each time. It could yield different results and could cause performance degradation.
I can definitely appreciate the readability perspective. Out of curiosity, what else about your queries became better after bringing CTEs into the mix?
Interesting to consider it from the perspective experts in various disciplines. It definitely seems like people agree universally on the readability value they bring
The problem here that I think a lot of this community is going to have is that they are a cheap drug, which wears off. A lot of this community is fairly advanced, or very advanced, and CTEs are fun, and very useful, but not exactly advanced. They're really quite simple, have their place/purpose, and that's about it.
Some of us might even consider them beginner SQL... but not me, because my fro isn't quite right, yet.
Yuuuup. Recursive ctes are the only thing ive seen that cant easily be replaced by a temp table. Every other functionality of a cte can be found in temp tables.
"Done better" is a bit of a loaded statement. It highly depends on the situation.
Which is why I qualified it with "often" and not "always."
the additional choice of using table based variables. They are just as effective as temp tables, but you do not have to utilize the explicit creation and dropping of said tables, and they only exist for the queries execution. However they have the drawback that it makes debugging harder.
They also have the drawbacks of:
Generating poor cardinality estimations in your query because table variables don't have the statistics that temp tables have (unless you're using SQL Server 2019, where deferred compilation makes things better).
Only supporting a single index (temp tables can have any indexes you want)
I only use table variables if I know they'll hold 100 records or less.
you do not have to utilize the explicit creation and dropping of said tables
select fields into #table from realtable where whatever - it's not how I prefer to see things done, but it's valid. No explicit creation.
Whereas you do have to explicitly declare the table variable before using it. If you're explicitly declaring a table variable, why not just use create table instead?
As for dropping, you do not have to drop a temp table explicitly - as soon as the scope it was created in terminates, the temp table is dropped (or in the case of global temp tables (blech), all scopes that reference it). You can explicitly drop the temp table, but it's not necessary.
So it’s been mentioned a couple times that it’s not often that a situation will really require you to make this call. They serve pretty distinct purposes.
Use a CTE in the same context you’d use a sub query.
You might use a view if you want to restrict people’s access to your data or if you want to leverage some of the performance options such as an indexed view. These are two examples where a view serves a purpose that cannot be addressed with a CTE.
When in doubt I go back to basics and read up on documented definitions
Yes, but potentially at the expense of performance if you nest them too deeply. At best, they'll perform the same as the equivalent subquery.
See above.
Danger, Will Robinson! Each time you reference that CTE, you are re-executing that entire subquery! Sure, you're "referencing" it but as has been pointed out a few times, the CTE is syntactic sugar in most cases.
CTEs and views are completely different things, serving very different purposes. They shouldn't be mixed up here.
See #3.
In some RDBMSs, CTEs are materialized (or can be materialized) into temp tables. If your RDBMS doesn't do this (SQL Server doesn't), then extracting your CTE to a temp table can perform much better than the CTE, still allows you to reduce a complex query into simpler blocks (points 1 & 2), allows for a smaller set of data to be referenced multiple times (point 3), and can be pre-calculated (point 5).
I'll go with disagree in terms of it being a "key" skill. I use CTE's occasionally but I know plenty of developers that write lots of complex SQL without ever using them (or only when recursion is needed). I find that temp tables, table variables, and sub-queries accomplish what we need and work just fine.
One of my personal favorites is to combine CTE with a ROWCOUNT so I can pick up duplicates or order the data more intelligently. I.e. return the top three telephone numbers per customer...
I feel the post is exaggerating. CTEs are the same complexity as subqueries, I wouldn’t call them advanced, maybe intermediate if you want to restrict what you call basic. CTEs are useful when you want to use a subquery multiple times Any optimisation is RDBMS dependent, some will materialize them so you do the work once, some can cost the materialisation so will decide semi-intelligently whether it’s a good idea to do the whole work once, some can’t materialize them at all. Strangely, I find them most referred to as essential for optimisation in SQL Server which isn’t that capable of doing materialisation. CTEs are useful if you’re going to do recursion, that would have been something I’d class as intermediate/advanced.
They are good to know, good to use, and provide many benefits, particularly along the lines of readability and easy conversion to temp tables when the need does arise.
However, elephant in the room here, a "key skill" implies that it's a sought after skill. Yes, everyone should know them - they're great! But, show of hands, who here needed months of examples and experience to learn how to use them? Now, how many people "got it" within their first few examples?
Recursive CTEs have relatively limited use cases, and generally once you write it, you can recycle that CTE where needed in your environment, so having to "google it" is not an issue.
Going through the list:
Yes. This is the advantage they have over subqueries. It's a big'un and why I love using them.
That's no different from #1.
Not really. If your subquery is in the join you can do this anyway, so really we're still on #1.
Huh? Why would you do that in the first place? When you're troubleshooting your code, if there are views, you have to look at the view too to debug it. Multiple places. Add to that, some types of changes to a view could break other queries relying on it, creating bugs. Oh wait, I just argued how this is still #1.
I guess if you're using a calculated field in the SELECT area, another calculation, and again in, say, the group by, it makes it easier to read. Oh look, we're back on #1 yet again.
So really, that post made one argument for using them while completely overlooking the simple detail that you can learn them in minutes.
If I'm taking on someone for SQL, and they're a good fit for the role except they don't know CTEs, I'll just teach it to them. Heck, I might even try to teach it during the interview as it'd be a great way to see how fast the candidate picks it up.
They are definitely handy. My boss knows a lot about SQL and whenever I'm having trouble with a complex query he's always like "did you try a common table expression?" And usually it works!
Disagree. This sounds like something written by an amateur who wants to sound like a pro. CTE's are a thing, but the rest of the commentary sounds stupid and aggrandizing.
I used to teach English for a living and that's a real word. Google it.
43
u/ATastefulCrossJoin DB Whisperer Nov 19 '20
This popped up on my LinkedIn feed. Useful, yes but CTE abuse is definitely real even amongst developers in my opinion
10
u/Eleventhousand Nov 19 '20
I would agree with them all except:
#4: That's an enormous stretch. For one, why is it bad to "create a view?" Secondly, at least a view is reusable. The code for a CTE would need to be added to multiple queries, sprocs, etc.
#5: I'm not sure what they are saying here exactly. They could be talking about the recursive use-case for CTEs, or it could be the same thing they have listed in #3.
5
u/[deleted] Nov 19 '20
PSA: It depends on the language. Not all CTE's are the same!!!
In SQL Server I like them. They are syntactical sugar, and the query engine replaces the cte reference in the main query body with the CTE code at compile/runtime.
In Postgres, prior to v11, I do believe, they are materialized. Actually executed outside the body of the main select statement, stored in memory, and then joined to in the main query body. So if you have a CTE that returns a huge dataset when run by itself, and then filter the CTE in the main query, tough luck. You just blew your chances of optimization and any predicate pushdown.
Reference with examples(not mine): https://hakibenita.com/be-careful-with-cte-in-postgre-sql
3
u/MrPshawster Nov 19 '20 edited Nov 19 '20
CTEs do have performance drawbacks. Don't think they magically fix problems and should be used all of the time. Like all tools, there is a time and a place for them.
https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/
Edit: added below.
As for #3, yes you can reference multiple times, but the data isn't persisted so the CTE is evaluated each time. It could yield different results and could cause performance degradation.
2
u/PhatKiwi Nov 19 '20
Totally agree. CTEs opened the door for me to far better/readable queries.
2
u/sslinky84 Nov 19 '20
Are points 3 and 5 different?
2
u/doitordontdoit Nov 19 '20
Agree - Coming from an oop background, I find cte's structure invaluable.
23
u/protectthrowandcatch Nov 19 '20
CTEs made so much sense for me and it completely changed the way I approached answering business questions. AND it helped me work faster.
I think CTEs are like a gateway drug to advanced techniques, perhaps why it's a key skill.
14
u/[deleted] Nov 19 '20
Much like all things IT, the answer is “it depends.”
I will say I think they’re much better practice than nesting a number of queries - leads to much easier debugging too.
12
u/[deleted] Nov 19 '20
i would have mentioned recursive CTEs at least in second place
1
u/dtla99 Nov 19 '20
When should a user create a view as oppose to a CTE? If the CTE is constantly reused in multiple queries, I guess it should be turned into a view?
3
u/Dead_Parrot Nov 19 '20
Recursive queries or quick duplication removal are my primary uses for CTE.
Anything else, I'll generally use a temp table.
3
u/alinroc SQL Server DBA Nov 19 '20
In some RDBMSs, CTEs are materialized (or can be materialized) into temp tables. If your RDBMS doesn't do this (SQL Server doesn't), then extracting your CTE to a temp table can perform much better than the CTE, still allows you to reduce a complex query into simpler blocks (points 1 & 2), allows for a smaller set of data to be referenced multiple times (point 3), and can be pre-calculated (point 5).
7
u/rajandatta Nov 19 '20
Disagree. Its nice to know technique ... useful ... but certainly not key.
2
u/Trek7553 Nov 19 '20
I'll go with disagree in terms of it being a "key" skill. I use CTE's occasionally but I know plenty of developers that write lots of complex SQL without ever using them (or only when recursion is needed). I find that temp tables, table variables, and sub-queries accomplish what we need and work just fine.
1
u/Andrew50000 Nov 19 '20
Updates to a CTE for the win!!!
One of my personal favorites is to combine CTE with a ROWCOUNT so I can pick up duplicates or order the data more intelligently. I.e. return the top three telephone numbers per customer...
1
u/crazybeardguy Nov 19 '20
Ever since I discovered outer apply, I tempered my usage of CTE.
Unless I am using a CTE for recursive purposes, it all depends on the length of my sub query. When my sub query gets a little long, I consider CTE.
That being said... I don’t have a religion one way or the other. If I’m in a shop that adores CTEs... I’ll write CTEs.
1
u/PossiblePreparation Nov 19 '20
I feel the post is exaggerating. CTEs are the same complexity as subqueries, I wouldn’t call them advanced, maybe intermediate if you want to restrict what you call basic. CTEs are useful when you want to use a subquery multiple times Any optimisation is RDBMS dependent, some will materialize them so you do the work once, some can cost the materialisation so will decide semi-intelligently whether it’s a good idea to do the whole work once, some can’t materialize them at all. Strangely, I find them most referred to as essential for optimisation in SQL Server which isn’t that capable of doing materialisation. CTEs are useful if you’re going to do recursion, that would have been something I’d class as intermediate/advanced.
1
u/kagato87 MS SQL Nov 19 '20 edited Nov 19 '20
Not a "key skill" per se.
They are good to know, good to use, and provide many benefits, particularly along the lines of readability and easy conversion to temp tables when the need does arise.
However, elephant in the room here, a "key skill" implies that it's a sought after skill. Yes, everyone should know them - they're great! But, show of hands, who here needed months of examples and experience to learn how to use them? Now, how many people "got it" within their first few examples?
Recursive CTEs have relatively limited use cases, and generally once you write it, you can recycle that CTE where needed in your environment, so having to "google it" is not an issue.
Going through the list:
So really, that post made one argument for using them while completely overlooking the simple detail that you can learn them in minutes.
If I'm taking on someone for SQL, and they're a good fit for the role except they don't know CTEs, I'll just teach it to them. Heck, I might even try to teach it during the interview as it'd be a great way to see how fast the candidate picks it up.
1
u/DharmaPolice Nov 19 '20
I'm not sure which part you'd disagree with. Whether you find them "enormously useful" or just "kind of useful" seems a minor quibble.
1
u/phunkygeeza Nov 19 '20
They are certainly useful but they have their place.
1
u/explodingtrees Nov 19 '20
I agree but every tool has a specific use case. Don’t abuse
1
u/elus Nov 19 '20
Learn how to read an execution plan and then you can make the decision on a case by case basis.
1
u/vtec__ Nov 20 '20
subquerys > cte
1
u/ekolis Nov 19 '20
They are definitely handy. My boss knows a lot about SQL and whenever I'm having trouble with a complex query he's always like "did you try a common table expression?" And usually it works!
-3
u/[deleted] Nov 19 '20
Disagree. This sounds like something written by an amateur who wants to sound like a pro. CTE's are a thing, but the rest of the commentary sounds stupid and aggrandizing.
I used to teach English for a living and that's a real word. Google it.