JCSE, vol. 4, no. 4, pp.276-290, December, 2010

DOI:

Evaluating Join Performance on Relational Database Systems

Carlos Ordonez, Javier Garcia-Garcia
Department of Computer Science, University of Houston, Houston, USA UNAM/IPN, Mexico, Mexico

Abstract: The join operator is fundamental in relational database systems. Evaluating join queries on large tables is challenging because records need to be efficiently matched based on a given key. In this work, we analyze join queries in SQL with large tables in which a foreign key may be null, invalid or valid, given a referential integrity constraint. We conduct an extensive join performance evaluation on three DBMSs. Specifically, we study join queries varying table sizes, row size and key probabilistic distribution, inserting null, invalid or valid foreign key values. We also benchmark three well-known query optimizations: view materialization, secondary index and join reordering. Our experiments show certain optimizations perform well across DBMSs, whereas other optimizations depend on the DBMS architecture.

Keyword: query optimization; performance evaluation, Join Processing, Foreign Key, RDBMS

Full Paper:   736 Downloads, 4885 View