Program Transformations for Holistic Query Optimization

Joint work with: Ravindra Guravannavar, Karthik Ramachandra, and Mahendra Chavan

Synchronous execution of database queries forces the calling application to block until the query/request is satisfied. The performance of applications can be significantly improved by rewriting application programs to use (a) batching of query requests, (b) asynchronous submission of queries, and (c) prefetching of query results. Batching of queries and asynchronous submission of multiple queries can greatly reduce the impact of network round-trip latency and delays at the database, and allow the query execution engine to improve performance by utilizing multiple processors and disks, and by reordering disk IO requests to minimize seeks.

We first review our earlier work on how to rewrite application programs to replace multiple query submissions by a single batch submission. We then describe more recent work on how to rewrite application programs to allow asynchronous submission of queries, allowing the application to perform other processing instead of blocking while a query is executed, and to concurrently issue multiple queries. Unlike most earlier work, our approach is holistic in that it integrates program transformation with query rewriting.

Our program transformation method is based on dataflow analysis, and is framed as a set of transformation rules. Our rules can handle query executions within loops, unlike some of the earlier work in this area. We have built a tool called DBridge, which uses the SOOT framework to implement our transformation techniques on Java code that uses JDBC calls. We have carried out a detailed experimental study on several real-life applications rewritten using our transformation techniques. The experimental study shows the effectiveness of the proposed rewrite techniques, both in terms of their applicability and performance gains achieved.