Reading Large DB Result Sets & Pagination

Use Case: Reading Large DB Result Sets & Pagination

The everyday problem: A user requests "all orders", a report needs "every audit log for the last year", or an export must return millions of rows. The naive approach — _db.Orders.ToList() — loads every row into a giant in-memory List<T>, spikes application memory by gigabytes, stalls the garbage collector, and can crash the process under load. The challenge is returning large result sets to callers (or pages of them) without ever holding the whole set in memory at once.

There are six primary ways to handle this in .NET, each suited to a different shape of the problem. The first three are about paging (returning data in chunks to a UI or API consumer); the last three are about streaming (processing or exporting the full set without buffering it).

---

Way 1: Offset Pagination (LIMIT/OFFSET)

Best used for: Admin grids, search results, and any UI where the user jumps to arbitrary page numbers ("page 47 of 200") and the dataset is small to moderate (tens of thousands of rows, not millions).

The concept: You ask the database to skip the first N rows and take the next page-size rows. EF Core translates Skip()/Take() into SQL OFFSET/FETCH NEXT (SQL Server) or OFFSET/LIMIT (PostgreSQL). It is the simplest paging model and the easiest to expose as ?page=3&pageSize=20.

// Offset pagination — the standard approach
public record PagedResult<T>(IReadOnlyList<T> Items, int Page, int PageSize, int TotalCount)
{
    public int TotalPages => (int)Math.Ceiling(TotalCount / (double)PageSize);
    public bool HasNext   => Page < TotalPages;
    public bool HasPrev   => Page > 1;
}

public async Task<PagedResult<OrderDto>> GetOrdersAsync(int page, int pageSize, CancellationToken ct)
{
    // Clamp inputs — never trust the caller
    page     = Math.Max(1, page);
    pageSize = Math.Clamp(pageSize, 1, 100); // cap page size to protect the server

    var query = _db.Orders
        .AsNoTracking()                     // read-only — skip change tracking overhead
        .OrderByDescending(o => o.PlacedAt); // OFFSET is meaningless without ORDER BY

    // Total count — a SECOND query against the database
    int total = await query.CountAsync(ct);

    var items = await query
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(o => new OrderDto(o.Id, o.CustomerName, o.Total, o.PlacedAt))
        .ToListAsync(ct);

    return new PagedResult<OrderDto>(items, page, pageSize, total);
}

// Generated SQL (SQL Server) for page 3, pageSize 20:
// SELECT o.Id, o.CustomerName, o.Total, o.PlacedAt
// FROM Orders AS o
// ORDER BY o.PlacedAt DESC
// OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY

Why it works: It maps directly to a page-number UI, supports jumping to any page, and is trivially cacheable per page. The database does the slicing, not the application.

Benchmark note: Offset pagination degrades linearly with offset depth. OFFSET 40 is instant; OFFSET 5,000,000 forces the database to read and discard five million rows before returning twenty — often hundreds of milliseconds to seconds, even with an index. On a 10-million-row table, page 1 typically returns in ~2 ms while page 250,000 can take 800 ms+ because the engine still walks the index from the start. The COUNT(*) query adds a second full or index scan on every request. Offset is fine to roughly 10,000–50,000 rows deep; beyond that, use keyset (Way 2).

---

Way 2: Keyset Pagination (Seek Method / Cursor)

Best used for: Infinite-scroll feeds, "load more" buttons, very large tables, and any API where deep pages must stay fast. This is how Twitter/X timelines, Slack messages, and Stripe's API paginate.

The concept: Instead of "skip N rows", you say "give me the rows after this specific value". You remember the sort key of the last row the client saw (the cursor) and ask for rows beyond it using a WHERE clause. Because the database can seek directly into the index, performance is constant regardless of how deep you page.

// Keyset pagination — constant performance at any depth
public record KeysetPage<T>(IReadOnlyList<T> Items, string? NextCursor);

