データサイエンスとデータベース

'Contains'を使用する場合のEntityFrameworkのパフォーマンスの詳細

日常業務では、EntityFrameworkを使用しています。とても便利ですが、性能が遅い場合があります。 EFのパフォーマンスの向上に関する優れた記事がたくさんあり、非常に優れた有用なアドバイスがいくつか提供されています（たとえば、複雑なクエリの回避、スキップアンドテイクのパラメーター、ビューの使用、必要なフィールドのみの選択など）。複雑なContainsを使用する必要があるときに実際に実行します2つ以上のフィールド、つまり、 データをメモリリストに結合するとき 。

問題

次の例を確認してみましょう。

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();

上記のコードはEF6ではまったく機能しません。 しますか EF Coreで作業する場合、結合は実際にはローカルで行われます。データベースに1,000万件のレコードがあるため、 すべて それらのうちダウンロードされ、すべてのメモリが消費されます。これはEFのバグではありません。期待されています。しかし、これを解決する何かがあったら素晴らしいと思いませんか？この記事では、このパフォーマンスのボトルネックを回避するために、別のアプローチでいくつかの実験を行います。

解決

これを実現するために、最も単純なものからより高度なものまで、さまざまな方法を試してみます。各ステップで、所要時間やメモリ使用量などのコードとメトリックを提供します。ベンチマークプログラムが10分より長く機能する場合は、実行を中断することに注意してください。

ベンチマークプログラムのコードは次の場所にありますリポジトリ。 C＃、. NET Core、EF Core、およびPostgreSQLを使用します。 Intel Core i5、8 GB RAM、およびSSDを搭載したマシンを使用しました。

nodejsは何に使用されますか

テスト用のDBスキーマは次のようになります。

データベース内のテーブル：価格、証券、価格ソース

価格、有価証券、価格ソースの3つのテーブルのみ。価格表には数千万のレコードがあります。

オプション1.シンプルでナイーブ

始めるために、簡単なことを試してみましょう。

var result = new List(); 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)); } }

アルゴリズムは単純です。テストデータの各要素について、データベースで適切な要素を見つけて、結果コレクションに追加します。このコードには1つの利点があります。実装が非常に簡単です。また、読みやすく、保守も容易です。その明らかな欠点は、それが最も遅いものであるということです。 3つの列すべてにインデックスが付けられている場合でも、ネットワーク通信のオーバーヘッドによってパフォーマンスのボトルネックが発生します。指標は次のとおりです。

最初の実験の結果

したがって、大容量の場合、約1分かかります。メモリ消費は妥当なようです。

オプション2.並列でナイーブ

それでは、コードに並列処理を追加してみましょう。ここでの中心的な考え方は、並列スレッドでデータベースにアクセスすると、全体的なパフォーマンスが向上する可能性があるということです。

var result = new ConcurrentBag(); 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倍）。メモリ消費量は少し変化しますが、それほど大きくはありません。

2回目の実験結果

オプション3。複数の内容

別のアプローチを試してみましょう。

Ticker、PriceSourceId、およびDateの一意の値の3つのコレクションを準備します。
3つのContainsを使用して、1回の実行フィルタリングでクエリを実行します。
ローカルで再確認します（以下を参照）。

var result = new List(); 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の価格を照会します。ただし、実際には4つのレコードが返されます。

Tickerの一意の値はTicker1およびTicker2。 TradedOnの一意の値は2018-01-01および2018-01-02。

オンラインデートサービスのリスト

したがって、4つのレコードがこの式に一致します。

そのため、ローカルでの再チェックが必要であり、このアプローチが危険である理由です。指標は次のとおりです。

3回目の実験結果

ひどいメモリ消費！大きなボリュームのテストは、10分のタイムアウトが原因で失敗しました。

オプション4.述語ビルダー

パラダイムを変えましょう：古き良き時代を築きましょうExpression各テストデータセットに対して。

