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
Last activity: Mar 31, 2025, 01:44 PM