Dapper Performance: Query Cache, GetRowParser and Bulk Alternatives with SqlBulkCopy and COPY

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

Dapper keeps a cache entry for every SQL text it runs. Now we read 2000 books one by one, embedding the id into the SQL each time; in other words, we produce 2000 different SQL texts:

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
conn.Execute("create table books (id integer primary key, title text not null, price real not null)");
conn.Execute("insert into books (title, price) values (@title, 10)",
    Enumerable.Range(1, 2000).Select(i => new { title = $"Book {i}" }));
SqlMapper.PurgeQueryCache();

var sw = Stopwatch.StartNew();
for (var id = 1; id <= 2000; id++)
{
    conn.QuerySingle<Book>($"select id, title, price from books where id = {id}");
    if (id % 400 == 0) Console.WriteLine($"{id}: cache = {SqlMapper.GetCachedSQLCount()}");
}
Console.WriteLine($"interpolated: {sw.ElapsedMilliseconds} ms");

sw.Restart();
for (var id = 1; id <= 2000; id++)
    conn.QuerySingle<Book>("select id, title, price from books where id = @id", new { id });
Console.WriteLine($"parameterized: {sw.ElapsedMilliseconds} ms, cache = {SqlMapper.GetCachedSQLCount()}");

public record Book(long Id, string Title, double Price);

How many entries are in the cache when the loop ends? 2000? And what kind of graph do the intermediate measurements draw?

400: cache = 400
800: cache = 301
1200: cache = 201
1600: cache = 101
2000: cache = 1
interpolated: 14 ms
parameterized: 7 ms, cache = 2

The cache did not grow forever; it rose and fell like a saw blade. The time difference is also smaller than expected. In this post we look at what is cached inside Dapper, when to turn the cache off, choosing a type per row, and the bulk operations Dapper does not have at all. Every timing comes from real runs on an Apple M5 machine.

What Does the Query Cache Hold?

When Dapper sees a query for the first time it does two expensive things: it generates, as IL at runtime, the code that copies values from the parameter object into the IDbCommand, and the deserializer that fills your type from the reader. That code is kept in a static ConcurrentDictionary. The key is made of the SQL text, the command type, the connection string, the result type and the parameter type; change any of them and you get a new entry.

The reason for the saw blade is in the source. Dapper writes to the cache twice for a new query, and every 1000 writes it removes entries that were never used again. One-off SQL texts are thus cleaned out periodically. The reason the time difference is small is that deserializers are also shared by column set: 2000 different SQL texts returned the same three columns, so the IL was generated once.

So the sentence "interpolation blows up the cache" is an exaggeration on the Dapper side. The real costs are elsewhere: every query is open to SQL injection, and on a server like SQL Server every distinct text gets its own compiled plan (we saw this in post 13). One danger does remain in Dapper: if the same dynamic text runs twice, the entry counts as "used" and survives the cleanup. In an application that embeds the user id in SQL and where every user refreshes the page a few times, the cache really does grow.

CommandFlags.NoCache and PurgeQueryCache

Some SQL texts really are one-off: a report query typed by hand on an admin screen, commands produced by a migration tool. To keep them out of the cache entirely, CommandDefinition has a flags parameter:

var command = new CommandDefinition(
    "select id, title, 10.0 as price from books where id = 1",
    flags: CommandFlags.NoCache);

var book = conn.QuerySingle<Book>(command);
Console.WriteLine($"{book.Title}, cache = {SqlMapper.GetCachedSQLCount()}");

Dune, cache = 0

CommandFlags is a flags enum: None = 0, Buffered = 1, Pipelined = 2, NoCache = 4. The default is Buffered; write only NoCache and you have turned off buffering too, so if you want both, write CommandFlags.Buffered | CommandFlags.NoCache.

To empty the cache completely there is SqlMapper.PurgeQueryCache(); we used it in post 11 to activate a type handler registered too late. If you want to observe purges, the SqlMapper.QueryCachePurged event fires. To look inside the cache there are GetCachedSQLCount() and GetCachedSQL(); the latter returns each entry's SQL and how many times it was used. Putting it behind an endpoint in production is the cheapest way to find the place that generates dynamic SQL.

