Evaluating Join Performance on Relational Database Systems

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 paper, the authors analyze join queries in SQL with large tables in which a foreign key may be null, invalid or valid, given a referential integrity constraint. They conduct an extensive join performance evaluation on three Relational DataBase Management Systems (RDBMSs). Specifically, they study join queries varying table sizes, row size and key probabilistic distribution, inserting null, invalid or valid foreign key values. They also benchmark three well-known query optimizations: view materialization, secondary index and join reordering.