It’s a common misperception that CTE queries are staged in memory or are “persisted data sets”. Not true. They’re simply syntactic sugar that SQL will in-line into your query. CTEs make queries easier to read but do not offer a performance benefit. They’re more like ad-hoc views than temp tables.
I made an account to say this. It's a really important distinction and will murder your performance sometimes.
I've seen the optimizer treat a CTE as a correlated subquery executed for every row in a nested loop execution plan.
CTE's are very easy to read, though. It is my default strategy for writing complex queries, and I'll only switch to #temptables if I see a performance issue with the CTE.
Also, sometime SQL Server's in-memory table variables can be useful. They are limited by memory but fast and they require no special permissions (I think?).
Thanks for the clarification regarding tempdb. It seems that as of SQL Server 2017 you can create in-memory table variables. docs.microsoft.com/en-us/sql/relat...
Very much RDBMS dependent in this respect, and I appreciate that this article and the responses focus on SQL Server.
In Oracle a CTE can be persisted by the query optimiser, and the decision is likely to be influenced by its expected size. The data is written to a temporary tablespace. Of course it is also unindexed.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
It’s a common misperception that CTE queries are staged in memory or are “persisted data sets”. Not true. They’re simply syntactic sugar that SQL will in-line into your query. CTEs make queries easier to read but do not offer a performance benefit. They’re more like ad-hoc views than temp tables.
I made an account to say this. It's a really important distinction and will murder your performance sometimes.
I've seen the optimizer treat a CTE as a correlated subquery executed for every row in a nested loop execution plan.
CTE's are very easy to read, though. It is my default strategy for writing complex queries, and I'll only switch to #temptables if I see a performance issue with the CTE.
Also, sometime SQL Server's in-memory table variables can be useful. They are limited by memory but fast and they require no special permissions (I think?).
Table variables are not in memory only objects. They are logged in temodb. Table variables only last for execution and don't generate statistics.
Thanks for the clarification regarding tempdb. It seems that as of SQL Server 2017 you can create in-memory table variables.
docs.microsoft.com/en-us/sql/relat...
Very much RDBMS dependent in this respect, and I appreciate that this article and the responses focus on SQL Server.
In Oracle a CTE can be persisted by the query optimiser, and the decision is likely to be influenced by its expected size. The data is written to a temporary tablespace. Of course it is also unindexed.