Un'analisi approfondita delle prestazioni di Entity Framework quando si utilizza "contiene"

Durante il mio lavoro quotidiano, utilizzo Entity Framework. È molto conveniente, ma in alcuni casi le sue prestazioni sono lente. Nonostante ci siano molti buoni articoli sui miglioramenti delle prestazioni di EF e vengano forniti alcuni consigli molto validi e utili (ad esempio, evitare query complesse, parametri in Salta e acquisisci, utilizzare visualizzazioni, selezionare solo i campi necessari, ecc.), non molto può essere davvero fatto quando è necessario utilizzare Contains complessi su due o più campi, in altre parole, quando si uniscono dati a un elenco di memoria .

Problema

Controlliamo il seguente esempio:

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

Il codice sopra non funziona affatto in EF 6 e, sebbene funzioni in EF Core, l'unione viene effettivamente eseguita localmente, poiché ho dieci milioni di record nel mio database, tutti vengono scaricati e tutta la memoria viene consumata . Questo non è un bug in EF. È previsto. Tuttavia, non sarebbe fantastico se ci fosse qualcosa per risolverlo? In questo articolo, farò alcuni esperimenti con un approccio diverso per aggirare questo collo di bottiglia delle prestazioni.

Soluzione

Proverò diversi modi per raggiungere questo obiettivo, dal più semplice al più avanzato. In ogni passaggio, fornirò codice e metriche, come il tempo impiegato e l'utilizzo della memoria. Nota che interromperò l'esecuzione del programma di benchmarking se funziona per più di dieci minuti.

Il codice per il programma di benchmarking si trova nel seguente repository. Usa C#, .NET Core, EF Core e PostgreSQL. Ho usato una macchina con Intel Core i5, 8 GB di RAM e un SSD.

Lo schema DB per il test è simile al seguente:

Opzione 1. Semplice e ingenuo

Proviamo qualcosa di semplice, solo per iniziare.

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

L'algoritmo è semplice: per ogni elemento nei dati del test, trova un elemento appropriato nel database e aggiungilo alla raccolta dei risultati. Questo codice ha solo un vantaggio: è molto facile da implementare. Inoltre, è leggibile e manutenibile. Il suo evidente svantaggio è che è il più lento. Anche se tutte e tre le colonne sono indicizzate, il sovraccarico della comunicazione di rete crea comunque un collo di bottiglia delle prestazioni. Ecco le metriche:

Quindi, per un grande volume, ci vuole circa un minuto. Il consumo di memoria sembra essere ragionevole.

Opzione 2. Ingenuo con parallelo

Ora proviamo ad aggiungere parallelismo al codice. L'idea di base qui è che colpire il database in thread paralleli può migliorare le prestazioni complessive.

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

È interessante notare che, per set di dati di test più piccoli, questo approccio funziona più lentamente della prima soluzione, ma per campioni più grandi è più veloce (in questo caso circa 2 volte). Il consumo di memoria cambia leggermente, ma non in modo significativo.

Opzione 3. Più contiene

Proviamo un altro approccio:

• Prepara 3 raccolte di valori univoci di Ticker, PriceSourceId e Date.
• Eseguire la query con un filtro di esecuzione utilizzando 3 contiene.
• Ricontrolla localmente (vedi sotto).

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

Questo approccio è problematico. Il tempo di esecuzione dipende molto dai dati. Potrebbe recuperare solo i record richiesti (nel qual caso sarà molto veloce), ma potrebbe restituirne molti di più (forse anche 100 volte di più).

Consideriamo i seguenti dati di prova:

Qui interrogo i prezzi per Ticker1 scambiati il ​​01-01-2018 e per Ticker2 scambiati il ​​02-01-2018. Tuttavia, verranno effettivamente restituiti quattro record.

I valori univoci per Ticker sono Ticker1 e Ticker2 . I valori univoci per TradedOn sono 2018-01-01 e 2018-01-02 .

Quindi, quattro record corrispondono a questa espressione.

Ecco perché è necessario un nuovo controllo locale e perché questo approccio è pericoloso. Le metriche sono le seguenti:

Consumo di memoria terribile! I test con grandi volumi non sono riusciti a causa di un timeout di 10 minuti.

Opzione 4. Costruttore di predicati

