Investigating slow queries

Define the measured operation before selecting a fix

A slow screen is an observation about a user-visible operation. It is not yet evidence that one SELECT has a poor plan. The operation may include waiting for a connection, preparing a statement, stepping through rows, creating application objects, and rendering the result. Define the timing boundary so that a later claim of improvement refers to the same operation.

For example, suppose a constructed request takes one hundred milliseconds. Ten milliseconds occur while executing and reading the database result, while ninety occur elsewhere. Even eliminating all ten database milliseconds would leave ninety milliseconds. This simple bound prevents attributing a potential tenfold improvement in the query to a tenfold improvement in the whole request. The diagram's stages illustrate this distinction; they are not measurements of your application.

Time statement execution through the required result consumption. Preparing a statement establishes an executable representation but does not generally perform the full retrieval. Reading just the first row also does not measure a query whose caller consumes ten thousand rows. Conversely, a caller that legitimately stops early has a different operation from one that drains every row. Match the measurement to the actual requirement.

Record whether the delay affects all requests or a subset. A large customer, an absent lookup value, a concurrent writer, or a first run after startup can expose different mechanisms. Preserve those conditions in the test case rather than replacing them with whichever example is easiest to run. Reproducibility requires the relevant conditions, not merely a copy of the SQL text.

A safe copy matters for correctness as well as safety. A live database can have committed data in a write-ahead log that a naive file copy omits. Use a supported backup method and check that the copied database contains the expected records. Then record its schema, statistics, and settings. A perfectly repeatable test against the wrong snapshot can still produce an explanation that does not apply to the observed delay.

Make the predicted change falsifiable

A useful hypothesis names the unnecessary work and the evidence that would change your mind. For the newest-orders query, say that a date-only route considers many other customers' entries before finding enough matches. Predict that a customer/date index will restrict the search to one customer's group while preserving the requested order. This is more specific than saying that the query needs an index.

Separate three checks. First, did SQLite use the proposed route? A new index in the schema does not prove that it was chosen. Second, did the query return the same intended rows? A smaller incorrect result can be faster. Third, did repeated measurements improve under the relevant conditions? A plan change is evidence about the route, not a direct measurement of benefit.

Suppose the plan changes as predicted but timings remain similar. Several explanations remain possible. The old route may already find enough matching rows quickly. The whole dataset may be small and cached. Application work may dominate the measured boundary. Or normal measurement variation may hide a small difference. Do not invent a cause merely to preserve the original hypothesis; design another comparison that distinguishes these possibilities.

Suppose instead that timings improve but the plan text stays the same. A warmer cache, different contention, or changed data can affect runtime without changing the high-level access description. Statistics and implementation details can also matter beyond the text you inspected. The observation supports an improvement in those measured runs, but it does not by itself establish that the intended index change caused it.

Write down what you expect before changing the test copy. This prevents choosing an explanation after seeing the result and then treating it as a prediction. Preserve a baseline that you can reproduce. If several indexes, the data distribution, and the query all change together, you may find a faster configuration, but you have not isolated which change produced the benefit or whether all of them are necessary.

Use repeated measurements to separate effects from variation

Consider two invented sets of elapsed times in milliseconds. The baseline is 8, 9, 10, 11, and 12. The candidate is 7, 8, 9, 10, and 11. Their medians are ten and nine, but the ranges overlap strongly. These few observations suggest a possible small improvement; they do not establish a precise ten-percent benefit across the workload. More repetitions under controlled conditions would help distinguish the effect from variation.

Now compare that baseline with candidate times of 2, 3, 3, 4, and 4. The difference is larger than the variation within either small set. That is stronger evidence for the tested condition, provided both configurations returned equivalent results and other conditions were controlled. It still says nothing directly about another customer, a larger database, or performance during heavy writes.

Alternating configurations or using a balanced test order reduces one simple bias: always measuring the candidate after the baseline has warmed the needed data. Repetition alone does not remove that bias if every pair uses the same favorable order. Likewise, two separate files can have different cache histories. Record the setup rather than claiming "same machine" is enough to make the runs comparable.

Return to the invented baseline measurements and predict what removing the slowest observation would do. Without twelve, the remaining times are eight, nine, ten, and eleven. Their median is nine and a half rather than ten, and their range is eight to eleven rather than eight to twelve. Removing an observation changes the summary even though no query improved. An unusually slow run can reveal contention or another workload condition, so do not discard it merely because it makes the candidate look less attractive. Investigate its cause or report the inclusion rule explicitly.

Keep result handling consistent. Printing thousands of rows to a terminal can dominate a shell measurement, while suppressing output changes that boundary. In application tests, object construction and serialization can matter too. Use the same handling for both configurations and explain whether the reported time includes it. A useful report gives the query, parameters, run conditions, number of repetitions, and summary of variation, not just a ratio between two selected observations.

Carry the evidence into a deployment decision

A successful test supports a particular change under particular conditions. Before deployment, repeat the important checks for common and rare parameter values, empty results, large groups, and tied ordering values. Compare full rows and multiplicities where order is not required, and compare the defined sequence where it is. Two results with the same row count can contain different customers or omit different duplicates.

For an outer-join report, even the choice of count can affect correctness. COUNT(*) counts an unmatched customer's preserved row, while COUNT of a non-NULL order identifier counts only rows with an actual order identifier. A customer with no orders can therefore contribute one to the first count and zero to the second. Include such a customer when testing a rewrite of an order-count report. Also check a customer with two orders, because a test that contains only zero or one match does not expose accidental removal of duplicates. This is a result-definition question before it is a performance question.

Include the cost of maintaining the candidate index. A read-heavy screen can benefit while a bulk import slows down. Measure relevant insert and update paths rather than assuming that a small read improvement outweighs every write cost. Also check the space required during index construction and afterward. The experiment's tiny database does not establish the resources a production build will need.

Plan how to observe the change and recover if it harms the workload. Preserve the earlier schema definition and the evidence that motivated the change. Removing a newly added ordinary index can restore the old set of access options, but do not casually remove indexes that enforce uniqueness or serve other queries. Schema changes and database backups require an application-appropriate deployment procedure.

If the application and shell disagree, compare their environments before concluding that SQLite behaves unpredictably. They may use different library versions, compile options, parameter bindings, connection settings, or data snapshots. A statement executed inside a long transaction also has different surrounding conditions from an isolated shell request. Record these differences as candidate explanations and test them separately.

A final investigation note should distinguish observations from conclusions. State the old and new access paths, the result-equivalence check, the timing summaries, and any write or storage tradeoff you measured. Name what remains unknown, such as performance during concurrent writes. That record lets another person reproduce the claim and judge whether the change fits their workload without mistaking a teaching diagram or a single fast run for production evidence.