# SaveChanges shouldn't take four seconds to insert 100,000 rows

> **Update, 1.0.3:** ADO.NET update and delete now take a `SqlTransaction`, `DbSet<T>` gets `BulkSyncAsync`, `UpdateOnlyIfChanged` skips unchanged rows, the `UpdateInsert` merge strategy is available, and ADO.NET now reads identity, computed, and rowversion columns from SQL Server's own metadata instead of guessing. The full suite (60 tests against a real SQL Server) passes. [NuGet: SqlBulkPro](https://www.nuget.org/packages/SqlBulkPro)

If you have ever watched an import job stall after `AddRange` and `SaveChanges`, you already know the feeling. EF Core is doing honest work: it tracks every entity, generates commands, and talks to SQL Server more than you wanted. That is the right tradeoff when you are saving a cart or a user profile. It is a painful one when you are writing thousands or hundreds of thousands of rows.

I also know the other path by heart: `SqlBulkCopy` into a temp table, a `MERGE` with `HOLDLOCK` so two imports do not race, a transaction you actually commit, and a decision about identity values. It works. It is also easy to get slightly wrong.

So I built **SqlBulkPro**: a small .NET 8/9 library that uses `SqlBulkCopy` and `MERGE` under the hood and gives you one method for the usual cases. It works with EF Core and with Dapper / ADO.NET. This post is the problem, the API, real numbers from one benchmark machine, and when you should *not* use it.

### Why `SaveChanges` slows down at volume

EF Core is a change tracker first and a SQL generator second. For a handful of rows that is a gift. At a few thousand rows, every entity is tracked, and setup plus round-trip overhead starts to dominate. That is not a knock on EF Core. I still use it for the rest of the app. I just stopped asking it to be a bulk loader.