Cambiamo il paradigma: costruiamo una buona vecchia Expression per ogni set di dati di test.

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

Il codice risultante è piuttosto complesso. Costruire espressioni non è la cosa più semplice e implica la riflessione (che, di per sé, non è così veloce). Ma ci aiuta a costruire una singola query usando molti … (.. AND .. AND ..) OR (.. AND .. AND ..) OR (.. AND .. AND ..) ... . Questi sono i risultati:

Anche peggio di entrambi gli approcci precedenti.

Opzione 5. Tabella dati query condivisa

Proviamo un altro approccio:

Ho aggiunto una nuova tabella al database che conterrà i dati della query. Per ogni domanda ora posso:

• Avvia una transazione (se non ancora iniziata)
• Carica i dati della query su quella tabella (temporaneo)
• Eseguire una query
• Annulla una transazione: per eliminare i dati caricati

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

Prima le metriche:

Il risultato è molto buono. Molto veloce. Anche il consumo di memoria è buono. Ma gli svantaggi sono:

• Devi creare una tabella aggiuntiva nel database per eseguire un solo tipo di query,
• Devi avviare una transazione (che comunque consuma risorse DBMS) e
• Devi scrivere qualcosa nel database (in un'operazione READ!) e in pratica, questo non funzionerà se usi qualcosa come read replica.

Ma a parte questo, questo approccio è piacevole, veloce e leggibile. E in questo caso viene memorizzato nella cache un piano di query!

Opzione 6. Estensione MemoryJoin

Qui userò un pacchetto NuGet chiamato EntityFrameworkCore.MemoryJoin. Nonostante il nome contenga la parola Core, supporta anche EF 6. Si chiama MemoryJoin, ma in realtà invia i dati della query specificati come VALUES al server e tutto il lavoro viene eseguito sul server SQL.

Controlliamo il codice.

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

Metrica:

Questo sembra fantastico. Tre volte più veloce dell'approccio precedente, questo lo rende il più veloce di sempre. 3,5 secondi per 64.000 record! Il codice è semplice e comprensibile. Funziona con repliche di sola lettura. Controlliamo la query generata per tre elementi:

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

Come puoi vedere, questa volta i valori effettivi vengono passati dalla memoria al server SQL nella costruzione VALUES. E questo fa il trucco: il server SQL è riuscito a eseguire un'operazione di join veloce e utilizzare correttamente gli indici.

Tuttavia, ci sono alcuni inconvenienti (potresti leggere di più sul mio blog):

• Devi aggiungere un DbSet extra al tuo modello (tuttavia non è necessario crearlo nel DB)
• L'estensione non supporta le classi del modello con molte proprietà: tre proprietà di stringa, tre proprietà di data, tre proprietà di guida, tre proprietà float/doppie e tre proprietà int/byte/long/decimal. Questo è più che sufficiente nel 90% dei casi, credo. Tuttavia, in caso contrario, puoi creare una classe personalizzata e utilizzarla. Quindi, SUGGERIMENTO: è necessario passare i valori effettivi in ​​una query, altrimenti le risorse vengono sprecate.

Conclusione

Tra le cose che ho testato qui, sceglierei sicuramente MemoryJoin. Qualcun altro potrebbe obiettare che gli inconvenienti sono insormontabili, e poiché non tutti sono al momento risolvibili, dovremmo astenerci dall'utilizzare l'estensione. Beh, per me è come dire che non dovresti usare un coltello perché potresti tagliarti. L'ottimizzazione non era un compito per gli sviluppatori junior, ma per qualcuno che capiva come funziona EF. A tal fine, questo strumento può migliorare notevolmente le prestazioni. Chissà? Forse un giorno qualcuno in Microsoft aggiungerà un supporto di base per VALUES dinamici.

Infine, ecco alcuni altri diagrammi per confrontare i risultati.

Di seguito è riportato un diagramma del tempo impiegato per eseguire un'operazione. MemoryJoin è l'unico che fa il lavoro in un tempo ragionevole. Solo quattro approcci possono elaborare grandi volumi: due implementazioni ingenue, tabella condivisa e MemoryJoin.

Il diagramma successivo riguarda il consumo di memoria. Tutti gli approcci dimostrano più o meno gli stessi numeri tranne quello con più Contains . Questo fenomeno è stato descritto sopra.