ExecuteReader and GetRowParser: A Type per Row

In Bookstore, printed books and e-books live in the same products table, told apart by a kind column. It is the same layout as TPH (table-per-hierarchy) in EF Core, which resolves it with a discriminator on its own (post 19 of the EF Core series). In Dapper, Query<T> fills a single type, but you can drop down to the reader and decide per row:

conn.Execute("""
    create table products (
        id integer primary key, kind text not null, title text not null,
        pages integer, file_size_mb real
    );
    insert into products (kind, title, pages, file_size_mb) values
        ('print', 'Dune', 896, null),
        ('ebook', 'Dune Messiah', null, 2.4),
        ('print', 'The Dispossessed', 387, null);
    """);
DefaultTypeMap.MatchNamesWithUnderscores = true;

var products = new List<Product>();
using (var reader = conn.ExecuteReader("select id, kind, title, pages, file_size_mb from products order by id"))
{
    var printParser = reader.GetRowParser<Product>(typeof(PrintBook));
    var ebookParser = reader.GetRowParser<Product>(typeof(EBook));
    var kindIndex = reader.GetOrdinal("kind");

    while (reader.Read())
    {
        var parser = reader.GetString(kindIndex) == "ebook" ? ebookParser : printParser;
        products.Add(parser(reader));
    }
}

foreach (var p in products)
    Console.WriteLine(p);

public abstract record Product
{
    public long Id { get; init; }
    public string Title { get; init; } = "";
}

public record PrintBook : Product
{
    public long Pages { get; init; }
}

public record EBook : Product
{
    public double FileSizeMb { get; init; }
}

PrintBook { Id = 1, Title = Dune, Pages = 896 }
EBook { Id = 2, Title = Dune Messiah, FileSizeMb = 2.4 }
PrintBook { Id = 3, Title = The Dispossessed, Pages = 387 }

GetRowParser builds a deserializer for that reader's column layout and returns a Func<DbDataReader, T>. Because we create the parsers once before the loop, the per-row cost is the same as in a normal Query. Each subtype filled only the columns it knows; for EBook, the pages column was silently skipped. ExecuteReader is also the shortest way to get down to a raw ADO.NET reader while keeping Dapper's parameter support.

Dapper Has No Bulk: What Does Execute(list) Do?

Remember from post 05: when you give Execute a list, Dapper runs the command once per element. It is a loop, not a bulk operation. What determines the cost of that loop most is the transaction. We insert 5000 rows twice into a file-based SQLite database:

File.Delete("bookstore-bulk.db");
using var conn = new SqliteConnection("Data Source=bookstore-bulk.db");
conn.Open();
conn.Execute("create table books (id integer primary key, title text not null, price real not null)");

var rows = Enumerable.Range(1, 5000).Select(i => new { title = $"Book {i}", price = 10.0 + i % 20 }).ToList();
const string insert = "insert into books (title, price) values (@title, @price)";

var sw = Stopwatch.StartNew();
conn.Execute(insert, rows);
Console.WriteLine($"no transaction: {sw.ElapsedMilliseconds} ms");

sw.Restart();
using (var tx = conn.BeginTransaction())
{
    conn.Execute(insert, rows, transaction: tx);
    tx.Commit();
}
Console.WriteLine($"one transaction: {sw.ElapsedMilliseconds} ms");
Console.WriteLine(conn.ExecuteScalar<long>("select count(*) from books"));

no transaction: 978 ms
one transaction: 6 ms
10000

The same 5000 inserts, a 160-fold difference. Without a transaction, SQLite treats every insert as its own transaction and writes and syncs the journal to disk for each one. In a single transaction this happens once. When people say "bulk insert" on SQLite, this is what they mean in practice: one transaction and a reused command. When Dapper gets a list it does not create the command anew for each element; it reuses the same command, clearing and re-adding its parameters every time.

SQL Server: SqlBulkCopy

With client-server databases a transaction alone is not enough, because every row is still a separate network round trip. SQL Server's real bulk path is the bulk load stream in the TDS protocol, and in SqlClient it is called SqlBulkCopy. Dapper is out of the picture here; we use the provider directly. 10,000 rows into the docker SQL Server from post 12:

