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: Donald R. Slutz, Massive Stochastic Testing of SQL, Proceedings of the 24rd International Conference on Very Large Data Bases, p.618-622, August 24-27, 1998
• 3: Gordon Bitter Davis, Management Information Systems: Conceptual Foundations, Structure and Development, McGraw-Hill, Inc., New York, NY, 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: Jim Melton , Alan R. Simon, Understanding the new SQL: a complete guide, Morgan Kaufmann Publishers Inc., San Francisco, CA, 1993
• 6: John M. Stephens , Meikel Poess, MUDD: a multi-dimensional data generator, Proceedings of the 4th international workshop on Software and performance, January 14-16, 2004, Redwood Shores, California
• 7: Ralph Kimball, The data warehouse toolkit: practical techniques for building dimensional data warehouses, John Wiley & Sons, Inc., New York, NY, 1996
• 8: Meikel Poess , Chris Floyd, New TPC benchmarks for decision support and web commerce, ACM SIGMOD Record, v.29 n.4, p.64-71, Dec. 2000 [doi>10.1145/369275.369291]
• 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: Naveen Reddy , Jayant R. Haritsa, Analyzing plan diagrams of database query optimizers, Proceedings of the 31st international conference on Very large data bases, August 30-September 02, 2005, Trondheim, Norway
• 12: R. H. Bonczek, C. W. Holsapple, and A. Whinston. Foundations of Decision Support Systems. Academic Press, 1981 ISBN 0-12-113050-9.
• 13: Raghunath Othayoth , Meikel Poess, The making of TPC-DS, Proceedings of the 32nd international conference on Very large data bases, September 12-15, 2006, Seoul, Korea
• 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

Primary Classification:
  B. Hardware
  B.8 Performance and Reliability

Additional Classification:
  C. Computer Systems Organization
  C.4 PERFORMANCE OF SYSTEMS

General Terms:
Design, Experimentation, Performance

Keywords:
TPC, TPC-DS, TPC-H, benchmark design, decision support, query generation