Query Processing In DBMS With Examples

Introduction

Query Processing In DBMS is the process of converting high level queries to low level expressions to extract the data from physical level file system like databases. It involves mainly four steps

• Parsing and translation
• Optimisation
• Evaluation
• Execution

In this article we will go through each of the step involved in query processing in DBMS in detail.So, let’s get started.

Query Processing In DBMS

The steps involved in query processing in dbms are summarised below

For our discussion let us take a scenario:

Input:

A user wants to fetch the records of the students whose percentage is greater than 90%. For this, user writes below SQL query.

Select student_name from Students where percentage >90;

Step 1: Parsing and translation

In this step of query processing in dbms the translator converts high level query provided by user to internal form where parser analyse this query and performs syntax, semantic and shared pool check. It also verifies the name of the relation in the database, the tuple, and finally the required attribute value.

The parser constructs a tree of the query, known as ‘parse-tree‘.And finally query is translated into relational algebra form. After this process all the usage of the views are replaced in the query. 

Following checks are performed in parsing phase:

• Syntax checking – In this step parser checks whether query is syntacticly correct or not. 
  Example: SELECT * Form Students.
  In the above query syntax check will fail as there is spelling error in from keyword.
• Semantic checking – This step checks the meaningfulness of the sql query. It also verifies all table & column names from the dictionary and checks to see if you are authorised to see the data.
  Example: Semantic check will fail if query refers to table in database which does not exist.
• Shared Pool check – The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. Every query is associated with hash code at the time of execution. This step checks whether there is existing hash code in shared pool. If there is a hash code already present DB will not perform optimisation and row source generation for this query

For example, our SQL query will be converted into a Relational Algebra equivalent as follows after this step:

π_{student_name(}σ_{percentage>90} (Students))

Hard Parse Vs Soft Parse

When the DB finds the query already processed by some other session using shared pool check it skips next two steps of query processing in dbms i.e. optimisation and row source generation this is known as Soft parsing.If we cannot find the query in already processed pool, then we need do all of the steps, this is known as a Hard Parsing.

Step 2: Optimisation

This step of query processing in dbms analyses SQL queries and determines efficient execution mechanisms. Optimiser uses the statistical data stored as part of data dictionary. The statistical data are information about the size of the table, the length of records, the indexes created on the table, etc.

A query optimiser generates one or more query plans for each query, each of which may be a mechanism used to run a query. The most efficient query plan is selected and used to run the query.

Note: The database will never optimise DDL unless it has a DML component like a subqueries that require optimisation.

Row Source Generation

The next step in query optimisation is row source generation. The row source generator receives the optimal plan from the optimiser. The output of this step is execution plan for the SQL query.

The execution plan is a collection of row sources structured in the form of a tree. A row source is an iterative control structure. It processes a set of rows, one row at a time, in an iterated manner. A row source produces a row set.

Step 3: Evaluation

From the previous step we got many execution plans constructed through statistical data which all gives the same output, but they differ in terms of time and space consumption to execute the query. Hence, it becomes mandatory to choose one plan which obviously consumes less cost and is effecitve.

In this stage, we choose one execution plan from the several plans provided by previous step. This Execution plan accesses data from the database to give the final result.

For our SQL query , it can also be written as

σ_{percentage>90} (π_{student_name} (Students))

The evaluation step picks up best execution plan for execution.

Step 4: Execution Engine

A query execution engine is responsible for generating the output of the given query. It
takes the query execution plan chosen from previous step of query processing in dbms and executes it. Finally the output is displayed to the user.

Conclusion

To conclude the query processing in dbms involves four steps. Parser and Translator checks for syntax and semantics and converts query to relational algebra. The Optimiser optimises DML queries and row source generation generates various execution plans. Finally in evaluation one of the most cost effective execution plan is chosen and execution engine executes the query and result is displayed to user.

Got a question or just want to chat? Comment below or drop by our forums, where a bunch of the friendliest people you’ll ever run into will be happy to help you out!