skip to main content
10.1145/1216993.1217001acmconferencesArticle/Chapter ViewAbstractPublication PagesicpeConference Proceedingsconference-collections
Article

Controlled SQL query evolution for decision support benchmarks

Published: 05 February 2007 Publication History

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

Cited By

View all

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences
WOSP '07: Proceedings of the 6th international workshop on Software and performance
February 2007
216 pages
ISBN:1595932976
DOI:10.1145/1216993
Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 05 February 2007

Permissions

Request permissions for this article.

Check for updates

Author Tags

  1. TPC
  2. TPC-DS
  3. TPC-H
  4. benchmark design
  5. decision support
  6. query generation

Qualifiers

  • Article

Conference

WOSP07
WOSP07: Workshop on Software and Performance
February 5 - 8, 2007
Buenes Aires, Argentina

Acceptance Rates

Overall Acceptance Rate 149 of 241 submissions, 62%

Upcoming Conference

Contributors

Other Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

  • Downloads (Last 12 months)4
  • Downloads (Last 6 weeks)0
Reflects downloads up to 17 Feb 2025

Other Metrics

Citations

Cited By

View all
  • (2014)CubeLoad: A Parametric Generator of Realistic OLAP WorkloadsAdvanced Information Systems Engineering10.1007/978-3-319-07881-6_41(610-624)Online publication date: 2014
  • (2009)Generating Shifting Workloads to Benchmark Adaptability in Relational Database SystemsPerformance Evaluation and Benchmarking10.1007/978-3-642-10424-4_9(116-131)Online publication date: 28-Oct-2009
  • (2009)Converting TPC-H Query Templates to Use DSQGEN for Easy ExtensibilityPerformance Evaluation and Benchmarking10.1007/978-3-642-10424-4_8(99-115)Online publication date: 28-Oct-2009
  • (2008)BibliographyHigh‐Performance Parallel Database Processing and Grid Databases10.1002/9780470391365.biblio(511-539)Online publication date: 11-Mar-2008

View Options

Login options

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

Figures

Tables

Media

Share

Share

Share this Publication link

Share on social media