Controlled SQL query evolution for decision support benchmarks

ABSTRACT

The synthesis of increased global competitiveness and the acceptance of commercially available multi purpose database management systems (DBMS) for decision support applications requires an ever more critical system evaluation and selection to be completed in a progressively short period of time. Designers of standard benchmarks, individual customer benchmarks and system stress tests alike are struggling to mastermind queries that are both representative to the real world and execute in a reasonable time. Additionally, the enriched functionality of every new DBMS release amplifies the complexity of today's decision support systems calling for a novel approach in query generation for benchmarks. This paper proposes a framework of so called query evolution rules that can be applied to typical decision support queries, written in SQL92. Deployed in combination with QGEN2, the query generator developed by the TPC for TPC-DS ?[13], these rules quickly turn a small set of queries into a large set of semantically similar queries for ad-hoc benchmarking purposes or they can be used to generate thousands of queries quickly to stress test optimizers or query execution engines without much user intervention.

References

1. C. Ballinger. Relevance of the TPC-D Benchmark Queries: The Question You Ask Every Day. http://www.tpc.org/
2. D. Slutz. Massive Stochastic Testing of SQL. In the Proceedings of the twenty-fourth International Conference of Very Large Data Bases, VLDB, New York, USA, August 1998.
3. G. B. Davis: Management Information Systems. Conceptual Foundations, Structure and Development. McGraw-Hill, 1974.
4. ISO/IEC 9075. Database Language SQL, International Standard ISO/IEC 9075:1992, American National Standard X3.135-1992, ANSI, New York, NY 10036, November 1992.
5. J. Melton, A. R. Simon. Understanding the New SQL: A Complete Guide, Morgan Kaufmann Series in Data Management Systems, Morgan Kaufmann Publishers, ISBN:1558602453 October 1992.
6. J. Stephens, M. Poess. Mudd: A Multi-Dimensional Data Generator, Proceedings of the Fourth International Workshop on Software and Performance, WOSP 2004, Redwood Shores, California, USA, January 14-16, 2004.
7. Kimball, R. The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses. John Wiley & Sons, 1996.
8. M. Poess, and C. Floyd. New TPC Benchmarks for Decision Support and Web Commerce". ACM SIGMOD RECORD, Volume 29, No 4 December 2000.
9. M. Poess, J. M. Stephens. Generating Thousand Benchmark Queries in Seconds. In Proceedings of the Thirtieth International Conference of Very Large Databases, pages 1045-1053, Toronto, Canada September 2004.
10. M. Stillger, J. C. Freytag. Testing the Quality of a Query Optimizer. In Proceedings of IEEE Data Engineering Bulleting. Volume 18(3): 41-48 March 1995.
11. N. Reddy, J. R. Haritsa. Analyzing Plan Diagrams of Database Query Optimizers, VLDB 2005: 1228--1240.
12. R. H. Bonczek, C. W. Holsapple, and A. Whinston. Foundations of Decision Support Systems. Academic Press, 1981 ISBN 0-12-113050-9.
13. R. Othayoth, M. Poess, The Making of TPC-DS. In Proceedings of the Fourtieth International Conference of Very Large Databases, Seoul, Korea September 2006.
14. Transaction Processing Performance Council (TPC), "TPC Benchmark D (Decision Support)", May 1995 http://www.tpc.org/tpcd/spec/tpcd_current.pdf
15. Transaction Processing Performance Council (TPC), "TPC-H Specification Version 2.4.0", August 2003 http://www.tpc.org/tpch/spec/tpch2.4.0.pdf
16. Transaction Processing Performance Council (TPC), "TPC-R Specification Version 2.1.0", August 2003 http://www.tpc.org/tpcr/spec/tpcr_2.1.0.pdf

Index Terms

• Computer systems organization

  • Dependable and fault-tolerant systems and networks

• General and reference

  • Cross-computing tools and techniques

    • Performance

• Hardware

  • Robustness

• Networks

  • Network performance evaluation