There is another long-open gap: EF Core has no built-in upsert. If you have followed [dotnet/efcore#4526](https://github.com/dotnet/efcore/issues/4526) since 2016, you have probably written the same "try update, else insert" loop more than once.

### Why hand-rolled `SqlBulkCopy` + `MERGE` is fiddly

`SqlBulkCopy` is fast because it is a streaming bulk path into SQL Server. Insert-only is the easy part. The moment you need "insert if new, update if the key already exists," you are creating a temp table, bulk-copying into it, `MERGE`ing with `HOLDLOCK` if two writers can collide, and keeping the whole thing in one transaction so a failed merge does not leave a half-written table.

It is easy to skip the lock hint or forget to enlist `SqlBulkCopy` in the transaction. Those bugs are rare until they are not.

### What SqlBulkPro does

SqlBulkPro wraps that pattern behind a boring API:

```csharp
using SqlBulkPro;

await dbContext.BulkInsertAsync(products);
await dbContext.BulkUpsertAsync(products);
await dbContext.BulkUpdateAsync(products);
await dbContext.BulkDeleteAsync(products);
```

Under the hood it still uses `SqlBulkCopy` and `MERGE`. It supports composite keys, identity values written back onto your entities, EF Core value converters, `HOLDLOCK` when you want to prevent races, batch size and timeout settings, column include/exclude lists, and ambient transactions. The hot path uses compiled expressions, not per-row reflection.

Install:

```bash
dotnet add package SqlBulkPro
```

If you only need insert, the free MIT sibling `SqlBulkPro.Lite` has the same insert API and no license key.

It targets .NET 8 and .NET 9, talks to SQL Server 2016 or later, and the EF helpers expect EF Core 8 or later.

### A few friendly examples

**EF Core insert.** This is the one-liner for an import job that would otherwise call `AddRange` + `SaveChanges`:

```csharp
using SqlBulkPro;

var products = LoadProductsFromFile(); // thousands of rows
await dbContext.BulkInsertAsync(products);
```

**Upsert with your own key columns.** Useful when the natural key is not the identity column — a product code plus a region, a source system id, and so on:

```csharp
using SqlBulkPro;

var options = new BulkOptions
{
    KeyColumns = new List<string> { "ProductCode", "Region" },
    ColumnsToIgnore = new List<string> { "LastModified" },
    InsertOnlyColumns = new List<string> { "CreatedAt" },
    BatchSize = 10000,
    TimeoutSeconds = 60,
    UseHoldLock = true,
    ReturnIdentities = true
};

await dbContext.BulkUpsertAsync(products, options);
```

`KeyColumns` tells `MERGE` how to match. `ColumnsToIgnore` stays out of the update. `InsertOnlyColumns` is written on insert and left alone later (`CreatedAt` is the usual case). `UseHoldLock` prevents concurrent upserts from racing. `ReturnIdentities` writes generated ids back onto the objects you passed in.

**Dapper / ADO.NET.** If you are not using a `DbContext` for the write path, the same operations hang off `SqlConnection`. You can point at a table and name the key with a short fluent config:

```csharp
using SqlBulkPro;
using Microsoft.Data.SqlClient;

using var connection = new SqlConnection(connectionString);

await connection.BulkInsertAsync(products);

await connection.BulkUpsertAsync(products, config => config
    .ToTable("Products", "dbo")
    .HasKey(nameof(Product.Id)));
```

**Inside a transaction.** The library respects ambient transactions, so a bulk write can sit next to other work and commit or roll back together:

```csharp
using SqlBulkPro;
using System.Transactions;

using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);

await dbContext.BulkInsertAsync(newProducts);
await dbContext.BulkUpsertAsync(existingProducts, options);

scope.Complete();
```

That way a bulk write can succeed or fail together with the rest of the unit of work.

### Numbers from our benchmark machine

These are from one BenchmarkDotNet run (1 warmup, 3 measured iterations) on our benchmark machine: a 4-core Xeon, .NET 9, and SQL Server 2022 in Docker on that same box. Insert compares `AddRange` + `SaveChanges` with `BulkInsertAsync`. "Update vs upsert" compares `UpdateRange` + `SaveChanges` with `BulkUpsertAsync` after the rows already exist. Your hardware and indexes will differ; this is what that machine measured.

| Operation | Rows | EF Core mean | SqlBulkPro mean | EF / SqlBulkPro |
|-----------|------:|-------------:|----------------:|----------------:|
| Insert | 1,000 | 96.96 ms | 17.84 ms | 5.4× |
| Insert | 10,000 | 517.66 ms | 54.06 ms | 9.6× |
| Insert | 100,000 | 3,768.89 ms | 554.19 ms | 6.8× |
| Update vs upsert | 1,000 | 106.31 ms | 28.11 ms | 3.8× |
| Update vs upsert | 10,000 | 614.58 ms | 109.36 ms | 5.6× |
| Update vs upsert | 100,000 | 5,226.42 ms | 856.17 ms | 6.1× |

On that run, 100,000 inserts went from about 3.8 seconds to about 0.55 seconds. Across these cases the mean time was roughly 4× to 10× lower than `SaveChanges`.

### When you should not use this

If you are saving 10 or 50 rows, stay with `SaveChanges`. The bulk path has setup cost, and EF Core's usual pipeline is simpler, more visible in logs, and plenty fast at that size.

If you need a graph of parents, children, and owned types saved with the full change tracker, this is the wrong tool. SqlBulkPro is for flat sets of rows headed at a table.

If you only need insert and you want zero licensing, start with Lite instead of Pro. And if your database is not SQL Server 2016 or later, this package will not help you. It is built around `SqlBulkCopy` and `MERGE`.

### Try it without buying anything

**SqlBulkPro.Lite** is MIT licensed and insert-only:

```bash
dotnet add package SqlBulkPro.Lite
```

**SqlBulkPro** adds upsert, update, delete, and sync. You can try it without a key: each operation is capped at 1,000 rows, and a 14-day trial check runs outside Development. In Development (or with a debugger attached) that 14-day check is skipped; the row cap still applies.

```bash
dotnet add package SqlBulkPro
```

When you have a key, register it at startup or set an environment variable. A missing variable leaves the library in trial mode. It does not throw.

```csharp
using SqlBulkPro;

var builder = WebApplication.CreateBuilder(args);
SqlBulkProLicense.Register(builder.Configuration["SqlBulkPro:LicenseKey"]!);
```

```bash
export SQLBULKPRO_LICENSE_KEY="paste-the-key-here"
```

- [SqlBulkPro on NuGet](https://www.nuget.org/packages/SqlBulkPro)
- [SqlBulkPro.Lite on NuGet](https://www.nuget.org/packages/SqlBulkPro.Lite)

If Pro fits, licenses are [$149 per developer per year](https://buy.stripe.com/cNi14pecGgBr0xh23ZdMI00) or [$599 per year for a team of up to 10 developers](https://buy.stripe.com/14AfZj3y20Cta7RaAvdMI01). The key is emailed after checkout. Questions and feedback are welcome at [dbdavis1990apple@gmail.com](mailto:dbdavis1990apple@gmail.com).

I built this because I was tired of choosing between a slow `SaveChanges` and a MERGE script I did not want to maintain again. If it saves you that choice on one import job, it did what I hoped.