0

I have a very complex query involving 6 tables, needing to find max values, and counting items, as based on multiple columns in multiple other tables and then combining them under one column.

I've mostly got everything worked out, but basically had to use the WITH clause so many times I'm a bit concerned about runtime speed. I've probably used it 8+ times. Does anybody know how SQL actually calculates these on the backend? is it actually building a whole table, or is it basically just another wording for sub queries and joins? How many with clauses is too many? The counting and combining is why I have so many fwiw.

I used the WITH clause and would like a way to gage this solutions runtime speed compared to other options.

Alexander The Great
  • 13
  • 4
  • The "with clause" is called a CTE or Common Table Expression. – Dale K Mar 28 '23 at 21:59
  • 1
    How you structure your query has little to do with performance, because it doesn't tell you what the engine is doing under the hood. To investigate performance you need to inspect the execution plan. – Dale K Mar 28 '23 at 22:00
  • Does it really matter as you need the 8 subqueries, you should check every performance of each cte and the end result will get as fast as it can get – nbk Mar 28 '23 at 22:03
  • 1
    SQL Server compiles CTEs the same way as derived subqueries and views: it embeds them into the outer query. There may be table spools, but these are unrelated to the way you express the query. For further performance help, we need to see the query, the tables and indexes involved, and the query plan (you can share via https://pastetheplan.com) – Charlieface Mar 28 '23 at 22:35
  • 1
    With regard to performance, using CTEs is not directly a problem, but they are evaluated just like any other sub-query. So your complex query is the problem. There are simple tricks to minimise performance impact, like avoiding functions or creating function/expression based fields in the CTE and using those fields in filter or join criteria expressions. Good news is that CTEs are easy to convert to using temp tables or table variables. Those techniques might serve you better in this case, but each query is different. – Chris Schaller Mar 28 '23 at 23:04

0 Answers0