From 50 Minutes to 90 Seconds: A Postmortem on Scaling a Bulk Data Export Feature

An "export roster to Excel" endpoint in a web application worked reliably for small classrooms (40–100 records). However, when pointed at an enterprise-scale dataset with ~8,600 rows and ~6,700 embedded student ID photos in a 152 MB file, two cascading failures occurred:

1. An immediate crash with System.InvalidCastException originating from inside Microsoft.EntityFrameworkCore.
2. Once that was resolved, a 40–50 minute execution duration that tripped reverse-proxy HTTP timeouts and froze browser tabs.

System Overview

Stripped of proprietary business logic, the bulk export endpoint coordinates four distinct layers across external APIs, relational stores, in-memory processing, and document serialization:

The complete runtime sequence across distributed boundaries is illustrated below:

sequenceDiagram
    autonumber
    actor Client as Browser Client
    participant API as Export Endpoint
    participant LMS as External REST API (Paginated)
    participant SIS as Relational DB #1 (Enrichment)
    participant Photos as Relational DB #2 (Binary Photos)
    participant XLSX as OpenXML Streaming Writer
 
    Client->>API: POST /export { filters, columns }
    API->>LMS: GET page 1 of roster
    LMS-->>API: rows + pagination header (rel="last")
    loop Remaining Pages (Bounded Concurrency x8)
        API->>LMS: GET page N
        LMS-->>API: page rows
    end
    API->>SIS: Lookup enrichment fields by ID (Defensive Batching)
    SIS-->>API: Matched records
    loop Photo Batches (Bounded Concurrency x4)
        API->>Photos: SELECT binary photo BLOBs by OwnerId
        Photos-->>API: Photo byte arrays
    end
    API->>API: O(1) Dictionary Merge (Records + Photos)
    API->>XLSX: Stream OpenXML ZipArchive parts directly
    XLSX-->>API: 152 MB XLSX binary stream
    API-->>Client: 200 OK (application/vnd.openxmlformats-officedocument)

Incident #1: The Crash (ORM Internals & Boundary Hygiene)

The Symptom

Exporting a large dataset containing several thousand student records immediately aborted with an unhandled exception:

System.InvalidCastException: Object must implement IConvertible.
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter.Sanitize[T](Object value)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](...)

The exception was traced to an innocuous-looking query expression:

var matches = queryable.Where(record => idsToMatch.Contains(record.ExternalId));

Where idsToMatch was an in-memory List<string>. While developers often assume .Contains() against a list is evaluated in-memory as a simple hash/equality check, against an IQueryable it is captured as an Expression Tree and translated into SQL dialect by the ORM provider.

Following the Trail

A careful investigation unraveled multiple interacting layers:

1. Unclean Upstream Data: The external REST API returned thousands of records where the majority had null external identifiers. The unvalidated list was passed directly into .Contains(record.ExternalId).
2. The Parameter Translation Mode Myth: A common hypothesis is that large collections exceed SQL Server's 2,100 parameter ceiling and force a fallback. However, in Entity Framework Core 10, expanding collections into multiple scalar parameters (WHERE Id IN (@p1, @p2, ...)) is the default. The alternative single-parameter mode (ParameterTranslationMode.Parameter, which serializes the collection as a JSON array unpacked server-side via OPENJSON) was the default in EF Core 8/9 and remains an opt-in mode in EF Core 10.
3. The Upstream Regression: The project had retained ParameterTranslationMode.Parameter. In EF Core 10.0.2, passing a null element inside a nullable collection to .Contains() under this mode caused ValueConverter.Sanitize<T>() to invoke Convert.ChangeType on a null value, triggering the exact InvalidCastException. This issue is tracked publicly as dotnet/efcore#37537 and resolved in PR #38066.
4. Mismatched Transitive Packages: A dependency audit revealed mixed package versions across projects (Microsoft.EntityFrameworkCore was patched, but a provider package was pinned to an older build).

The Fix: Defend the Boundary, Then Align Dependencies

