Kishore Karanam archive  

A High-Level Overview of SQL DB Engine

March 19, 2024


I've been dabbling in SQL for more than four years now, although not consistently. Recently, over the last four months, I've been tackling Leetcode SQL problems and have become quite proficient at it. I've handled a lot of problems involving various joins, window functions, and even dabbled in stored procedures. But like many others who work with SQL, I don't have a deep understanding of how it works behind the scenes.

Lately, I've developed a sudden interest in SQL and databases in general. I started exploring and reading some research papers by PhD candidates on the subject. It got me thinking that I really want to grasp SQL at a deeper level.

So, I've decided to dive into understanding it better. This guide can serve as a perfect overview to quickly grasp a high-level understanding of what's going on under the hood.

Also, bullet points? New obsession! Great stuff! Easy, and straightforward.


How Data Comes?

How the Data is Processed?

The stages of processing:

Compiling (Parsing): Compiling (Checks semantics): Compiling (Binding): Optimizing: Executing:

The process of compiling SQL statements involves more steps than optimizing and executing due to the nature of each task. Parsing or tokenizing a SQL statement is quick and doesn't require database access. Optimizing a query plan can be CPU-intensive, especially for complex queries, as the optimizer explores various execution strategies. Spending time on optimization is preferred over inefficient query execution, as optimization bears a lower opportunity cost. Reusing the same optimized query plan for similar schemas further enhances efficiency.

The Engines: Storage, Relational, and Execution Engine

How Data Looks AS Tables?


As stated already, this is all a very high-level overview, but it still helps. It's a nice introduction. However, I would like to dive deeper into B-Trees, Query Optimization, etc. As I do so, I'll attempt to simplify it all in another post. For now, this works.