The performance enhancement approach for parameterized queries

Abstract

Listen

Translate article

All database systems must be able to respond to requests for information from the user — i.e. execute user's queries and obtain the required information from a database system in a expected and reliable fashion. Database queries are usually define in declarative manner and the database system has to choose an appropriate execution plan for the query. There are two basic ways to write a query first is to explicitly specify the values for each parameter in the WHERE clause and second way is to replace the values in the WHERE clause with variable placeholders i.e. known as Parametric query. A query optimizer in a database system is responsible for transforming an SQL query into an execution plan. All query optimizers are cost-based in that they decide between alternative execution plans by comparing their estimated execution costs. The cost of a query plan depends on many parameters such as predicate selectivity, available memory, and presence of access paths, whose values may not be known at optimization time. PQO optimizes a query into a number of candidate plans and when the actual parameter values are known, the candidate plan corresponding to the actual parameter values is select and use. Parametric query optimization attempts to recognize at compile time several execution plans, each one of which is optimal for a subset of all possible values of the run-time parameters. The objective of this paper is to define a technique when the actual parameter values are known the appropriate plan should be express with fundamentally no overhead.