4 ms·

I really like jvns's work but I have to say I found this diagram misleading when I first saw it! SQL queries "happen" in whatever order the database optimiser d

I really like jvns's work but I have to say I found this diagram misleading when I first saw it! SQL queries "happen" in whatever order the database optimiser decides. I'm really glad the accompanying article has a bunch of qualifications of the form "Database engines don’t actually literally run queries in this order" but a lot of the beauty of these diagrams is that they work on their own. I wish the title of the picture said something more like "SQL queries are interpreted in this order" to make it clear we're talking about semantics and not what actually takes place.

SketchySeaBeast 7y ago
It really is "whatever the optimizer feels like". Pretty sure that's the first law of SQL performance optimization - "The optimizer is always your best friend and worst enemy".
- fennecfoxen 7y ago
  I've worked at a place where running time-sensitive batch file submission was the company's main line of business. It is always fun when the optimizer heuristic suddenly makes a 3-minute query take over 3 hours! And there's basically no way to defend against this while still using SQL, short of moving everything to incremental generation.
  - hobs 7y ago
    Query Hints. Simplifying code. Compiled stored procedures. Plan guides/fixed plans. If your code may run from 3m to 3 hours, its probably the SQL at fault.
    - SketchySeaBeast 7y ago
      Not having query hints isn't the "SQL at fault" - it's very much the optimizer, or you wouldn't need to override it with said hint. I've seen it where one index hit an arbitrary number of rows and then the query flipped to joining on another, non-indexed column, increasing the time to run massively - that sort of 3m to 3 hours magnitude. Had to throw on a hint but that's only because the optimizer decided it knew better.
      - mikepurvis 7y ago
        A slightly more charitable view of this situation is that an optimization which was previously triggering to make the query fast suddenly no longer applied.
        SketchySeaBeast 7y ago
        I think that might be too optimistic - it went from a table with thousands of records to hundreds of millions of records. If I were to speak technically the oracle optimizer "did a derp".
        PeterisP 7y ago
        So... stale statistics? I.e. the optimizer making the query plan assuming that the table has thousands of records (because that's what it had earlier) and now it has hundreds of millions of records? A sudden order of magnitude change in table sizes isn't normal operation, for most DB platforms that requires running some "everything's different now, please recalculate statistics" command to maintain proper functioning.
      - hobs 7y ago
        And that's why I listed a bunch of other options as well - simplifying code is probably the best one, but there will always be gaps in their heuristics. In my experience, you have one of three scenarios: You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective. Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options. Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much. In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.
  - NikolaNovak 7y ago
    Depending on why, static / manual statistics on tables (also normally a no-no) may also help. (This is assuming whatever applicable regular maintenance (stats, index or table reorgs if needed) is done prior to the time-sensitive batch... and understanding sometimes there's just not enough hours in a day to run batch AND maintenance :| )
deleted 7y ago
[deleted]
ketralnis 7y ago
She has a whole section called "queries aren’t actually run in this order (optimizations!)" SQL and other declarative languages can be frustrating to talk about because what you tell it you want and _how_ it does the work isn't supposed to matter. So there are two questions that you might answer with either jvsn's answer (how should you imagine it works to understand how to use SQL?) and your answer (how should you imagine it works to figure out why it's slow?). Someone learning SQL is probably asking the first question, somebody debugging performance is probably asking the second one. So this is a weird criticism because neither of you is incorrect, it's just that different audiences require different kinds of detail. If she instead wrote the answer to your question then someone else would inevitably come along with a comment nearly identical to yours except it would say "well that's just implementation details but you should instead imagine that...".
- Gormisdomai 7y ago
  > She has a whole section called "queries aren’t actually run in this order (optimizations!)" Yep! Article is a big improvement; first I saw the image was earlier today when it was tweeted on its own here: https://twitter.com/b0rk/status/1179449535938076673?s=20 https://twitter.com/b0rk/status/1179449535938076673?s=20 and her images are often awesome because they capture nuance in a tiny poster
zepearl 7y ago
Concerning the database optimizer: is it only my (wrong) subjective feeling, or did the one of Oracle become much more stubborn about following "hints"? https://en.wikipedia.org/wiki/Hint_(SQL) https://en.wikipedia.org/wiki/Hint_(SQL) Reason: I started dealing with Oracle when it was at v8 and I think up to including v9 when I specified a hint it was usually followed. Then later with v10 and v11 it progressively became more difficult and nowadays with v12 I'm having a really hard time (I usually have to use some "tricks" that don't change the logic of the SQL but do change its technical execution, like placing somewhere a useless but tactical "distinct", to confuse it enough so that my hints are more or less followed/used). Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited and the app will anyway be decommissioned in 1-2 years I cannot start rewriting half of the application to break down and improve the statements => when once per quarter the data distribution/quantity/etc... change and the optimizer thinks that it has a new brilliant idea about how to exec some SQL and then of course the SQL hangs then I usually just try to spend 1-2 hours trying to find some hint(s) that will bring back the old execution plan, but since we upgraded to Oracle12 I often see no change in the exec plan unless I do what I mentioned above.
- itwasntandy 7y ago
  This is somewhat intentional I think to guide you toward buying Enterprise edition, which since 11G includes plan baselines ( see https://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#PFGRF00702 https://docs.oracle.com/cd/B28359_01/server.111/b28274/optpl... ) - which allows you to lock a SQL queryID to a specific plan.
  - zepearl 7y ago
    Thx. At the company we're using EE, and I've heard about the plan locking functionality, but I never dared to use it. Does it survive DB-restarts? Additionally we have a setup of an active/primary cluster that is replicated to a passive/secondary one in our secondary datacenter (which then becomes leading in case of a disaster in the primary datacenter) => I don't think that a locked plan is replicated to the secondary cluster (which, in a case of a disaster would become a 2nd disaster as many SQL all of a sudden would stop working). But thanks for the hint :)
Sharlin 7y ago
She’s pretty clear that she means the order things happen semantically. Just like in any programming languages we talk of things happening in some order, but under the ”as if” rule the compiler and processor can reorder things however they want.

SketchySeaBeast 7y ago

It really is "whatever the optimizer feels like". Pretty sure that's the first law of SQL performance optimization - "The optimizer is always your best friend and worst enemy".

fennecfoxen 7y ago
I've worked at a place where running time-sensitive batch file submission was the company's main line of business. It is always fun when the optimizer heuristic suddenly makes a 3-minute query take over 3 hours! And there's basically no way to defend against this while still using SQL, short of moving everything to incremental generation.
- hobs 7y ago
  Query Hints. Simplifying code. Compiled stored procedures. Plan guides/fixed plans. If your code may run from 3m to 3 hours, its probably the SQL at fault.
  - SketchySeaBeast 7y ago
    Not having query hints isn't the "SQL at fault" - it's very much the optimizer, or you wouldn't need to override it with said hint. I've seen it where one index hit an arbitrary number of rows and then the query flipped to joining on another, non-indexed column, increasing the time to run massively - that sort of 3m to 3 hours magnitude. Had to throw on a hint but that's only because the optimizer decided it knew better.
    - mikepurvis 7y ago
      A slightly more charitable view of this situation is that an optimization which was previously triggering to make the query fast suddenly no longer applied.
      - SketchySeaBeast 7y ago
        I think that might be too optimistic - it went from a table with thousands of records to hundreds of millions of records. If I were to speak technically the oracle optimizer "did a derp".
        PeterisP 7y ago
        So... stale statistics? I.e. the optimizer making the query plan assuming that the table has thousands of records (because that's what it had earlier) and now it has hundreds of millions of records? A sudden order of magnitude change in table sizes isn't normal operation, for most DB platforms that requires running some "everything's different now, please recalculate statistics" command to maintain proper functioning.
    - hobs 7y ago
      And that's why I listed a bunch of other options as well - simplifying code is probably the best one, but there will always be gaps in their heuristics. In my experience, you have one of three scenarios: You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective. Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options. Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much. In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.
- NikolaNovak 7y ago
  Depending on why, static / manual statistics on tables (also normally a no-no) may also help. (This is assuming whatever applicable regular maintenance (stats, index or table reorgs if needed) is done prior to the time-sensitive batch... and understanding sometimes there's just not enough hours in a day to run batch AND maintenance :| )

deleted 7y ago

[deleted]

ketralnis 7y ago

She has a whole section called "queries aren’t actually run in this order (optimizations!)" SQL and other declarative languages can be frustrating to talk about because what you tell it you want and _how_ it does the work isn't supposed to matter. So there are two questions that you might answer with either jvsn's answer (how should you imagine it works to understand how to use SQL?) and your answer (how should you imagine it works to figure out why it's slow?). Someone learning SQL is probably asking the first question, somebody debugging performance is probably asking the second one. So this is a weird criticism because neither of you is incorrect, it's just that different audiences require different kinds of detail. If she instead wrote the answer to your question then someone else would inevitably come along with a comment nearly identical to yours except it would say "well that's just implementation details but you should instead imagine that...".

Gormisdomai 7y ago
> She has a whole section called "queries aren’t actually run in this order (optimizations!)" Yep! Article is a big improvement; first I saw the image was earlier today when it was tweeted on its own here: https://twitter.com/b0rk/status/1179449535938076673?s=20 https://twitter.com/b0rk/status/1179449535938076673?s=20 and her images are often awesome because they capture nuance in a tiny poster

zepearl 7y ago

Concerning the database optimizer: is it only my (wrong) subjective feeling, or did the one of Oracle become much more stubborn about following "hints"? https://en.wikipedia.org/wiki/Hint_(SQL) https://en.wikipedia.org/wiki/Hint_(SQL) Reason: I started dealing with Oracle when it was at v8 and I think up to including v9 when I specified a hint it was usually followed. Then later with v10 and v11 it progressively became more difficult and nowadays with v12 I'm having a really hard time (I usually have to use some "tricks" that don't change the logic of the SQL but do change its technical execution, like placing somewhere a useless but tactical "distinct", to confuse it enough so that my hints are more or less followed/used). Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited and the app will anyway be decommissioned in 1-2 years I cannot start rewriting half of the application to break down and improve the statements => when once per quarter the data distribution/quantity/etc... change and the optimizer thinks that it has a new brilliant idea about how to exec some SQL and then of course the SQL hangs then I usually just try to spend 1-2 hours trying to find some hint(s) that will bring back the old execution plan, but since we upgraded to Oracle12 I often see no change in the exec plan unless I do what I mentioned above.

itwasntandy 7y ago
This is somewhat intentional I think to guide you toward buying Enterprise edition, which since 11G includes plan baselines ( see https://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#PFGRF00702 https://docs.oracle.com/cd/B28359_01/server.111/b28274/optpl... ) - which allows you to lock a SQL queryID to a specific plan.
- zepearl 7y ago
  Thx. At the company we're using EE, and I've heard about the plan locking functionality, but I never dared to use it. Does it survive DB-restarts? Additionally we have a setup of an active/primary cluster that is replicated to a passive/secondary one in our secondary datacenter (which then becomes leading in case of a disaster in the primary datacenter) => I don't think that a locked plan is replicated to the secondary cluster (which, in a case of a disaster would become a 2nd disaster as many SQL all of a sudden would stop working). But thanks for the hint :)

Sharlin 7y ago

She’s pretty clear that she means the order things happen semantically. Just like in any programming languages we talk of things happening in some order, but under the ”as if” rule the compiler and processor can reorder things however they want.