Chatty I/O and N+1 Queries

Before you begin: Loops, collections, and the idea of a database request.

Chatty I/O performs many small input/output operations when fewer larger operations could do the job. An N+1 query is a common form: one request obtains N items, followed by one additional request for each item. 1

A page showing thirty customers can therefore make thirty-one requests even though no individual query looks alarming.

Count the requests, not just the loop iterations

The following store is an in-memory teaching stand-in. Each method increments Calls to represent one database round trip. It does not simulate network latency, query planning, or a real database connection.

public sealed class Store
{
    private readonly Dictionary<int, string> names =
        new Dictionary<int, string>
        {
            { 1, "Mira" }, { 2, "Sol" }, { 3, "Tess" }
        };
    public int Calls { get; private set; }

    public int[] GetIds()
    {
        Calls++;
        return new[] { 1, 2, 3 };
    }

    public string GetName(int id)
    {
        Calls++;
        return names[id];
    }

    public string[] GetAllNames()
    {
        Calls++;
        return new[] { names[1], names[2], names[3] };
    }
}

public static class Loader
{
    public static string[] OneAtATime(Store store)
    {
        var result = new List<string>();
        foreach (int id in store.GetIds())
        {
            result.Add(store.GetName(id));
        }
        return result.ToArray();
    }

    public static string[] Batched(Store store)
    {
        return store.GetAllNames();
    }
}

Store original = new Store();
Loader.OneAtATime(original);
Console.WriteLine(original.Calls); // 4
Store revised = new Store();
Loader.Batched(revised);
Console.WriteLine(revised.Calls);  // 1

OneAtATime calls GetIds once and GetName three times. Batched requests all required names in one operation. Both return Mira, Sol, and Tess in that order.

class Store:
    def __init__(self):
        self._names = {1: "Mira", 2: "Sol", 3: "Tess"}
        self.calls = 0

    def get_ids(self):
        self.calls += 1
        return [1, 2, 3]

    def get_name(self, customer_id):
        self.calls += 1
        return self._names[customer_id]

    def get_all_names(self):
        self.calls += 1
        return [self._names[i] for i in [1, 2, 3]]


def one_at_a_time(store):
    result = []
    for customer_id in store.get_ids():
        result.append(store.get_name(customer_id))
    return result


def batched(store):
    return store.get_all_names()

original = Store()
one_at_a_time(original)
print(original.calls)  # 4
revised = Store()
batched(revised)
print(revised.calls)   # 1

one_at_a_time requests identifiers once and a name three times. batched requests the required names together. Both return the same three names in the same order.

What to change in a real application

Inspect the actual database commands or outbound requests generated by one user action. A property access can trigger a lazy database load, so the network operation may not be obvious in the source code.

Select the fields the page actually needs, load related data deliberately, or add a bounded batch operation. Do not simply move the loop into a helper while leaving the same requests underneath it.

A single enormous query is not automatically better. Large joins can duplicate data, and loading every row can consume excessive memory. Pagination and a small, deliberate number of requests may be preferable. Concurrent requests can overlap waiting, but do not remove the N requests or their load on the server.

Finally, multiple requests may observe different moments in time. Consolidating them can affect consistency as well as speed. Preserve the page’s required ordering and missing-data behavior, and measure the real query count and latency after changing the implementation.

Sources

Microsoft — Efficient querying in EF Core, including lazy loading, N+1 queries, projection, and split-query trade-offs.