Re:Expressive Power of SQL (2003) [pdf]
by dspillett on Friday November 03, 2017 @10:31AM
(#15619438)
Be careful with CTEs with regard to performance, some SQL DBs can optimise by pushing predicates through to the inner queries, where for some (postgres being the one example that I know but there are probably others) a CTE presents an optimisation fence.
Re:Expressive Power of SQL (2003) [pdf]
by MarkusWinand on Friday November 03, 2017 @12:41PM
(#15620471)
PostgreSQL being the one noteworthy: http://modern-sql.com/feature/with/performance
Re:Expressive Power of SQL (2003) [pdf]
by nerdponx on Friday November 03, 2017 @12:54PM
(#15620561)
I never understood this. Why can't CTEs be transparently compiled into subqueries?
Re:Expressive Power of SQL (2003) [pdf]
by dspillett on Friday November 03, 2017 @01:09PM
(#15620683)
There is plenty of discussion about it out there (search for "postgres cte optimisation fence") including official word.
Note though that simply replacing CTE references with appropriate sub-queries simply won't work in the recursive case, and in some cases where a CTE is referred to multiple times the result may be an even less optimal query plan.
Re:Expressive Power of SQL (2003) [pdf]
by MarkusWinand on Friday November 03, 2017 @12:42PM
(#15620473)
Not to forget SQLite: https://sqlite.org/lang_with.html
Just so you know, CTE means in this context Common Table Expression. This StackOverflow question [0] can explain a bit about it. Can be used as pseudo-views to clarify sql code.
[0] https://stackoverflow.com/questions/4740748/when-to-use-comm...
Re:Expressive Power of SQL (2003) [pdf]
by kwillets on Friday November 03, 2017 @04:02PM
(#15621841)
To be fair, neither does Vertica. The big data warehouses are expecting fairly flat predigested fact tables.
Re:Expressive Power of SQL (2003) [pdf]
by __coaxialcabal on Friday November 03, 2017 @06:51PM
(#15622746)
Recursive CTEs have been implemented as UDx in Vertica, although possibly not available publicly. This is where you need to look carefully at can vs. should and carefully consider what you are trying to accomplish.
Re:Expressive Power of SQL (2003) [pdf]
by __coaxialcabal on Friday November 03, 2017 @01:19PM
(#15620753)
Recursive CTEs are significantly more difficult to implement in an MPP platform than SMP.
However, most other DBs do (e.g. Postgres, MS SQL, MySQL 8.0, MariaDB 10.2, Oracle, etc.).
Which is disappointing, given how powerful BQ and Presto/Athena are for startups.