Measuring Query Performance Considered Harmful

HerbstlaubRecently, I’ve been part of or have listened to a number of discussions around performance, benchmarks and the like. What surprises me is that performance is frequently considered single sided, i.e. there is a single focus on the absolute value: “query returns in 0.345 sec”. But what is the expense of achieving this result? This is why I decided to add this blog to the list of considered harmful articles that have some tradition in computer science.

To elaborate on my point, consider the following 3 scenarios behind the query mentioned above:

  • Scenario 1: The best performance can be achieved if the query result has been pre-calculated and sits in some cache waiting to be displayed on screen. Here, the only time spent is the one for displaying / rendering the query result on the end user’s screen. This has to be done for all queries, potentially an outrageously large number. Tuning costs: potentially prohibitive.
  • Scenario 2: The performance is as given by the data design. No efforts are spent to improve the performance. Tuning costs: zero.
  • Scenario 3: A trade-off decision is made and partial results are pre-calculated that are frequently reused by queries. Such partial results include all forms of indexes like B-trees, bitmaps, materialised views etc. This is inbetween scenarios 1 and 2 as it does not reach the extreme of scenario 1 but is more than doing nothing as in scenario 2. Scenario 3 is what happens in reality. Tuning costs: as much as you want to afford.

In scenario 1, the end user will be delighted as this is as good as it can be for him/her. Now, in order to create such a situation, someone (a DBA) needs to anticipate those queries. Good candidates are queries that are executed on a regular base, e.g. every morning showing the respectively top-5 critical customers for sales executives. In that case, it is realistic and feasible to prepare for such a situation and it is reasonable to pre-calculate a query result if the underlying query complexity requires that. However, if queries cannot be anticipated (like in data science or ad-hoc analyses) or if there are simply a lot of them (1000s of employees, each with a slightly different perspective – filter, projection – on the data) then pre-calculation starts to become either very expensive or unrealistic. In contrast, scenario 2 represents the other extreme, namely that nothing is pre-calculated and the performance is “as-is”. Scenario 3 reflects reality as it trades off query performance versus tuning costs. Figure 1 pictures the 3 scenarios with respect to emphasising those 2 dimensions.

Trade-off between query performance and tuning costs for the 3 scenarios.

Figure 1: Trade-off between query performance and tuning costs for the 3 scenarios.

Benchmarks that measure only (query) performance bear the risk that results tend towards scenario 1, thereby potentially implying enormous tuning costs. This is why the most prominent DB benchmarks – the ones published by the Transaction Processing Council (TPC) – have gradually incorporated pricing (i.e. costs) into the benchmark metrics, e.g.

Naturally, pricing is also a very flexible component and in reality subject to customer-supplier negotiations. However, the TPC has defined pricing as well as possible in order to address this issue. A different approach has been taken by SAP’s benchmark council by removing price completely from the publications in the context of SAP’s application benchmarks – see section 4.2.8 in here.

As pictured in figure 1, performance (in general!) needs to be considered as a trade-off versus (tuning, hardware and other) costs. Therefore, looking only at a pure performance measure (like query runtime, transaction throughput, INSERT rates etc) does not necessarily represent what can be achieved in reality as it might (!) go along with prohibitive costs or effort.

This blog is also available on Linkedin Pulse. You can follow me on Twitter via @tfxz.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s