The Slow Path: One Query at a Time

Post.get(connection, id) in a loop is the most common performance bug in every data library ever written. It has a name — N+1 — and today you get to measure it rather than assume it.

The measurement has a surprise in it.

The benchmark

benchmark.py fills a table with 2000 rows and loads them back two ways: one get() per id, and one get_many() for all of them. It reports the number of selects and the elapsed time. Here is what it actually prints:

2000 rows, cache_size=128

in-memory SQLite (no latency):
  one by one     2000 selects       3.7 ms
  get_many          1 selects       3.5 ms
  ratio: 1.0x

Two thousand queries are exactly as fast as one.

That is not a mistake, and it’s worth sitting with for a moment. SQLite runs inside your process, against memory. A query costs a function call and a little parsing; there is no socket, no round trip, no other process to wait for. The work that dominates is building 2000 model objects, which both versions do identically.

If the benchmark stopped there, the honest conclusion would be: don’t write get_many. It’s more code, more API surface, more to get wrong, for no gain.

Where the cost actually lives

Databases are usually not in your process. Add an assumed per-query delay of 0.2 ms — a stand-in for the round trip a real server pays, and a deliberately optimistic one:

class LatentConnection(CountingConnection):
    """A connection with a simulated per-query delay (see the note below)."""

    def execute(self, sql, parameters=(), /):
        time.sleep(LATENCY)          # a stand-in for a round trip, not a real one
        return super().execute(sql, parameters)

with 0.2 ms of simulated latency per query:
  one by one     2000 selects     636.2 ms
  get_many          1 selects       8.2 ms
  ratio: 77x

Same code, same rows, same machine — one run on the author’s laptop. The only change is that each query now pays a fixed 0.2 ms.

The simulated latency is doing something specific here: it is a rough model of a cost your users will pay, so a measurement on your laptop can hint at the shape of it. It’s a legitimate and underused technique — better than guessing — but a model, not a measurement: it predicts the O(n)-versus-O(1) behaviour, not the milliseconds. For real numbers you need the real database.

LATENCY_MS = 0.2


def cost(queries, rows_per_query=1):
    return queries * LATENCY_MS


for rows in (10, 100, 1_000, 10_000):
    one_by_one = cost(rows)
    batched = cost(1)
    print(f"{rows:6} rows: {one_by_one:9.1f} ms one-by-one   {batched:5.1f} ms batched"
          f"   {one_by_one / batched:7.0f}x")

The ratio is the row count. Any per-item round trip has this shape, whether the items are database rows, HTTP requests or file reads — which is why batching is the first thing to look for whenever something gets slower as data grows.

The batched API

    @classmethod
    def get_many(cls, connection, ids):
        wanted = list(dict.fromkeys(ids))                    # de-duplicate, keep order
        found = {}
        missing = []
        for id in wanted:
            cached = cls._instances.get(id)
            if cached is None:
                missing.append(id)
            else:
                cls._instances.move_to_end(id)
                found[id] = cached
        if missing:
            marks = ", ".join("?" for _ in missing)
            rows = connection.execute(
                f"select * from {cls.table} where id in ({marks})", missing
            ).fetchall()
            for row in rows:
                obj = cls(**{key: row[key] for key in row.keys()})
                obj._remember()
                found[obj.id] = obj
        return {id: found[id] for id in wanted if id in found}

Four decisions are hiding in there, and each is an API question rather than a performance one.

De-duplicate, keep order. dict.fromkeys is the shortest way to do both. Callers pass ids from somewhere else — a join, a list of references — and duplicates are normal. Asking the database twice for the same row is the bug this method exists to prevent.

Only fetch what isn’t cached. The identity map already holds some of them; the placeholders are built from the missing ids alone. A test pins it:

    def test_a_partially_cached_request_only_fetches_the_rest(self) -> None:
        Post.get_many(self.connection, [1, 2])
        self.connection.reset()
        Post.get_many(self.connection, [1, 2, 3, 4])
        self.assertEqual(self.connection.selects, 1)
        self.assertEqual(self.connection.queries[-1].count("?"), 2, "only the missing ids")

Missing ids are absent, not an error. get() raises NotFound for one missing row, because you asked for that row specifically. get_many returns what exists, because “which of these still exist?” is a normal question. Two methods, two contracts — and both need saying out loud in the docstring.

One placeholder per id. Never build where id in (1, 2, 3) by formatting values into the string. The ids here are integers you could argue are safe, and the moment someone reuses this pattern for titles it isn’t. Day 3’s rule holds: identifiers from your code, values as parameters.

import unittest

MAX_PARAMETERS = 4


class Backend:
    """Stands in for a database: returns rows for ids, within a limit."""

    def __init__(self):
        self.calls = []

    def fetch(self, ids):
        if len(ids) > MAX_PARAMETERS:
            raise ValueError(f"too many parameters: {len(ids)} > {MAX_PARAMETERS}")
        self.calls.append(list(ids))
        return {id: f"row {id}" for id in ids if id <= 100}


def fetch_in_chunks(backend, ids, chunk_size=MAX_PARAMETERS):
    """Return {id: row} for every id that exists, in the order asked for.

    TODO: de-duplicate, chunk to at most chunk_size ids per call, and skip
    ids the backend doesn't know about.
    """
    raise NotImplementedError("fetch_in_chunks")


class Chunking(unittest.TestCase):
    def test_a_small_request_is_one_call(self):
        backend = Backend()
        self.assertEqual(fetch_in_chunks(backend, [1, 2]), {1: "row 1", 2: "row 2"})
        self.assertEqual(len(backend.calls), 1)

    def test_a_big_request_is_split(self):
        backend = Backend()
        fetch_in_chunks(backend, list(range(1, 11)))
        self.assertEqual(len(backend.calls), 3)
        self.assertTrue(all(len(call) <= MAX_PARAMETERS for call in backend.calls))

    def test_duplicates_are_asked_for_once(self):
        backend = Backend()
        fetch_in_chunks(backend, [1, 1, 2, 2, 3])
        self.assertEqual(backend.calls, [[1, 2, 3]])

    def test_order_is_preserved(self):
        backend = Backend()
        found = fetch_in_chunks(backend, [5, 3, 1])
        self.assertEqual(list(found), [5, 3, 1])

    def test_unknown_ids_are_left_out(self):
        backend = Backend()
        found = fetch_in_chunks(backend, [1, 500])
        self.assertEqual(list(found), [1])


unittest.TextTestRunner(verbosity=2).run(
    unittest.defaultTestLoader.loadTestsFromTestCase(Chunking)
)

def fetch_in_chunks(backend, ids, chunk_size=MAX_PARAMETERS):
    wanted = list(dict.fromkeys(ids))
    found = {}
    for start in range(0, len(wanted), chunk_size):
        found.update(backend.fetch(wanted[start:start + chunk_size]))
    return {id: found[id] for id in wanted if id in found}

What you learned

• N+1 costs one round trip per row, so its penalty is the number of rows times the latency — and it is invisible without latency.
• Simulating production latency locally turns a guess into a prediction.
• A batched API needs decisions about duplicates, order, missing items and parameter limits, and each belongs in the docstring.
• Values stay parameters even when there are a hundred of them.