Consecutive DB Queries

A Consecutive DB Queries issue is created when a request runs several database queries one after another, and those queries don't depend on each other. Because they are independent, they could have run at the same time, and the request would have finished sooner.

When queries run in sequence, the total time is the sum of all of them. When independent queries run together, the total time is closer to the time of the slowest one. On a request that runs several queries of reasonable size, that difference is the time you get back.

This issue is found in APM.

How Middleware detects it#

Middleware looks at the database calls made under one operation within a request and finds SELECT queries that ran back to back without overlapping. It then asks a simple question: if these queries had run in parallel, how much time would the request have saved?

An issue is created only when the answer is meaningful, both as an absolute amount of time and as a share of the whole operation. Very short queries are not reported, because there's little to gain from running them together.

Middleware can see the order and timing of your queries, but it can't see inside your code. That means it can't know for certain that the queries are independent. Before you change anything, check that none of the queries uses the result of an earlier one. If one does, it has to stay in order.

If the same query repeats many times instead, that is reported as an N+1 DB Query, not as consecutive queries.

What you'll see in Middleware#

The title shows the database, how many queries ran in sequence, and how much time could be saved by running them together, for example 6 sequential queries in /orders (208ms could be saved). Open the issue to see each query in order and the operation they belong to.

Common causes#

• Code that reads naturally from top to bottom. Each step is written after the last, so each query waits, even though it doesn't need to.
• Loading a page's data in stages. Fetching a user, then their settings, then their notifications, one at a time.
• Helper functions that each run their own query, called one after another.
• No use of async or parallel features. The language or framework supports concurrency, but the code doesn't use it here.

How to fix it#

The goal is to start independent queries together and wait for all of them to finish.

1. Run them in parallel. Use your language's concurrency tools, such as promises, async tasks, goroutines, or thread pools, to start every independent query first and then wait for the results.
2. Combine them into one query. If the queries read from related tables, a single query with a join, or a database view, can replace several round trips.
3. Only parallelize independent work. If one query needs the result of another, keep them in order. Running them in parallel would give wrong results.

Keep your database connection pool in mind. Running many queries at once needs enough connections available, otherwise the queries just queue up and the time saved disappears.

Example#

A profile page loads a user's account details, their recent activity, and their notification settings. The code runs these as three separate queries, one after the other. Each takes around 70 milliseconds, so the page spends about 210 milliseconds waiting on the database.

None of the three queries needs anything from the others. Changing the code to start all three together brings the total down to roughly the time of the slowest one, about 70 milliseconds. The Consecutive DB Queries issue stops receiving new occurrences.

Related issues#

• N+1 DB Query: the same query repeated many times.
• Consecutive HTTP: the same idea, for calls to other services.
• Slow DB Query: a query that is slow on its own.