public async Task<KeysetPage<OrderDto>> GetOrdersAsync(string? cursor, int pageSize, CancellationToken ct)
{
    pageSize = Math.Clamp(pageSize, 1, 100);

    var query = _db.Orders.AsNoTracking();

    // Decode the cursor — the last (PlacedAt, Id) the client saw
    if (cursor is not null)
    {
        var (lastPlacedAt, lastId) = DecodeCursor(cursor);

        // Seek: everything ordered AFTER the cursor.
        // Compound comparison handles ties on PlacedAt using Id as a tiebreaker.
        query = query.Where(o =>
            o.PlacedAt < lastPlacedAt ||
            (o.PlacedAt == lastPlacedAt && o.Id < lastId));
    }

    var rows = await query
        .OrderByDescending(o => o.PlacedAt)
        .ThenByDescending(o => o.Id)   // stable, unique tiebreaker — CRITICAL
        .Take(pageSize + 1)             // fetch one extra to detect "has more"
        .Select(o => new OrderDto(o.Id, o.CustomerName, o.Total, o.PlacedAt))
        .ToListAsync(ct);

    bool hasMore = rows.Count > pageSize;
    var items    = hasMore ? rows.Take(pageSize).ToList() : rows;

    string? next = hasMore
        ? EncodeCursor(items[^1].PlacedAt, items[^1].Id)
        : null;

    return new KeysetPage<OrderDto>(items, next);
}

// Cursor encoding — opaque base64 so clients treat it as a token, not data
private static string EncodeCursor(DateTime placedAt, int id)
    => Convert.ToBase64String(
        System.Text.Encoding.UTF8.GetBytes($"{placedAt:O}|{id}"));

private static (DateTime, int) DecodeCursor(string cursor)
{
    var parts = System.Text.Encoding.UTF8
        .GetString(Convert.FromBase64String(cursor)).Split('|');
    return (DateTime.Parse(parts[0], null, System.Globalization.DateTimeStyles.RoundtripKind),
            int.Parse(parts[1]));
}

// Generated SQL — a seek, not a skip:
// SELECT TOP(21) o.Id, o.CustomerName, o.Total, o.PlacedAt
// FROM Orders AS o
// WHERE o.PlacedAt < @lastPlacedAt
//    OR (o.PlacedAt = @lastPlacedAt AND o.Id < @lastId)
// ORDER BY o.PlacedAt DESC, o.Id DESC

Why it works: With an index on (PlacedAt DESC, Id DESC), the database seeks straight to the cursor position and reads forward — no rows are scanned and discarded. Page 1 and page 1,000,000 take the same time.

Benchmark note: On the same 10-million-row table, keyset returns any page in ~1–3 ms regardless of depth — versus offset's 800 ms+ at depth. The trade-off: you cannot jump to an arbitrary page number (no "go to page 500"), only next/previous. You also need a unique, stable sort key — paginating on a non-unique column like PlacedAt alone will skip or duplicate rows when timestamps collide, which is why the Id tiebreaker is mandatory.

---

Way 3: Streaming with IAsyncEnumerable (no buffering)

Best used for: Exporting an entire result set to a file or HTTP response — CSV/JSON exports of millions of rows — where the consumer processes rows one at a time and you must never hold them all in memory.

The concept: EF Core's AsAsyncEnumerable() keeps the database connection open and yields rows one at a time as they arrive over the wire. Combined with AsNoTracking(), each row is materialised, handed to your code, and garbage-collected before the next arrives. Memory stays flat.

// Streaming export — flat memory regardless of row count
[HttpGet("export/orders.csv")]
public async Task ExportOrdersCsv(CancellationToken ct)
{
    Response.ContentType = "text/csv";
    Response.Headers.ContentDisposition = "attachment; filename=orders.csv";

    await using var writer = new StreamWriter(Response.Body);
    await writer.WriteLineAsync("Id,Customer,Total,PlacedAt");

    // AsAsyncEnumerable streams rows one at a time — NOT into a List
    var rows = _db.Orders
        .AsNoTracking()
        .OrderBy(o => o.Id)
        .Select(o => new { o.Id, o.CustomerName, o.Total, o.PlacedAt })
        .AsAsyncEnumerable();

    await foreach (var o in rows.WithCancellation(ct))
    {
        // One row in memory at a time. Written to the response and discarded.
        await writer.WriteLineAsync(
            $"{o.Id},{Escape(o.CustomerName)},{o.Total},{o.PlacedAt:O}");
    }
    // No ToList() ever called — 10 million rows stream through ~constant memory
}

