When Should You Optimize a Query? Understanding System‑Wide Impact

Which queries should be optimized? The answer depends on the overall system impact: optimizing the query that yields the greatest benefit to the system is most needed.

Generally, high‑concurrency, low‑cost queries have a larger impact than low‑concurrency, high‑cost ones.

Example

Assume Query A runs 10,000 times per hour, each consuming 20 I/O operations, while Query B runs 10 times per hour, each consuming 20,000 I/O operations. Both consume the same total I/O (200,000 I/O/hour).

(1) IO‑based analysis

If Query A is optimized from 20 I/O to 18 I/O, it saves 2 I/O per execution, i.e., 2 × 10,000 = 20,000 I/O/hour. To achieve the same saving with Query B, each execution would need to reduce by 2,000 I/O (20,000 / 10), which is far less realistic.

(2) CPU‑based analysis

The principle is the same: a small resource saving on a frequently executed query frees a large amount of system resources, especially for CPU‑intensive operations such as sorting and grouping.

(3) System‑wide impact

A frequently executed high‑concurrency query poses a much greater risk than a low‑concurrency one. A mis‑planned low‑concurrency query only affects its own requester, whereas a mis‑planned high‑concurrency query can cause disastrous system‑wide consequences.