Query Processing In DBMS

Introduction

Query Processing in DBMS is the approach of selecting the best strategy or plan which can be used to respond to a database request. In Query Processing, we focus on different facts of converting the query of user into standard form and afterward into a plan which can be executed to generate response.

Query Processing

Query Processing is also referred as Query Optimization in database literature. The component of database management system responsible for generating this strategy is known as query processor. This is a stepwise process.

First step transforms the query into a standard form. The next step generates a number of strategies known as access plans to evaluate the transformed query.

The main goal to create a database is to keep the related data at one place, retrieve, and access and manipulate them when it is required. But as we know the database and the users both are system. The user can request data in its preferred language but database management system has its own language. Therefore the users query the database in its language which is SQL.

In RDBMS (Relational Database Management System) the data is stored in the form of tables (rows and columns). The users can select, insert, update, delete and manipulate the data without violating the constraints (These Constraints are provided at the time of definition of the table). If we want to search the list of employee who have salary more than 10,000 then

SELECT EMP-NAME
FROM EMPLOYEE WHERE SALARY>10,000;

Database Management System cannot understand this statement. So for that we have structured query language.

The Structured Query language is a high- level language which is used as a bridge between the users and the Database Management System. Normally any query written in structured query language (SQL) is translated into low-level language which the system can understand. But to write relational algebra kind of queries will be difficult for any user Therefore DBMS asks its users to write queries in SQL. The DBMS verifies the code and convert them into low-level languages. It then adopts the best execution path to execute the query. All of the process together known as query processing in Database Management System.

Block diagram of Query Processing

A query processor in Database Management System performs this task.

The diagram shows the processing of a query in the database. When a query is submitted it is received by the compiler that scans the query and divides it into tokens. After that the tokens are verified by the parser for their correctness. Then they are transformed into different relational trees or graphs. The query optimizer then picks the best query plans and generates different execution plans. The command processor uses this plan to retrieve the data and return the result.

There are various techniques used in Query Processing. These are as follows:

• Parsing and Translation
• Query optimization
• Evaluation
• Execution Engine

Parsing and Translation

In query processing in database management system this is the first step to be performed. In this step the user writes its request in structured query language (SQL) and the DBMS convert it into machine understandable low level language. The query is first picked by the query processor which scans the queries and parses them into individual tokens. After that it examines the correctness of the query, and then it converts the tokens into trees, graphs and relational expressions. These are the following checks performed in parsing phase

• Syntax check
• Semantic check
• Shared pool check

When a query is found already processed by any session using shared pool check the database skips next two steps i.e. optimization and row source generation, this is known as soft parsing. If the query is not found in already processed pool it is known as hard parsing.

Example: Suppose a user wants to know the details of employees who are working in PROCESS_1. If it says ‘Retrieve Employees details that are in PROCESS_1’, DBMS can never understand. Hence it provides a language i.e. SQL to communicate and both user and DBMS can understand. So the request could be written as

SELECT EMP_NAME, ADDRESS, DOB FROM EMPLOYEE E, PROCESS P WHERE E.EMP_ID = P.EMP_ID AND PROCESS_NAME = ‘PROCESS_1’;

The DBMS reads it and convert it for further process and synthesis it. This phase is known as parsing and translation. Query processor scans the query and divides it into tokens. In our example

 ‘SELECT * FROM’, ‘EMPLOYEE E’, ‘PROCESS P’, ‘WHERE’, ‘E.EMP_ID = P.EMP_ID’, ‘AND’, ‘PROCESS NAME = ‘PROCESS_1’’

Query Optimization

This step analyses SQL queries and determines effective execution mechanisms in query processing. Optimiser uses the statistical data i.e. information about the length of records, size of the table, the indexes created on the table etc, stored as part of data dictionary. The query optimiser produces one or more query plans and the most efficient is selected and used to execute the query.

The next step is Row source generation. The optimal plan is received by the Row source generator from the optimiser and the execution plan for sql query is the output of this step.

A collection of row sources is known as Execution plan and they are structured in the form of a tree.

A row source processes a set of rows, one at a time in an iterated manner. It is an iterative control manner that produces a row set.

The above example can be represented in relational structures like tree/graphs as below:

Evaluation

We got many execution plans through query optimization. Although they give the same output but differ in terms of space and time consumption. Evaluation helps us to choose effective and less cost consuming execution plan to give the final result by accessing the data from the database. It can also be written as

σSALARY>90 (πEMP_NAME (EMPLOYEE))

In SQL Some of the factors considered to calculate the cost of evaluation plan by the optimizer are

• CPU time
• Number of operations
• Number of tupples to be scanned and
• Disk access time

Execution Engine

Execution engine is responsible for producing the output of the given query. It executes the query execution plan which is chosen from the previous step i.e. Evaluation and the output is finally displayed to the user.

Summary

The step-wise process of translating high-level queries into low-level expressions is known as Query Processing. This technique is used at the physical level (file system), query optimization, and actual execution by using steps like parsing, translation, optimization, and evaluation. After summarising query processing involves two steps

• Compile time:  Parsing and Translation, Optimization, and Query generation
• Runtime:  Evaluate and Execute