Typically I advise people to use CTE's for ad hoc queries and to avoid CTE's for production code. CTE's are easily abused.
Modern SQL: With – Organize Complex Queries
41–50 of 63 posts
Re: Modern SQL: With – Organize Complex Queries
#42If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or function, if it performs a write). Now you can independently test and reuse that view, which you can't do with a CTE. (Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)
Probably 30% of my queries are complex enough to have a CTE but none of them should be views. There is no reuse and views are opaque and exist in a single namespace. Views have their purpose but this isn't it.
> There is no reuse and views are opaque and exist in a single namespace.
I'm not sure what you mean by "opaque"; they encapsulate logic, which is a plus.
On the "single namespace" thing, that sort of depends on the RDBMS; e.g., in postgres, within a single DB, one can have multiple schemas, and there is nothing stopping a view in one schema from referencing objects in another schema, so views for one database may exist in separate schemas, which act as namespaces. Other DBs offer equivalent functionality, though the names of structures may be different.
Re: Modern SQL: With – Organize Complex Queries
#43Typically I advise people to use CTE's for ad hoc queries and to avoid CTE's for production code. CTE's are easily abused.
Easily abused and can throw the optimizer for a loop, especially if using them to reference within the CTE (not a recursive CTE but joining back).
Re: Modern SQL: With – Organize Complex Queries
#44If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or function, if it performs a write). Now you can independently test and reuse that view, which you can't do with a CTE. (Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)
Allow me to disagree. Before CTEs I would have agreed, but CTEs gain you the same in clarity (decomposition really helps with comprehensibility of complex queries) without the loss of having to chase down 5 different views in order to understand the one you're looking at. My rule is to refactor into a view when and only when it's going to be reused. > Postgres optimizes across views, but not across CTEs Sadly true, a…
> Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope.
I've read quite a few people on the email lists claiming that this is a benefit of CTEs. How they justify that, I don't know. Sure, you get predictability of performance, but I have never once written a query worried about how predictably it performed, instead of how fast it performed.
Re: Modern SQL: With – Organize Complex Queries
#45Earlier quoted context omitted.
Allow me to disagree. Before CTEs I would have agreed, but CTEs gain you the same in clarity (decomposition really helps with comprehensibility of complex queries) without the loss of having to chase down 5 different views in order to understand the one you're looking at. My rule is to refactor into a view when and only when it's going to be reused. > Postgres optimizes across views, but not across CTEs Sadly true, a…
> Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope. I've read quite a few people on the email lists claiming that this is a benefit of CTEs. How they justify that, I don't know. Sure, you get predictability of performance, but I have never once written a query worried about how predictably it performed, instead of how fast…
It's a bizarre side-effect of lacking query hints. This is my interpretation of the sequence of events:
1. Postgres will never implement query hints, because query hints are evil
2. In the real world people need them anyway, so they (ab)use implementation details that offer some control over the query plan (WITH is one example, see also OFFSET 0 and other hacks)
3. Everyone starts relying on these implementation details, e.g. you'll find people actually recommending this as a way to optimize queries in the mailing lists
4. Now when someone asks to fix the planner, someone else will point out this will break all the queries that have been "optimized" by depending on the old behavior
Postgres is generally a fairly sane project though, so I have some confidence they will come to their senses. Eventually.
Re: Modern SQL: With – Organize Complex Queries
#46Updatable CTEs are great, for example if you want to set a column to 1 where the date is the latest day for each value in a different column, you would do something like this ;WITH cte AS(SELECT ROW_NUMBER() OVER(PARTITION BY SomeVal ORDER BY SomeDate DESC) AS row, * FROM SomeTable) UPDATE cte SET id = CASE WHEN row = 1 THEN 1 ELSE 0 END Here is the DDL and DML in case you want to play around with this CREATE TABLE S…
So I've never come across INSERT table SELECT values UNION ALL SELECT values. Any reason to prefer that over INSERT INTO table (col, col) VALUES (v1, v2), (v1, v2), ... ?
One advantage of this syntax is that you can just run the select part, look at the data to make sure it is correct and then finally run the whole statement
Re: Modern SQL: With – Organize Complex Queries
#47Markus Winand is a great SQL expert. I'm really grateful for everything that he shares with us. He writes well, explains us the differences between the different SQL engines. And his books are fantastic references. I highly recommend his "No to offset" tutorial http://use-the-index-luke.com/no-offset Should be common knowledge, but I still see offset being used way to often, even in core ORM frameworks :-(
A summary: "don't use offset unless you need to be able to navigate to specific pages, in which case you have to use offset". That "navigate to specific pages" use case is pretty common.
If page numbers are important to you, then you have to find a different method anyway because of the drifting issue.
Re: Modern SQL: With – Organize Complex Queries
#48Markus Winand is a great SQL expert. I'm really grateful for everything that he shares with us. He writes well, explains us the differences between the different SQL engines. And his books are fantastic references. I highly recommend his "No to offset" tutorial http://use-the-index-luke.com/no-offset Should be common knowledge, but I still see offset being used way to often, even in core ORM frameworks :-(
A summary: "don't use offset unless you need to be able to navigate to specific pages, in which case you have to use offset". That "navigate to specific pages" use case is pretty common.
Yes, but people need to understand how expensive it is to implement.
Re: Modern SQL: With – Organize Complex Queries
#49Earlier quoted context omitted.
Allow me to disagree. Before CTEs I would have agreed, but CTEs gain you the same in clarity (decomposition really helps with comprehensibility of complex queries) without the loss of having to chase down 5 different views in order to understand the one you're looking at. My rule is to refactor into a view when and only when it's going to be reused. > Postgres optimizes across views, but not across CTEs Sadly true, a…
> Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope. I've read quite a few people on the email lists claiming that this is a benefit of CTEs. How they justify that, I don't know. Sure, you get predictability of performance, but I have never once written a query worried about how predictably it performed, instead of how fast…
For the record, I have also read a quite a few people on the mailing lists strongly disagreeing with those claims.
Re: Modern SQL: With – Organize Complex Queries
#50Earlier quoted context omitted.
A summary: "don't use offset unless you need to be able to navigate to specific pages, in which case you have to use offset". That "navigate to specific pages" use case is pretty common.
Yes, but people need to understand how expensive it is to implement.
Yeah, sorry if I seemed to suggest otherwise. I thought it was a good article, and it should be more widely understood that offset isn't a free lunch.