flowchart LR
    A["Raw ID list from Upstream API"] --> B{"Null or Blank?"}
    B -- "Yes" --> C["Drop / Ignore"]
    B -- "No" --> D{"Duplicate Entry?"}
    D -- "Yes" --> C
    D -- "No" --> E["Add to Valid Set (Distinct)"]
    E --> F["Chunk into 1,000-Item Batches"]
    F --> G["Execute Parameterized DB Query"]

public async Task<List<StudentRecord>> GetEnrichmentRecordsAsync(List<string> rawIds)
{
    if (rawIds == null || rawIds.Count == 0)
        return new List<StudentRecord>();
 
    // 1. Defend the boundary: filter nulls, empty strings, and duplicates
    var validIds = rawIds
        .Where(id => !string.IsNullOrWhiteSpace(id))
        .Distinct(StringComparer.Ordinal)
        .ToList();
 
    if (validIds.Count == 0)
        return new List<StudentRecord>();
 
    var queryable = (await _repository.GetQueryableAsync()).AsNoTracking();
 
    // 2. Chunk defensively to respect database engine parameter limits
    var result = new List<StudentRecord>();
    foreach (var batch in validIds.Chunk(1000))
    {
        var batchIds = batch.ToList();
        var batchResult = await queryable
            .Where(record => batchIds.Contains(record.ExternalId))
            .ToListAsync();
        result.AddRange(batchResult);
    }
    return result;
}

# Verify that all EntityFrameworkCore packages share identical version numbers
dotnet list <project>.csproj package --include-transitive | Select-String 'EntityFrameworkCore'

Diagnostic Telemetry

Adding privacy-safe structural logging before the database boundary provided immediate clarity:

var validIds = rawIds.Where(id => !string.IsNullOrWhiteSpace(id))
                      .Distinct(StringComparer.Ordinal)
                      .ToList();
 
logger.LogInformation(
    "Export ID diagnostics: Total={TotalCount}, Null={NullCount}, DistinctValid={ValidCount}",
    rawIds.Count,
    rawIds.Count(id => id is null),
    validIds.Count);

Output: Total=3180, Null=3131, DistinctValid=49. This revealed that 98.4% of rows lacked external keys upstream, instantly clarifying why the crash was triggered only on specific datasets.

Incident #2: The Slowness (4 Compounded Bottlenecks)

With the crash resolved, exporting a complete dataset of ~8,600 rows still took 40–50 minutes. Profiling each stage with structured Stopwatch timers uncovered an evidence-backed distribution:

Bottleneck 1: Sequential Pagination Against External API

The upstream REST API paginated records at 100 items per page (87 total pages). The original implementation fetched pages serially:

// ❌ ANTI-PATTERN: Serial await per page over HTTP
string url = BuildPageUrl(page: 1);
while (url != null)
{
    var response = await httpClient.GetAsync(url);
    var page = await ParseAsync(response);
    allRecords.AddRange(page.Items);
    url = GetNextPageUrl(response);
}

At ~1.9 seconds per roundtrip across 87 pages, serial execution guaranteed a ~2m 43s floor regardless of server CPU or memory.

The Fix: Bounded Fan-Out via SemaphoreSlim

The REST API exposes the total page count via a Link response header (rel="last"). We parse this on the first request and fan out remaining requests with SemaphoreSlim to bound concurrency:

var firstPageResponse = await httpClient.GetAsync(BuildPageUrl(page: 1));
var firstPage = await ParseAsync(firstPageResponse);
allRecords.AddRange(firstPage.Items);
 
int lastPage = TryGetLastPageFromLinkHeader(firstPageResponse.Headers);
 
