L’ordine delle clausole della query LINQ dovrebbe influire sulle prestazioni di Entity Framework?

Sto utilizzando Entity Framework (prima il codice) e trovare le clausole di ordine I specific nelle mie query LINQ sta avendo un enorme impatto sulle prestazioni, quindi ad esempio:

using (var db = new MyDbContext()) { var mySize = "medium"; var myColour = "vermilion"; var list1 = db.Widgets.Where(x => x.Colour == myColour && x.Size == mySize).ToList(); var list2 = db.Widgets.Where(x => x.Size == mySize && x.Colour == myColour).ToList(); } 

Dove la (rara) clausola di colore precede la clausola di dimensioni (comune) è veloce, ma viceversa gli ordini di grandezza sono più lenti. La tabella ha un paio di milioni di righe ei due campi in questione sono nvarchar (50), quindi non normalizzati ma sono indicizzati ciascuno. I campi sono specificati in un codice come segue:

  [StringLength(50)] public string Colour { get; set; } [StringLength(50)] public string Size { get; set; } 

Dovevo davvero preoccuparmi di queste cose nelle mie query LINQ, pensavo che fosse il lavoro del database?

Le specifiche del sistema sono:

  • Visual Studio 2010
  • .NET 4
  • EntityFramework 6.0.0-beta1
  • SQL Server 2008 R2 Web (64 bit)

Aggiornare:

Giusto, per ogni ghiottone per punizione l’effetto può essere replicato come di seguito. Il problema sembra essere tremendamente sensibile a una serie di fattori, quindi per favore sopportare la natura forzata di alcuni di questi:

Installa EntityFramework 6.0.0-beta1 tramite nuget, quindi genera il codice primo stile con:

 public class Widget { [Key] public int WidgetId { get; set; } [StringLength(50)] public string Size { get; set; } [StringLength(50)] public string Colour { get; set; } } 

 public class MyDbContext : DbContext { public MyDbContext() : base("DefaultConnection") { } public DbSet Widgets { get; set; } } 

Genera i dati fittizi con il seguente SQL:


 insert into gadget (Size, Colour) select RND1 + ' is the name is this size' as Size, RND2 + ' is the name of this colour' as Colour from (Select top 1000000 CAST(abs(Checksum(NewId())) % 100 as varchar) As RND1, CAST(abs(Checksum(NewId())) % 10000 as varchar) As RND2 from master..spt_values t1 cross join master..spt_values t2) t3 

Aggiungi un indice ciascuno per Colore e Dimensione, quindi esegui una query con:


 string mySize = "99 is the name is this size"; string myColour = "9999 is the name of this colour"; using (var db = new WebDbContext()) { var list1= db.Widgets.Where(x => x.Colour == myColour && x.Size == mySize).ToList(); } using (var db = new WebDbContext()) { var list2 = db.Widgets.Where(x => x.Size == mySize && x.Colour == myColour).ToList(); } 

Il problema sembra collegato alla raccolta ottusa dei confronti NULL nell’SQL generato, come di seguito.

 exec sp_executesql N'SELECT [Extent1].[WidgetId] AS [WidgetId], [Extent1].[Size] AS [Size], [Extent1].[Colour] AS [Colour] FROM [dbo].[Widget] AS [Extent1] WHERE ((([Extent1].[Size] = @p__linq__0) AND ( NOT ([Extent1].[Size] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[Size] IS NULL) AND (@p__linq__0 IS NULL))) AND ((([Extent1].[Colour] = @p__linq__1) AND ( NOT ([Extent1].[Colour] IS NULL OR @p__linq__1 IS NULL))) OR (([Extent1].[Colour] IS NULL) AND (@p__linq__1 IS NULL)))',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)', @p__linq__0=N'99 is the name is this size', @p__linq__1=N'9999 is the name of this colour' go 

La modifica dell’operatore di uguaglianza nel LINQ su StartWith () fa scomparire il problema, così come la modifica di uno dei due campi come non annullabile nel database.

Io dispero!

Aggiornamento 2:

