Part of my job is tuning complex queries: I’ve seen some recently with eye watering complexity. A post from Erik Darling explains how abstraction can be the cause of poor performance in SQL Server:
Sometimes it’s views, CTEs, or derived tables. Sometimes it’s functions. obviously functions can have a weirder set of effects, but the general idea is the same.
If you start chaining things, or nesting them together, you’re making the optimizer’s job harder and likely introducing a lot of overhead.
There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.
Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.
Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.
In addition, as your query becomes complex, the query optimiser eventually gives up and produces a less than efficient query plan because there are too many potential query plans to choose from.
Erik references Grant Fritchey’s post from 2012, The Seven Sins against TSQL Performance, which is still as relevant today.