What you're saying is analogous to "My horse won't run when I tie its legs together." You'll see what I mean here and hopefully it helps you. Not sure which en

What you're saying is analogous to "My horse won't run when I tie its legs together." You'll see what I mean here and hopefully it helps you. Not sure which engine you're using, but for MSSQL at least, the engine writes stuff to disk where it can index it so that it can avoid table scans on retrieval. That is ideal where you have big joins or large tables. And is generally good for most use cases. When you use CTE's you're telling the engine, "I want this in memory, don't do any indexing." That's awesome for write heavy workloads. But if you plan on joining the results of the CTE later, you will find that you can't read from it very quickly because for every row joined you must scan the entire table. Now you'd think that since it's also forcing the data to stay in memory (if there's enough) that this would be faster. But that's not true. There's compute power at work to make comparisons on each row. The point of indexing is to reduce the required compute power by reducing the number of rows that you need to compare. With that in mind, it's obvious that it kills performance if you just told your engine to not use indexes but do a big join on a large set of data. Temporary tables are a better choice for read heavy workload compared with CTEs. MSSQL will index temp tables automatically. And in very rare cases where the automatic indexes aren't performant, then you can index them manually with a few extra keywords. Happy querying.