This post was originally written by Glenn Paulley and published to sybase.com in December of 2008. The quest for a good, representative benchmark continues today...
For more than a decade we at iAnywhere have been arguing that schema and query complexity is largely uncorrelated to the size of the database instance, or the characteristics of the hardware platform:
In our experience, there is little correlation between application or schema complexity, and the database size or deployment platform. Developers tend to complicate, rather than simplify, application design when they migrate applications to business front-lines, even when targeting platforms like hand-held devices with few computing resources. It is usually only the user-interface that is re-architected because of the input mode differences on such devices.
Three separate customer interactions this week reinforced this view:
- One customer's schema consists of 121 tables -- an increase of 20 over their 2003 instance -- with declared RI constraints (including
ON DELETE CASCADE), CLOB attributes, and views containing self-referencing "star" joins. Not that much out of the ordinary, except for the fact that this SQL Anywhere database is deployed on thousands of rugged-ized handheld Intermec devices running Windows Mobile. - Another customer wanted assistance with a three-level nested SQL query that utilizes both existential (
EXISTS) a nd universal (NOT EXISTS) quantification. That query executes over an Ultralite database, again on a Windows Mobile device. - Finally, another customer wanted assistance with a 8-way
UNION query, where each query specification within the statement contained both a 3-way join and a universally-quantified subquery. Again, not that much out of the ordinary except for one important detail: the query is over an UltraliteJ database running on a Blackberry device.
In other posts I have taken the position that industry-standard benchmarks such as TPC-C [2] and TPC-E are unrepresentative of most, if not all, customer workloads, a finding reinforced by an IBM study from 2001 [1]. In our view, because these benchmarks lack tests of sophisticated query processing that we commonly find in customer applications, there is little relevance between these TPC benchmark results and customer application performance. I wonder if it is time to propose a different benchmark that addresses this weakness. A catalyst for producing such a benchmark is the continuing proliferation of query-generation tools such as Hibernate and LINQ, both of which can generate reasonably complex queries to construct related sets of objects in use by the application but which returns relatively few rows (Aside: when was the last time you wrote a query with an outer join whose ON condition contained an existentially-quantified subquery?) The benchmark I have in mind is one that could be characterized as "mixed-workload", so that it combines simple DML requests, multi-way join queries that return few rows, and occasional "reporting" queries that analyzes the database's contents for specific trends. Coupled with appropriate working set analysis and data skew, such a benchmark, I believe, would be much more representative of real-world workloads.
[1] W. W. Hsu, A. J. Smith, and H. C. Young (2001). Characteristics">http://www.research.ibm.com/journal/sj/403/hsu.html">Characteristics of production .... IBM Systems Journal 40(3), pages 781-802.
[2] Francois Raab (1991). An Overview of the TPC Benchmark C: A Complex OLTP Benchmark. In The Benchmark Handbook, Jim Gray, ed., 2nd edition. Morgan Kaufmann Publishers, San Francisco.
