  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 all 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.
