SQL Architecture

  

SQL Architecture

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.

There are various components included in this process.

Query Dispatcher

Optimization Engines

Classic Query Engine

SQL Query Engine, etc.

 

A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle logical files.

 

A simple diagram showing the SQL Architecture −

 

Query Language Processor

In a relational database system the query processor is the module responsible for executing database queries.

The query processor receives input queries in the form of SQL text, parses and optimizes them, and completes their execution by employing specific data access methods and database operator implementations.

The query processor communicates with the storage engine, which reads and writes data from the disk, manages records, controls concurrency, and maintains log files.

 

Syntax check: checks for SQL syntactic validity

Semantic check: checks whether the statement is meaningful or not.

Shared Pool check: Every query possess a hash code during its execution. So, this check determines existence of written hash code in shared pool if code exists in shared pool then database will not take additional steps for optimization and execution.

Hard Parse and Soft Parse: If there is a fresh query and its hash code does not exist in shared pool then that query has to pass through from the additional steps known as hard parsing otherwise if hash code exists then query does not passes through additional steps. It just passes directly to execution engine. This is known as soft parsing.

Execution Engine: Finally runs the query and display the required result.

Optimizer: During optimization stage, database must perform a hard parse at least for one unique DML statement and perform optimization during this parse. Database never optimizes DDL unless it includes a DML component such as subquery that require optimization.

It is a process in which multiple query execution plan for satisfying a query are examined and most efficient query plan is satisfied for execution.

Database catalog stores the execution plans and then optimizer passes the lowest cost plan for execution.

Row Source Generation: The Row Source Generation is a software that receives an optimal execution plan from the optimizer and produces an iterative execution plan that is usable by the rest of the database.

The iterative plan is the binary program that when executes by the SQL engine produces the result set. 

CMD Parser: It is the first component of the relational engine to receive the query data. It mainly check the query for Syntactic and Semantic error. And finally generates a query tree.

Optimizer: Optimizer’s main task is to find the cheapest, not the best, cost-effective execution plan. Optimization is mainly done for DML (SELECT, INSERT, UPDATE, DELETE) commands and not all of the queries. The ultimate goal is to minimize query run time.

Transaction Manager: It manages Non-Select Transaction with help of Log and Lock Managers. Also, promotes implementation of Write Ahead logging and Lazy writers.