在使用“Contains”时深入研究实体框架的性能 _性能

本文概述

问题
解
选项1。简单而天真
选项2。天真与并行
选项3.多个包含
选项4.谓词生成器
选项5.共享查询数据表
选项6. MemoryJoin扩展
总结

在日常工作中, 我使用实体框架。它非常方便, 但是在某些情况下, 它的性能很慢。尽管有很多有关提高EF性能的好文章, 并且给出了一些非常有用的建议(例如, 避免复杂的查询, “ 跳过并取” 中的参数, 仅使用视图, 仅选择所需的字段等), 但没有太多可以当你需要在两个或多个字段上使用复杂的包含时(换句话说, 当你将数据连接到内存列表时), 便可以完成此操作。
问题让我们检查以下示例：

var localData = http://www.srcmini.com/GetDataFromApiOrUser(); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in localDataon 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 = http://www.srcmini.com/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 = http://www.srcmini.com/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 = http://www.srcmini.com/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。