Parallel Query in DBMS – This article is about Parallel Query in DBMS, which describes the various Parallel Queries Evaluation Techniques in DBMS.
Table of Contents
Query Parallelism
Query Parallelism is running a query with multiple processors in parallel with one another. The main aim of query parallelism is to speed up the query processing and reduce the output-producing time to return the result in less time.
Forms of Query Parallelism
Inter Query Parallelism
Interquery parallelism allows multiple queries to run on different processors simultaneously, making it easy to achieve the Parallelism that improves the system’s output.
For example, If four queries are submitted at a time, and each question takes 4 seconds for evaluation, then the total time taken for the query is 16 seconds. But inter query parallelism achieves this query evaluation task only in 4 seconds. However, inter-query Parallelism is challenging to perform every time.
Intra Query Parallelism
Intra Query Parallelism allows running single query evaluations by breaking them into subqueries and running them on different processors simultaneously, which minimizes the query evaluation time. This technique of Parallelism is mainly focused on saving response time.
For example, if we have a query that takes 10 seconds to evaluate, but with the intra-query evaluation, the query evaluation can be achieved in only 2 seconds by breaking the query into subqueries.
Intra-operation Parallelism
Speeding up a query by parallelizing the execution of individual operations. Parallelism occurs because the data is partitioned into small data based on many records. The operations which can be parallelized are Sort, Join, Projection, Selection, and so on.
Inter-operation Parallelism
When the different operations in a query are executed parallel, this is known as Inter-operation Parallelism.
It contains two types of Parallelism –
Pipeline Parallelism
In this Parallelism, operations depend on the result of the previous operation except for the first operation. The immediate output of the first operation is taken by the following operation even before the result is finished. Operations are run on different CPUs simultaneously to run in parallel with one another. It is mainly helpful for a small number of CPUs, which helps avoid the disk’s intermediate result.
Independent Parallelism
In this Parallelism, operations that are not dependent on each other can be executed in parallel. This Parallelism is beneficial for the lower degree of Parallelism.
Optimization of Parallel Query
Optimization of Parallel Query is selecting the best plan to optimize the query evaluation. Parallel query optimization is essential in determining the best strategy and minimizing the cost of query evaluation.
Two essential factors play a role in Parallel Query Optimization
- Time is taken to select the best plan
- Amount of time taken to execute the plan
The goal of Query Optimization
- Speed up the queries by finding the questions which can give the fastest result on execution.
- Increase the performance of the system.
- Select the best query evaluation plan.
- Avoid unwanted scenarios.
Approaches of Query Optimization
Following are the three approaches to Query Optimization:
- Horizontal partitioning: Tables are created vertically using columns.
- Vertical partitioning: Tables are created with fewer columns and partitioned the table row-wise.
- De-normalization: In this approach, multiple tables are combined into one table.
