Ein tiefer Einblick in die Leistung von Entity Framework bei Verwendung von „Contains“

In meiner täglichen Arbeit verwende ich Entity Framework. Es ist sehr praktisch, aber in einigen Fällen ist seine Leistung langsam. Obwohl es viele gute Artikel zu EF-Leistungsverbesserungen gibt und einige sehr gute und nützliche Ratschläge gegeben werden (z. B. vermeiden Sie komplexe Abfragen, Parameter in Skip and Take, verwenden Sie Ansichten, wählen Sie nur benötigte Felder aus usw.), kann nicht so viel wirklich getan werden, wenn Sie komplexe Contains auf zwei oder mehr Felder anwenden müssen – mit anderen Worten, wenn Sie Daten mit einer Speicherliste verbinden .

Problem

Sehen wir uns das folgende Beispiel an:

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

Der obige Code funktioniert überhaupt nicht in EF 6, und während er in EF Core funktioniert , wird die Verknüpfung tatsächlich lokal durchgeführt – da ich zehn Millionen Datensätze in meiner Datenbank habe, werden alle heruntergeladen und der gesamte Speicher verbraucht . Dies ist kein Fehler in EF. Es wird erwartet. Wäre es jedoch nicht fantastisch, wenn es etwas gäbe, um dies zu lösen? In diesem Artikel werde ich einige Experimente mit einem anderen Ansatz durchführen, um diesen Leistungsengpass zu umgehen.

Lösung

Ich werde verschiedene Wege ausprobieren, um dies zu erreichen, angefangen von den einfachsten bis hin zu fortgeschritteneren. Bei jedem Schritt stelle ich Code und Metriken bereit, wie z. B. die benötigte Zeit und die Speichernutzung. Beachten Sie, dass ich den Lauf des Benchmarking-Programms unterbrechen werde, wenn es länger als zehn Minuten läuft.

Der Code für das Benchmarking-Programm befindet sich im folgenden Repository. Es verwendet C#, .NET Core, EF Core und PostgreSQL. Ich habe eine Maschine mit Intel Core i5, 8 GB RAM und einer SSD verwendet.

Das DB-Schema zum Testen sieht folgendermaßen aus:

Option 1. Einfach und naiv

Versuchen wir etwas Einfaches, nur um anzufangen.

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

Der Algorithmus ist einfach: Suchen Sie für jedes Element in den Testdaten ein geeignetes Element in der Datenbank und fügen Sie es der Ergebnissammlung hinzu. Dieser Code hat nur einen Vorteil: Er ist sehr einfach zu implementieren. Außerdem ist es lesbar und wartbar. Sein offensichtlicher Nachteil ist, dass es das langsamste ist. Obwohl alle drei Spalten indiziert sind, verursacht der Overhead der Netzwerkkommunikation immer noch einen Leistungsengpass. Hier sind die Metriken:

Bei einem großen Volumen dauert es also etwa eine Minute. Der Speicherverbrauch scheint angemessen zu sein.

Option 2. Naiv mit parallel

Versuchen wir nun, dem Code Parallelität hinzuzufügen. Die Kernidee hier ist, dass das Aufrufen der Datenbank in parallelen Threads die Gesamtleistung verbessern kann.

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

Interessanterweise arbeitet dieser Ansatz bei kleineren Testdatensätzen langsamer als die erste Lösung, bei größeren Stichproben aber schneller (hier ca. 2-mal). Der Speicherverbrauch ändert sich ein wenig, aber nicht wesentlich.

Option 3. Mehrere enthält

Versuchen wir einen anderen Ansatz:

• Bereiten Sie 3 Sammlungen eindeutiger Werte von Ticker, PriceSourceId und Date vor.
• Führen Sie die Abfrage mit einer einmaligen Filterung durch, indem Sie 3 Contains verwenden.
• Lokal erneut prüfen (siehe unten).

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

Dieser Ansatz ist problematisch. Die Ausführungszeit ist sehr datenabhängig. Es kann nur die erforderlichen Datensätze abrufen (in diesem Fall wird es sehr schnell sein), aber es kann viel mehr zurückgeben (vielleicht sogar 100-mal mehr).

Betrachten wir die folgenden Testdaten:

Hier frage ich die Kurse für Ticker1 gehandelt am 01.01.2018 und für Ticker2 gehandelt am 02.01.2018 ab. Tatsächlich werden jedoch vier Datensätze zurückgegeben.

Die eindeutigen Werte für Ticker sind Ticker1 und Ticker2 . Die eindeutigen Werte für TradedOn sind 2018-01-01 und 2018-01-02 .

Vier Datensätze stimmen also mit diesem Ausdruck überein.

Aus diesem Grund ist eine erneute Überprüfung vor Ort erforderlich und dieser Ansatz ist gefährlich. Die Metriken sind wie folgt:

Schrecklicher Speicherverbrauch! Tests mit großen Volumina schlugen aufgrund einer Zeitüberschreitung von 10 Minuten fehl.

Option 4. Prädikatgenerator

Lassen Sie uns das Paradigma ändern: Lassen Sie uns einen guten alten Expression für jeden Testdatensatz erstellen.

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

