srcmini本文概述
在日常工作中, 我使用实体框架。它非常方便, 但是在某些情况下, 它的性能很慢。尽管有很多有关提高EF性能的好文章, 并且给出了一些非常有用的建议(例如, 避免复杂的查询, “跳过并取”中的参数, 仅使用视图, 仅选择所需的字段等), 但没有太多可以当你需要在两个或多个字段上使用复杂的包含时(换句话说, 当你将数据连接到内存列表时), 便可以完成此操作。
问题
让我们检查以下示例：
var localData = GetDataFromApiOrUser();
var query = from p in context.Prices
join s in context.Securities on
p.SecurityId equals s.SecurityId
join t in localData on
new { s.Ticker, p.TradedOn, p.PriceSourceId } equals
new { t.Ticker, t.TradedOn, t.PriceSourceId }
select p;
var result = query.ToList();
上面的代码根本无法在EF 6中运行, 尽管可以在EF Core中运行, 但是联接实际上是在本地完成的-因为我的数据库中有一千万条记录, 所以所有记录都被下载并且所有内存都被消耗了。这不是EF中的错误。可以预料的。但是, 如果有什么方法可以解决这个问题, 那不是很好吗？在本文中, 我将使用不同的方法进行一些实验, 以解决此性能瓶颈。
解
我将尝试从最简单到最高级的各种方法来实现这一目标。在每个步骤中, 我将提供代码和指标, 例如花费的时间和内存使用情况。请注意, 如果基准测试程序的运行时间超过十分钟, 我将中断其运行。
基准测试程序的代码位于以下存储库中。它使用C＃、. NET Core, EF Core和PostgreSQL。我使用了配备Intel Core i5、8 GB RAM和SSD的计算机。
用于测试的数据库模式如下所示：
只有三个表格：价格, 证券和价格来源。价格表有数千万条记录。
选项1。简单而天真
让我们尝试一些简单的方法, 以开始使用。
var result = new List<Price>();
using (var context = CreateContext())
{
foreach (var testElement in TestData)
{
result.AddRange(context.Prices.Where(
x => x.Security.Ticker == testElement.Ticker &&
x.TradedOn == testElement.TradedOn &&
x.PriceSourceId == testElement.PriceSourceId));
}
}
该算法很简单：对于测试数据中的每个元素, 在数据库中找到一个合适的元素并将其添加到结果集中。这段代码只有一个优点：易于实现。而且, 它易于阅读和维护。它的明显缺点是它是最慢的一个。即使对所有三列都建立了索引, 网络通信的开销仍然会造成性能瓶颈。以下是指标：
因此, 对于大容量, 大约需要一分钟。内存消耗似乎是合理的。
选项2。天真与并行
现在, 让我们尝试向代码添加并行性。这里的核心思想是在并行线程中访问数据库可以提高整体性能。
var result = new ConcurrentBag<Price>();
var partitioner = Partitioner.Create(0, TestData.Count);
Parallel.ForEach(partitioner, range =>
{
var subList = TestData.Skip(range.Item1)
.Take(range.Item2 - range.Item1)
.ToList();
using (var context = CreateContext())
{
foreach (var testElement in subList)
{
var query = context.Prices.Where(
x => x.Security.Ticker == testElement.Ticker &&
x.TradedOn == testElement.TradedOn &&
x.PriceSourceId == testElement.PriceSourceId);
foreach (var el in query)
{
result.Add(el);
}
}
}
});
有趣的是, 对于较小的测试数据集, 此方法的工作速度比第一种解决方案慢, 但是对于较大的样本, 它的工作速度更快(在这种情况下约为2倍)。内存消耗有少许变化, 但变化不大。
选项3.多个包含
让我们尝试另一种方法：
- 准备3个唯一的Ticker, PriceSourceId和Date值集合。
- 通过使用3个包含, 使用一次运行过滤来执行查询。
- 在本地重新检查(请参阅下文)。
var result = new List<Price>();
using (var context = CreateContext())
{
var tickers = TestData.Select(x => x.Ticker).Distinct().ToList();
var dates = TestData.Select(x => x.TradedOn).Distinct().ToList();
var ps = TestData.Select(x => x.PriceSourceId)
.Distinct().ToList();
var data = context.Prices
.Where(x => tickers.Contains(x.Security.Ticker) &&
dates.Contains(x.TradedOn) &&
ps.Contains(x.PriceSourceId))
.Select(x => new {
x.PriceSourceId, Price = x, Ticker = x.Security.Ticker, })
.ToList();
var lookup = data.ToLookup(x =>
$"{x.Ticker}, {x.Price.TradedOn}, {x.PriceSourceId}");
foreach (var el in TestData)
{
var key = $"{el.Ticker}, {el.TradedOn}, {el.PriceSourceId}";
result.AddRange(lookup[key].Select(x => x.Price));
}
}
这种方法是有问题的。执行时间取决于数据。它可能只检索所需的记录(在这种情况下将非常快), 但它可能返回更多(甚至可能多100倍)。
让我们考虑以下测试数据：
在这里, 我查询2018年1月1日交易的Ticker1和2018年1月2日交易的Ticker2的价格。但是, 实际上将返回四个记录。
股票行情的唯一值是股票行情1和股票行情2。 TradedOn的唯一值是2018-01-01和2018-01-02。
因此, 有四个记录与此表达式匹配。
这就是为什么需要本地重新检查以及为什么这种方法很危险的原因。指标如下：
可怕的内存消耗！由于超时10分钟, 大容量测试失败。
选项4.谓词生成器
让我们更改范例：为每个测试数据集构建一个良好的旧表达式。
var result = new List<Price>();
using (var context = CreateContext())
{
var baseQuery = from p in context.Prices
join s in context.Securities on
p.SecurityId equals s.SecurityId
select new TestData()
{
Ticker = s.Ticker, TradedOn = p.TradedOn, PriceSourceId = p.PriceSourceId, PriceObject = p
};
var tradedOnProperty = typeof(TestData).GetProperty("TradedOn");
var priceSourceIdProperty =
typeof(TestData).GetProperty("PriceSourceId");
var tickerProperty = typeof(TestData).GetProperty("Ticker");
var paramExpression = Expression.Parameter(typeof(TestData));
Expression wholeClause = null;
foreach (var td in TestData)
{
var elementClause =
Expression.AndAlso(
Expression.Equal(
Expression.MakeMemberAccess(
paramExpression, tradedOnProperty), Expression.Constant(td.TradedOn)
), Expression.AndAlso(
Expression.Equal(
Expression.MakeMemberAccess(
paramExpression, priceSourceIdProperty), Expression.Constant(td.PriceSourceId)
), Expression.Equal(
Expression.MakeMemberAccess(
paramExpression, tickerProperty), Expression.Constant(td.Ticker))
));
if (wholeClause == null)
wholeClause = elementClause;
else
wholeClause = Expression.OrElse(wholeClause, elementClause);
}
var query = baseQuery.Where(
(Expression<Func<TestData, bool>>)Expression.Lambda(
wholeClause, paramExpression)).Select(x => x.PriceObject);
result.AddRange(query);
}
结果代码非常复杂。构建表达式不是最简单的事情, 它涉及反射(反射本身并不那么快)。但是, 这有助于我们使用大量…(.. AND .. AND ..)OR(.. AND .. AND ..)OR(.. AND .. AND ..)….这些来构建单个查询。结果是：
比以前的任何一种方法都更糟糕。
选项5.共享查询数据表
让我们尝试另一种方法：
我向数据库添加了一个新表, 该表将保存查询数据。现在, 对于每个查询, 我可以：
- 开始交易(如果尚未开始)
- 将查询数据上传到该表(临时)
- 执行查询
- 回滚事务—删除上传的数据
var result = new List<Price>();
using (var context = CreateContext())
{
context.Database.BeginTransaction();
var reducedData = TestData.Select(x => new SharedQueryModel()
{
PriceSourceId = x.PriceSourceId, Ticker = x.Ticker, TradedOn = x.TradedOn
}).ToList();
// Here query data is stored to shared table
context.QueryDataShared.AddRange(reducedData);
context.SaveChanges();
var query = from p in context.Prices
join s in context.Securities on
p.SecurityId equals s.SecurityId
join t in context.QueryDataShared on
new { s.Ticker, p.TradedOn, p.PriceSourceId } equals
new { t.Ticker, t.TradedOn, t.PriceSourceId }
select p;
result.AddRange(query);
context.Database.RollbackTransaction();
}
指标优先：
结果非常好。非常快。内存消耗也不错。但是缺点是：
- 你必须在数据库中创建一个额外的表才能执行一种查询,
- 你必须启动一个事务(无论如何都会消耗DBMS资源), 并且
- 你必须向数据库中写入一些内容(在READ操作中！), 并且基本上, 如果你使用诸如只读副本之类的内容, 则此操作将无效。
但是除此之外, 这种方法还不错-快速且可读。在这种情况下, 将缓存查询计划！
选项6. MemoryJoin扩展
在这里, 我将使用一个名为EntityFrameworkCore.MemoryJoin的NuGet包。尽管其名称中包含Core一词, 但它也支持EF6。它称为MemoryJoin, 但实际上, 它会将指定的查询数据作为VALUES发送到服务器, 并且所有工作都在SQL Server上完成。
让我们检查一下代码。
var result = new List<Price>();
using (var context = CreateContext())
{
// better to select needed properties only, for better performance
var reducedData = TestData.Select(x => new {
x.Ticker, x.TradedOn, x.PriceSourceId
}).ToList();
var queryable = context.FromLocalList(reducedData);
var query = from p in context.Prices
join s in context.Securities on
p.SecurityId equals s.SecurityId
join t in queryable on
new { s.Ticker, p.TradedOn, p.PriceSourceId } equals
new { t.Ticker, t.TradedOn, t.PriceSourceId }
select p;
result.AddRange(query);
}
指标：
看起来很棒比以前的方法快三倍, 这使其成为最快的方法。 3.5秒可获得64K记录！该代码简单易懂。这适用于只读副本。让我们检查一下针对三个元素生成的查询：
SELECT "p"."PriceId", "p"."ClosePrice", "p"."OpenPrice", "p"."PriceSourceId", "p"."SecurityId", "p"."TradedOn", "t"."Ticker", "t"."TradedOn", "t"."PriceSourceId"
FROM "Price" AS "p"
INNER JOIN "Security" AS "s" ON "p"."SecurityId" = "s"."SecurityId"
INNER JOIN
( SELECT "x"."string1" AS "Ticker", "x"."date1" AS "TradedOn", CAST("x"."long1" AS int4) AS "PriceSourceId"
FROM
( SELECT *
FROM (
VALUES (1, @__gen_q_p0, @__gen_q_p1, @__gen_q_p2), (2, @__gen_q_p3, @__gen_q_p4, @__gen_q_p5), (3, @__gen_q_p6, @__gen_q_p7, @__gen_q_p8)
) AS __gen_query_data__ (id, string1, date1, long1)
) AS "x"
) AS "t" ON (("s"."Ticker" = "t"."Ticker")
AND ("p"."PriceSourceId" = "t"."PriceSourceId")
如你所见, 这次将实际值从内存传递到VALUES构造中的SQL Server。这可以解决问题：SQL服务器设法执行快速联接操作并正确使用索引。
但是, 存在一些缺点(你可以在我的博客上阅读更多内容)：
- 你需要向模型中添加一个额外的DbSet(但是无需在数据库中创建它)
- 该扩展程序不支持具有许多属性的模型类：三个字符串属性, 三个日期属性, 三个指南属性, 三个浮点/双精度属性和三个int / byte / long / decimal属性。我猜这在90％的情况下绰绰有余。但是, 如果不是这样, 则可以创建一个自定义类并使用它。因此, 提示：你需要在查询中传递实际值, 否则会浪费资源。
总结
在这里进行的测试中, 我肯定会选择MemoryJoin。其他人可能会反对这些缺点是无法克服的, 并且由于目前尚无法解决所有缺点, 因此我们应该放弃使用扩展。好吧, 对我来说, 这就像在说你不应该使用刀子, 因为这样可能会割伤自己。优化不是对初级开发人员的任务, 而是对了解EF如何工作的人的任务。为此, 该工具可以显着提高性能。谁知道？也许有一天, Microsoft的某人会为动态VALUES添加一些核心支持。
最后, 这里还有一些比较结果的图表。
下面是执行操作所花费时间的示意图。 MemoryJoin是唯一在合理时间内完成工作的程序。只有四种方法可以处理大量数据：两个朴素的实现, 共享表和MemoryJoin。
下一个图表用于内存消耗。除了带有多个包含的数字外, 所有方法都或多或少地显示了相同的数字。上面已经描述了这种现象。
评论前必须登录！
注册