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

[frankbuckley]

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

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

BenchmarkRunner.Run<SqlJsonTest>();

[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<Entry>();

        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string title = reader.GetString(1);
            var tagsJson = reader.GetSqlJson(2);
            string[] tags = JsonSerializer.Deserialize<string[]>(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<Entry>();

        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string title = reader.GetString(1);
            string tagsJson = reader.GetString(2);
            string[] tags = JsonSerializer.Deserialize<string[]>(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?

SqlClient/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlTypes/SqlJson.cs

Lines 38 to 43 in 27aaa9e

	// Ask JsonDocument to parse it for validity, or throw.
	//
	// Note that we do not support trailing commas or comments in the
	// JSON.
	//
	JsonDocument.Parse(jsonString).Dispose();

[apoorvdeshmukh]

Storage layout of varchar(max) and JSON column differs significantly at the server side. There may be some conversion happening from the native storage layout to the format expected on the TDS and that may be causing this performance issue. I need to check.
I'll investigate this with the server team and update my findings here.
JsonDocument.Parse doesn't seem to be causing this issue as I see similar numbers without it as well.

[frankbuckley]

Thank you!

JsonDocument.Parse doesn't seem to be causing this issue as I see similar numbers without it as well.

I would imagine this might depend on the size of the JSON payload.

Out of curiosity, why does SqlJson validate the JSON?

If it is invalid, the server will generate an error. This would be consistent with what happens if you attempt to (say) set a varchar(10) column to something longer or if a column constraint is not satisfied. And, when reading, presumably you trust the server not to be returning invalid JSON.

[frankbuckley]

A modest payload gives me a notable difference (279x) on this simple benchmark ---

BenchmarkDotNet v0.15.2, Windows 11 (10.0.26120.4741)
11th Gen Intel Core i7-11370H 3.30GHz, 1 CPU, 8 logical and 4 physical cores
.NET SDK 10.0.100-preview.6.25358.103
  [Host]     : .NET 10.0.0 (10.0.25.35903), X64 RyuJIT AVX-512F+CD+BW+DQ+VL+VBMI
  DefaultJob : .NET 10.0.0 (10.0.25.35903), X64 RyuJIT AVX-512F+CD+BW+DQ+VL+VBMI


| Method                    | Mean         | Error      | StdDev     | Ratio  | RatioSD | Gen0    | Gen1   | Allocated | Alloc Ratio |
|-------------------------- |-------------:|-----------:|-----------:|-------:|--------:|--------:|-------:|----------:|------------:|
| CreateSqlJsonWithParse    | 1,206.448 us | 21.2805 us | 19.9058 us | 279.25 |    5.72 | 15.6250 |      - | 101.73 KB |        3.24 |
| CreateSqlJsonWithoutParse |     4.321 us |  0.0650 us |  0.0577 us |   1.00 |    0.02 |  5.1270 | 0.5646 |  31.42 KB |        1.00 |

[MemoryDiagnoser]
public class SqlJsonParseBenchmark
{
    private const int N = 1000;

    private readonly string _json = """
    {
        "Id": 1002,
        "Title": "Sample Entry",
        "Tags": [ "tag1", "tag2", "tag3", "tag4", "tag5", "tag6", "tag7", "tag8", "tag9", "tag10", "tag11", "tag12", "tag13", "tag14", "tag15", "tag16", "tag17", "tag18", "tag19", "tag20" ],
        "Related": [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 ]
    }
    """;

    [Benchmark]
    public SqlJsonWithParse[] CreateSqlJsonWithParse()
    {
        return [.. Enumerable.Range(0, N).Select(i => new SqlJsonWithParse(_json))];
    }

    [Benchmark(Baseline = true)]
    public SqlJsonWithoutParse[] CreateSqlJsonWithoutParse()
    {
        return [.. Enumerable.Range(0, N).Select(i => new SqlJsonWithoutParse(_json))];
    }

    public class SqlJsonWithParse
    {
        public SqlJsonWithParse(string json)
        {
            JsonDocument.Parse(json).Dispose();
            Json = json;
        }

        public string Json { get; }
    }

    public class SqlJsonWithoutParse
    {
        public SqlJsonWithoutParse(string json)
        {
            Json = json;
        }

        public string Json { get; }
    }
}

[frankbuckley]

Seems worse with large payload of floats (8.7x) ---

BenchmarkDotNet v0.15.2, Windows 11 (10.0.26120.4741)
11th Gen Intel Core i7-11370H 3.30GHz, 1 CPU, 8 logical and 4 physical cores
.NET SDK 10.0.100-preview.6.25358.103
  [Host]     : .NET 10.0.0 (10.0.25.35903), X64 RyuJIT AVX-512F+CD+BW+DQ+VL+VBMI
  DefaultJob : .NET 10.0.0 (10.0.25.35903), X64 RyuJIT AVX-512F+CD+BW+DQ+VL+VBMI


| Method             | Mean      | Error     | StdDev    | Median    | Ratio | RatioSD | Gen0      | Gen1     | Allocated | Alloc Ratio |
|------------------- |----------:|----------:|----------:|----------:|------:|--------:|----------:|---------:|----------:|------------:|
| ReadEntriesJson    | 89.976 ms | 1.4025 ms | 1.2433 ms | 89.692 ms | 48.64 |    3.89 | 1000.0000 | 800.0000 |    6.1 MB |        3.81 |
| ReadEntriesVarchar | 10.351 ms | 0.0511 ms | 0.0399 ms | 10.354 ms |  5.60 |    0.44 |  890.6250 | 390.6250 |   5.38 MB |        3.37 |
| ReadEntriesVector  |  1.862 ms | 0.0553 ms | 0.1587 ms |  1.812 ms |  1.01 |    0.12 |  265.6250 | 138.6719 |    1.6 MB |        1.00 |

This also suggests the 50x read improvement claimed in the release notes may be more to do with the (not so good) performance of SqlJson than the performance of SqlVector (which appears to be ~5.6x faster than deserializing an array of 1024 floats from a JSON string)?

Benchmark:

using System.Text.Json;
using BenchmarkDotNet.Attributes;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlTypes;

namespace SqlJsonBenchmark;

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

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

        _sqlConnection.Open();

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

        foreach (string table in tables)
        {
            string embeddingColumn = table.EndsWith("Varchar")
                ? "VARCHAR(MAX) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL"
                : table.EndsWith("Vector")
                    ? "VECTOR(1024) 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,
                    Embedding {embeddingColumn}
                );
                """;

            _ = createTableCommand.ExecuteNonQuery();
        }

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

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

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

                ReadOnlyMemory<float> embedding = Enumerable.Range(0, 1024).Select(i => Random.Shared.NextSingle()).ToArray();

                _ = table.EndsWith("Vector")
                    ? insertCommand.Parameters.AddWithValue("@Embedding", new SqlVector<float>(embedding))
                    : insertCommand.Parameters.AddWithValue("@Embedding", JsonSerializer.Serialize(embedding));

                _ = insertCommand.ExecuteNonQuery();
            }
        }
    }

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

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

        using var reader = selectCommand.ExecuteReader();

        var entries = new List<Entry>();

        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string title = reader.GetString(1);
            var embeddingJson = reader.GetSqlJson(2);
            var embedding = JsonSerializer.Deserialize<ReadOnlyMemory<float>>(embeddingJson.Value);
            entries.Add(new Entry { Id = id, Title = title, Embedding = embedding });
        }

        return [.. entries];
    }

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

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

        using var reader = selectCommand.ExecuteReader();

        var entries = new List<Entry>();

        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string title = reader.GetString(1);
            string embeddingJson = reader.GetString(2);
            var embedding = JsonSerializer.Deserialize<ReadOnlyMemory<float>>(embeddingJson);
            entries.Add(new Entry { Id = id, Title = title, Embedding = embedding });
        }

        return [.. entries];
    }

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

        selectCommand.CommandText = $"SELECT Id, Title, Embedding FROM ItemVector";

        using var reader = selectCommand.ExecuteReader();

        var entries = new List<Entry>();

        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string title = reader.GetString(1);
            var sqlVector = reader.GetSqlVector<float>(2);
            entries.Add(new Entry { Id = id, Title = title, Embedding = sqlVector.Memory });
        }

        return [.. entries];
    }

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

        public required string Title { get; set; }

        public required ReadOnlyMemory<float> Embedding { get; set; }
    }
}