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

What is national data warehouse? What is census data?

-
Image
National Data warehouse   A large number of national data warehouses can be identified from the existing data resources within the central government ministries. These are potential subject areas in which data warehouses may be developed at present and in the future. Census Data Census data can help planners and others understand their communities' social, economic, and demographic conditions. Census data is the primary data used by planners to understand the social, economic, and demographic conditions locally and nationally. We sometimes think the census is unique to the United States, but census data is collected in other countries.  The total process of collecting, compiling, and publishing demographic, economic, and social data about a specific time to all people in a country or fixed or defined area part of a country. Most countries also include a housing census. A census is a procedure of systematically acquiring and recording information about the members of a given populat
Read more