On redundant data for faster recursive querying via ORM systems

Persistent data of most business applications contain recursive data structures, i.e. hierarchies and networks. Processing such data stored in relational databases is not straightforward, since the relational algebra and calculus do not provide adequate facilities. Therefore, it is not surprising that initial SQL standards do not contain recursion as well. Although it was introduced by SQL:1999, even now it is implemented in few selected database management systems. In particular, one of the most popular DBMSs (MySQL) does support recursive queries yet. Numerous classes of queries can be accelerated using redundant data structures. Recursive queries form such a class. In this paper we consider four materialization solutions that speed up recursive queries. Three of them belong to the state-of-the-art, while the fourth one is the contribution of this paper. The latter method assures that the required redundant storage is linearithmic. The other methods do not guarantee such a limitation. We also present thorough experimental evaluation of all these solutions using data of various sizes up to million records. Since all these methods require writing complex code if applied directly, we have prototyped an integration of them into Hibernate object-relational mapping system. This way all the peculiarities are hidden from application developers. Architects can simply choose the appropriate materialization method and record their decisions in configuration files. All necessary routines and storage objects are then generated automatically by the ORM layer.

[1]  Carlos Ordonez,et al.  Optimization of Linear Recursive Queries in SQL , 2010, IEEE Transactions on Knowledge and Data Engineering.

[2]  Daniel M. Brandon,et al.  Recursive database structures , 2005 .

[3]  Krzysztof Stencel,et al.  Recursive Query Facilities in Relational Databases: A Survey , 2010, FGIT-DTA/BSBT.

[4]  Aleksandra Boniewicz,et al.  Automatic Selection of Functional Indexes for Object Relational Mapping System , 2013 .

[5]  Krzysztof Stencel,et al.  Extending HQL with Plain Recursive Facilities , 2012, ADBIS.

[6]  Marta Burzanska,et al.  Hibernate the Recursive Queries - Defining the Recursive Queries using Hibernate ORM , 2011, ADBIS.

[7]  Dawit Yimam Seid,et al.  Recursive SQL Query Optimization with k-Iteration Lookahead , 2006, DEXA.

[8]  Piotr Wisniewski,et al.  Partial Aggregation Using Hibernate , 2011, FGIT.

[9]  Krzysztof Stencel,et al.  Efficient Implementation of Recursive Queries in Major Object Relational Mapping Systems , 2011, FGIT.

[10]  Philip A. Bernstein,et al.  Compiling mappings to bridge applications and databases , 2007, SIGMOD '07.

[11]  Elizabeth J. O'Neil,et al.  Object/relational mapping 2008: hibernate and the entity data model (edm) , 2008, SIGMOD Conference.

[12]  Christian Bauer,et al.  Java Persistence with Hibernate , 2006 .

[13]  Krzysztof Stencel,et al.  Pushing Predicates into Recursive SQL Common Table Expressions , 2009, ADBIS.

[14]  Wolfgang Keller.  Mapping Objects to Tables A Pattern Language , 1997 .

[15]  Krzysztof Stencel,et al.  On Materializing Paths for Faster Recursive Querying , 2013, ADBIS.

[16]  Krzysztof Stencel,et al.  Recursive Queries Using Object Relational Mapping , 2010, FGIT.

[17]  Krzysztof Stencel,et al.  Unrolling SQL: 1999 Recursive Queries , 2012, FGIT-EL/DTA/UNESST.