首先，我需要非常清楚：我懒惰。知道这一点，我需要透露一个秘密。如果我不喜欢做一件事，那就是在SQL中编写查询或命令。通常，这些结构在我的想象中都是美丽而奇妙的，当我需要将它们放入代码中时，有很多'和加入的地方，我已经感到头晕了。因此，我一直是Chatgpt的非常顽固的用户，这通过做无聊的部分对我有很大帮助。
这与本文有什么关系？我开始探索使用C＃，Dapper和SQL Server从复杂对象或关系中存储和检索数据的不同方法，我将描绘这些经验的摘要。
对象和关系
要启动这些测试，我们需要一些对象，这些对象至少有一个与许多之间的关系，毕竟，这项研究的目的是找到最有效的方法来使这种关系在运行时组装。目前，在我的日常工作中，我在财务部门工作很多，所以我建议一个帐户及其分期付款。这样：
public class Account
{
public Guid Id { get; set; }
public string Description { get; set; }
public decimal TotalValue { get; set; }
public List<Installment> Installments { get; set; }
}
public class Installment
{
public Guid Id { get; set; }
public DateTime DueDate { get; set; }
public decimal Value { get; set; }
public Guid AccountId { get; set; }
}
非常普遍的是，此配置中的对象存储在其表中的关系数据库中，而外国键则用作参考，创建关系。这是我使用的SQL的示例：
CREATE TABLE Accounts
(
Id UNIQUEIDENTIFIER PRIMARY KEY,
Description NVARCHAR(255) NOT NULL,
TotalValue DECIMAL(18, 2) NOT NULL
);
CREATE TABLE Installments
(
Id UNIQUEIDENTIFIER PRIMARY KEY,
DueDate DATETIME NOT NULL,
Value DECIMAL(18, 2) NOT NULL,
AccountId UNIQUEIDENTIFIER NOT NULL,
FOREIGN KEY(AccountId) REFERENCES Accounts(Id)
);
最常见的方法
我遇到的最常见方法是检索此类信息的最常见方法是使用单个SQL查询，然后执行映射，在我的情况下，这是从分期付款到他们的帐户。
public const string ClassicQuery = @"
SELECT acc.*, i.* FROM Accounts acc
inner join Installments i ON i.AccountId = acc.Id";
public List<Account> GetAllAccounts()
{
var lookup = new Dictionary<Guid, Account>();
_ = sqlConnection.Query<Account, Installment, Account>(SqlConstants.ClassicQuery,
(acc, ins) =>
{
if (!lookup.TryGetValue(acc.Id, out var accEntry))
{
accEntry = acc;
accEntry.Installments ??= new List<Installment>();
lookup.Add(acc.Id, accEntry);
}
accEntry.Installments.Add(ins);
return accEntry;
}, splitOn: "Id");
return lookup.Values.AsList();
}
简短说明：
- 对于那些已经习惯了SQL工作原理的人，知道这种类型的查询会生成表的笛卡尔产品，因此有必要在映射过程中使用“查找”。尝试没有它，最终将获得重复的帐户和一对一的关系。
- 我不完全确定，但是我相信C＃编译器能够意识到可以将硬编码的字符串优化为常量或静态，但是我更喜欢明确，因此毫无疑问，毫无疑问，既然我们很快就会增加基准测试，我不希望字符串分配来弄乱结果。
-
AsList()方法来自dapper，并认为，如果已经将一个枚举的实现为List<T>，则它会像使用ToList()一样，而不是分配列表。 会发生。
另一种非常常见的方法是在.QueryMultiple()的结果上多次使用.Read<T>()命令。这样：
public const string MultipleQuery = @"
SELECT * FROM Accounts;
SELECT * FROM Installments;";
public List<Account> GetAllAccounts()
{
var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);
var acc = res.Read<Account>();
var installments = res.Read<Installment>();
foreach (var account in acc)
{
account.Installments = installments.Where(i => i.AccountId == account.Id).ToList();
}
return acc.AsList();
}
代码非常简单，直截了当，它没有任何魔法。到目前为止，我从未质疑过这种情况的性能，可用性或可读性，实际上，在现实世界中，从来没有需要尝试改善这些线路的任何东西。但是由于我“懒惰”，所以我开始多次编写这种代码。
如果... JSON怎么办？
对于那些已经使用非关系数据库（例如MongoDB或Azure Cosmos）冒险的人，您知道可以将整个对象存储为一个大而美丽的JSON。而且，如果我们冷冷地进行分析，我们可以尝试在关系数据库中混合一些小JSON，为什么不呢？包括SQL Server在内的所有主流发动机都已经制定了与桌子中的JSON打交道的准则，无论是列表，插入，过滤和其他几项常见活动。在下面的代码段中，我代表JSON中的一对多关系，并使用自定义Dapper映射器自动以这种格式读取和编写帐户分期。
public const string CreateAccountsJson = @"
CREATE TABLE AccountsJson(
Id UNIQUEIDENTIFIER PRIMARY KEY,
Description NVARCHAR(255) NOT NULL,
TotalValue DECIMAL(18,2) NOT NULL,
Installments NVARCHAR(MAX) NULL
);";
public class AccountJson
{
public Guid Id { get; set; }
public string Description { get; set; }
public decimal TotalValue { get; set; }
public List<InstallmentJson> Installments { get; set; }
}
public struct InstallmentJson
{
public DateTime DueDate { get; set; }
public decimal Value { get; set; }
}
public class InstallmentJsonTypeMapper
: SqlMapper.TypeHandler<List<InstallmentJson>>
{
public override List<InstallmentJson> Parse(object value)
{
if (value is null) return new List<InstallmentJson>();
var json = value.ToString();
if (string.IsNullOrWhiteSpace(json)) return new List<InstallmentJson>();
var res = JsonSerializer.Deserialize<List<InstallmentJson>>(json);
if (res is null) return new List<InstallmentJson>();
return res;
}
public override void SetValue(IDbDataParameter parameter,
List<InstallmentJson> value)
{
parameter.Value = value is null ? null : JsonSerializer.Serialize(value);
}
}
我确实知道它有效，但是值得吗？您可以在生产中使用它并感到安全吗？我们使用BenchmarkDotNet来进行基准测试。我为一个本地数据库播种，其中有10个帐户，每个帐户分别有10个分期付款。结果是：
| Method | Accounts | Installments | Mean | Gen0 | Gen1 | Allocated |
|-----------------|----------|--------------|----------:|--------:|-------:|-----------:|
| ClassicQuery | 10 | 1 | 153.5 us | 1.4648 | - | 9.38 KB |
| MultipleQuery | 10 | 1 | 135.7 us | 1.4648 | - | 10.16 KB |
| JsonQuery | 10 | 1 | 146.0 us | 2.1973 | - | 14.51 KB |
| ClassicQuery | 10 | 5 | 287.4 us | 3.9063 | - | 26.34 KB |
| MultipleQuery | 10 | 5 | 162.9 us | 2.9297 | - | 19.32 KB |
| JsonQuery | 10 | 5 | 182.8 us | 5.1270 | - | 32.47 KB |
| ClassicQuery | 10 | 10 | 376.6 us | 7.8125 | - | 48.88 KB |
| MultipleQuery | 10 | 10 | 204.1 us | 4.8828 | - | 31.19 KB |
| JsonQuery | 10 | 10 | 214.3 us | 9.0332 | 0.2441 | 56.28 KB |
老实说，我期望JSON方法更糟。内存分配高于其他分配，但执行时间与QueryMultiple()匹配。对我来说，这已经成为一笔不错的交易。
我们还可以停止考虑经典方法。在考虑分配和时间之间，她是最糟糕的。
如果...数据结构怎么办？
您注意到恶作剧吗？ MultipleQuery测试是使用LINQ和一种非常简单的方法将分期付款映射到其帐户。我们可以使用类似于经典方法中使用的技巧。看：
public List<Account> GetAllAccounts()
{
var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);
var accounts = res.Read<Account>(buffered: true).AsList();
var installments = res.Read<Installment>();
var lookup = new Dictionary<Guid, int>();
for (int i = 0; i < accounts.Count; i++)
{
lookup.Add(accounts[i].Id, i);
}
foreach (var installment in installments)
{
if (lookup.TryGetValue(installment.AccountId, out int i))
{
accounts[i].Installments.Add(installment);
}
}
return accounts;
}
如果数据结构确实是一件事情，那么到目前为止，此版本将是最好的，所以让我们进行测试。
| Method | Accounts | Installments | Mean | Allocated |
|:------------------:|:--------:|:------------:|------------:|------------:|
| MultipleQuery | 1 | 10 | 129.8 us | 6.18 KB |
| JsonQuery | 1 | 10 | 121.9 us | 7.63 KB |
| MultipleLookup | 1 | 10 | 121.8 us | 6.22 KB |
| MultipleQuery | 1 | 100 | 188.2 us | 26.7 KB |
| JsonQuery | 1 | 100 | 204.3 us | 48.57 KB |
| MultipleLookup | 1 | 100 | 190.7 us | 26.74 KB |
| MultipleQuery | 10 | 10 | 223.5 us | 31.19 KB |
| JsonQuery | 10 | 10 | 236.4 us | 56.34 KB |
| MultipleLookup | 10 | 10 | 197.2 us | 30.38 KB |
| MultipleQuery | 10 | 100 | 720.0 us | 232.26 KB |
| JsonQuery | 10 | 100 | 1,256.1 us | 465.83 KB |
| MultipleLookup | 10 | 100 | 719.4 us | 231.43 KB |
| MultipleQuery | 100 | 10 | 1,318.5 us | 276.86 KB |
| JsonQuery | 100 | 10 | 1,244.9 us | 541.36 KB |
| MultipleLookup | 100 | 10 | 751.1 us | 269.34 KB |
| MultipleQuery | 100 | 100 | 13,786.4 us | 2386.42 KB |
| JsonQuery | 100 | 100 | 12,036.7 us | 4635.98 KB |
| MultipleLookup | 100 | 100 | 8,879.9 us | 2379.31 KB |
为了帮助阅读结果，我们需要了解，尽管每个帐户一百个分期付款之类的数字不是一个非常现实的情况，但我们可以考虑在其他情况下，一个对象可以在其层次结构中有一百个孩子。这就是使测试更有趣的原因。MultipleLookup方法闪耀，这给我带来了一定的悲伤，因为我必须接受我需要更多地担心SQL以确保更好的性能。
但是像所有好的“懒惰”人一样，我不轻易放弃，我一直在尝试尽可能少地做。
如果...跨度怎么办？
自Span<T>被用来在某种程度上用来优化例程已经有一段时间了，我在这里提出的是一种险恶的方法，它完全不寻常，但可以回报。我们的关系案例研究对象由一个集合表示，因此有资格成为Span<T>。
然后剩下的问题是如何将一组分期付款转换为分期付款的Koude10并将其保存在数据库中，而答案可能是在二进制数据中，请参见：
public const string CreateAccountsSpan = @"
CREATE TABLE AccountsSpan(
Id UNIQUEIDENTIFIER PRIMARY KEY,
Description NVARCHAR(255) NOT NULL,
TotalValue DECIMAL(18,2) NOT NULL,
Installments VARBINARY(MAX) NULL
);";
public class InstallmentSpanTypeMapper
: SqlMapper.TypeHandler<List<InstallmentSpan>>
{
public override List<InstallmentSpan> Parse(object value)
{
if (value is not byte[] bytes)
{
return new List<InstallmentSpan>();
}
var span = bytes.AsSpan();
var structSpan = MemoryMarshal.Cast<byte, InstallmentSpan>(span);
return structSpan.ToArray().ToList();
}
public override void SetValue(IDbDataParameter parameter,
List<InstallmentSpan> value)
{
var s = CollectionsMarshal.AsSpan(value);
Span<byte> span = MemoryMarshal.AsBytes(s);
parameter.Value = span.ToArray();
}
}
至少可以说很奇怪，但是有效。如果帐户中的分期付款是Installment[]而不是List<Installment>，则可以避免使用.ToArray().ToList()。必须有某种直接转换为列表的方法，但是我很懒，还记得吗？
我们可以去测试吗？
| Method | Accounts | Installments | Mean | Allocated |
|--------------- |--------- |------------- |------------:|-----------:|
| JsonQuery | 1 | 10 | 127.2 us | 7.64 KB |
| SpanQuery | 1 | 10 | 107.7 us | 3.55 KB |
| MultipleLookup | 1 | 10 | 124.9 us | 6.23 KB |
| JsonQuery | 1 | 100 | 205.9 us | 48.57 KB |
| SpanQuery | 1 | 100 | 113.9 us | 12 KB |
| MultipleLookup | 1 | 100 | 188.0 us | 26.73 KB |
| JsonQuery | 10 | 10 | 211.9 us | 56.2 KB |
| SpanQuery | 10 | 10 | 124.5 us | 15.34 KB |
| MultipleLookup | 10 | 10 | 197.9 us | 30.45 KB |
| JsonQuery | 10 | 100 | 1,237.3 us | 465.74 KB |
| SpanQuery | 10 | 100 | 192.0 us | 99.84 KB |
| MultipleLookup | 10 | 100 | 674.6 us | 231.42 KB |
| JsonQuery | 100 | 10 | 1,207.0 us | 541.52 KB |
| SpanQuery | 100 | 10 | 268.6 us | 132.97 KB |
| MultipleLookup | 100 | 10 | 747.2 us | 269.28 KB |
| JsonQuery | 100 | 100 | 11,859.5 us | 4636.01 KB |
| SpanQuery | 100 | 100 | 1,234.6 us | 976.76 KB |
| MultipleLookup | 100 | 100 | 9,424.9 us | 2379.24 KB |
是！它非常快，分配的内存比其他内存要少得多，但是如果有人需要通过SQL Server Management Studio等工具读取数据库，则它将有缺点，他将无法看到那里有什么有些过滤器，也不可能（懒惰思考）。
结论
我真的很喜欢Span<T>版本，因为它简化了数据库查询。另一方面，我不知道在实际情况下，它在多大程度上将在多大程度上有用，具有更多属性甚至具有深层嵌套层次结构的真实对象。这是值得调查的，因为现在这更像是一个敞开的门。具有JSON的版本是实用的，在SQL Server的最新版本中，已经可以直接在数据库中查询和操纵JSON，因此我认为在需要存储非常复杂的对象并且将始终使用的情况下，它是有效的总体而言，没有努力将NOSQL数据库添加到团队堆栈中。最后，我们可以反思以下事实：我们通常不会在代码中每小时编写一个新表和新存储库，因此我的建议是坚持使用QueryMultiple，在映射对象时，在每种情况下应用一个良好的数据结构，如果您确实需要优化某些东西，请尝试使用数据库中的二进制文件跨度尝试此版本，并且在运行基准之前不要下定决心。
想要代码？来here。
ps（s）：
- 测试环境使用本地数据库连接来最大程度地减少网络时间，并具有以下设置： BenchmarkDotnet = V0.13.5，OS = Windows 11（10.0.22621.1848/22H2/2022update/sunvalley2） Intel Core i7-8700 CPU 3.20GHz（咖啡湖），1个CPU，12逻辑和6个物理核心 .NET SDK = 7.0.300-preview.23122.5 [主机]：.NET 6.0.14（6.0.1423.7309），x64 ryujit avx2 DefaultJob：.net 6.0.14（6.0.1423.7309），x64 ryujit avx2 Microsoft SQL Server 2022（RTM -GDR）（KB5021522）-16.0.1050.5（x64）
- 我不是很懒惰，文章中的虚构人物是懒惰的角色。 Dapper的性能案例已经进行了广泛的测试，我真正想显示的是数据库中跨度的解决方法。
- 我自己想到了使用跨度的AI，我自己想到了：）