Optimization of nested SQL queries revisited
SIGMOD '87 Proceedings of the 1987 ACM SIGMOD international conference on Management of data
A recursive algebra and query optimization for nested relations
SIGMOD '89 Proceedings of the 1989 ACM SIGMOD international conference on Management of data
Loop distribution with arbitrary control flow
Proceedings of the 1990 ACM/IEEE conference on Supercomputing
A transformation-based approach to optimizing loops in database programming languages
SIGMOD '92 Proceedings of the 1992 ACM SIGMOD international conference on Management of data
Advanced compiler design and implementation
Advanced compiler design and implementation
On optimizing an SQL-like nested query
ACM Transactions on Database Systems (TODS)
Orthogonal optimization of subqueries and aggregation
SIGMOD '01 Proceedings of the 2001 ACM SIGMOD international conference on Management of data
ICDE '96 Proceedings of the Twelfth International Conference on Data Engineering
VLDB '87 Proceedings of the 13th International Conference on Very Large Data Bases
Parallelizing while loops for multiprocessor systems
IPPS '95 Proceedings of the 9th International Symposium on Parallel Processing
JReq: database queries in imperative languages
CC'10/ETAPS'10 Proceedings of the 19th joint European conference on Theory and Practice of Software, international conference on Compiler Construction
Holistic optimization by prefetching query results
SIGMOD '12 Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data
Program analysis and transformation for holistic optimization of database applications
Proceedings of the ACM SIGPLAN International Workshop on State of the Art in Java Program analysis
Automatic partitioning of database applications
Proceedings of the VLDB Endowment
Keyword search on form results
The VLDB Journal — The International Journal on Very Large Data Bases
Data-based research at IIT Bombay
ACM SIGMOD Record
| Hi-index | 0.00 |
Queries, or calls to stored procedures/user-defined functions are often invoked multiple times, either from within a loop in an application program, or from the where/select clause of an outer query. When the invoked query/procedure/function involves database access, a naive implementation can result in very poor performance, due to random I/O. Query decorrelation addresses this problem in the special case of nested sub-queries, but is not applicable otherwise. This problem is traditionally addressed by manually rewriting the application to make it set-oriented, by creating a batch of parameters, and by rewriting the query/procedure to work on the batch instead of one parameter at a time. Such manual rewriting is time-consuming and error prone. In this paper, we propose techniques that can be used to do the following, (a) Automatically rewrite programs to replace multiple calls to a query by a batched call to a correspondingly rewritten query, (b) Rewrite a stored procedure/function to accept a batch of bindings, instead of a single binding. Thereby, for example, a query which would have been invoked many times from different invocations of a stored procedure would be automatically replaced by one (or a few) invocations of a batched version of the query. Our techniques can be applied to code written in any language, such as procedural versions of SQL, or Java. We have implemented the proposed rewriting techniques for a subset of Java, where database operations are performed using an API over JDBC. We demonstrate the benefits due to our rewrites with three cases from real-world applications, which faced significant performance problems due to repeated invocations of queries/procedures.