L'ordre des clauses de requête LINQ doit-il affecter les performances de Entity Framework?

Le cœur de la question n'est pas "pourquoi l'ordre est-il important avec LINQ?". LINQ traduit juste littéralement sans réordonner. La vraie question est "pourquoi les deux requêtes SQL ont-elles des performances différentes?".

J'ai été capable de reproduire le problème en insérant seulement 100k lignes. Dans ce cas, une faiblesse de l'optimiseur est déclenchée: il ne reconnaît pas qu'il peut effectuer une search sur Colour raison de la condition complexe. Dans la première requête, l'optimiseur reconnaît le motif et crée une search d'index.

Il n'y a pas de raison sémantique pour laquelle cela devrait être. Une search sur un index est possible même en cherchant NULL . Ceci est une faiblesse / un bug dans l'optimiseur. Voici les deux plans:

EF essaie d'être utile ici car il suppose que la variable de colonne et la variable de filter peuvent être nulles. Dans ce cas, il essaie de vous donner une correspondance (ce qui selon la sémantique C # est la bonne chose).

J'ai essayé d'annuler cela en ajoutant le filter suivant:

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

Espérant que l'optimiseur utilise maintenant cette connaissance pour simplifier l'expression complexe du filter EF. Il n'a pas réussi à le faire. Si cela avait fonctionné, le même filter aurait pu être ajouté à la requête EF en fournissant une solution facile.

Voici les correctifs que je recommand dans l'ordre où vous devriez les essayer:

1. Rendre les colonnes de la database non-null dans la database
2. Rendre les colonnes non-nulles dans le model de données EF en espérant que cela empêchera EF de créer la condition de filter complexe
3. Créer des index: Colour, Size et / ou Size, Colour . Ils enlèvent également leur problème.
4. Assurez-vous que le filtrage est fait dans le bon ordre et laissez un commentaire de code
5. Essayez d'utiliser INTERSECT / Queryable.Intersect pour combiner les filters. Cela entraîne souvent différentes forms de plan.
6. Créez une fonction de table en ligne qui effectue le filtrage. EF peut utiliser une telle fonction dans le cadre d'une requête plus importante
7. Descendre au SQL brut
8. Utilisez un guide de plan pour modifier le plan

Toutes ces solutions sont des solutions de contournement et non des correctifs de cause racine.

À la fin, je ne suis pas heureux avec SQL Server et EF ici. Les deux produits devraient être fixés. Hélas, ils ne seront probablement pas et vous ne pouvez pas attendre pour cela non plus.

Voici les scripts d'index:

 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] 

Il est difficile de savoir si ce problème est dû à l'exécution d'une bêta EF, mais le code SQL généré pour la requête LINQ simple n'est pas utile du sharepoint vue de l'optimiseur de requêtes SQL Server.

Les constructions SQL courantes pour la gestion des comparaisons d'égalité Nullable ont une prise en charge spécifique de l'optimiseur de requêtes SQL Server. Une (parmi les nombreuses) formulaires de requête SQL compatibles est:

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

Le SQL généré par EF suit plutôt ce model:

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

La clause AND NOT supplémentaire est redondante; Cependant, il est juste assez opaque pour éviter de faire correspondre une simplification cruciale de l'optimiseur de requête. L'ironie est que de nombreuses règles de simplification dans l'optimiseur de requête existent uniquement pour prendre en charge le code généré.

Si EF avait généré du code correspondant au premier formulaire SQL (par exemple):

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

… le plan d'exécution serait une simple intersection d'index, utilisant un seul prédicat d'égalité NULL -aware sur chaque search:

Ce même plan efficace est généré si l'ordre des sets de prédicats principaux dans la clause WHERE est inversé.

Le problème peut être évité dans ce cas en déclarant les colonnes dans la database et / ou le code EF comme NOT NULL (en supposant que datatables ne contiennent pas de NULLs ), mais cela ne change pas le fait que les comparaisons de colonnes nullables doivent être mieux géré.

Si les équipes EF et SQL Server avaient travaillé set à ce sujet, soit EF générerait du code dans une forme plus optimisée pour l'optimiseur, soit un support spécifique pour cette syntaxe aurait été ajouté à l'optimiseur SQL Server. Notez qu'il y aura toujours plus de façons d'écrire la même exigence logique dans SQL qu'un optimiseur de database polyvalent peut (ou devrait) anticiper. D'une part, il n'est tout simplement pas possible d'anticiper toutes les constructions possibles, et d'autre part, vous n'aimeriez pas les time de compilation du plan si c'était le cas.

Il y a une fonctionnalité finale de SQL Server que je devrais mentionner, parce que c'est une honte que les générateurs de code semblent généralement ne pas le soutenir. Lorsque la requête générée est marquée pour la recompilation à chaque exécution à l'aide de l' OPTION (RECOMPILE) requête OPTION (RECOMPILE) , l'optimiseur de requête peut utiliser l'optimization de l'incorporation de parameters pour produire un plan de requête unique pour les valeurs de paramètre spécifiques.