private static string Escape(string s)
    => s.Contains(',') ? $"\"{s.Replace("\"", "\"\"")}\"" : s;

Why it works: The HTTP response body is itself a stream. You read one row from the DB, write it to the network, and the GC reclaims it — a continuous pipeline from database to client with no buffer in between. This is the read-side mirror of the streaming upload from the large-payload use case.

Benchmark note: Exporting 5 million rows with ToList() first can consume 2–4 GB of RAM and trigger large object heap fragmentation and gen-2 GC pauses. The streaming version holds well under 50 MB and produces first bytes to the client in milliseconds (time-to-first-byte) instead of after the whole query completes. The cost: the database connection is held for the full duration of the stream — a slow client keeps a connection (and often a transaction) open, so cap concurrent exports and set a command timeout.

---

Way 4: Raw ADO.NET DataReader (maximum throughput)

Best used for: The hottest export/ETL paths where even EF Core's per-row materialisation overhead matters, or when reading from a database without a full EF model.

The concept: DbDataReader is a forward-only, read-only cursor over the result set. It reads one row at a time directly from the network buffer with no entity materialisation, no change tracker, and no LINQ translation layer — the lowest-overhead way to read rows in .NET.

// Raw DataReader — lowest overhead, forward-only streaming
public async IAsyncEnumerable<OrderDto> StreamOrdersAsync(
    [EnumeratorCancellation] CancellationToken ct = default)
{
    await using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync(ct);

    await using var cmd = conn.CreateCommand();
    cmd.CommandText = """
        SELECT Id, CustomerName, Total, PlacedAt
        FROM Orders
        ORDER BY Id
        """;
    cmd.CommandTimeout = 300; // 5 min — long exports need a generous timeout

    // CommandBehavior.SequentialAccess streams large columns without buffering the row
    await using var reader = await cmd.ExecuteReaderAsync(
        System.Data.CommandBehavior.SequentialAccess, ct);

    // Cache ordinals once — never look up columns by name inside the loop
    int idCol = reader.GetOrdinal("Id");
    int nameCol = reader.GetOrdinal("CustomerName");
    int totalCol = reader.GetOrdinal("Total");
    int placedCol = reader.GetOrdinal("PlacedAt");

    while (await reader.ReadAsync(ct))
    {
        yield return new OrderDto(
            reader.GetInt32(idCol),
            reader.GetString(nameCol),
            reader.GetDecimal(totalCol),
            reader.GetDateTime(placedCol));
    }
}

Why it works: No EF Core pipeline means no expression-tree compilation, no entity tracking, no identity resolution. You pull primitives straight out of the reader. For pure data egress this is as fast as managed .NET gets.

Benchmark note: Raw DataReader is typically 2–5× faster than EF Core for large reads (EF Core 8+ narrows this gap considerably with compiled queries). On a 1-million-row read, EF Core with AsNoTracking() might take ~1.2 s while raw DataReader takes ~400 ms. The trade-off is verbosity and manual mapping — only reach for this on proven hot paths. Always cache GetOrdinal outside the loop; calling it per row is a common performance bug that adds a dictionary lookup to every iteration.

---

Way 5: Server-Side Cursors / Batched Fetch

Best used for: Processing huge tables in a background job where you want to commit progress in batches, avoid holding one giant transaction, and survive restarts.

The concept: Rather than one query streaming the whole table (which holds a connection and snapshot for the entire duration), you fetch in bounded batches using keyset paging internally, process each batch, and move the cursor forward. Each batch is an independent short query.