Qualche aiuto per eventuali cacciatori di taglie, il problema può essere riprodotto su SQL Server 2008 R2 Web (64 bit) in un database pulito, come segue:

 CREATE TABLE [dbo].[Widget]( [WidgetId] [int] IDENTITY(1,1) NOT NULL, [Size] [nvarchar](50) NULL, [Colour] [nvarchar](50) NULL, CONSTRAINT [PK_dbo.Widget] PRIMARY KEY CLUSTERED ( [WidgetId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_Widget_Size ON dbo.Widget ( Size ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_Widget_Colour ON dbo.Widget ( Colour ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO insert into Widget (Size, Colour) select RND1 + ' is the name is this size' as Size, RND2 + ' is the name of this colour' as Colour from (Select top 1000000 CAST(abs(Checksum(NewId())) % 100 as varchar) As RND1, CAST(abs(Checksum(NewId())) % 10000 as varchar) As RND2 from master..spt_values t1 cross join master..spt_values t2) t3 GO 

e quindi confrontare le prestazioni relative delle due query seguenti (potrebbe essere necessario regolare i valori di test dei parametri per ottenere una query che restituisce un paio di righe per osservare l’effetto, ovvero il secondo id della query molto più lento).

 exec sp_executesql N'SELECT [Extent1].[WidgetId] AS [WidgetId], [Extent1].[Size] AS [Size], [Extent1].[Colour] AS [Colour] FROM [dbo].[Widget] AS [Extent1] WHERE ((([Extent1].[Colour] = @p__linq__0) AND ( NOT ([Extent1].[Colour] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[Colour] IS NULL) AND (@p__linq__0 IS NULL))) AND ((([Extent1].[Size] = @p__linq__1) AND ( NOT ([Extent1].[Size] IS NULL OR @p__linq__1 IS NULL))) OR (([Extent1].[Size] IS NULL) AND (@p__linq__1 IS NULL)))', N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)', @p__linq__0=N'9999 is the name of this colour', @p__linq__1=N'99 is the name is this size' go exec sp_executesql N'SELECT [Extent1].[WidgetId] AS [WidgetId], [Extent1].[Size] AS [Size], [Extent1].[Colour] AS [Colour] FROM [dbo].[Widget] AS [Extent1] WHERE ((([Extent1].[Size] = @p__linq__0) AND ( NOT ([Extent1].[Size] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[Size] IS NULL) AND (@p__linq__0 IS NULL))) AND ((([Extent1].[Colour] = @p__linq__1) AND ( NOT ([Extent1].[Colour] IS NULL OR @p__linq__1 IS NULL))) OR (([Extent1].[Colour] IS NULL) AND (@p__linq__1 IS NULL)))', N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)', @p__linq__0=N'99 is the name is this size', @p__linq__1=N'9999 is the name of this colour' 

Potresti anche trovare, come faccio io, che se riesegui l’inserimento dei dati fittizi in modo che ci siano ora due milioni di righe, il problema scompare.

Il nocciolo della questione non è “perché l’ordine conta con LINQ?”. LINQ si traduce letteralmente senza riordino. La vera domanda è “perché le due query SQL hanno prestazioni diverse?”.

Sono stato in grado di riprodurre il problema inserendo solo 100k righe. In tal caso viene triggersto un punto debole dell’ottimizzatore: non riconosce che può eseguire una ricerca sul Colour causa della condizione complessa. Nella prima query l’ottimizzatore riconosce il modello e crea un indice di ricerca.

Non c’è una ragione semantica per cui dovrebbe essere così. Una ricerca su un indice è ansible anche quando si cerca su NULL . Questo è un punto debole / bug nell’ottimizzatore. Ecco i due piani:

inserisci la descrizione dell'immagine qui

EF cerca di essere utile qui perché presuppone che sia la colonna sia la variabile del filtro possono essere nulle. In tal caso cerca di darti una corrispondenza (che secondo la semantica C # è la cosa giusta).

Ho provato a annullarlo aggiungendo il seguente filtro:

 Colour IS NOT NULL AND @p__linq__0 IS NOT NULL AND Size IS NOT NULL AND @p__linq__1 IS NOT NULL 

Sperando che l’ottimizzatore ora usi quella conoscenza per semplificare la complessa espressione del filtro EF. Non è riuscito a farlo. Se ciò avesse funzionato, lo stesso filtro avrebbe potuto essere aggiunto alla query EF fornendo una soluzione semplice.

Ecco le correzioni che raccomando nell’ordine in cui dovresti provarle:

  1. Rendere le colonne del database non nulle nel database
  2. Rendere le colonne non nulle nel modello di dati EF sperando che ciò impedisca a EF di creare la condizione di filtro complessa
  3. Crea indici: Colour, Size e / o Size, Colour . Rimuovono anche loro il problema.
  4. Assicurarsi che il filtraggio sia fatto nell’ordine corretto e lasciare un commento sul codice
  5. Prova ad usare INTERSECT / Queryable.Intersect per combinare i filtri. Ciò si traduce spesso in diverse forms di piano.
  6. Crea una funzione con valori di tabella incorporata che esegue il filtraggio. EF può utilizzare tale funzione come parte di una query più ampia
  7. Scendi verso il basso per SQL
  8. Utilizzare una guida di piano per modificare il piano

Tutti questi sono soluzioni alternative, non le correzioni di root.

Alla fine non sono soddisfatto sia di SQL Server che di EF qui. Entrambi i prodotti dovrebbero essere corretti. Ahimè, probabilmente non lo saranno e non puoi nemmeno aspettare.

Ecco gli script dell’indice:

 CREATE NONCLUSTERED INDEX IX_Widget_Colour_Size ON dbo.Widget ( Colour, Size ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] CREATE NONCLUSTERED INDEX IX_Widget_Size_Colour ON dbo.Widget ( Size, Colour ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

È difficile sapere se questo problema è causato dall’esecuzione di una beta EF, ma l’SQL generato per la semplice query LINQ è inaspettatamente inutile dal punto di vista di Query Optimizer di SQL Server.

Le costruzioni SQL comuni per la gestione dei confronti di uguaglianza nullable hanno un supporto specifico per Query Optimizer di SQL Server. Uno (dei molti) moduli di query SQL compatibili è:

 (x = y) OR (x IS NULL AND y IS NULL) 

L’SQL generato da EF segue invece questo schema:

 ((x = y) AND NOT (x IS NULL OR y IS NULL)) OR (x IS NULL AND y IS NULL) 

La clausola aggiuntiva AND NOT è ridondante; tuttavia è abbastanza opaco da evitare la corrispondenza con una semplificazione cruciale di Query Optimizer. L’ironia è che molte delle regole di semplificazione in Query Optimizer esistono puramente per supportare il codice generato.

Se EF ha generato codice corrispondente al primo modulo SQL (ad esempio):

 SELECT W.WidgetId, W.Size, W.Colour FROM dbo.Widget AS W WHERE ( (W.Size = @p__linq__0) OR (W.Size IS NULL AND @p__linq__0 IS NULL) ) AND ( (W.Colour = @p__linq__1) OR (W.Colour IS NULL AND @p__linq__1 IS NULL) ); 

… il piano di esecuzione sarebbe un semplice incrocio di indice, utilizzando un singolo predicato di uguaglianza NULL su ogni ricerca:

Piano di intersezione dell'indice

Questo stesso piano efficiente viene generato se l’ordine del predicato principale impostato nella clausola WHERE viene invertito.

In questo caso il problema può essere evitato dichiarando le colonne nel database e / o nel codice EF come NOT NULL (supponendo che i dati non contengano di fatto NULLs ), ma ciò non cambia il fatto che i semplici confronti a colonne nullable dovrebbero essere gestito meglio.

Se i team EF e SQL Server avessero lavorato insieme su questo argomento, l’EF genererebbe il codice in un formato più ottimizzatore, oppure il supporto specifico per questa syntax sarebbe stato aggiunto all’ottimizzatore di SQL Server. Si noti che ci saranno sempre più modi per scrivere lo stesso requisito logico in SQL rispetto a un ottimizzatore di database generico che può (o dovrebbe) anticipare. Per prima cosa, non è ansible anticipare ogni ansible costruzione, e per un altro non ti piacerebbe che i tempi di compilazione del piano se lo facessero.

C’è una funzionalità finale di SQL Server che dovrei menzionare, perché è un peccato che i generatori di codice in genere non lo supportino. Laddove la query generata viene contrassegnata per la ricompilazione su ogni esecuzione utilizzando l’hint di query OPTION (RECOMPILE) , Query Optimizer può utilizzare l’ottimizzazione dell’inclusione dei parametri per produrre un piano di query una tantum per i valori dei parametri specifici.

Questa è una funzione potente, in grado di rimuovere completamente o riscrivere parti non necessarie di una query prima dell’ottimizzazione. Ad esempio, semplicemente aggiungendo il suggerimento per la query all’originale SQL generato:

 SELECT [Extent1].[WidgetId] AS [WidgetId], [Extent1].[Size] AS [Size], [Extent1].[Colour] AS [Colour] FROM [dbo].[Widget] AS [Extent1] WHERE ((([Extent1].[Size] = @p__linq__0) AND ( NOT ([Extent1].[Size] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[Size] IS NULL) AND (@p__linq__0 IS NULL))) AND ((([Extent1].[Colour] = @p__linq__1) AND ( NOT ([Extent1].[Colour] IS NULL OR @p__linq__1 IS NULL))) OR (([Extent1].[Colour] IS NULL) AND (@p__linq__1 IS NULL))) OPTION (RECOMPILE); 

… produce un piano ottimale su misura per i valori dei parametri specifici:

Piano ricompilato

C’è un prezzo da pagare per la compilazione del piano su ogni esecuzione, ma la qualità del piano migliorata spesso ripaga questo costo molte volte. Dove un buon piano dipende sensibilmente dai valori dei parametri passati, questa può essere una tecnica molto efficace.

Nota: mi sono imbattuto in questa domanda molto tempo dopo che altri hanno già fornito risposte generalmente corrette. Ho deciso di postare questa risposta come risposta separata solo perché ritengo che la soluzione alternativa possa essere utile e perché potresti apprezzare una migliore comprensione del motivo per cui EF si comporta in questo modo.

Risposta breve: la soluzione migliore per questo problema è impostare questo flag sull’istanza di DbContext:

 context.Configuration.UseDatabaseNullSemantics = true; 

Quando lo fai, tutti i controlli null supplementari andranno via e le tue query dovrebbero essere più veloci se sono interessate da questo problema.

Risposta lunga: altri in questo thread hanno ragione che in EF6 abbiamo introdotto i termini extra di verifica nulla per compensare le differenze tra la semantica dei confronti nulli nel database ( logica a tre valori ) e i confronti nulli standard in memoria. L’objective di questo è soddisfare la seguente richiesta molto popolare:

Gestione errata delle variabili nulle nella clausola ‘where’

Paul White ha anche ragione che nella seguente espressione la parte ‘AND NOT’ è meno comune nel compensare la logica a tre valori:

 ((x = y) AND NOT (x IS NULL OR y IS NULL)) OR (x IS NULL AND y IS NULL) 

Questa condizione aggiuntiva è necessaria nel caso generale per evitare che il risultato dell’intera espressione sia NULL, ad esempio supponiamo che x = 1 ey = NULL. Poi

 (x = y) --> NULL (x IS NULL AND y IS NULL) --> false NULL OR false --> NULL 

La distinzione tra NULL e false è importante nel caso in cui l’espressione di confronto sia negata in un secondo momento nella composizione dell’espressione di query, ad esempio:

 NOT (false) --> true NOT (NULL) --> NULL 

È anche vero che potremmo potenzialmente aggiungere l’intelligenza a EF per capire quando questo termine aggiuntivo non è necessario (ad esempio se sappiamo che l’espressione non è negata nel predicato della query) e per ottimizzarla fuori dalla query.

A proposito, stiamo monitorando questo problema nel seguente bug di EF su codeplex:

[Prestazioni] Ridurre l’albero delle espressioni per query complesse in caso di semantica di confronto nullo C #

Linq-to-SQL genererà la query SQL equivalente per il codice Linq. Ciò significa che filtrerà nello stesso ordine specificato. Non ha davvero un modo per sapere quale sarà più veloce senza eseguirlo per testare.

In ogni caso, il primo filtraggio funzionerà sull’intero set di dati e sarà quindi lento. Però…

  • Se si filtra prima la condizione rara, allora si può ridurre l’intera tabella a un piccolo insieme di risultati. Quindi il tuo secondo filtro ha solo un piccolo set su cui lavorare, il che non richiede molto tempo.
  • Se si filtra prima la condizione comune, l’insieme di dati rimasti in seguito è ancora piuttosto grande. Il secondo filtro opera quindi su un ampio set di dati e quindi richiede un po ‘più di tempo.

Quindi, raro primo significa lento + veloce, mentre il primo comune significa lento + lento. L’unico modo per Linq-to-SQL di ottimizzare questa distinzione per te è prima di fare una query per verificare quale delle due condizioni è più rara, ma ciò significa che l’SQL generato sarebbe diverso ogni volta che lo hai eseguito (e quindi non potrebbe essere memorizzato nella cache per accelerarlo) o sarebbe molto più complesso di quello che hai scritto in Linq (che i progettisti Linq-to-SQL non volevano, probabilmente perché potrebbe rendere il debug un incubo per l’utente).

Non c’è niente che ti impedisca di fare questa ottimizzazione tu stesso; aggiungi una query in anticipo per contare e vedi quale dei due filtri produrrà un set di risultati più piccolo per il secondo filtro su cui lavorare. Per i piccoli database, questo sarà più lento in quasi tutti i casi perché stai facendo un’intera query in più, ma se il tuo database è abbastanza grande e la tua query di controllo è intelligente, potrebbe finire per essere più veloce in media. Inoltre, potrebbe essere ansible calcolare quanti dovrebbero essere presenti nella condizione A perché sia ​​più veloce indipendentemente da quanti oggetti B di condizione si abbiano, quindi conteggiare semplicemente la condizione A, che potrebbe aiutare a rendere più veloce la query di controllo.

Quando si sintonizzano le query SQL, è certamente importante quale ordine si filtrano i risultati. Perché si prevede che Linq-to-SQL non venga mai influenzato dall’ordine di filtraggio?