Der resultierende Code ist ziemlich komplex. Das Erstellen von Ausdrücken ist nicht die einfachste Sache und erfordert Reflexion (die selbst nicht so schnell ist). Aber es hilft uns, eine einzige Abfrage mit vielen … (.. AND .. AND ..) OR (.. AND .. AND ..) OR (.. AND .. AND ..) ... zu erstellen. Das sind die Ergebnisse:

Noch schlimmer als jeder der vorherigen Ansätze.

Option 5. Gemeinsame Abfragedatentabelle

Versuchen wir einen weiteren Ansatz:

Ich habe der Datenbank eine neue Tabelle hinzugefügt, die Abfragedaten enthält. Für jede Abfrage kann ich jetzt:

• Transaktion starten (falls noch nicht gestartet)
• Abfragedaten in diese Tabelle hochladen (temporär)
• Führen Sie eine Abfrage durch
• Machen Sie eine Transaktion rückgängig – um hochgeladene Daten zu löschen

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

Metriken zuerst:

Das Ergebnis ist sehr gut. Sehr schnell. Der Speicherverbrauch ist auch gut. Aber die Nachteile sind:

• Sie müssen eine zusätzliche Tabelle in der Datenbank erstellen, um nur eine Art von Abfrage durchzuführen,
• Sie müssen eine Transaktion starten (die sowieso DBMS-Ressourcen verbraucht) und
• Sie müssen etwas in die Datenbank schreiben (in einem READ-Vorgang!) – und das funktioniert im Grunde nicht, wenn Sie so etwas wie Read Replica verwenden.

Aber abgesehen davon ist dieser Ansatz nett – schnell und lesbar. Und in diesem Fall wird ein Abfrageplan zwischengespeichert!

Option 6. MemoryJoin-Erweiterung

Hier werde ich ein NuGet-Paket namens EntityFrameworkCore.MemoryJoin verwenden. Trotz der Tatsache, dass sein Name das Wort Core enthält, unterstützt es auch EF 6. Es heißt MemoryJoin, aber tatsächlich sendet es die angegebenen Abfragedaten als WERTE an den Server und die gesamte Arbeit wird auf dem SQL-Server erledigt.

Lassen Sie uns den Code überprüfen.

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

Metriken:

Das sieht toll aus. Dreimal schneller als der vorherige Ansatz – das macht ihn zum bisher schnellsten. 3,5 Sekunden für 64K-Aufzeichnungen! Der Code ist einfach und verständlich. Dies funktioniert mit schreibgeschützten Replikaten. Lassen Sie uns die generierte Abfrage auf drei Elemente überprüfen:

 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")

Wie Sie sehen können, werden diesmal tatsächliche Werte in der VALUES-Konstruktion aus dem Speicher an den SQL-Server übergeben. Und das macht den Trick: Der SQL-Server hat es geschafft, eine schnelle Join-Operation durchzuführen und die Indizes korrekt zu verwenden.

Es gibt jedoch einige Nachteile (mehr dazu in meinem Blog):

• Sie müssen Ihrem Modell ein zusätzliches DbSet hinzufügen (es muss jedoch nicht in der DB erstellt werden).
• Die Erweiterung unterstützt keine Modellklassen mit vielen Eigenschaften: drei String-Eigenschaften, drei Datumseigenschaften, drei Guide-Eigenschaften, drei Float/Double-Eigenschaften und drei Int/Byte/Long/Decimal-Eigenschaften. Das ist mehr als genug in 90% der Fälle, denke ich. Ist dies jedoch nicht der Fall, können Sie eine benutzerdefinierte Klasse erstellen und diese verwenden. Also, TIPP: Sie müssen die tatsächlichen Werte in einer Abfrage übergeben, sonst werden Ressourcen verschwendet.

Fazit

Unter den Dingen, die ich hier getestet habe, würde ich mich definitiv für MemoryJoin entscheiden. Jemand anderes mag einwenden, dass die Nachteile unüberwindbar sind, und da derzeit nicht alle behoben werden können, sollten wir auf die Verwendung der Erweiterung verzichten. Nun, für mich ist es so, als würde man sagen, dass man kein Messer benutzen sollte, weil man sich schneiden könnte. Die Optimierung war keine Aufgabe für Junior-Entwickler, sondern für jemanden, der versteht, wie EF funktioniert. Zu diesem Zweck kann dieses Tool die Leistung erheblich verbessern. Wer weiß? Vielleicht wird eines Tages jemand bei Microsoft Kernunterstützung für dynamische WERTE hinzufügen.

Schließlich sind hier noch ein paar Diagramme, um die Ergebnisse zu vergleichen.

Unten ist ein Diagramm für die Zeit, die benötigt wird, um eine Operation durchzuführen. MemoryJoin ist das einzige, das die Arbeit in angemessener Zeit erledigt. Nur vier Ansätze können große Mengen verarbeiten: zwei naive Implementierungen, gemeinsam genutzte Tabellen und MemoryJoin.

Das nächste Diagramm ist für den Speicherverbrauch. Alle Ansätze weisen mehr oder weniger die gleichen Zahlen auf, mit Ausnahme des Ansatzes mit mehreren Contains . Dieses Phänomen wurde oben beschrieben.