On the Cost of Transitive Closures in Relational Databases

Abstract

We consider the question of taking transitive closures on top of pure relational systems Sybase and Ingres in this case We developed three kinds of transitive closure pro grams one using a stored procedure to simulate a built in transitive closure operator one using the C language embedded with SQL statements to simulate the iterated execution of the transitive closure operation and one using Floyd s matrix algorithm to compute the transitive closure of an input graph By comparing and analyzing the respective performances of their di erent versions in terms of elapsed time spent on tak ing the transitive closure we identify some of the bottlenecks that arise when de ning the transitive closure operator on top of existing relational systems The main purpose of the work is to estimate the costs of taking transitive closures on top of relational systems isolate the di erent cost factors such as logging network transmission cost etc and identify some necessary enhancements to existing relational systems in order to support transitive closure operation e ciently We argue that relational databases should be augmented with e cient transitive closure operators if such queries are made frequently On the Cost of Transitive Closures in Relational