3 ms·
It was an honest question, professional to professional. I did not interpret your post as a personal attack nor did I intend my post as an attack on you. The
by mulmen 7y ago
It was an honest question, professional to professional. I did not interpret your post as a personal attack nor did I intend my post as an attack on you.
The last time I used MSSQL in anger I was an intern and Michael Jackson was alive. It’s been a while.
My question is: “what is it about your work that makes (or made) MSSQL a better choice than Postgres or something else, specifically because of CTE differences.”
- throwaway_pdp09 7y agoSorry. When you said "really proud" and "so great", it was perhaps an emotive choice of words. In the end the semantics of CTEs should be much the same throughout. There may be small differences, and perhaps larger ones such as being able to update through a CTE, something like with x as (...) update x set ... I don't know if PG supprts this, and it wouldn't break my heart if it didn't. It would be easy to work around. Also IIRC PG has 'materialized' and 'recursive' keywords. No biggie. So the semantics are substantially the same. Difference is, how the optimiser treats it. That means you will get the same results back but the time difference may be enormous. This explains it: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ... In my previous work, the predicate pushdown (which is the posh term for the optimiser rewriting the query as the above link demonstrates) made CTEs usable. Without them, performance would have destroyed the company. Can we get round it? Mostly, yes, but it would have been more work for us (meaning more human hours) which the DB optimiser should have saved us from. The only unique thing CTEs bring to the party is recursion. Otherwise they are no more than a convenience, albeit a great one. HTH EDIT: MSSQL has a good optimiser when it works. I recently wrote a trivial CTE and had it take a minute to run, WTF? Looked at the query plan, something was very wrong there, pulled out the CTE part and put it into a temp table, ran in one second. 60x speedup. No idea why, was not impressed though.