Lifecycle of a SQL Query

Introduction

SQL is the fundamental skillset for anyone working with data. To write an optimized SQL code, it is very important to understand how a SQL query gets executed .

In this article I will cover the details about the steps involved from query submission to results retrieval .

SQL Query Processing : Steps

SQL query is processed by the database engines by performing set of steps in a certain order before returning the response to the user. Detailed understanding of query processing steps will help in the reading the explain plan,SQL tuning and debugging.

At a high level database engine executes a query in three steps.

1. SQL Parsing and Translation

The role of the SQL parser is to perform for syntax and semantic checks of the SQL query. Once SQL is parsed the translator converts the SQL into the level instruction in the form of relational algebra.

Below are the actions performed at this stage.

• checks the syntax of the query
• checks for user privileges on the queries DB objects (read/write on tables/schemas etc)
• semantic checks on the queried DB objects
• validation against the data dictionary(tables, column existence in database)

SQL Parser also checks if the query exists in the shared pool before passing it to the query optimizer. If query is found in the shared pool ,the hard parsing is skipped.

Soft Parse — checks for the query in shared pool . Queries in shared pool are already optimized and executed.

Hard Parse — Query not found in the shared pool. SQL parser sends the query to Optimizer.

2. Query Optimizer

Query Optimizer gather the statistical data from the data dictionaries which has information about row counts, column values, indexes, size of the table etc.Optimizer analysis the conditions used in queries and reorders the joins, where to enable faster reads and partition pruning. Query optimizer generates multiple query plans to retrieve the data from the database and each plan defines a path and cost to retrieve the data.

In the final step optimal query plan is selected to sent to the execution engine for running the optimized query.

3. Query Execution Engine

Query execution engine executed the query and returns the result back to the user.

Query Processing Steps

SQL Query: Order of Execution

Now we know how a query gets executed in the database, lets see in the order in which every statement in executed in the database.

Order of Query Execution:

1. FROM — Starting point for the query
2. JOIN — working set is generated after getting from multiple sources
3. WHERE — Filtering to required dataset for the given query
4. GROUP BY — Data grouping
5. HAVING — Filtering in the grouped rows
6. SELECT — All expressions like WINDOW functions,CASE statements.
7. DISTINCT — Duplication handling
8. ORDER BY — Sorting based on the clause.
9. LIMIT/OFFSET — Limiting to specific number of rows.

Conclusion

Good understanding of steps involved in SQL execution not only helps in the getting the correct results but also in SQL tuning and performance improvements.

Thanks for reading!