dotnet / dotnet/SqlClient

Reading large data (binary, text) asynchronously is extremely slow

Open
#593 283 comments 169 reactions 0 assignees View on GitHub
Approved Area\Async Performance :chart_with_upwards_trend:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 19h
Merged PRs (30d)
72

Description

Reading a 10MB `VARBINARY(MAX)` value asynchronously takes around 5 seconds my machine, while doing the same synchronously takes around 20ms. Increasing the data size to 20MB increases the running time to around 52 seconds.

These numbers were with Microsoft.Data.SqlClient 1.1.3, but 2.0.0-preview4.20142.4 has the same issue (it actually seems slightly slower). Note that I'm not posting final BDN results because the benchmark ran extremely slowly.

Note that this looks very similar to #245, but that issue was fixed for 1.1.0. The difference may be that here we're writing binary data - or possibly the bigger size.

Benchmark code

```c#
[MemoryDiagnoser]
public class Benchmarks
{
const string ConnectionString = "Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0";

[GlobalSetup]
public void Setup()
{
using var conn = new SqlConnection(ConnectionString);
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = @"
IF OBJECT_ID('dbo.data', 'U') IS NOT NULL
DROP TABLE data;
CREATE TABLE data (id INT, foo VARBINARY(MAX))
";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO data (id, foo) VALUES (@id, @foo)";
cmd.Parameters.AddWithValue("id", 1);
cmd.Parameters.AddWithValue("foo", new byte[1024 * 1024 * 10]);
cmd.ExecuteNonQuery();
}

[Benchmark]
public async ValueTask Async()
{
using var conn = new SqlConnection(ConnectionString);
using var cmd = new SqlCommand("SELECT foo FROM data", conn);
await conn.OpenAsync();

return ((byte[])await cmd.ExecuteScalarAsync()).Length;
}

[Benchmark]
public async ValueTask Sync()
{
using var conn = new SqlConnection(ConnectionString);
using var cmd = new SqlCommand("SELECT foo FROM data", conn);
conn.Open();

return ((byte[])cmd.ExecuteScalar()).Length;
}

static void Main(string[] args)
=> BenchmarkRunner.Run();
}
```

Originally raised in https://github.com/dotnet/efcore/issues/21147

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.