Microsoft.EntityFrameworkCore.SqlServer 9.0-rc2 downgraded the Microsoft.Data.SqlClient to 5.1.6 and drastically slow for large data set

[sa-es-ir]

Microsoft.EntityFrameworkCore.SqlServer 9.0-rc2 downgraded the Microsoft.Data.SqlClient to 5.1.6 where the latest version is 5.2.2 and causes way more memory allocation and decreasing performance, the rc1 version was ok.

As I'm preparing for my talk on .NET Conf 2024 and the main idea is about investigating this long-term bug dotnet/SqlClient#593, here is the benchmark result for EF Core 9.0 rc1 and rc2.

Benchmark Code

[MemoryDiagnoser(false)]
[Config(typeof(Config))]
[HideColumns(Column.RatioSD, Column.AllocRatio)]
[CategoriesColumn]
[GroupBenchmarksBy(BenchmarkLogicalGroupRule.ByCategory)]
[ShortRunJob]
public class EFBenchmark
{
    private class Config : ManualConfig
    {
        public Config()
        {
            SummaryStyle =
                SummaryStyle.Default.WithRatioStyle(RatioStyle.Trend);
        }
    }

    [GlobalSetup]
    public async Task Setup()
    {
        var dbcontext = new SqlServerDbContext();

        dbcontext.TextTable2MB.Add(new TextTable2MB { Text = new string('x', 1024 * 1024 * 2) });
        dbcontext.TextTable5MB.Add(new TextTable5MB { Text = new string('x', 1024 * 1024 * 5) });

        await dbcontext.SaveChangesAsync();
    }

    [BenchmarkCategory("2MB"), Benchmark(Baseline = true)]
    public async Task Async2MB()
    {
        using var dbcontext = new SqlServerDbContext();

        _ = await dbcontext.TextTable2MB.FirstOrDefaultAsync();
    }

    [BenchmarkCategory("2MB"), Benchmark]
    public void Sync2MB()
    {
        using var dbcontext = new SqlServerDbContext();

        _ = dbcontext.TextTable2MB.FirstOrDefault();
    }

    [BenchmarkCategory("5MB"), Benchmark(Baseline = true)]
    public async Task Async5MB()
    {
        using var dbcontext = new SqlServerDbContext();

        _ = await dbcontext.TextTable5MB.FirstOrDefaultAsync();
    }

    [BenchmarkCategory("5MB"), Benchmark]
    public void Sync5MB()
    {
        using var dbcontext = new SqlServerDbContext();

        _ = dbcontext.TextTable5MB.FirstOrDefault();
    }
}

Result for EF Core 9.0-rc1 using SqlClient 5.2.2 internally:

| Method   | Categories | Mean        | Error       | StdDev    | Ratio         | Allocated |
|--------- |----------- |------------:|------------:|----------:|--------------:|----------:|
| Async2MB | 2MB        |   156.29 ms |    73.54 ms |  4.031 ms |      baseline |   8.45 MB |
| Sync2MB  | 2MB        |    30.58 ms |    48.54 ms |  2.661 ms |  5.14x faster |   4.09 MB |
|          |            |             |             |           |               |           |
| Async5MB | 5MB        | 1,197.66 ms | 1,209.84 ms | 66.316 ms |      baseline |  21.02 MB |
| Sync5MB  | 5MB        |    64.83 ms |    97.46 ms |  5.342 ms | 18.55x faster |  10.09 MB |

And Result for EF Core 9.0-rc2 using SqlClient 5.1.6 internally:

| Method   | Categories | Mean        | Error       | StdDev     | Ratio           | Allocated   |
|--------- |----------- |------------:|------------:|-----------:|----------------:|------------:|
| Async2MB | 2MB        | 1,334.32 ms |   537.61 ms |  29.468 ms |        baseline |  4216.65 MB |
| Sync2MB  | 2MB        |    27.53 ms |    25.58 ms |   1.402 ms |   48.55x faster |    12.09 MB |
|          |            |             |             |            |                 |             |
| Async5MB | 5MB        | 9,350.87 ms | 2,619.80 ms | 143.600 ms |        baseline | 26281.61 MB |
| Sync5MB  | 5MB        |    65.92 ms |    40.35 ms |   2.212 ms | 141.965x faster |     30.1 MB |

Thankfully, we still have time to patch for the EF Core 9.0 stable version.

EF Core version: 9.0.0-rc.2.24474.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 9.0
Operating system: Windows 11

[ErikEJ]

Feel free to reference the newer version, but EF Core only use LTS versions of SqlClient due to past bad experience of non LTS going out of support before the EF Core version

https://learn.microsoft.com/en-us/sql/connect/ado-net/sqlclient-driver-support-lifecycle?view=sql-server-ver16

[ErikEJ]

Quite good number for 5.2.2 actually @Wraith2

[ErikEJ]

@sa-es-ir I got this tip about upgrading to latest added to the docs at some point: https://learn.microsoft.com/en-us/ef/core/providers/sql-server/?tabs=dotnet-core-cli

[sa-es-ir]

@ErikEJ Ok thank for the point for using LTS version of SqlClient, but I wonder why for a EF version which is not a LTS we need to use a LTS version of internal package, I know SqlClient is an exception but still.

[ErikEJ]

As I said: due to past bad experience of non LTS going out of support before the EF Core version

[roji]

why for a EF version which is not a LTS we need to use a LTS version of internal package

Even if EF 9 isn't LTS, SqlClient 5.2 will most probably be going out of support before EF 9, so it's problematic to take a dependency on it.

However, you're free to take a direct dependency on SqlClient 5.2 from your own project - EF should work well either way.