Edit this essay
only $12.90/page

Query Optimization Paper Essay Sample

Query Optimization Paper Pages
Pages: Word count: Rewriting Possibility: % ()

I. Abstract

The query optimizer is an important system component of a relational database management system (DBMS). It is the responsibility of this component to translate the user-submitted query – usually written in a non the database. The research literature describes a wide variety of optimization strategies for different quer y languages and implementation environments. However, very little is known about how to design and structure the query optimization component to implement these strategies. This paper proposes a first step towards the design of a modular query optimizer. We describe its operations by transformation rules which generate different QEPs from initial query specifications. As we distinguish different aspects of the query optimization process, our hope is that the approach taken in this paper will contribute to the more general goal of a modular query optimizer as part of an extensible database management system. -procedural language – into an efficient query evaluation plan (QEP) which is then executed against


Query optimization is a function of many relational database in which multiple query for satisfying a query are examined and a good query plan is identified. This may or not be the absolute best strategy because there are many ways of doing plans. There is a trade-off between the amount of time spent figuring out the best plan and the amount running the plan. Different qualities of database management systems have different ways of balancing these two. Cost based query optimizers evaluate the resource footprint of various query plans and use this as the basis for plan selection. Typically the resources which are costed are CPU path length, amount of disk buffer space, disk storage service time, and interconnect usage between units of parallelism. The set of query plans examined is formed by examining possible access paths (e.g., primary index access, secondary index access, full file scan) and various relational table join techniques (e.g., merge join, hash join, product join). The search space can become quite large depending on the complexity of the SQL query.

There are two types of optimization. These consist of logical optimization which generates a sequence of relational algebra to solve the query. In addition there is physical optimization which is used to determine the means of carrying out each operation. queries. Query optimization is an important skill for SQL developers and database administrators (DBAs). In order to improve the performance of SQL queries, developers and DBAs need to understand the query optimizer and the techniques it uses to select an access path and prepare a query execution plan. Query tuning involves knowledge of techniques such as cost-based and heuristic-based optimizers, plus the tools an SQL platform provides for explaining a query execution plan. Query Processing in Relational Database Systems

The conventional method of processing a query in a relational DBMS is to parse the SQL statement and produce a relational calculus-like logical representation of the query, and then to invoke the query optimizer, which generates a query plan. The query plan is fed into an execution engine that directly executes it, typically with little or no runtime decision-making The query plan can be thought of as a tree of unary and binary relational algebra operators, where each operator is annotated with specific details about the algorithm to use (e.g., nested loops join versus hash join) and how to allocate resources (e.g., memory). In many cases the query plan also includes low-level “physical” operations like sorting, network shipping, etc. that do not affect the logical representation of the data.

Certain query processors consider only restricted types of queries, rather than full-blown SQL. A common example of this is selectproject- join or SPJ queries: an SPJ query essentially represents a single SQL SELECT-FROM-WHERE block with no aggregation or subqueries.

Second, the issue of scheduling computation in a query plan has many performance implications. Traditional query processing makes the assumption that an individual operator implementation (e.g., anested loops join) should be able to control how CPU cycles are allocated to its child operators. This is achieved through a so-called iterator architecture: each operator has open, close, and getNextTuple methods. The query engine first invokes the query plan root node’s open method, which in turn opens its children, and the process repeats recursively down the plan. Then getNextTuple is called on the root node. Depending on the operator implementation, it will make calls to its children’s getNextTuple methods until it can return a tuple to its parent. The process completes until no more tuples are available, and then the engine closes the query plan.

III. Proposed work idea

The choice of optimization goal can have a big impact on the performance of your queries. Therefore, two important questions must be answered when upgrading.

1. Which optimization goal should be set server‐wide?

2. Is it necessary to specify session‐ or query‐level optimization goals that are different from the server‐wide setting?

Since it should be considered unfeasible to analyze all queries in your system, a pragmatic approach should be taken towards these issues. Please note that blindly setting the server‐wide optimization goal to allrows_dss (since it is the widest optimization goal) is not recommend since this may consume more resources, including compilation time, than needed. It is worth trying to determine if a narrower optimization goal will do.

As for the choice of the server‐wide optimization goal, two approaches can be distinguished:

. A. Pick the optimization goal that provides the best overall performance for your system. This should be set so that it matches the type of workload of the majority of your queries. If you have a pure OLTP or pure DSS system, then it is obvious which optimization goal to pick. Ideally, you should experiment with the different optimization goals to see which one provides best results for your mix of applications and queries. See the section “Experimenting with optimization goals” for some suggestions.

B. Just set the server‐wide optimization goal to allrows_oltp ‐‐ for the simple reason that this optimization goal resembles the behaviour of ASE 12.x most closely. This will reduce the possibility of running into unexpected query plan issues after upgrading to ASE 15, but may also not let you benefit from the full potential of ASE 15’s capabilities.

IV. Analysis of result

One of the most important components of web performance optimization is to speed up the database access because it is one of the most common problem areas. The whole point of the slow query log is to identify slow queries and optimize them for faster overall performance. Of course, optimizing a query requires a basic understanding of MySQL . However, one surefire technique for optimizing slow queries is to look for the tables in your database and index them appropriately. This will allow your database to quickly identify requested data without scanning an entire table. But be aware, this approach is an art, not a science. “Over-indexing” will cause your database to do extra work by requiring it to build indices for even small tables. When the average coder is unsatisfied with database performance in the web application, a gut reaction might be to upgrade the hardware. Perhaps a hardware upgrade is necessary, especially if traffic to the database is increasing and expected to continue to increase. But an experienced coder will first set up and analyze a slow query log looking for any improvement possible in the code or configuration. V. Evaluation

Query optimization is an important task in a relational DBMS. Must understand optimization in order to understand the performance impact of a given database design (relations, indexes) on a workload (set of queries). Two parts to optimizing a query:

Consider a set of alternative plans:
* Must prune search space; typically, left-deep plans only.
* Must estimate cost of each plan that is considered.
* Must estimate size of result and cost for each plan node.

VI. Conclusion

Optimization is much more than transformations and query equivalence. The infrastructure for optimization is significant. Designing effective and correct SQL transformations is hard, developing a robust cost metric is elusive, and building an extensible enumeration architecture is a significant undertaking. Despite many years of work, significant open problems remain. However, an understanding of the existing engineering framework is necessary for making effective contribution to the area of query optimization.

VII. References

[1] Apers, P.M.G., Hevner, A.R., Yao, S.B. Optimization Algorithms for Distributed Queries. IEEE Transactions on Software Engineering, Vol 9:1, 1983. [2] Bancilhon, F., Maier, D., Sagiv, Y., Ullman, J.D. Magic sets and other strange ways to execute logic programs. In Proc. of ACM PODS, 1986. [3] Bernstein, P.A., Goodman, N., Wong, E., Reeve, C.L, Rothnie, J. Query Processing in a System for Distributed Databases (SDD-1), ACM TODS 6:4 (Dec 1981). [4] Chaudhuri, S., Shim K. An Overview of Cost-based Optimization of Queries with Aggregates. IEEE DE Bulletin, Sep. 1995. (Special Issue on Query Processing). [5] Chaudhuri, S., Shim K. Including Group-By in Query Optimization. In Proc. of VLDB, Santiago, 1994.

Search For The related topics

  • database