The batch size bug I found while benchmarking

Davide Dal Cortivo · October 8, 2026

What I learned benchmarking my Dapper extension against hand-written Dapper and EF Core: reads and writes slow down for different reasons, a test turned up a real bug in code I had already shipped, and one release later I went after a small gap the first round of fixes had left open.

Forget is a thin layer over Dapper for MySQL, Oracle, PostgreSQL and SQL Server: type-safe CRUD, filtering, sorting and paging, with the SQL each engine actually wants. This post is about what happened when I sat down to benchmark it properly, and what the numbers taught me.

Publishing the numbers, including the bad ones

The benchmark suite is in the repository. It runs with BenchmarkDotNet against real PostgreSQL containers, and the results go into the benchmarks README as they come out, including the cases where Forget loses. I also tried not to stop once the numbers looked good enough to show, and to keep asking why a specific number moved the way it did.

Asking that a few times led to two real findings and one bug fix. One release later it led to a smaller gap, and to a fix that first made things worse.

Reads bottleneck on round trips

GetByIdRange fetches many rows by id in one call. Forget splits the ids into batches, 500 per round trip by default. One reason is the parameter limit of some engines (SQL Server accepts about 2,100 per command); the other is not handing the database a single statement with thousands of parameters. The hand-written Dapper query and EF Core's query both send all the ids at once, in a single = ANY(...) / IN (...).

So I expected more ids to cost Forget more round trips, and they do. What I didn't expect was the direction EF Core moves as the id count grows:

Forget gets worse with more ids, EF Core gets better, and the cause is the same. EF Core's single query never needs batching, so its relative cost is mostly fixed overhead, which matters less as the result grows. Forget's default batch needs more round trips as the ids grow (2 at 1,000, 20 at 10,000), and each one costs something.

To check this instead of just assuming it, I added a variant that sets batchSize to the id count, so it always makes a single round trip, like Dapper and EF Core:

connection.GetByIdRange<Product>(ids, batchSize: ids.Length);

That's flat and close to Dapper at every size, so for reads the round trips were most of the gap (the rest comes later in this post). The batch is still there for a reason: the parameter limits are real, and a statement with thousands of parameters has its own cost. But on PostgreSQL, the only engine these benchmarks run on, if you know how many ids you have and want one round trip, you can ask for it.

Writes bottleneck on statement count

I expected InsertRange and UpdateRange to tell the same story. On PostgreSQL, EF Core sends one statement per row for these, so I thought round trips would explain its cost, and that it would fall further behind Forget as the row count grew.

First surprise: EF Core doesn't make one round trip per row. I counted DbCommand executions with LogTo filtered to CommandExecuted, and it sends the statements in chunks of 1,000 per round trip: 1 round trip for 1,000 rows, 10 for 10,000.

Second surprise, and the important one: at 10,000 rows EF Core makes fewer round trips than Forget with its default batch (10 against 20). If round trips were what mattered, EF Core should close the gap, as it did for reads. It doesn't:

EF Core falls further behind as the rows grow, even with fewer round trips. What grows badly is the number of separate statements the database has to parse and plan: at 10,000 rows, that's 10,000 single-row statements for EF Core and 20 multi-row statements of 500 rows each for Forget. Round trips and statement count usually grow together, so it's easy to blame the wrong one. Here they didn't, and that's what showed the real cause. Forget itself stayed roughly 13x to 16x faster than the naive per-row Dapper loop at every size, with or without batching.

Typed overloads: removing the client-side work

If you already had your ids in a typed array like int[] ids, you still went through a non-generic IEnumerable parameter. Every id came back boxed from the enumerator, had its type checked one by one, and was copied into an array built with reflection before being bound as a parameter. None of that is needed when the type is known at compile time.

So GetByIdRange and DeleteRange got a generic overload:

IReadOnlyList<Product> rows = connection.GetByIdRange<Product, int>(ids);

With TKey known, the type is checked once instead of once per id, nothing is boxed, and the array is built directly instead of through Array.CreateInstance/SetValue. I also changed the non-generic overload to size its list of ids to the id count up front instead of growing it one item at a time, which is why its numbers below are lower than when the typed overload first shipped. Measured without a database, building the commands for 100, 1,000 and 10,000 ids takes:

That's 7x to 100x faster with 44% to 88% less memory, and the gap grows with the id count, because the non-generic cost grows with it and the typed one barely moves. Against a real database, with the round trips equalized, the typed call came in at 1.04x Dapper at 10,000 ids, against 1.07x for the non-generic one. The difference is much smaller there because most of the time is the query itself. Those two numbers also moved in the next release; more on that below.

GetById and Delete take a single id, and got the same typed overload for consistency. That one made no measurable difference: 204 ns against 194 ns, the same memory, well inside the noise. With a single id there is no loop for the typed version to skip, and checking one type costs the same either way. The gain was in the loop.

The bug a test found, twice

Adding the typed overloads meant writing a test that checks both overloads build the same commands for the same ids. On SQL Server, with enough ids and a large explicit batchSize, that test failed: DeleteRangeCommands produced a command that SQL Server refused to run:

I was using an AI assistant for this part of the work, and its first verdict was that my new test was the problem: it asked for more ids than one command can hold, so the fix belonged in the test. I didn't trust that answer, so before changing the test I went back to the code.

GetByIdRangeCommands caps batchSize to the parameter limit before building anything. DeleteRangeCommands didn't. It capped the size of each chunk it built, but let batchSize, however large, decide how many chunks went into one command. Its own documentation already promised the cap; the code just didn't apply it:

// what it did
return DbCommandStrategy.Instance.DeleteRangeCommands(connection, entities, batchSize, SqlDialectStrategy.Instance.MaxParameterCount);

// what it does now
batchSize = batchSize > 0 ? Math.Min(batchSize, SqlDialectStrategy.Instance.MaxParameterCount) : SqlDialectStrategy.Instance.MaxParameterCount;
return DbCommandStrategy.Instance.DeleteRangeCommands(connection, entities, batchSize, 0);

DeleteRange and DeleteRangeAsync, the methods that actually run the commands, already capped batchSize correctly and were never affected. Only the method that builds commands for inspection or manual execution had the problem, and it was there before this work started.

Then the obvious question: did InsertRangeCommands, UpdateRangeCommands and UpsertRangeCommands on SQL Server have the same problem, since they build commands the same way? They did, and on those three it didn't even take an explicit batchSize: the default of 500 was enough to break an entity with five or more columns once a call had 420 rows or more. Same missing cap, same fix, and three more tests that build the commands and run them against a real SQL Server.

None of this was visible from reading the code. It only showed up when SQL Server had to run the commands.

What's in 2.3.0

Typed GetByIdRange, DeleteRange, GetById and Delete overloads on all four providers, and the parameter-limit fix above for SQL Server's DeleteRangeCommands, InsertRangeCommands, UpdateRangeCommands and UpsertRangeCommands. The full numbers, the benchmark code and how the suite is kept fair (the same connection for every library, no change tracking for EF Core, every result checked against the Dapper baseline before it's trusted) are in the repository.

The typed overload still boxed on the way out

That shipped as 2.3.0. When I re-ran the benchmarks on it, the typed GetByIdRange no longer boxed ids on the way in, but it still did on the way out. A batched call deduplicates the rows it returns by id, which needs a set keyed by id, and that set was still the HashSet<object> shared with the non-generic overload: I hadn't made it generic when the typed overload shipped. With the round trips equalized, the typed call was still a couple of percent behind EF Core's query. Small, but consistent across runs, so I went after it.

My first guess was the dedup pass itself. A diagnostic variant, the same Dapper query followed by the same HashSet and copy that Forget does and nothing else, cost about 0.1 to 0.3 ms per 10,000 rows, with more noise between runs than that. Real, but not enough to explain the gap.

Next I ran the exact command Forget builds straight through Dapper, with no Forget code after the query. That matched the plain Dapper baseline, so the gap wasn't in the SQL or the parameters. It was somewhere in the few hundred lines of C# between the query returning and the typed overload returning. Rebuilding that code piece by piece inside the database benchmark didn't settle it: each piece moved by 0.2 to 0.9 ms between identical runs, more than the effect I was trying to measure. A database round trip is too noisy to measure something that small.

So I moved the comparison out of the database, into a plain benchmark over 10,000 rows already in memory, and measured it twice: at rest, and after first creating about 2.7 MB of short-lived garbage, to stand in for what a query leaves behind before the dedup pass runs:

At rest, boxing 10,000 ids costs about what you'd expect, and still too little to explain a gap of almost a millisecond against a real database. Under some memory pressure, the boxed version got about three times slower while the typed one barely moved. Measuring the garbage collector's pause time directly (GC.GetTotalPauseDuration(), before and after each block) showed the pause accounted for almost all of that jump. A small harness that measured the same pause around the real database call confirmed it: the boxed dedup pass added about 290 µs of pause over the bare command, and the typed one added none that the harness could detect.

The fix was the same one 2.3.0 had applied to the input side: make the dedup pass generic too. The typed overload now deduplicates with a HashSet<TKey> (with a byte-content comparer when TKey is byte[], since .NET compares byte arrays by reference) instead of boxing every id into the set the non-generic overload uses.

The first measurement after the fix was worse, not better: allocation went up, from 3,403 KB to about 3,500 KB at 10,000 ids. The new set had no initial capacity, so it grew from empty to 10,000 entries through several reallocations, and that cost more than the boxing it removed. The set can never hold more entries than the ids it's given, so that count is an exact upper bound. Sizing the set to it up front, as the non-generic set already did, fixed it: 3,129 KB, lower than before any of this, and the same in three separate runs.

Once I knew to look for it, the same mistake (a collection grown one item at a time when the final count is already known) turned up in a few other places: the boxed id list the non-generic overloads build (the reason their numbers in the section above went down), the list of commands the four providers build one batch at a time, and the list of values a Contains/In filter collects. The last two are exact upper bounds as well, but at the sizes this suite measures (a few dozen batches, 10 ids in the Contains case) they make no measurable difference. I fixed them anyway.

With one round trip, the typed call now measures 1.05x, 1.02x and 1.03x Dapper at 100, 1,000 and 10,000 ids, against 1.29x, 1.13x and 1.03x for EF Core's query. Below 10,000 ids EF Core is the slowest of the three, because its per-call overhead is a bigger share of a shorter call; at 10,000 it's roughly level with Forget. The typed call also allocates less than EF Core at every size: 35 KB against 76 at 100 ids, 298 KB against 384 at 1,000, and 3,129 KB against 3,573 at 10,000.

What's in 2.3.1

The typed GetByIdRange dedup pass no longer boxes ids. The boxed id list built by the non-generic GetByIdRange/DeleteRange overloads, the list of commands the providers build in batches, and the list of values a Contains/In filter collects are now all sized up front instead of grown one item at a time. Behaviour and public API are unchanged. The numbers above, and the full results in the benchmarks README, are the ones to quote.