使用“包含”時深入了解實體框架的性能

已發表: 2022-03-11

在我的日常工作中，我使用實體框架。 這很方便，但在某些情況下，它的性能很慢。 儘管有很多關於 EF 性能改進的好文章，並且給出了一些非常好的和有用的建議（例如，避免複雜的查詢、Skip 和 Take 中的參數、使用視圖、僅選擇需要的字段等），但沒有多少可以當您需要在兩個或多個字段上使用複雜的Contains時，確實可以做到這一點——換句話說，當您將數據連接到內存列表時。

問題

讓我們檢查以下示例：

 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 中起作用，但連接實際上是在本地完成的——因為我的數據庫中有 1000 萬條記錄，所有這些都被下載並且所有內存都被消耗. 這不是 EF 中的錯誤。 這是預期的。 但是，如果有辦法解決這個問題，那不是很棒嗎？ 在本文中，我將使用不同的方法做一些實驗來解決這個性能瓶頸。

解決方案

我將嘗試不同的方法來實現這一點，從最簡單到更高級。 在每個步驟中，我將提供代碼和指標，例如所用時間和內存使用情況。 請注意，如果基準測試程序運行時間超過十分鐘，我將中斷它的運行。

基準測試程序的代碼位於以下存儲庫中。 它使用 C#、.NET Core、EF Core 和 PostgreSQL。 我使用了一台配備 Intel Core i5、8 GB RAM 和 SSD 的機器。

用於測試的 DB 模式如下所示：

數據庫中的表格：價格、證券和價格來源

只有三個表：價格、證券和價格來源。 價格表有數千萬條記錄。

選項 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. 多個包含

讓我們嘗試另一種方法：

  • 準備 Ticker、PriceSourceId 和 Date 的 3 個唯一值集合。
  • 使用 3 Contains 執行一次運行過濾的查詢。
  • 在本地重新檢查（見下文）。
 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-01-01 交易的 Ticker1 和 2018-01-02 交易的 Ticker2 的價格。 但是，實際上將返回四條記錄。

Ticker的唯一值是Ticker1和Ticker2 。 2018-01-02的唯一值是TradedOn和2018-01-01 。

因此，有四個記錄與此表達式匹配。

這就是為什麼需要進行本地重新檢查以及這種方法很危險的原因。 指標如下：

第三次實驗的結果

可怕的內存消耗！ 由於 10 分鐘超時，大容量測試失敗。

選項 4. 謂詞生成器

讓我們改變範式：讓我們為每個測試數據集構建一個好的舊Expression 。

 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這個詞，但它也支持EF 6。它叫做MemoryJoin，但實際上它把指定的查詢數據作為VALUES發送到服務器，所有的工作都在SQL服務器上完成。

讓我們檢查一下代碼。

 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 服務器。 這就是訣竅：SQL 服務器設法執行快速連接操作並正確使用索引。

但是，有一些缺點（您可以在我的博客上閱讀更多內容）：

  • 您需要在模型中添加一個額外的 DbSet（但無需在數據庫中創建它）
  • 該擴展不支持具有許多屬性的模型類：三個字符串屬性、三個日期屬性、三個指南屬性、三個浮點/雙精度屬性和三個 int/byte/long/decimal 屬性。 我猜這在 90% 的情況下已經足夠了。 但是，如果不是，您可以創建一個自定義類並使用它。 因此，提示：您需要在查詢中傳遞實際值，否則會浪費資源。

結論

在我在這裡測試過的東西中，我肯定會選擇 MemoryJoin。 其他人可能會反對它的缺點是無法克服的，並且由於目前無法解決所有問題，因此我們應該避免使用擴展程序。 嗯，對我來說，這就像說你不應該使用刀，因為你可能會割傷自己。 優化不是初級開發人員的任務，而是了解 EF 工作原理的人的任務。 為此，該工具可以顯著提高性能。 誰知道？ 也許有一天，微軟的某個人會為動態 VALUES 添加一些核心支持。

最後，這裡還有一些圖表來比較結果。

下面是執行操作所用時間的圖表。 MemoryJoin 是唯一一個在合理的時間內完成這項工作的人。 只有四種方法可以處理大容量：兩種簡單的實現、共享表和 MemoryJoin。

每個實驗在不同情況下花費的時間

下圖用於內存消耗。 除了具有多個Contains的方法之外，所有方法都或多或少地展示了相同的數字。 上面描述了這種現象。

每個實驗在不同情況下的內存消耗