var connectionString = "Server=localhost,1433;Database=Bookstore;User Id=sa;Password=Bookstore_Pass1;TrustServerCertificate=True";
using var conn = new SqlConnection(connectionString);
conn.Open();
conn.Execute("""
    drop table if exists dbo.bulk_books;
    create table dbo.bulk_books (id int identity primary key, title nvarchar(200) not null, price decimal(10, 2) not null);
    """);

var books = Enumerable.Range(1, 10_000)
    .Select(i => new { title = $"Book {i}", price = 10m + i % 20 })
    .ToList();

var sw = Stopwatch.StartNew();
using (var tx = conn.BeginTransaction())
{
    conn.Execute("insert dbo.bulk_books (title, price) values (@title, @price)", books, transaction: tx);
    tx.Commit();
}
Console.WriteLine($"Execute(list): {sw.ElapsedMilliseconds} ms");

var table = new DataTable();
table.Columns.Add("title", typeof(string));
table.Columns.Add("price", typeof(decimal));
foreach (var b in books)
    table.Rows.Add(b.title, b.price);

sw.Restart();
using (var bulk = new SqlBulkCopy(conn) { DestinationTableName = "dbo.bulk_books" })
{
    bulk.ColumnMappings.Add("title", "title");
    bulk.ColumnMappings.Add("price", "price");
    bulk.WriteToServer(table);
}
Console.WriteLine($"SqlBulkCopy: {sw.ElapsedMilliseconds} ms");
Console.WriteLine(conn.ExecuteScalar<int>("select count(*) from dbo.bulk_books"));

Execute(list): 2215 ms
SqlBulkCopy: 25 ms
20000

Even inside a transaction, 10,000 single inserts took more than two seconds; SqlBulkCopy wrote the same data in 25 ms. On the first run it took 98 ms, because SqlClient's bulk code path was warming up for the first time. SQL Server runs under emulation here; on a real server both numbers would differ, but the ratio is similar. Don't leave ColumnMappings empty: without it, columns are mapped by position and the identity column shifts everything by one. I tried it; title went to id, price to title, and SQL Server reported "Cannot insert the value NULL into column 'price'". The Dapper.AOT documentation also has helpers for SqlBulkCopy; we move on to AOT in post 16.

PostgreSQL: COPY

PostgreSQL's counterpart is the COPY command. Npgsql exposes it as a writer in binary format:

var pgConnectionString = "Host=localhost;Port=5432;Username=postgres;Password=bookstore;Database=bookstore";
using var conn = new NpgsqlConnection(pgConnectionString);
conn.Open();
conn.Execute("""
    drop table if exists bulk_books;
    create table bulk_books (id int generated always as identity primary key, title text not null, price numeric(10, 2) not null);
    """);

var books = Enumerable.Range(1, 10_000)
    .Select(i => new { title = $"Book {i}", price = 10m + i % 20 })
    .ToList();

var sw = Stopwatch.StartNew();
using (var tx = conn.BeginTransaction())
{
    conn.Execute("insert into bulk_books (title, price) values (@title, @price)", books, transaction: tx);
    tx.Commit();
}
Console.WriteLine($"Execute(list): {sw.ElapsedMilliseconds} ms");

sw.Restart();
using (var writer = conn.BeginBinaryImport("copy bulk_books (title, price) from stdin (format binary)"))
{
    foreach (var b in books)
    {
        writer.StartRow();
        writer.Write(b.title);
        writer.Write(b.price);
    }
    writer.Complete();
}
Console.WriteLine($"COPY: {sw.ElapsedMilliseconds} ms");
Console.WriteLine(conn.ExecuteScalar<long>("select count(*) from bulk_books"));

Execute(list): 1193 ms
COPY: 10 ms
20000

More than a hundredfold difference. Forget to call Complete() and the operation is cancelled when the writer is disposed, and no rows are written. I tried it: no exception, empty table. Because it is silent, look for that line specifically in code review. In binary format, each value's type must match the column exactly; we wrote a decimal into the numeric column and a string into text.

Measuring Properly: BenchmarkDotNet

