Progressive Parametric Query Optimization

Abstract

Commercial applications usually rely on precompiled parameterized procedures to interact with a database.Unfortunately, executing a procedure with a set of parameters different from those used at compilation time may be arbitrarily suboptimal. Parametric query optimization (PQO) attempts to solve this problem by exhaustively determining the optimal plans at each point of the parameter space at compile time. However, PQO is likely not cost-effective if the query is executed infrequently or if it is executed with values only within a subset of the parameter space. In this paper, we propose instead to progressively explore the parameter space and build a parametric plan during several executions of the same query. We introduce algorithms that, as parametric plans are populated, are able to frequently bypass the optimizer but still execute optimal or near-optimal plans.

Existing System:-

• We have used different optimization techniques

 Optimize-Always.
 Optimize-Once.

• Optimize-Always, is to call the optimizer and generate a new execution plan every time a new instance of the query is invoked.
• Optimize-Once, is to optimize the query just once, with some set of parameter values, and reuse the resulting physical plan for any subsequent set of parameters.

Proposed System:-

• Parametric Query Optimization (PQO). At optimization time, PQO determines a set of plans such that for each point in the parameter space, there is at least one plan in the set that it is optimal.

• No additional optimization call is required to execute each of the query instances.

Hardware Interface:-
• Hard disk : 40 GB
• RAM : 512 MB
• Processor Speed : 2.20GHz
• Processor : Pentium IV Processor

Software Interface:-
• JDK 1.5
• Swing Builder