3 ms·

I'd say it would be almost as bad. In SQL you're describing the result that you want -- you aren't (and shouldn't be) saying how to achieve that result. The e

I'd say it would be almost as bad. In SQL you're describing the result that you want -- you aren't (and shouldn't be) saying how to achieve that result. The engine goes off and (hopefully) finds the optimal way to create the result you are describing. When you break queries down too far, the engine has more trouble finding the optimal path. SQL Server can "see through" views and CTEs optimize them combined with a query but depending on the calculations it might have to generate and process intermediate data that it doesn't need. In my case above, the engine was reprocessing the same data set over and over, generating duplicate rows, and combining everything removing the duplicates. My query was significantly simpler but also, in a way, harder to follow. It wasn't as clear what the intent was. Where they had nice named CTE subqueries, I had to use comments.

emodendroket 4y ago
If writing CTEs is breaking the optimization I’d argue that’s also an example of not really meeting the “declarative” aspect of the design fully.
- wvenable 4y ago
  I agree but all systems have limitations and SQL engines get better at the job every year. The result of the query is say declaration X. In this case, imagine you can construct a query to formulate X with out a CTE. The database goes off and computes X from the data. But if you start adding CTE's for say readability or because you're building up the query piece by piece and testing it in stages. You might be adding additional declarations A, B, and C and combining them to ultimately get X. In theory, the database engine should be able to see that you're still trying to get X and perform the query that same way as if it was a single query without a CTE. And, in some cases, it can actually do that. But if A, B, and C are complicated enough then it's just unable to determine that it all equals X in the end. This is a fairly understandable limitation of the engine -- SQL can get pretty complicated. It's also making the assumption that all these CTE's are actually needed to compute the result. It took me a whole day to convert a CTE based query into a non-CTE based query and get all the same results in the same way.
  - emodendroket 4y ago
    Yes, the more complicated it is, the more likely the optimizer is to get confused... but also the greater the likelihood of bugs and the greater the benefit of something that makes your query easier to read.

emodendroket 4y ago

If writing CTEs is breaking the optimization I’d argue that’s also an example of not really meeting the “declarative” aspect of the design fully.

wvenable 4y ago
I agree but all systems have limitations and SQL engines get better at the job every year. The result of the query is say declaration X. In this case, imagine you can construct a query to formulate X with out a CTE. The database goes off and computes X from the data. But if you start adding CTE's for say readability or because you're building up the query piece by piece and testing it in stages. You might be adding additional declarations A, B, and C and combining them to ultimately get X. In theory, the database engine should be able to see that you're still trying to get X and perform the query that same way as if it was a single query without a CTE. And, in some cases, it can actually do that. But if A, B, and C are complicated enough then it's just unable to determine that it all equals X in the end. This is a fairly understandable limitation of the engine -- SQL can get pretty complicated. It's also making the assumption that all these CTE's are actually needed to compute the result. It took me a whole day to convert a CTE based query into a non-CTE based query and get all the same results in the same way.
- emodendroket 4y ago
  Yes, the more complicated it is, the more likely the optimizer is to get confused... but also the greater the likelihood of bugs and the greater the benefit of something that makes your query easier to read.