var result = new List(); 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)Expression.Lambda( wholeClause, paramExpression)).Select(x => x.PriceObject); result.AddRange(query); }

結果のコードはかなり複雑です。式の作成は最も簡単なことではなく、リフレクションが含まれます（それ自体はそれほど高速ではありません）。ただし、多くの… (.. AND .. AND ..) OR (.. AND .. AND ..) OR (.. AND .. AND ..) ...を使用して単一のクエリを作成するのに役立ちます。結果は次のとおりです。

4回目の実験結果

Android用の最高のデータベースアプリ

以前のアプローチのいずれよりもさらに悪い。

オプション5.共有クエリデータテーブル

もう1つのアプローチを試してみましょう。

クエリデータを保持する新しいテーブルをデータベースに追加しました。クエリごとに、次のことができます。

トランザクションを開始します（まだ開始されていない場合）
そのテーブルにクエリデータをアップロードします（一時的）
クエリを実行する
トランザクションをロールバックします—アップロードされたデータを削除します

var result = new List(); 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(); }

最初のメトリクス：

5番目の実験の結果

結果はとても良いです。とても早い。メモリ消費も良好です。ただし、欠点は次のとおりです。

1種類のクエリを実行するには、データベースに追加のテーブルを作成する必要があります。
トランザクション（とにかくDBMSリソースを消費する）を開始する必要があり、
データベースに何かを書き込む必要があります（READ操作で！）。基本的に、リードレプリカのようなものを使用する場合、これは機能しません。

しかし、それを除けば、このアプローチは優れており、高速で読みやすくなっています。この場合、クエリプランはキャッシュされます。

オプション6。MemoryJoin拡張機能

ここでは、というNuGetパッケージを使用します EntityFrameworkCore.MemoryJoin 。名前にCoreという単語が含まれているにもかかわらず、EF 6もサポートしています。これはMemoryJoinと呼ばれますが、実際には、指定されたクエリデータをVALUESとしてサーバーに送信し、すべての作業はSQLサーバーで行われます。

コードを確認しましょう。

cプログラミングを学ぶ方法

var result = new List(); 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倍の速さで、これまでで最速になります。 64Kレコードで3.5秒！コードはシンプルで理解しやすいです。これは、読み取り専用レプリカで機能します。次の3つの要素に対して生成されたクエリを確認してみましょう。

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を追加する必要があります（ただし、DBで作成する必要はありません）。
拡張機能は、3つの文字列プロパティ、3つの日付プロパティ、3つのガイドプロパティ、3つのfloat / doubleプロパティ、および3つのint / byte / long / decimalプロパティなどの多くのプロパティを持つモデルクラスをサポートしていません。これは、90％のケースで十分すぎると思います。ただし、そうでない場合は、カスタムクラスを作成して使用できます。したがって、ヒント：クエリで実際の値を渡す必要があります。そうしないと、リソースが無駄になります。

結論

ここでテストしたものの中で、私は間違いなくMemoryJoinに行きます。他の誰かが欠点を克服できないことに反対するかもしれません、そしてそれらのすべてが現時点で解決できるわけではないので、私たちは拡張機能の使用を控えるべきです。ええと、私にとっては、自分で切ることができるので、ナイフを使うべきではないと言っているようなものです。最適化は、ジュニア開発者にとってではなく、EFの仕組みを理解している人にとってのタスクでした。そのために、このツールはパフォーマンスを劇的に向上させることができます。知るか？たぶんいつか、Microsoftの誰かが動的VALUESのコアサポートを追加するでしょう。

最後に、結果を比較するための図をさらにいくつか示します。

以下は、操作の実行にかかる時間の図です。 MemoryJoinは、妥当な時間内に仕事をする唯一のものです。大きなボリュームを処理できるのは、2つの単純な実装、共有テーブル、およびMemoryJoinの4つのアプローチのみです。

実験ごとにさまざまな場合にかかる時間