Estimating costs of materialization methods for SQL:1999 recursive queries

Abstract

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.