Content area
Full Text
Using artificial intelligence to optimize database queries
Database query optimizers are resident components in relational database management system (RDBMS) servers. Their function is to optimize Structured Query Language (SQL) statements by generating alternative execution plans to find the one with the least estimated cost. Typically, when a SQL statement is sent to the RDBMS server, it is parsed and presented to the query optimizer. The query optimizer then rewrites the query and evaluates its expression to produce alternative execution plans. A cost estimation is calculated for each alternative execution plan and the plan with the least cost executes that SQL statement.
This approach has two intrinsic deficiencies:
* A lack of alternative execution plans due to limited plan space or a result of the SQL statement syntax itself.
* Imprecise cost estimation, which is highly dependent on the accuracy of the data dictionary- even a small miscalculation in a single step may result in a high degree of inaccuracy in the final estimated cost.
So while its unrealistic to expect an optimizer to find the very best plan in every case, most optimizers aim to find a relatively good execution plan or, more importantly, to avoid the worst plan.
Today's commercial query optimizers are complex pieces of software with many closely guarded details, estimated to represent 40 to 50 man-years of development. As database architectures become more sophisticated and powerful, query optimizers are facing new challenges to accommodate database features such as data warehousing, parallel query, and distributed query. And, as new database features are implemented and databases become bigger, a tradeoff is required since increasing query optimizer intelligence leads to an increase in the database performance overhead.
A Now SQL Optimization Concept
It is not unusual that experienced programmers can sometimes boost the performance of a problematic SQL statement up to thousands of times faster. With deep knowledge of the database characteristics and by reconstructing the SQL statement syntax, developers can help the query optimizer find a better execution plan- one that cannot be generated by the query optimizer alone. The issue now is whether an algorithm can accomplish the same optimization. Whether we can implement an algorithm that can perform the same result is a heuristic question.
Figure 1 shows the...