C'est une fonctionnalité puissante, capable de supprimer ou de réécrire complètement des parties inutiles d'une requête avant l'optimization. Par exemple, en ajoutant simplement l'indicateur de requête au SQL généré original:

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

… produit un plan optimal adapté aux valeurs de parameters spécifiques:

Il y a un prix à payer pour la compilation du plan à chaque exécution, mais la qualité du plan amélioré rembourse souvent ce coût plusieurs fois. Lorsqu'un bon plan dépend de façon sensible des valeurs de parameters transmises, cela peut être une technique très efficace.

Note: J'ai rencontré cette question longtime après que d'autres ont déjà fourni des réponses généralement correctes. J'ai décidé de publier cette réponse séparément parce que je pense que la solution de contournement peut être utile, et parce que vous pourriez apprécier d'avoir une meilleure idée de la raison pour laquelle EF se comporte de cette façon.

Réponse courte: La meilleure solution pour ce problème consiste à définir cet indicateur sur votre instance DbContext:

 context.Configuration.UseDatabaseNullSemantics = true; 

Lorsque vous faites cela, tous les controls inutiles supplémentaires disparaîtront et vos requêtes devraient être plus rapides si elles étaient affectées par ce problème.

Réponse longue: d' autres dans ce fil ont raison de dire que dans EF6 nous avons introduit par défaut les termes de vérification supplémentaires pour compenser les différences entre la sémantique des comparaisons nulles dans la database ( logique à trois valeurs ) et les comparaisons nulles standard en memory. Le but de ceci est de satisfaire la request très populaire suivante:

Gestion incorrecte des variables nulles dans la clause 'where'

Paul White a également raison de dire que la partie «AND NOT» dans l'expression suivante est less courante pour compenser la logique à trois valeurs:

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

Cette condition supplémentaire est nécessaire dans le cas général pour éviter que le résultat de l'expression entière soit NULL, par exemple supposer que x = 1 et y = NULL. alors

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

La distinction entre NULL et false est importante dans le cas où l'expression de comparaison est annulée à un point ultérieur dans la composition de l'expression de requête, par exemple:

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

Il est également vrai que nous pourrions potentiellement append l'intelligence à EF pour déterminer quand ce terme supplémentaire est inutile (par exemple, si nous soaps que l'expression n'est pas annulée dans le prédicat de la requête) et pour l'optimiser hors de la requête.

En passant, nous suivons ce problème dans le bug EF suivant au codeplex:

[Performance] Réduit l'tree d'expression pour les requêtes complexes en cas de sémantique de comparaison C # null

Linq-to-SQL génère la requête SQL équivalente pour votre code Linq. Ce que cela signifie, c'est qu'il va filterr dans le même ordre que vous spécifiez. Il n'a pas vraiment de moyen de savoir lequel sera le plus rapide sans l'avoir testé.

Quoi qu'il en soit, votre premier filtrage fonctionnera sur l'set des données et sera donc lent. Toutefois…

• Si vous filterz d'abord la condition rare, elle peut réduire la table entière à un petit nombre de résultats. Ensuite, votre deuxième filtrage n'a qu'un petit set à travailler, ce qui ne prend pas longtime.
• Si vous filterz d'abord la condition commune, alors l'set de données restant après est encore assez grand. Le deuxième filtrage fonctionne donc sur un grand nombre de données et prend donc un peu plus de time.

Ainsi, rare signifie d'abord lent + rapide, tandis que premier commun signifie lent + lent. La seule façon pour Linq-to-SQL d'optimiser cette distinction est de faire d'abord une requête pour vérifier laquelle des deux conditions est la plus rare, mais cela signifie que le SQL généré sera soit différent chaque fois que vous l'exécutez (et donc ne pourrait pas être mis en cache pour l'accélérer) ou serait significativement plus complexe que ce que vous avez écrit dans Linq (que les concepteurs de Linq-to-SQL ne voulaient pas, probablement parce que cela pourrait faire du debugging un cauchemar pour l'user).

Il n'y a rien pour vous empêcher de faire cette optimization vous-même; append une requête à l'avance pour countr et voir lequel des deux filters produira un set de résultats plus petit pour le second filter sur lequel travailler. Pour les petites bases de données, cela sera plus lent dans la plupart des cas parce que vous faites une requête supplémentaire, mais si votre database est assez grande et que votre requête de vérification est intelligente, elle pourrait être plus rapide en moyenne. En outre, il peut être possible de déterminer le nombre de conditions de la condition A pour qu'il soit plus rapide, quel que soit le nombre d'objects de condition B, puis de simplement countr la condition A, ce qui facilitera la vérification de la requête.

Lors de l'optimization des requêtes SQL, il est important de savoir dans quel ordre vous filterz vos résultats. Pourquoi voulez-vous que Linq-to-SQL ne soit jamais affecté par l'ordre de filtrage?