Query Cost Estimation
Query Processing can be understood as the activities involved in retrieving data from the database. It can also refer to SQL query translation in to low-level language implementing relational algebra. The process to select an efficient query execution plan is called as Query Optimization. Basic step in query processing are parsing and translation, optimization and evaluation. We may choose a strategy based on reliable information, database system may store statistics for each relation r. These statistics includes the number of tuples in a relation, the size of tuple in a relation and the number of distinct values that appear in the relation r for a particular attribute. The cost is generally measured as total elapsed time for answering query. There are many factors which contribute to time cost such as disk accesses, CPU or even network communication. Typically disk access is the predominant cost, and is also relatively easy to eliminate. The cost to write a block is greater than the cost to read a block. The data is read back after being written to ensure that the write was successful.
Summary
Query Processing can be understood as the activities involved in retrieving data from the database. It can also refer to SQL query translation in to low-level language implementing relational algebra. The process to select an efficient query execution plan is called as Query Optimization. Basic step in query processing are parsing and translation, optimization and evaluation. We may choose a strategy based on reliable information, database system may store statistics for each relation r. These statistics includes the number of tuples in a relation, the size of tuple in a relation and the number of distinct values that appear in the relation r for a particular attribute. The cost is generally measured as total elapsed time for answering query. There are many factors which contribute to time cost such as disk accesses, CPU or even network communication. Typically disk access is the predominant cost, and is also relatively easy to eliminate. The cost to write a block is greater than the cost to read a block. The data is read back after being written to ensure that the write was successful.
Things to Remember
- Query Processing can be understood as the activities involved in retrieving data from the database. It can also refer to SQL query translation in to low-level language implementing relational algebra.
- The process to select an efficient query execution plan is called as Query Optimization.
- Basic step in query processing are parsing and translation, optimization and evaluation.
- We may choose a strategy based on reliable information, database system may store statistics for each relation r. These statistics includes the number of tuples in a relation, the size of tuple in a relation and the number of distinct values that appear in the relation r for a particular attribute.
- The cost is generally measured as total elapsed time for answering query.
- There are many factors which contribute to time cost such as disk accesses, CPU or even network communication. Typically disk access is the predominant cost, and is also relatively easy to eliminate.
- The cost to write a block is greater than the cost to read a block. The data is read back after being written to ensure that the write was successful.
MCQs
No MCQs found.
Subjective Questions
No subjective questions found.
Videos
No videos found.
Query Cost Estimation
Query Processing can be understood as the activities involved in retrieving data from the database. It can also refer to SQL query translation into low-level language implementing relational algebra. The process to select an efficient query execution plan is called as Query Optimization. Phases of query processing is presented below:
Basic steps in Query Processing can be listed as below:
- Parsing and translation
- Optimization
- Evaluation
Parsing and translation: This process translates the query into its internal form. Then it is translated into relational algebra. The parser checks syntax and verifies the relations.
Evaluation: The query-execution engine takes a query-evaluation plan, executes that plan and returns the answer to the query.
Optimization: Amongst all equivalent evaluation plans, we need to choose the one with the lowest cost. The cost is estimated using statistical information from the database catalog.
Query Cost Estimation
We may choose a strategy based on reliable information, a database system may store statistics for each relation r. These statistics includes
- The number of tuples in a relation
- The size of tuple in a relation
- The number of distinct values that appear in the relation r for a particular attribute.
The cost is generally measured as total elapsed time for answering the query. There are many factors which contribute to time costs such as disk accesses, CPU or even network communication. Typically disk access is the predominant cost and is also relatively easy to eliminate.
Measured by taking into account
- Number of seeks which gives average-seek-cost
- Number of blocks read which gives average-block-read-cost
- Number of blocks written which gives average-block-write-cost
The cost to write a block is greater than the cost to read a block. The data is read back after being written to ensure that the write was successful. For simplicity, we just use the number of block transfers from disk and the number of seeking as the cost measures.
- tT- time to transfer one block
- tS- time for one seek
- Cost for b block transfers plus S seeks
b *tT+ S *tS
References:
- H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
- A.K.Majumdar and p, Bhatt acharya,"Database Management Systems",Tata McGraw Hill,India,2004
- F.Korth, Henry. Database System Concepts. 6th edition.
Lesson
Query Processing and Optimization
Subject
Computer Engineering
Grade
Engineering
Recent Notes
No recent notes.
Related Notes
No related notes.