skip to content

In EF Core 10, how many statements does SaveChangesAsync send per round trip on SQL Server, PostgreSQL and SQLite, and when would you change MaxBatchSize?

level: middleimportance: should knowfreq 30%

answer

  1. several statements, one DbCommand
  2. SQL Server stops at 42
  3. Npgsql allows 1000
  4. SQLite sends one at a time
  5. a set-based update needs another API

basics

~20 s

SaveChangesAsync packs its INSERT, UPDATE and DELETE statements into batches: up to 42 per round trip on SQL Server, 1000 with the Npgsql provider, and one at a time on SQLite. MaxBatchSize changes that limit, but only measurement justifies raising it.

solid answer

~40 s

EF Core turns each pending change into one statement and sends several statements as one command per round trip. The SQL Server provider defaults `MaxBatchSize` to 42, caps any configured value at 1000, and also splits a batch before it passes 2,098 parameters. The Npgsql provider defaults to 1000, and the SQLite provider never batches: it uses `SingularModificationCommandBatch`, one statement per command, which costs little because the database is in-process. You set it per context with `UseSqlServer(cs, o => o.MaxBatchSize(100))` or the same call on `UseNpgsql`. Raise it only when a measured import is dominated by round trips; it never turns 10,000 updates into one statement, which is what `ExecuteUpdateAsync` or a driver's bulk-copy API is for.

code

csharp · 13 lines
csharp
builder.Services.AddDbContext<GearImportContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("GearWarehouse"),
        sql => sql.MaxBatchSize(100)));   // default 42, values above 1000 are capped

// nightly price import: 5,000 rows -> 50 batches instead of about 120
var products = await importDb.Products.ToDictionaryAsync(p => p.Sku, cancellationToken);
foreach (var row in priceFeed)
{
    var product = products[row.Sku];
    product.PriceCents = row.PriceCents;
}
await importDb.SaveChangesAsync(cancellationToken);

go deeper

for a junior

Recall that SaveChangesAsync batches statements, that SQL Server's default is 42, and that MaxBatchSize is set on the provider's options builder.

for a middle

Explain the per-provider defaults, the SQL Server cap and parameter limit, and why the SQLite provider sends one statement per command.

for a senior

Show how you would measure batches in the log, decide whether a write path is round-trip bound, and pick ExecuteUpdateAsync or bulk copy instead.

for a principal

Decide where bulk writes leave EF Core for driver-level APIs, and how import jobs are isolated from request-serving contexts and their settings.

## How SaveChanges sends its work When `SaveChangesAsync` runs, EF Core turns each added, modified or deleted entity into a **modification command**: one `INSERT`, `UPDATE` or `DELETE`. It orders those commands so dependent rows come after the rows they point to, and then packs them into **batches**: several statements sent in one database command, so one network round trip carries many changes. `MaxBatchSize` is the provider option that caps how many statements go into one batch. ## Provider defaults | Provider package | Default statements per batch | Notes | |---|---|---| | `Microsoft.EntityFrameworkCore.SqlServer` | 42 | a configured value above 1000 is capped at 1000; a batch is also split before it exceeds 2,098 parameters, just under SQL Server's limit of 2,100 | | `Npgsql.EntityFrameworkCore.PostgreSQL` | 1000 | the provider also sets `MinBatchSize` to 2 unless you change it | | `Microsoft.EntityFrameworkCore.Sqlite` | one statement per command | the provider uses `SingularModificationCommandBatch`, so it does not batch whatever `MaxBatchSize` says | The SQL Server default comes from measurement: the EF Core documentation reports that batching is generally less efficient below about four statements and that its benefit fades after about forty, hence 42. SQLite runs inside the application's process, so a round trip is a function call rather than a network hop, and batching would buy little. ## Changing the limits Both options live on the provider's options builder: - `UseSqlServer(connectionString, o => o.MaxBatchSize(100))` raises the SQL Server limit for that context; - `UseNpgsql(connectionString, o => o.MaxBatchSize(200))` lowers the PostgreSQL one; - `MinBatchSize(n)` makes EF send a group smaller than `n` as individual statements instead of a batch; the relational default is 1; - `MaxBatchSize(1)` disables batching entirely. A value of zero or below is rejected when the options are built or the batch factory is created. ## Reading batches in the log Each batch appears as one `Executed DbCommand` entry that carries the SQL and parameters of all its modification commands. On SQL Server, inserts into the same table inside one batch may even be combined into a single `MERGE` or multi-row `INSERT`, but the batch still holds at most `MaxBatchSize` modification commands. An import of 100 new products on SQL Server therefore shows three commands, covering 42, 42 and 16 rows, as long as the parameter limit is not reached first. On SQLite the same import shows 100 commands, one per `INSERT`. Counting those entries, and reading their elapsed times, tells you whether round trips or the statements themselves cost the time. When a `SaveChangesAsync` call needs more than one statement, EF wraps all of its batches in one transaction by default, so splitting the work into more or fewer batches does not change what commits together. ## When to touch it 1. **Measure first.** Each batch is one `Executed DbCommand` entry in EF's log, so a nightly import of 5,000 rows on SQL Server shows about 120 of them. Time the import before and after any change. 2. **Raise it on SQL Server** when a measured write path is dominated by round trips, for example an import over a high-latency link. Benchmark a few values; bigger is not automatically faster. 3. **Lower it** when very large batches hold locks or memory longer than you can accept, or when a batch hits a server-side limit. ## What it cannot fix - Batching reduces **round trips**, not **statements**. Ten thousand changed products are still ten thousand `UPDATE` statements, each executed by the database. - A set-based change, such as marking every discontinued item inactive, belongs in `ExecuteUpdateAsync`, which sends one statement and loads no entities. - Very large inserts outgrow EF altogether: `SqlBulkCopy` in Microsoft.Data.SqlClient and Npgsql's `BeginBinaryImport` stream rows through the drivers' bulk paths. - Loading the entities first is often the bigger cost; check the reads before tuning the writes. - By default a batch that fails rolls back with the rest of the `SaveChangesAsync` call, so bigger batches do not make partial success more likely; they only change how the work is split on the wire.

  • Your SQLite-based integration tests show one command per inserted row; is batching broken?
    No. The SQLite provider's batch factory creates `SingularModificationCommandBatch`, which limits every batch to one statement, so each change is its own command regardless of `MaxBatchSize`. SQLite is in-process, so there is no network round trip to save, and the tests do not predict how SQL Server or PostgreSQL will batch.
  • Why does a SQL Server batch sometimes hold fewer than MaxBatchSize statements?
    SQL Server accepts at most 2,100 parameters per command, so EF's SQL Server batch stops adding statements before it exceeds 2,098. Entities with many columns therefore produce smaller batches than the configured limit, and raising `MaxBatchSize` does not change that.

  • EF Core sends one round trip per entity unless a bulk extension is installed.
  • SQL Server's default MaxBatchSize is 1000, the same as Npgsql's.
  • Setting MaxBatchSize(5000) on SQL Server sends 5000 statements per round trip.
  • A larger MaxBatchSize turns many UPDATE statements into one set-based UPDATE.
  • SQLite batches inserts the same way SQL Server does.