使用数据库设置
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;
以下代码以流式传输方式传递 TVP 值。可枚举值直到调用后才被评估ExecuteScalarAsync，并且不需要将全部 5,000,000 个元素具体化为客户端中的集合。
越来越流行的是放弃 TVP 而选择 JSON 字符串，对于 JSON 能做类似的事情吗？
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<SqlDataRecord> 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<Dto> 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);
