Sample Header Ad - 728x90

Can a JSON array be sent as a stored procedure parameter in a streaming fashion?

6 votes
2 answers
285 views
With the database setup
CREATE TYPE dbo.TableType AS TABLE (
prop1 int, 
prop2 datetime2, 
prop3 varchar(1000)
);


GO

CREATE OR ALTER PROC dbo.TestTableTypePerf
@Data dbo.TableType READONLY
AS
SELECT COUNT(*)
FROM @Data;
The following code passes the TVP values in a streaming fashion. The enumerable isn't evaluated until after the ExecuteScalarAsync call and there is no need for the whole 5,000,000 elements to be materialized into a collection in the client. It is becoming increasingly popular to eschew TVPs in favour of JSON strings, can anything similar be done for JSON?
-csharp
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;

const string connectionString =
    @"...";

await TvpTest();

return;

static async Task TvpTest()
{
    await using var conn = new SqlConnection(connectionString);
    await conn.OpenAsync();
    await using var cmd = new SqlCommand("dbo.TestTableTypePerf", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter
    {
        ParameterName = "@Data",
        SqlDbType = SqlDbType.Structured,
        TypeName = "dbo.TableType",
        Value = GetEnumerableOfRandomSqlDataRecords(5_000_000)
    });

    Console.WriteLine($"calling ExecuteScalarAsync at {DateTime.Now:O}");
    var result = await cmd.ExecuteScalarAsync();

    Console.WriteLine($"writing result at {DateTime.Now:O}");
    Console.WriteLine(result);
}

static IEnumerable GetEnumerableOfRandomSqlDataRecords(uint length)
{
    SqlMetaData[] metaData =
    [
        new SqlMetaData("prop1", SqlDbType.Int),
        new SqlMetaData("prop2", SqlDbType.DateTime2),
        new SqlMetaData("prop3", SqlDbType.VarChar, 1000)
    ];

    foreach (var dto in GetEnumerableOfRandomDto(length))
    {
        var record = new SqlDataRecord(metaData);
        record.SetInt32(0, dto.Prop1);
        record.SetDateTime(1, dto.Prop2);
        record.SetString(2, dto.Prop3);

        yield return record;
    }
}


static IEnumerable GetEnumerableOfRandomDto(uint length)
{
    var rnd = new Random();

    for (var i = 0; i < length; i++)
    {
        yield return new Dto(rnd.Next(1, int.MaxValue), 
                             DateTime.Now.AddMinutes(rnd.Next(1, 10000)),
                             Guid.NewGuid().ToString()
                             );

        if ((i + 1) % 100_000 == 0)
            Console.WriteLine($"Generated enumerable {i + 1} at {DateTime.Now:O}");
    }
}


internal record Dto(int Prop1, DateTime Prop2, string Prop3);
Asked by Martin Smith (88061 rep)
Jan 25, 2025, 07:28 PM
Last activity: Mar 31, 2025, 01:44 PM