Empirical Evidence of Heuristic and Cost Based Query Optimizations in Relational Databases

Abstract

A SQL query may be expressed in different forms with varying execution costs; hence, it is essential to choose the best when performance is considered. There are many factors that affect the cost and performance of a query. For instance, a query on a relation without proper indexes can cause full relation scans, which in turn causes either performance or locking problems. Query asking for a relation held by another query can cause dead locks and an improperly formed query can also lead to greater complexity. In this context, the paper presents an empirical analysis of the heuristic and cost-based query optimization techniques. The execution cost of a number of queries was calculated and compared with execution plans obtained after optimization. In particular, we have experimentally checked the impact of query rewriting; the ordering of SQL joins and Select statements and replacement of joins with semi-joins. The experiments have yielded a clear insight into the effect of the mentioned optimizations.