3 ms·

Interesting to think about how to guard against these cases where query optimisation leads to unexpected results. I mean this could lead to serious bugs. What

Interesting to think about how to guard against these cases where query optimisation leads to unexpected results. I mean this could lead to serious bugs. What can be a way to detect these using linters or in CI before they hit the production.

magicalhippo 1y ago
On a somewhat related note... We've moved to MSSQL due to several reasons including customer demand. We're experiencing the MSSQL query planner occasionally generating terrible plans for core queries which then gets cached, leading to support calls. Workaround for now has been to have our query geneator append a fixed-value column which and have it change the value every 10 minutes, as a cache defeat. Still, surprised the engine doesn't figure this out itself, like try regenerating plans frequently if they contain non-trivial table scans say. Or just expire cache entries every 15 minutes or so, so a bad plan doesn't stick around for too long.
- baq 1y ago
  It’s one of RDBMS peculiarities that you just need to learn about. Any half decent MSSQL course or book will tell you about the plan cache and the occasional gotcha exactly like yours. Time wasted debugging this issue by the unaware is probably in kilo man years by now. …but MSSQL is still a fantastic database, if you can afford it. Postgres and mysql come with their own set of gotchas, some of which need an actually decent book to be explained. (Note all the RDBMS manuals are decent books and everyone without exception should read at least the TOC of the db they’re using, which IME is still a rarity.)
  - magicalhippo 1y ago
    My point was more that I feel it's a discrepancy that the DB tries to be very clever, but seemingly doesn't self-evaluate its decisions enough to determine when it wasn't. Why should I have to guard against poor optimization decisions when the whole point is to just let the DB figure out the best way?
    - baq 1y ago
      Because the point isn't that the DB should figure everything out, as it is a baseless claim that it's a 'poor optimization decision'. The point is to allow you to meet your requirements within your constraints. You're trading some of your constraints for different ones (here - you don't want to implement your own database, so you're using one that's already built with its own requirements and constraints). A database is a database, not a holy grail. I haven't used MSSQL for some time now, but I'm sure there are tools which do exactly what you say you want, i.e. continuously monitor the DBMS and suggest actions or even act itself. This is usually done by a team of DBAs, though.
- rotis 1y ago
  To add to sibling comment. This sounds like a parameter sniffing. Read it up. It should help you understand the problem. It is a known issue with SQL Server. Or rather a tradeoff. You don't want to compile a new execution plan for every query. This would eat up CPU by itself.
  - magicalhippo 1y ago
    Yeah seems likely but haven't had time to really dig into it. For now our workaround works. So my point was that if they want to keep parameter sniffing as default then short cache expiration can at the very least mitigate the performance cliffs that can occur. Better to have a slow query for 10-15 minutes than for hours. Recompiling once in a while shouldn't have a significant performance impact. In general though I think in many cases Carmack's approach when he made Quake's renderer is a good one. That is, it's often better to use an algorithm which is on average slightly slower but has much lower variance in execution time, than one which is on average slightly faster but has much greater variance. Parameter sniffing seems to fall into the latter category.
dathinab 1y ago
in general end to end tests/whole system tests which mock/stub as little as possible and are focused on testing whole use-case flows is a good starting point (sadly in many teams also a constant uphill battle from convincing PM over allocate time to bootstrap it to making CI harder due to basically having to bootstrap a whole testing system including db etc. to educating devs to write such tests properly, instead of writing unit tests at a end to end test level (which rarely ends well). Optimal you then combine this with automatic collection and analysis of log traced to detect performance regression (which is hard but just because of the involved tooling but now it needs a CI env with reliable perf. characteristics e.g. not GitHub hosted GitHub action runners). Through in this case the problem is a trap in the behavior of `IN (...)` sub queries which probably is not specific to WITH clause and might be as deeply rooted as the SQL standard but a you have to pay for being able to read it I'm not sure.

magicalhippo 1y ago

On a somewhat related note... We've moved to MSSQL due to several reasons including customer demand. We're experiencing the MSSQL query planner occasionally generating terrible plans for core queries which then gets cached, leading to support calls. Workaround for now has been to have our query geneator append a fixed-value column which and have it change the value every 10 minutes, as a cache defeat. Still, surprised the engine doesn't figure this out itself, like try regenerating plans frequently if they contain non-trivial table scans say. Or just expire cache entries every 15 minutes or so, so a bad plan doesn't stick around for too long.

baq 1y ago
It’s one of RDBMS peculiarities that you just need to learn about. Any half decent MSSQL course or book will tell you about the plan cache and the occasional gotcha exactly like yours. Time wasted debugging this issue by the unaware is probably in kilo man years by now. …but MSSQL is still a fantastic database, if you can afford it. Postgres and mysql come with their own set of gotchas, some of which need an actually decent book to be explained. (Note all the RDBMS manuals are decent books and everyone without exception should read at least the TOC of the db they’re using, which IME is still a rarity.)
- magicalhippo 1y ago
  My point was more that I feel it's a discrepancy that the DB tries to be very clever, but seemingly doesn't self-evaluate its decisions enough to determine when it wasn't. Why should I have to guard against poor optimization decisions when the whole point is to just let the DB figure out the best way?
  - baq 1y ago
    Because the point isn't that the DB should figure everything out, as it is a baseless claim that it's a 'poor optimization decision'. The point is to allow you to meet your requirements within your constraints. You're trading some of your constraints for different ones (here - you don't want to implement your own database, so you're using one that's already built with its own requirements and constraints). A database is a database, not a holy grail. I haven't used MSSQL for some time now, but I'm sure there are tools which do exactly what you say you want, i.e. continuously monitor the DBMS and suggest actions or even act itself. This is usually done by a team of DBAs, though.
rotis 1y ago
To add to sibling comment. This sounds like a parameter sniffing. Read it up. It should help you understand the problem. It is a known issue with SQL Server. Or rather a tradeoff. You don't want to compile a new execution plan for every query. This would eat up CPU by itself.
- magicalhippo 1y ago
  Yeah seems likely but haven't had time to really dig into it. For now our workaround works. So my point was that if they want to keep parameter sniffing as default then short cache expiration can at the very least mitigate the performance cliffs that can occur. Better to have a slow query for 10-15 minutes than for hours. Recompiling once in a while shouldn't have a significant performance impact. In general though I think in many cases Carmack's approach when he made Quake's renderer is a good one. That is, it's often better to use an algorithm which is on average slightly slower but has much lower variance in execution time, than one which is on average slightly faster but has much greater variance. Parameter sniffing seems to fall into the latter category.

dathinab 1y ago

in general end to end tests/whole system tests which mock/stub as little as possible and are focused on testing whole use-case flows is a good starting point (sadly in many teams also a constant uphill battle from convincing PM over allocate time to bootstrap it to making CI harder due to basically having to bootstrap a whole testing system including db etc. to educating devs to write such tests properly, instead of writing unit tests at a end to end test level (which rarely ends well). Optimal you then combine this with automatic collection and analysis of log traced to detect performance regression (which is hard but just because of the involved tooling but now it needs a CI env with reliable perf. characteristics e.g. not GitHub hosted GitHub action runners). Through in this case the problem is a trap in the behavior of `IN (...)` sub queries which probably is not specific to WITH clause and might be as deeply rooted as the SQL standard but a you have to pay for being able to read it I'm not sure.