Recursive SQL Query Optimization with k-Iteration Lookahead

Recursive queries were introduced as part of ANSI SQL 99 to support processing of hierarchical data like air flight schedules, bill-of-materials, data cube dimension hierarchies, and ancestordescendant information (e.g. XML data stored in relations). Recently, recursive queries have also found extensive use in web data analysis such as social network and click stream data. Teradata implemented recursive queries in V2R6 using static plans whereby a query is executed in multiple iterations, each iteration corresponding to one level of the recursion. Such a static planning strategy may not be optimal since the demographics of intermediate results from recursive iterations often vary to a great extent. Gathering feedback at each iteration could address this problem by providing size estimates to the optimizer which, in turn, can produce an execution plan for the next iteration. However, such a full feedback scheme suffers from lack of pipelining and the inability to exploit global optimizations across the different recursion iterations. In this paper, we propose adaptive optimization techniques that avoid the issues with static as well as full feedback optimization approaches. Our approach employs a mix of multi-iteration pre-planning and dynamic feedback techniques which are generally applicable to any recursive query implementation in an RDBMS. We also validated the effectiveness of our proposed techniques by conducting experiments on a prototype implementation using a real-life social network data from the FriendFeed online blogging service.

show abstract

“…Preliminary results of the work described in this paper were presented in [10]. This paper provides a complete solution.…”

Section: Related Workmentioning

confidence: 89%

Adaptive optimizations of recursive queries in teradata

Ghazal

Seid

Crolotte

et al. 2012

Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data

Self Cite

View full text Add to dashboard Cite

show abstract

“…Such queries have already been implemented in a significant number of database systems, both commercial and open-source [2]. Researchers developed optimization methods for recursive queries [3][4][5]. However, there are still database management systems that do not implement SQL:1999 recursive queries, e.g.…”

Section: Introductionmentioning

confidence: 98%

Unrolling SQL: 1999 Recursive Queries

Boniewicz

Stencel

Wiśniewski

2012

Communications in Computer and Information Science

View full text Add to dashboard Cite

Abstract. Hierarchical and graph data structures are common in practical application development. In order to query such data, one can use SQL:1999 recursive queries based on Common Table Expressions. Nowadays, numerous relational database management systems implement them. However, some popular systems, e.g. MySQL, still lack this useful feature. In this paper we show three methods to circumvent this situation: (1) the direct loop, (2) horizontal unrolling and (3) vertical unrolling. We analyze them and present a report on tests of their efficiency. We also describe our implementation of these methods as a prototype extension to Hibernate, a major object-relational mapping system. This extension allows running SQL:1999 recursive queries directly from an application code, even when the underlying DBMS does not support them.

show abstract