Aggify: Lifting the Curse of Cursor Loops using Custom Aggregates
Authors:
Open Access
- 31 May 2020
- conference paper
- Published by Association for Computing Machinery (ACM)
- p. 559-573
Abstract
Loops that iterate over SQL query results are quite common, both in application programs that run outside the DBMS, as well as User Defined Functions (UDFs) and stored procedures that run within the DBMS. It can be argued that set-oriented operations are more efficient and should be preferred over iteration; but from real world use cases, it is clear that loops over query results are inevitable in many situations, and are preferred by many users. Such loops, known as cursor loops, come with huge trade-offs and overheads w.r.t. performance, resource consumption and concurrency. We present Aggify, a technique for optimizing loops over query results that overcomes these overheads. It achieves this by automatically generating custom aggregates that are equivalent in semantics to the loop. Thereby, Aggify completely eliminates the loop by rewriting the query to use this generated aggregate. This technique has several advantages such as: (i) pipelining of entire cursor loop operations instead of materialization, (ii) pushing down loop computation from the application layer into the DBMS, closer to the data, (iii) leveraging existing work on optimization of aggregate functions, resulting in efficient query plans. We describe the technique underlying Aggify, and present our experimental evaluation over benchmarks as well as real workloads that demonstrate the significant benefits of this technique.Keywords
This publication has 9 references indexed in Scilit:
- Iterative Query Processing based on Unified Optimization Techniques2019
- Automatically Leveraging MapReduce Frameworks for Data-Intensive Applications2018
- Extracting Equivalent SQL from Imperative Code in Database Applications2016
- Translating imperative code to MapReduce2014
- Optimizing database-backed applications with query synthesisACM SIGPLAN Notices, 2013
- User-defined aggregate functions2006
- Orthogonal optimization of subqueries and aggregation2001
- A transformation-based approach to optimizing loops in database programming languagesACM SIGMOD Record, 1992
- The program dependence graph and its use in optimizationACM Transactions on Programming Languages and Systems, 1987