Re: Life-Cycle of SQL Query

On Sat, 12 Sep 2009 09:03:03 -0700 (PDT), David wrote:

>I am trying to get a top-down understanding of what an RDBMS does to
>go
>from a SQL Query to actual data (records served up).
>
>Your thoughts / Links to good resources that cover this would be much
>appreciated.

Hi David,

I guess that there will be differences between vendors. For Microsoft SQL Server, the high level path is:

• Parse (includes syntax checking and transforming to an internal structure, called parse tree IIRC)
• Optimize (based on statistical information on data distribution, indexes present, trusted constraints, et al, choose one of the many ways in which the query can be satisfied - basically transforming the parse tree to an execution plan)
• Execute (based on execution plan, access data, combine, filter, evaluate etc until desired results are attained).

Each of these steps is probably complex enough to warrant whole books, though obviously much of the finer details would be privvy to MS staff only.

Also, I left out some optimization steps such as using a hash of the query to check if there is an execution plan for it already in the procedure cache, parametrizing it so that identical queries in which only hard-coded constants change can re-use the same execution plan, etc.

Best, Hugo Received on Mon Sep 14 2009 - 18:22:03 CEST