Query Execution Engine in SQL

In this article, we will be discussing Query-Execution Engine in SQL and all aspects when we run any query. When we run any query to fetch the data from the database then at the same time many things happen simultaneously to get the desired results. 

Let’s discuss what happens when we submit a query in brief and then we will discuss all phases of query execution.

When a query comes how does the database actually answer it? So the first thing that the database system does is to parse the SQL query and figure out what the query is trying to do then it evaluates different expressions that are equivalent to the given SQL query. The SQL query is broken down into relational algebra.             

For example, consider a query: 

Query:

Select * from account
Where balance>1000;

The aim of this query is to find out the details of all accounts where the balance is >1000. Once we write this query, this is equivalent to the following relational algebra expression.

πbalance( δbalance>1000(account) ) 

The above SQL query is also equivalent to the below given relational algebra expression.

δbalance>1000( πbalance(account) ) 

Both of the query expressions are the same. When a query is parsed then generally 2 things happen:

• All the equivalent action plans are generated
• And for each of generated equivalent action plans a query order tree is created.

And then query order tree is evaluated.

Phases of Query Evaluation:

• Parser/Translator
• Optimizer
• Execution Plan
• Query Execution Engine

Now we will discuss each phase of query evaluation in detail.

1. Parser/Translator: When we submit any query to run the task of the parser is to checks the syntax and verify the query. It checks whether query syntax is correct or not. It also checks for query semantics. Our SQL command is a high-level language so we need to convert it to low-level language. The translator converts SQL query to relational algebra expression i.e from high-level language to low-level language.

2. Optimizer: As we know when we submit any query then it is converted into various relational algebra expressions. We have many relational algebra expressions for the given query. Optimizer selects the query which is having a low cost. For this optimizer uses the statistics about the data.

3. Execution Plan: In this phase, the database decides the order of execution of given query instructions. For example, consider the following query:

Select * From emp_table
Where experience>5;

Here database will first execute From statement then Where and finally Select statement. So the order of execution for this query is as follows:   

From->Where->Select

4. Query Execution Engine: The execution engine will perform operations by fetching data from the database. The query execution engine will take a query evaluation plan then executes that plan and return the answers to the query. Examples of query engines are Apache Spark, Apache Drill, Presto, etc. So basically query execution engine interprets SQL command and access data from the database and then finally returns the answer of the given SQL query. There are many functions performed by the query execution, some of them being:

• It acts as a dispatcher for all commands in the execution plan. It iterates through all the commands in the plan until the batch is complete.
• And it interacts with the storage engine to retrieve and update data from tables and indexes.