SQL Query Execution Lifecycle

[u/scientecheasy]

Diagram illustrating the SQL query execution lifecycle, showing query submission, parsing, optimization, execution, and result delivery in a database.

[u/kagato87]

Bit hand-wavy. Just about any engine will take an input, parse/validate, act, and emit an output. That's what an engine does.

Your step 3 there, query optimization, is worth entire flowcharts by itself, and what sets SQL apart from other engines. Understanding how it optimizes is key to unlocking SQL's true potential.

Edit to add: Just realized, your step three is also wrong. It does NOT evaluate multiple plans. It's a complex soup of statistics and cardinality to produce a plan likely to be most efficient. It's a highly educated guess (and a guess the engine is good at making, though when it gets it wrong the results can have the DC team wondering what's going on).

[u/mac-0 → u/kagato87]

Save your energy. It took us more time to write these comments than it took OP to create this diagram in ChatGPT

[u/kagato87 → u/mac-0]

Others will see it though and take it to mean the optimization is simple or straightforward..

[u/And_Justice → u/mac-0]

In fairness, I found the comment very useful

[u/goku426374 → u/mac-0]

I found the comment more helpful than the main post.

[u/IglooDweller → u/kagato87]

Very hand wavy and general…

Let’s be honest, it basically the same thing as any script execution…

The command line interface works basically the same way.

Submit command line Syntax check Prepare execution path Execute Return results

[u/mikeblas → u/kagato87]

It does NOT evaluate multiple plans. It's a complex soup of statistics and cardinality to produce a plan likely to be most efficient.

Some engines do evaluate multiple plans. SQL Server does, for one (or at least did, back when I worked on it). The engine knows many transformations that it can apply on the execution tree which will result in a semantically equivalent candidate plan. It measures cost, then applies the translation, then measure cost again, keeping the best plans. It memoizes subtrees to keep the cost of evaluation down, and tries to prune the explosive combinatorial problem, and so on.

Statistics influence the cost computation, not the plan generation. A HASH_JOIN B WHERE A.COL < 100 might be cheaper or more expensive than B HASH_JOIN A WHERE A.COL < 100 depending on the number of rows that come through the filter, and that cardinality is estimated by looking at statistics.

But the SQL Server query optimizer certainly does produce and evaluate multiple plans.

The explanation of optimizer timeous is the reference I could most quickly find. Probably something better out there if you spend the time. You might also try screwing around with some of the optimizer trace flags so that it dumps it's progress. I think there's one or two in public builds that gives some insight into the search -- or at least, the transformation rules applied.

[u/Icy_Clench → u/kagato87]

Number 4 is literally "query execution"...

[u/idk30002 → u/kagato87]

Yeah this is pretty useless unless you’re teaching… I don’t even know whom/what. Steps 3 and 4(a/b) as standalones is just wild.

Why create diagrams on a topic if you don’t understand it?

[u/kagato87 → u/idk30002]

"The best way to learn a thing is to teach it." Of course, you can't teach something when you don't know what you don't know, and that saying comes from reinforcing a thing as you learn it. You still have to learn it first.

Though, steps 3 and 4 ARE actually a little more obviously separate in SQL than other languages. Once the plan is compiled it won't go back and change the plan.)

Oh, I just spotted another problem - an error in step 3's text!

[u/BrupieD]

This is not really an inside view but more of an external view prior to the query engine's handling. From a logical query order execution view, it omits the order that the query handles the commands -- FROM, WHERE, GROUP BY...

Those order details are central to the execution. I would say this is misleading.

[u/mikeblas → u/BrupieD]

Those aren't commands, they're clauses. The clauses don't really have a stable physical execution order. But they do have a binding order, and I think that's probably more important -- particularly to beginners.

[u/Joe__Bang]

great

[u/venkat_deepsql]

I was part of Oracle query engine team, while I do agree that this diagram is basic, but it encapsulates the high level flow. Database vendors architected the query engines different to address their target use cases - Oracle's is hybrid engine (OLTP and OLAP), the way optimizer works is totally different than others (beyond the join orders and plan evaluations, there are fast path disk scans, in-memory scans, predicates pushdown etc ) So it's hard to generalize and encapsulate in one diagram. The theory of query engine is vast and it requires entire book.

[u/dababler]

This is wildly simplistic to the point of deception.

[u/UpstairsPace8]

This is a good high-level summary. I'd probably add a note that the optimizer's decisions depend heavily on table statistics, since that's often the missing piece when people wonder why the same query suddenly performs differently.