Optimizer validation and performance evaluation for distributed queries

Abstract

Few database query optimizer models have been validated against actual performance. This paper extends an earlier optimizer validation and performance evaluation of R* to distributed queries, i.e. single SQL statements having tables at multiple sites. Actual R* message, I/O, and CPU resources consumed - and the corresponding costs estimated by the optimizer - were written to database tables using new SQL commands, permitting automated control from application programs for collecting, reducing, and comparing tests data. A number of tests were run over a wide variety of dynamically-created test databases, SQL queries, and system parameters. Both high-speed networks (comparable to a local area network) and medium-speed long-haul networks (for linking geographically dispersed hosts) were evaluated.