if (lastPage > 1)
{
    using var throttle = new SemaphoreSlim(8); // Cap concurrent upstream HTTP requests
 
    var pageTasks = Enumerable.Range(2, lastPage - 1).Select(async pageNumber =>
    {
        await throttle.WaitAsync();
        try
        {
            var response = await httpClient.GetAsync(BuildPageUrl(pageNumber));
            return response.IsSuccessStatusCode
                ? await ParseAsync(response)
                : new PageResult(Array.Empty<Record>());
        }
        catch (Exception ex)
        {
            logger.LogWarning(ex, "Failed to fetch page {PageNumber}", pageNumber);
            return new PageResult(Array.Empty<Record>());
        }
        finally
        {
            throttle.Release();
        }
    });
 
    var pages = await Task.WhenAll(pageTasks);
    foreach (var page in pages)
        allRecords.AddRange(page.Items);
}

Benchmark Result: Runtime dropped from 2 min 43 s → 31.2 s (~5.2x faster).

Bottleneck 2: Sequential Batched Database Lookups

Photo lookups for thousands of records required batching to respect database IN parameter limits (1,000 items). While chunking was present, batches executed serially over a single database connection:

// ❌ ANTI-PATTERN: Correct chunking, but executed serially
foreach (var batch in ids.Chunk(1000))
{
    var batchIds = batch.ToList();
    var photos = await dbContext.Photos
        .Where(p => batchIds.Contains(p.OwnerId))
        .ToListAsync();
    result.AddRange(photos);
}

The Fix: Parallel Bounded DbContext Execution

By creating short-lived, dedicated DbContext instances per task and throttling via SemaphoreSlim(4), queries execute concurrently without saturating connection pools:

var batches = ids.Chunk(1000).ToList();
using var throttle = new SemaphoreSlim(4); // Sized to match database connection pool headroom
 
var batchTasks = batches.Select(async batch =>
{
    await throttle.WaitAsync();
    try
    {
        var batchIds = batch.ToList();
        // DbContext is NOT thread-safe: create an isolated instance per task
        using var dbContext = CreateDedicatedDbContext();
        return await dbContext.Photos
            .AsNoTracking()
            .Where(p => batchIds.Contains(p.OwnerId))
            .ToListAsync();
    }
    finally
    {
        throttle.Release();
    }
});
 
var results = await Task.WhenAll(batchTasks);
foreach (var batch in results)
    photos.AddRange(batch);

Benchmark Result: Photo querying dropped from 3 min 00 s → 49.2 s (~3.7x faster).

Bottleneck 3: O(n²) Join Hiding in Plain Sight

Once records and photo records were in memory, matching each row to its corresponding photo was performed via a linear scan:

// ❌ ANTI-PATTERN: O(n × m) linear scan for every row
var photo = photos.FirstOrDefault(p => p.OwnerId == record.ExternalId);

While harmless for 40 rows, with ~8,600 records against ~6,700 photos, this required up to 57 million equality evaluations—and was duplicated across multiple transformation passes.

The Fix: O(1) Pre-Indexed Hash Lookup

Building a dedicated dictionary before the loop transforms linear scans into instantaneous hash lookups:

public static Dictionary<string, byte[]> BuildPhotoLookup(IEnumerable<PhotoRecord> photos)
{
    var lookup = new Dictionary<string, byte[]>(StringComparer.OrdinalIgnoreCase);
    foreach (var photo in photos)
    {
        if (string.IsNullOrEmpty(photo.OwnerId) || photo.Bytes is not { Length: > 0 })
            continue;
 
        lookup[photo.OwnerId] = photo.Bytes; // First match wins
    }
    return lookup;
}
 
// Inside the row iteration loop — O(1) instantaneous lookup:
photoLookup.TryGetValue(record.ExternalId, out var photoBytes);

Benchmark Result: In-memory merging dropped from 7.7 s → 5 ms (~1,500x faster).

Bottleneck 4: DOM-Based Spreadsheet Serialization

The single largest bottleneck consumed 85% of total request time (40 to 50 minutes). The spreadsheet library constructed the entire workbook as a deep in-memory Document Object Model (DOM)—allocating nodes for every cell, formula, style, and embedded image—before serializing and deflating the entire package at the end.

