query execution time and query optimization
DESCRIPTION
Query Execution Time calculation and Query Optimization....TRANSCRIPT
04/10/2023
Query Execution Time Calculation and Query Optimization
04/10/2023
OBJECTIVE
The objective of this presentation is to show how to calculate the time required during the execution of a query and then optimize the query.
04/10/2023
1. Query Execution Time
What is Query?
Query is piece of code that is send to a Database in order to get information back from database. It is used to interact with Databse.
What is Query Execution Time?
Query Execution Time means time required by query to get the required information from the database.
04/10/2023
How To Calculate ??? ?
Execution of any query involve CPU time and time to access data from disk. CPU speed are increasing much faster than the disk access time. CPU time as it depends on the low level details of execution code so it is harder to calculate. We have used System time in order to get Execution time.
As the System time represent the computer system's notion of the passing of time.
System time is measured by a system clock, which is typically implemented as a simple count of the number of ticks
Implementation:-
The system clock is typically implemented as a
programmable interval timer that periodically interrupts the CPU,
which then starts executing a timer interrupt service routine. That
routine typically adds one tick to the system clock (a simple
counter) and handles other periodic housekeeping tasks before
returning to whatever the CPU was doing before the interruption.
04/10/2023
Screen Shots:-
Advantage:-
If we get the execution time then we can reduce that time by using some query optimization method.
It also Help in Query Scheduling, Progress Monitoring and also control to System size.
04/10/2023
2. Query Optimization IntroductionSteps in Cost-based query optimization-
Query FlowTransformation of Relational ExpressionsOptimization AlgorithmsFuture ScopeConclusion
04/10/2023
Query Optimization
What is Query Optimization??? ?
Query optimization is a function of many relational database management systems.
The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans.
Why ? ◦ Many different ways of executing a given query.
◦ Huge differences in Execution Time.
◦ Increase performance of the system.
◦ Uses Less Memory.
◦ Lesser Stress on the Database.
04/10/2023
Query Optimization
Example:◦ select * from person where ssn = “123”
◦ Size of person = 1MB
◦ Sequential Scan: Takes 1MB / (20mb/s) = 50ms
◦ Use an index on SSN (assuming one exists): Approx 4 Random I/Os = 40ms
04/10/2023
Query Optimization
IntroductionSteps in Time-based query optimization-
Query FlowTransformation of Relational ExpressionsOptimization AlgorithmsFuture ScopeConclusion
04/10/2023
Steps in Time Based Query Optimization:-
1. Parsing.
2. Transformation.
3. Implementation.
4. Plan selection based on Time estimates.
Query Flow:-
Query Language
Relational Calculus
Relational and Physical Algebra
Record at a time calls
04/10/2023
SQL– Structured query Language. It is used to take or any type of data from database or to Interact with database.
Query Parser – Verify validity of the SQL statement. Translate
query into an internal structure using relational calculus. Query Optimizer – Find the best expression from various
different algebraic expressions. Code Generator/Interpreter -It takes the physical and
relational algebra as a input and gave it to the query processor.
Query Processor – Execute the calls obtained from the code
generator.
Steps in Time Based Query Optimization:-
04/10/2023
Query Optimization
IntroductionSteps in Time-based query optimization-
Query FlowTransformation of Relational ExpressionsOptimization AlgorithmsFuture ScopeConclusion
04/10/2023
Equivalence of Expressions
Two relational expressions equivalent iff:◦ Their result is identical on all legal databases
Equivalence rules:◦ Allow replacing one expression with another
Examples:
1.
2. Selections are commutative
))(()(2121EE
))(())((1221EE
04/10/2023
Pictorial representation of Some more equivalence Rule :-
04/10/2023
Transformation to Relational Algebra
Simple Algorithm
Start with the original expression Apply all possible applicable rules to get a new
set of expressions Repeat with this new set of expressions Till no new expressions are generated
04/10/2023
Find the names of all customers with an account at a Brooklyn branch whose account balance is over $1000.
customer_name(branch_city = “Brooklyn” balance > 1000
(branch (account depositor)))
Apply the rules one by one
customer_name((branch_city = “Brooklyn” balance > 1000
(branch account)) depositor)
customer_name(((branch_city = “Brooklyn” (branch)) ( balance > 1000
(account))) depositor)
Example
04/10/2023
Query Optimization
IntroductionSteps in Time-based query optimization-
Query FlowTransformation of Relational ExpressionsOptimization AlgorithmsFuture ScopeConclusion
04/10/2023
Optimization Algorithms
Two Types:
◦ Exhaustive:
That attempt to find the best plan and have dynamic programming.
◦ Heuristic:
That are simpler, but are not guaranteed to find the optimal plan.
04/10/2023
Heuristic Optimization
Dynamic programming is expensive.
Use heuristics to reduce the number of choices.
Typically rule-based:
◦ Perform selection early (reduces the number of tuples)
◦ Perform projection early (reduces the number of attributes)
◦ Perform most restrictive selection and join operations before other similar operations.
Some systems use only heuristics, others combine
heuristics with partial time-based optimization.
04/10/2023
Steps in Typical Heuristic Optimization
1.Deconstruct conjunctive selections into a sequence of single selection operations .
2. Move selection operations down the query tree for the earliest possible execution .
3. Execute first those selection and join operations that will produce the smallest relations.
4. Replace Cartesian product operations that are followed by a selection condition by join operations.
5. Deconstruct and move as far down the tree as possible lists of projection attributes, creating new projections where needed.
6. Identify those subtrees whose operations can be pipelined, and execute them using pipelining).
04/10/2023
Advantages of Query Optimization
1. Faster Processing of Query.
2. Lesser Cost Per Query.
3. High Performance of The System.
4. Lesser Stress on Database.
5. Lesser Memory is Consumed.
04/10/2023
Query Optimization
Introduction.Steps in Time-based query optimization-
Query Flow.Transformation of Relational Expressions.Optimization Algorithms.
04/10/2023
Thanks