Why do we need query optimization in databases? Compare heuristic query optimization with cost-based query optimization.

-

Query optimization

Query optimization is the process of selecting an efficient execution plan for evaluating the query. The main goal of query optimization is to choose the best execution strategy for a given query under the given resource constraints. Query optimization reduces the system resources required to fulfill a query, and ultimately provides the user with the correct result.

We need query optimization because of the following reasons:- 

The goal of query optimization is to reduce the system resources required to fulfill a query and ultimately provide the user with the correct result set faster. 

  • First, it provides the user with faster results, which makes the application seem faster to the user. 
  • Secondly, it allows the system to service more queries in the same amount of time because each request takes less time than unoptimized queries. 
  • Thirdly, query optimization ultimately reduces the amount of wear on the hardware (e.g. disk drives), and allows the server to run more efficiently (e.g. lower power consumption, less memory usage). 

OR,
We need query optimization in the database because of the following reasons:-

-Reduce the total execution time of the query:

  • Minimize the sum of the execution of all individual operation
  • Reduce the number of disk access.

-Reduce the response time of the query

  • to maximize parallel operations.
-It minimizes the use of certain resources (mainly the number of input/output and CPU time), by selecting the best execution plan.

The comparison between heuristic query optimization and cost-based optimization is as follows:- 


 Heuristic query optimization 

  • Heuristic optimization transforms the query tree by using a set of rules. (Heuristics) that typically improves execution performance.
  • In the heuristic approach, the initial query tree from the SQL statement is generated which is transformed into a more efficient query tree via a series of tree modifications.  
  • Common heuristic rules include performing selection early performing projection early and performing the most restrictive selection and joining operation.
  • A single query tree is involved at least in the heuristic approach.
  • It is also called the logical approach.
  • It is comparatively cheaper than a cost-based one.
  • It does not always ensure to provide an optimal solution.

Cost-Based Optimization 

  • Cost-Based Optimization compares different strategies based on relative cost (amount of time that the query needs to run) and selects and executes one that minimizes the cost.
  • The query can use a lot of paths based on the value of indexes, available sorting methods, constraints, etc in cost-based optimization.
  • The cost of an algorithm depends pon the cardinality of the input.
  • The most efficient path of implementing the query at the lowest minimum cost in the form of an algorithm is chosen in the cost-based approach.
  • It is also called the physical approach. 
  • It is more expensive than the heuristic one.
  • It always ensures an optimal solution.

OR,

Cost-based Optimization

This process of selecting a lower-cost mechanism is known as cost-based optimization. This is based on the cost of the query. The query can use different paths based on indexes, constraints, sorting methods, etc. This method mainly uses the statistics like record size, number of records, number of records per block, number of blocks, table size, whether the whole table fits in a block, organization of tables, uniqueness of column values, size of columns, etc. Some of the features of cost-based optimization are as follows:

  • It is based on the cost of the query that to be optimized.
  • The query can use a lot of paths based on the value of indexes, available sorting methods, constraints, etc.
  • The aim of query optimization is to choose the most efficient path of implementing the query at the possible lowest minimum cost in the form of an algorithm.
  • The cost of executing the algorithm needs to be provided by the query Optimizer so that the most suitable query can be selected for an operation.
  • The cost of an algorithm also depends upon the cardinality of the input.

Example to illustrate cost-based query optimization:

We will consider query Q2 and its query tree in the below figure to illustrate cost-based query optimization.


Heuristic-based Optimization

Heuristic optimization transforms the query tree by using a set of rules (Heuristics) that typically (but not in all cases) improve execution performance. Some common the common heuristic rules are:

  • Perform selection early (reduces the number of tuples) Perform projection early (reduces the number of attributes)
  • Perform most restrictive selection and join operations (i.e., with smallest result size) before other similar operations Initially query tree from SQL statement is generated. A query tree is transformed into a more efficient query tree, via a series of tree modifications, each of which hopefully reduces the execution time. A single query tree is involved at last.


              






Comments

Post a Comment

Popular posts from this blog

Suppose that a data warehouse for Big-University consists of the following four dimensions: student, course, semester, and instructor, and two measures count and avg_grade. When at the lowest conceptual level (e.g., for a given student, course, semester, and instructor combination), the avg_grade measure stores the actual course grade of the student. At higher conceptual levels, avg_grade stores the average grade for the given combination. a) Draw a snowflake schema diagram for the data warehouse. b) Starting with the base cuboid [student, course, semester, instructor], what specific OLAP operations (e.g., roll-up from semester to year) should one perform in order to list the average grade of CS courses for each BigUniversity student. c) If each dimension has five levels (including all), such as “student < major < status < university < all”, how many cuboids will this cube contain (including the base and apex cuboids)?

-
Image
a) A Snowflake Shema is shown in figure below: b) The specific OLAP operations to be performed: 1. Roll-up on course from course_id to department. 2. Roll-up on a student from student_id to university. 3. Dice on course, student with department ="CS" and university = "biguniversity" 4. Drill-down on a student from the university to student_name. c) N = 4 dimensions The cube will contain 54 = 625 cuboids.
Read more

Suppose that a data warehouse consists of the four dimensions; date, spectator, location, and game, and the two measures, count and charge, where charge is the fee that a spectator pays when watching a game on a given date. Spectators may be students, adults, or seniors, with each category having its own charge rate. a) Draw a star schema diagram for the data b) Starting with the base cuboid [date; spectator; location; game], what specific OLAP operations should perform in order to list the total charge paid by student spectators at GM Place in 2004?

-
Image
  b) The specific OLAP operations to be performed are: 1. Roll-up on date from date id to year. 2. Roll-up on spectator from spectator id to status. 3. Roll-up on location from location id to location name. 4. Roll-up on the game from game id to all. 5. Dice with status= “students”, location name= “GM Place”, and year=2004
Read more

Discuss classification or taxonomy of virtualization at different levels.

-
Image
Taxonomy of virtualization Virtualization is mainly used to emulate the execution environment, storage, and networks. The execution environment is classified into two: – Process-level – implemented on top of an existing operating system.  – System-level – implemented directly on hardware and does not or minimum requirement of the existing operating system. OR,  Virtualization covers a wide range of emulation techniques that are applied to different areas of computing. A classification of these techniques helps us better understand their characteristics and use  V irtualization is mainly used to emulate  ● Execution Environments: To provide support for the execution of the programs eg. OS, and Application.  ○ Process Level: Implemented on top of an existing OS that has full control of the hardware  ○ System Level: Implemented directly on Hardware and do not require support from existing OS.  ● Storage: Storage vi...
Read more