Estimating Costs of Materialization Methods for SQL: 1999 Recursive Queries

Although querying hierarchies and networks is one of common tasks in numerous business application, the SQL standard has not acquired appropriate features until its 1999 edition. Furthermore, neither relational algebra nor calculus offer them. Since the announcement of the abovementioned standard, various database vendors introduced SQL:1999 recursive queries into their products. Yet, there are popular database management systems that do not support such recursion. MySQL is probably the most profound example. If the DBMS used is contacted via an object-relational mapper (ORM), there is a possibility to offer recursive queries provided by this middleware layer. Moreover, data structures materialized in the DBMS can be used to accelerate such queries. In prequel papers, we have presented a product line of features that eventually allow MySQL users to run SQL:1999 recursive queries via ORM. They were: (1) appropriate ORM programmer interfaces, (2) optimization methods of recursive queries, and (3) methods to build materialized data structures that accelerate recursive queries. We have indicated four such methods, i.e.: full paths, logarithmic paths, materialized paths and logarithmic paths. In this paper we aim to assist a database/system architect in the choice of the optimal solutions for the expected workload. We have performed exhaustive experiments to build a cost model for each of the solutions. Their results have been analyzed to build empirical formulae of the cost model. Using this formulae and estimated properties of the expected workload, the database architect or administrator can choose the best materialization method for his/her application.

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

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

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

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

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

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

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

[8]  Wenfei Fan,et al.  Conditional functional dependencies for capturing data inconsistencies , 2008, TODS.

[9]  Philip A. Bernstein,et al.  Compiling mappings to bridge applications and databases , 2008, TODS.

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

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

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

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

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

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

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

[17]  Krzysztof Stencel,et al.  On redundant data for faster recursive querying via ORM systems , 2013, 2013 Federated Conference on Computer Science and Information Systems.

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