dotnet / dotnet/SqlClient

Reading JSON column appears to be 5x to 8x slower than reading VARCHAR(MAX) column

Open
#3,499 4 comments 1 reaction 1 assignee Claimed by @apoorvdeshmukh View on GitHub
Performance :chart_with_upwards_trend:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 18h
Merged PRs (30d)
69

Description

Given two identical tables other than a column defined as either JSON or VARCHAR(MAX), reading the same data from the JSON column appears to be 5-6 times slower than from the VARCHAR column.

### To reproduce

```c#
using System.Text.Json;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using Microsoft.Data.SqlClient;

BenchmarkRunner.Run();

[MemoryDiagnoser]
public class SqlJsonTest
{
private readonly SqlConnection _sqlConnection;

public SqlJsonTest()
{
_sqlConnection = new SqlConnection("Server=(local);Database=SqlJsonBenchmark;Trusted_Connection=True;TrustServerCertificate=true");

_sqlConnection.Open();

string[] tables =
[
"ItemJson",
"ItemVarchar"
];

foreach (string table in tables)
{
string tagsColumn = table.EndsWith("Varchar")
? "VARCHAR(MAX) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL"
: "JSON NOT NULL";

var createTableCommand = _sqlConnection.CreateCommand();

createTableCommand.CommandText = $"""
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.{table}') AND type in (N'U'))
DROP TABLE dbo.{table};

CREATE TABLE {table} (
Id INT IDENTITY(1,1) PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
strings {tagsColumn}
);
""";

_ = createTableCommand.ExecuteNonQuery();
}

foreach (string table in tables)
{
for (int i = 0; i < 500; i++)
{
var insertCommand = _sqlConnection.CreateCommand();

insertCommand.CommandText = $"""
INSERT INTO {table} (Title, strings)
VALUES (@Title, @strings);
""";

_ = insertCommand.Parameters.AddWithValue("@Title", $"Entry {i}");

string[] tags = [.. Enumerable.Range(1, 20).Select(j => new string($"tag{j}"))];

_ = insertCommand.Parameters.AddWithValue("@strings", JsonSerializer.Serialize(tags));

_ = insertCommand.ExecuteNonQuery();
}
}
}

[Benchmark]
public Entry[] ReadEntriesJson()
{
var selectCommand = _sqlConnection.CreateCommand();

selectCommand.CommandText = $"SELECT Id, Title, strings FROM ItemJson";

using var reader = selectCommand.ExecuteReader();

var entries = new List();

while (reader.Read())
{
int id = reader.GetInt32(0);
string title = reader.GetString(1);
var tagsJson = reader.GetSqlJson(2);
string[] tags = JsonSerializer.Deserialize(tagsJson.Value) ?? [];
entries.Add(new Entry { Id = id, Title = title, Tags = tags });
}

return [.. entries];
}

[Benchmark(Baseline = true)]
public Entry[] ReadEntriesVarchar()
{
var selectCommand = _sqlConnection.CreateCommand();

selectCommand.CommandText = $"SELECT Id, Title, strings FROM ItemVarchar";

using var reader = selectCommand.ExecuteReader();

var entries = new List();

while (reader.Read())
{
int id = reader.GetInt32(0);
string title = reader.GetString(1);
string tagsJson = reader.GetString(2);
string[] tags = JsonSerializer.Deserialize(tagsJson) ?? [];
entries.Add(new Entry { Id = id, Title = title, Tags = tags });
}

return [.. entries];
}
}

public class Entry
{
public int Id { get; set; }

public required string Title { get; set; }

public required string[] Tags { get; set; }
}
```

### Actual behaviour

```
// * Summary *

BenchmarkDotNet v0.15.2, Windows 11 (10.0.26120.4741)
Unknown processor
.NET SDK 10.0.100-preview.6.25358.103
[Host] : .NET 10.0.0 (10.0.25.35903), X64 RyuJIT AVX2
DefaultJob : .NET 10.0.0 (10.0.25.35903), X64 RyuJIT AVX2

| Method | Mean | Error | StdDev | Ratio | RatioSD | Gen0 | Gen1 | Allocated | Alloc Ratio |
|------------------- |-----------:|---------:|---------:|------:|--------:|--------:|--------:|----------:|------------:|
| ReadEntriesJson | 2,516.4 us | 30.39 us | 31.21 us | 5.82 | 0.13 | 58.5938 | 39.0625 | 1.11 MB | 1.04 |
| ReadEntriesVarchar | 432.8 us | 7.77 us | 8.32 us | 1.00 | 0.03 | 59.0820 | 38.5742 | 1.07 MB | 1.00 |
```

### Expected behaviour

Similar, if not better, performance for reading JSON.

### Further technical details

Microsoft.Data.SqlClient version: 6.1.0-preview2.25178.5
.NET target: 10.0.100-preview.6.25358.103
SQL Server version: SQL Server 2025 (17.0.800.3)
Operating system: Windows 11 (10.0.26120.4741)

### Discussion

Is this perhaps due to the cost of parsing JSON every time a `SqlJson` instance is created?

https://github.com/dotnet/SqlClient/blob/27aaa9e0f148b50f9c712187894c0942006cc831/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlTypes/SqlJson.cs#L38-L43

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.