The Stopwatch measurements in this post are good enough to see rough magnitudes, because the differences are a hundredfold. When microseconds are at stake, Stopwatch lies: JIT warm-up, Dapper's IL generation on the first call and the GC get in the way. That is what BenchmarkDotNet (0.15.8) is for. Let's compare Dapper with hand-written ADO.NET code:

BenchmarkRunner.Run<BookQueries>();

[MemoryDiagnoser]
[ShortRunJob]
public class BookQueries
{
    private SqliteConnection _conn = null!;

    [GlobalSetup]
    public void Setup()
    {
        _conn = new SqliteConnection("Data Source=:memory:");
        _conn.Open();
        _conn.Execute("create table books (id integer primary key, title text not null, price real not null)");
        _conn.Execute("insert into books (title, price) values (@title, 10)",
            Enumerable.Range(1, 1000).Select(i => new { title = $"Book {i}" }));
    }

    [GlobalCleanup]
    public void Cleanup() => _conn.Dispose();

    [Benchmark(Baseline = true)]
    public Book AdoNet()
    {
        using var cmd = _conn.CreateCommand();
        cmd.CommandText = "select id, title, price from books where id = @id";
        cmd.Parameters.AddWithValue("@id", 500);
        using var reader = cmd.ExecuteReader();
        reader.Read();
        return new Book(reader.GetInt64(0), reader.GetString(1), reader.GetDouble(2));
    }

    [Benchmark]
    public Book Dapper() =>
        _conn.QuerySingle<Book>("select id, title, price from books where id = @id", new { id = 500 });
}

dotnet run -c Release

| Method | Mean     | Error     | StdDev    | Ratio | Gen0   | Allocated | Alloc Ratio |
|------- |---------:|----------:|----------:|------:|-------:|----------:|------------:|
| AdoNet | 1.253 us | 0.0324 us | 0.0018 us |  1.00 | 0.1297 |   1.07 KB |        1.00 |
| Dapper | 1.769 us | 0.2069 us | 0.0113 us |  1.41 | 0.1945 |    1.6 KB |        1.50 |

For a single-row read, Dapper adds about half a microsecond and half a KB. ShortRunJob gives a quick idea but only does 3 iterations; for a measurement you will base a decision on, use the default job. On a real database, where a network round trip is measured in milliseconds, this difference is mostly noise. Always run benchmarks in Release mode; with a Debug build, BenchmarkDotNet refuses to run, saying the assembly is "non-optimized".

Common Pitfalls

Measuring the first call. Dapper's first call generates IL, and the provider's first call sets up the connection pool and its own internals. Before saying "Dapper is slow" from a single Stopwatch reading, do a warm-up round or use BenchmarkDotNet.

Mistaking Execute(list) for bulk. N elements mean N round trips. Up to a few hundred rows inside a transaction is fine; for thousands of rows, switch to the provider's bulk path.

Writing in bulk without a transaction. We saw 160-fold on SQLite above; on other databases every row is committed separately too. A bulk write should always happen inside a single transaction.

Reading large lists buffered. As we saw in post 10, reading a million-row export with Query<T> pulls everything into memory; ExecuteReader or unbuffered reading is the right tool here.

Writing CommandFlags.NoCache on its own and losing buffering. The flags are a bit mask; if you want Buffered too, add it explicitly.

A Small Experiment

★☆☆ In the opening loop, query every id twice. Does the saw blade in the intermediate measurements disappear, and how many entries does the cache reach in the end? Explain the result with the "remove entries that were never used" rule from the source.

★★☆ Add a third route to the SQLite example: inside a transaction, create a single SqliteCommand, change its parameter values by hand and execute it 5000 times. Measure the difference from Dapper's Execute(list).

★★★ Remove the DataTable from the SqlBulkCopy example and write a small wrapper that exposes the list as an IDataReader. With 100,000 rows, compare the memory use of the two routes with GC.GetTotalAllocatedBytes().

In this post we talked about the cache for the code Dapper generates at runtime. The next and final post of the series is about Dapper.AOT, which removes exactly that: generating the same code at build time with a source generator and interceptors, Native AOT publishing and the DAP analyzers.