// Batched processing — bounded memory AND bounded transaction scope
public async Task ReprocessAllOrdersAsync(CancellationToken ct)
{
    const int batchSize = 1000;
    int lastId = 0;

    while (!ct.IsCancellationRequested)
    {
        // Each batch is a fresh, short query — keyset on the primary key
        var batch = await _db.Orders
            .AsNoTracking()
            .Where(o => o.Id > lastId)
            .OrderBy(o => o.Id)
            .Take(batchSize)
            .ToListAsync(ct);

        if (batch.Count == 0) break; // done

        foreach (var order in batch)
            await ProcessOrderAsync(order, ct);

        lastId = batch[^1].Id; // advance the cursor

        // Optionally persist progress so a restart resumes from lastId
        await _checkpointStore.SaveAsync("reprocess-orders", lastId, ct);

        _logger.LogInformation("Processed up to order {LastId}", lastId);
    }
}

Why it works: Each batch holds only 1,000 rows in memory and runs a fast keyset query. Because batches are independent, you can checkpoint progress, throttle between batches, and resume after a crash — none of which is possible with a single long-running stream.

Benchmark note: Batch size is a tuning knob. Too small (e.g. 10) means excessive round-trips dominate (network latency × millions/10). Too large (e.g. 100,000) reintroduces memory pressure and long transactions. For most workloads 500–5,000 rows per batch balances round-trip cost against memory; measure with your actual row width. A 2-million-row reprocess at batch size 1,000 is 2,000 round-trips — at ~2 ms each that is ~4 s of pure latency overhead, usually negligible against the processing work itself.

---

Way 6: SqlBulkCopy / COPY for Export (DB → file)

Best used for: Moving entire tables out of the database as fast as physically possible — database migrations, nightly warehouse loads, full backups to flat files.

The concept: Both SQL Server (bcp/SqlBulkCopy read path) and PostgreSQL (COPY TO via Npgsql) have bulk-export paths that bypass the normal row-by-row protocol and stream rows in an optimised binary or text format. For PostgreSQL, Npgsql's BeginRawBinaryCopy/COPY ... TO STDOUT is the fastest way to get rows out.

// PostgreSQL COPY export via Npgsql — fastest bulk egress
public async Task ExportOrdersToFileAsync(string filePath, CancellationToken ct)
{
    await using var conn = new NpgsqlConnection(_connectionString);
    await conn.OpenAsync(ct);

    await using var outFile = File.Create(filePath);

    // COPY ... TO STDOUT streams the whole table in PostgreSQL's optimised format
    await using var reader = await conn.BeginTextExportAsync(
        "COPY (SELECT id, customer_name, total, placed_at FROM orders ORDER BY id) " +
        "TO STDOUT WITH (FORMAT csv, HEADER true)", ct);

    await using var writer = new StreamWriter(outFile);
    char[] buffer = new char[8192];
    int read;
    while ((read = await reader.ReadAsync(buffer, ct)) > 0)
        await writer.WriteAsync(buffer.AsMemory(0, read));
}

Why it works: COPY is purpose-built for bulk transfer — it skips per-row protocol overhead, planner round-trips, and result-set framing. The database writes a continuous stream in a compact format that you pipe straight to disk.

Benchmark note: PostgreSQL COPY TO can export 5–10× faster than a row-by-row SELECT — exporting 10 million rows might take ~8 s with COPY versus ~60 s reading row-by-row. The catch: you get raw rows with no application-side transformation mid-stream (you are pushing format work to the database's COPY formatter), and it is best for full-table or simple-filter extracts, not complex per-row business logic.

---

Choosing the right approach

• UI grid with page numbers, moderate data → Offset pagination (Way 1)
• Infinite scroll / very deep paging / public API → Keyset pagination (Way 2)
• Export to CSV/JSON over HTTP → IAsyncEnumerable streaming (Way 3)
• Hottest ETL read path, no business logic → Raw DataReader (Way 4)
• Background reprocessing with checkpoints → Batched keyset (Way 5)
• Full-table dump to file, maximum speed → COPY / bulk export (Way 6)

The two rules that apply to all six: always use AsNoTracking() for read-only queries (change tracking on a million rows wastes memory and CPU), and always have an index that matches your ORDER BY — without it, the database sorts the entire result set in memory or tempdb before returning the first row, which negates every other optimisation here.

Note: this is an informational engineering overview. Benchmark figures are illustrative order-of-magnitude estimates — actual numbers depend heavily on schema, indexes, row width, hardware, and database configuration, so always measure against your own workload before optimising.