When embedding ~6,700 images into a 152 MB file, the DOM library caused severe GC pressure, repetitive tree traversals, and wasted CPU cycles attempting to compress already-compressed JPEG/PNG streams.

The Fix: Direct Streaming OpenXML ZipArchive

An .xlsx workbook is simply a standard ZIP package containing OpenXML parts. Writing XML strings and raw image streams directly to a ZipArchive with CompressionLevel.NoCompression bypasses the DOM entirely:

using var memoryStream = new MemoryStream();
using (var zip = new ZipArchive(memoryStream, ZipArchiveMode.Create, leaveOpen: true))
{
    // 1. Write foundational OpenXML structural manifests
    WriteZipEntry(zip, "[Content_Types].xml", ContentTypesXml(hasImages: photoLookup.Count > 0));
    WriteZipEntry(zip, "_rels/.rels", RootRelsXml());
    WriteZipEntry(zip, "xl/workbook.xml", WorkbookXml());
    WriteZipEntry(zip, "xl/styles.xml", StylesXml());
 
    // 2. Stream worksheet rows directly without intermediate cell objects
    WriteWorksheetStreaming(zip, records, columns, photoLookup);
 
    // 3. Write DrawingML anchors and store image bytes directly
    if (photoLookup.Count > 0)
    {
        WriteDrawingRelationships(zip, photoLookup);
        // Images are already compressed — NoCompression avoids redundant CPU deflation
        WriteImageParts(zip, photoLookup, compressionLevel: CompressionLevel.NoCompression);
    }
}
return memoryStream.ToArray();

Complementary Image Optimization

Each embedded photo is validated, bounded to thumbnail dimensions, and re-encoded (JPEG for opaque images, PNG for alpha channels) before embedding, preventing oversized raw uploads from ballooning output sizes.

Benchmark Result: Workbook serialization dropped from ~45 minutes → 429 milliseconds (>5,000x faster).

Results & Comparative Benchmarks

Applying all four optimizations eliminated all major scaling cliffs for the ~8,600 record / ~6,700 photo dataset:

gantt
    title Bulk Export Duration — Baseline vs Optimized (Same Dataset)
    dateFormat X
    axisFormat %s
 
    section Baseline (~50 min)
    Paginated API Fetch (Sequential)        :done, b1, 0, 163
    Enrichment DB Lookup                    :done, b2, 163, 165
    Photo DB Lookups (Sequential)           :done, b3, 165, 345
    In-Memory Merge (O(n²))                 :done, b4, 345, 405
    DOM Spreadsheet Writer (DOM + Deflate)  :crit, b5, 405, 2805
 
    section Optimized (~90 s)
    Paginated API Fetch (Parallel x8)       :done, a1, 0, 31
    Enrichment DB Lookup                    :done, a2, 31, 32
    Photo DB Lookups (Parallel x4)          :done, a3, 32, 81
    In-Memory Merge (O(1) Dictionary)       :done, a4, 81, 82
    Streaming OpenXML ZipArchive            :done, a5, 82, 83
    HTTP Network Transfer                   :done, a6, 83, 91

Comprehensive Benchmark Summary

Architectural Lessons & Best Practices

1. Know Your ORM's SQL Translation Mode

.Contains() against an IQueryable compiles to an Expression Tree, not in-memory evaluation. Understand whether your ORM uses scalar expansion or JSON array parameters, and audit transitive package dependencies to prevent mixed-version behavior.

2. Guard Against O(n²) Iterations

A list.FirstOrDefault(x => x.Id == target) is harmless once, but inside a loop over thousands of records, it creates quadratic complexity. Construct a dictionary or lookup index once before looping.

3. General-Purpose Libraries Are Built for General Use Cases

Full-featured spreadsheet or PDF libraries prioritize versatility over extreme throughput. When profiling reveals that library-internal DOM abstractions consume 85%+ of execution time, writing a narrow, spec-compliant streaming writer unlocks orders of magnitude in speed.