Pourquoi l'opérateur Contains () dégrade-t-il les performances d'Entity Framework?

Question

Mise à JOUR 3: Selon cette annonce, cette question a été traitée par l'équipe EF dans EF6 alpha 2.

Mise à JOUR 2: j'ai créé une suggestion pour résoudre ce problème. Pour voter, rendez-vous ici.

Considérons une base de données SQL avec une table très très simple.

CREATE TABLE Main (Id INT PRIMARY KEY)

- Je remplir la table avec 10 000 enregistrements.

WITH Numbers AS
(
  SELECT 1 AS Id
  UNION ALL
  SELECT Id + 1 AS Id FROM Numbers WHERE Id <= 10000
)
INSERT Main (Id)
SELECT Id FROM Numbers
OPTION (MAXRECURSION 0)

- Je construire un modèle EF de la table et exécutez la requête suivante dans LINQPad (je suis à l'aide de "C# Déclarations" mode LINQPad ne pas créer un dump automatiquement).

var rows = 
  Main
  .ToArray();

Le temps d'exécution est d'environ 0,07 seconde. Maintenant, j'ajoute l'opérateur Contient et de ré-exécuter la requête.

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  Main
  .Where (a => ids.Contains(a.Id))
  .ToArray();

Le temps d'exécution pour ce cas est 20.14 secondes (288 fois plus lent)!

Au début, je soupçonne que le T-SQL émis pour la requête a été plus long à exécuter, donc j'ai essayé de les couper et coller à partir LINQPad du volet SQL dans SQL Server Management Studio.

SET NOCOUNT ON
SET STATISTICS TIME ON
SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Primary] AS [Extent1]
WHERE [Extent1].[Id] IN (1,2,3,4,5,6,7,8,...

Et le résultat a été

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 88 ms.

Ensuite, j'ai soupçonné LINQPad a été à l'origine du problème, mais la performance est la même, que je le lance dans LINQPad ou dans une application console.

Donc, il semble que le problème est quelque part à l'intérieur de l'Entity Framework.

Suis-je en train de faire quelque chose de mal ici? C'est une partie essentielle de mon code, donc, il y a quelque chose que je peux faire pour accélérer les performances?

Je suis à l'aide de Entity Framework 4.1 et Sql Server 2008 R2.

Mise à JOUR 1:

Dans la discussion ci-dessous, il y avait quelques questions au sujet de si le retard s'est produit alors que les EF de la construction de la requête initiale ou alors qu'il était de l'analyse des données, il a reçu en retour. Pour tester cela, j'ai couru le code suivant

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  (ObjectQuery<MainRow>)
  Main
  .Where (a => ids.Contains(a.Id));
var sql = rows.ToTraceString();

qui des forces EF pour générer la requête sans l'exécuter sur la base de données. Le résultat est que ce code ~20 secords à courir, de sorte qu'il semble que presque tous les temps est pris dans la construction de la requête initiale.

CompiledQuery à la rescousse? Pas si vite ... CompiledQuery exige que les paramètres passés à la requête fondamentaux types (int, string, float, et ainsi de suite). Il n'accepte pas les groupes ou les IEnumerable, donc je ne peux pas l'utiliser pour une liste d'Id.

Answer 1

Mise à JOUR: EF6 comprend amélioration spectaculaire de la performance pour Énumérable.Contient.

Vous avez raison que la plupart du temps est consacré à la transformation de la traduction de la requête. EF du modèle de fournisseur n'est pas actuellement une expression qui représente une clause, donc ADO.NET les fournisseurs peuvent pas en charge EN mode natif. Au lieu de cela, la mise en œuvre de Énumérable.Contient traduit de l'arbre de la OU des expressions, c'est à dire de quelque chose qu'en C# ressemble comme ceci:

new []{1, 2, 3, 4}.Contains(i)

... nous allons générer un DbExpression arbre qui pourrait être représenté comme ceci:

((1 = @i) OR (2 = @i)) OR ((3 = @i) OR (4 = @i))

(L'expression arbres doivent être en équilibre, parce que si nous avions toutes les Rup sur un seul long de la colonne vertébrale, il y aurait plus de chances que l'expression visiteur aurait frappé un débordement de pile (oui, nous avons fait frappé que dans nos tests))

Nous les envoyer plus tard un arbre de ce genre pour les ADO.NET fournisseur, qui peut avoir la capacité de reconnaître ce modèle et de le réduire à la clause lors de la génération de SQL.

Lorsque nous avons ajouté le support pour Énumérable.Contient en EF4, nous avons pensé qu'il était souhaitable de le faire, sans avoir à introduire la prise EN charge des expressions dans le modèle de fournisseur, et honnêtement, 10 000, c'est beaucoup plus que le nombre d'éléments que nous avions anticipé que les clients passent à Énumérable.Contient. Cela dit, je comprends que c'est une gêne et que la manipulation des expressions arbres rend les choses trop cher dans votre scénario.

J'ai discuté avec l'un de nos développeurs et nous pensons que dans le futur, nous pourrions changer la mise en œuvre par l'ajout d'un soutien de première catégorie EN de. Je vais m'assurer de ce qui est ajouté à notre carnet de commandes, mais je ne peux pas promettre quand il sera donné qu'il n'existe de nombreuses autres améliorations, nous voudrions faire.

Pour les solutions de contournement déjà suggéré dans le thread, je voudrais ajouter le texte suivant:

Envisager la création d'une méthode qui permet d'équilibrer le nombre de base de données d'allers-retours avec le nombre d'éléments que vous passez à Contient. Par exemple, dans mes tests, j'ai constaté que le calcul et l'exécution à l'encontre d'une instance locale de SQL Server la requête avec 100 éléments prend 1/60 de seconde. Si vous pouvez écrire votre requête d'une manière telle que l'exécution de 100 requêtes avec plus de 100 différents ensembles de id voudrais vous donner un résultat équivalent à la requête avec 10 000 éléments, alors vous pouvez obtenir les résultats dans environ 1.67 secondes au lieu de 18 secondes.

Différentes tailles de segment devrait mieux fonctionner selon la requête et la latence de la connexion de base de données. Pour certaines requêtes, c'est à dire si la séquence transmise a des doublons ou si Énumérable.Contient est utilisée dans une étude condition que vous pouvez obtenir les éléments en double dans les résultats.

Voici un extrait de code (désolé si le code utilisé pour la tranche de l'entrée en morceaux ressemble un peu trop complexe. Il y a des moyens plus simples pour obtenir la même chose, mais j'essayais de trouver un modèle qui préserve streaming pour la séquence et je ne pouvais pas trouver quelque chose de semblable dans LINQ, donc j'ai sans doute exagéré que de la partie :) ):

Utilisation:

var list = context.GetMainItems(ids).ToList();

Méthode pour le contexte ou le référentiel:

public partial class ContainsTestEntities
{
    public IEnumerable<Main> GetMainItems(IEnumerable<int> ids, int chunkSize = 100)
    {
        foreach (var chunk in ids.Chunk(chunkSize))
        {
            var q = this.MainItems.Where(a => chunk.Contains(a.Id));
            foreach (var item in q)
            {
                yield return item;
            }
        }
    }
}

Les méthodes d'Extension pour le tranchage énumérable séquences:

public static class EnumerableSlicing
{

    private class Status
    {
        public bool EndOfSequence;
    }

    private static IEnumerable<T> TakeOnEnumerator<T>(IEnumerator<T> enumerator, int count, 
        Status status)
    {
        while (--count > 0 && (enumerator.MoveNext() || !(status.EndOfSequence = true)))
        {
            yield return enumerator.Current;
        }
    }

    public static IEnumerable<IEnumerable<T>> Chunk<T>(this IEnumerable<T> items, int chunkSize)
    {
        if (chunkSize < 1)
        {
            throw new ArgumentException("Chunks should not be smaller than 1 element");
        }
        var status = new Status { EndOfSequence = false };
        using (var enumerator = items.GetEnumerator())
        {
            while (!status.EndOfSequence)
            {
                yield return TakeOnEnumerator(enumerator, chunkSize, status);
            }
        }
    }
}

Espérons que cette aide!

Answer 2

Si vous trouvez un problème de performance qui est bloquant pour vous, n'essayez pas de passer des plombes à le résoudre, car vous aurez très probablement de ne pas réussir et que vous aurez à communiquer avec MME directement (si vous avez de la prime de soutien) et ça prend une éternité.

Utiliser la solution de contournement et une solution de contournement en cas de problème de performance et EF signifie SQL directe. Il n'y a rien de mal à ce sujet. Global de l'idée que l'utilisation de EF = pas de l'aide de SQL, c'est un mensonge. Vous disposez de SQL Server 2008 R2 donc:

• Créer une procédure stockée en acceptant de table d'une valeur de paramètre à passer vos id
• Laissez votre retour d'une procédure stockée plusieurs ensembles de résultats pour émuler Include logique dans la façon optimale
• Si vous avez besoin d'une requête complexe et bâtiment de l'utilisation de dynamic SQL à l'intérieur d'une procédure stockée
• Utiliser SqlDataReader pour obtenir des résultats et la réalisation de vos entités
• Les attacher au contexte et à travailler avec eux comme s'ils étaient chargés de EF

Si la performance est critique pour vous, vous ne trouverez pas de meilleure solution. Cette procédure ne peut pas être mappé et exécuté par EF parce que la version actuelle ne prend pas en charge soit la table de paramètres ou de plusieurs ensembles de résultats.

Answer 3

Nous avons été en mesure de résoudre le EF Contient problème en ajoutant une table intermédiaire et de le rejoindre sur la table de requête LINQ nécessaires à l'utilisation de la clause contains. Nous avons été en mesure d'obtenir des résultats étonnants avec cette approche. Nous avons un grand modèle EF et que "Contient" n'est pas autorisé lors de la pré-compilation EF requêtes nous avons été faire de très mauvaises performances pour les requêtes qui utilisent des "Contient une clause".

Un aperçu:

• Créer une table dans SQL Server, par exemple HelperForContainsOfIntType avec HelperID de Guid -type de données et d' ReferenceID de int -type de données des colonnes. Créer des tables différentes avec ReferenceID de différents types de données que nécessaire.

• Créer une Entité / EntitySet pour HelperForContainsOfIntType , et d'autres tables dans le modèle EF. Créer différents Entité / EntitySet pour les différents types de données que nécessaire.

• Créer une méthode d'assistance .NET code qui prend l'entrée de l' IEnumerable<int> et renvoie un Guid. Cette méthode génère un nouveau Guid et insère les valeurs de IEnumerable<int> en HelperForContainsOfIntType avec le générés Guid. Ensuite, la méthode renvoie cette nouvellement générés Guid pour l'appelant. Pour rapide de l'insertion en HelperForContainsOfIntType tableau, créer une procédure stockée qui prend en entrée une liste de valeurs et de l'insertion. Voir les Paramètres de la Table dans SQL Server 2008 (ADO.NET). Créer différentes aides pour les différents types de données ou de créer un générique de la méthode d'assistance pour gérer différents types de données.

• Créer un EF compilé requête qui est semblable à quelque chose comme ci-dessous:

  static Func<MyEntities, Guid, IEnumerable<Customer>> _selectCustomers =
      CompiledQuery.Compile(
          (MyEntities db, Guid containsHelperID) =>
              from cust in db.Customers
              join x in db.HelperForContainsOfIntType on cust.CustomerID equals x.ReferenceID where x.HelperID == containsHelperID
              select cust 
      );
  

• Appel de la méthode d'assistance avec les valeurs à utiliser dans l' Contains clause et obtenir l' Guid à utiliser dans la requête. Par exemple:

  var containsHelperID = dbHelper.InsertIntoHelperForContainsOfIntType(new int[] { 1, 2, 3 });
  var result = _selectCustomers(_dbContext, containsHelperID).ToList();
  

Answer 4

L'édition ma réponse originale à cette question - Là est une solution de contournement possible, selon la complexité de vos entités. Si vous connaissez le code sql généré par EF pour remplir vos entités, vous pouvez l'exécuter directement à l'aide de DbContext.La base de données.SqlQuery. En EF 4, je pense que vous pouvez utiliser ObjectContext.ExecuteStoreQuery, mais je ne l'ai pas essayé.

Par exemple, en utilisant le code de ma réponse originale à cette question ci-dessous pour générer l'instruction sql à l'aide d'un StringBuilder, j'ai été en mesure de faire ce qui suit

var rows = db.Database.SqlQuery<Main>(sql).ToArray();

et le temps total est passée d'environ 26 secondes, 0,5 secondes.

Je serai le premier à dire que c'est moche, et je l'espère une meilleure solution se présente à lui.

mise à jour

Après un peu plus de réflexion, j'ai réalisé que si vous utilisez une jointure de filtrer vos résultats, EF n'a pas à construire cette longue liste d'id. Cela pourrait être complexe selon le nombre de requêtes simultanées, mais je crois que vous pourriez utiliser les id d'utilisateur ou id de session afin de les isoler.

Pour tester cela, j'ai créé un Target tableau avec le même schéma que Main. J'ai ensuite utilisé un StringBuilder créer INSERT commandes pour remplir l' Target table en lots de 1 000 depuis c'est le plus SQL Serveur accepte dans un seul INSERT. Directement de l'exécution des instructions sql a été beaucoup plus rapide que de passer par EF (environ 0,3 secondes, contre 2,5 secondes), et je crois que ce serait ok depuis le schéma de la table ne devrait pas changer.

Enfin, la sélection à l'aide d'un join a entraîné une beaucoup plus simple requête et exécuté en moins de 0,5 secondes.

ExecuteStoreCommand("DELETE Target");

var ids = Main.Select(a => a.Id).ToArray();
var sb = new StringBuilder();

for (int i = 0; i < 10; i++)
{
    sb.Append("INSERT INTO Target(Id) VALUES (");
    for (int j = 1; j <= 1000; j++)
    {
        if (j > 1)
        {
            sb.Append(",(");
        }
        sb.Append(i * 1000 + j);
        sb.Append(")");
    }
    ExecuteStoreCommand(sb.ToString());
    sb.Clear();
}

var rows = (from m in Main
            join t in Target on m.Id equals t.Id
            select m).ToArray();

rows.Length.Dump();

Et le sql généré par EF pour la rejoindre:

SELECT 
[Extent1].[Id] AS [Id]
FROM  [dbo].[Main] AS [Extent1]
INNER JOIN [dbo].[Target] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

---

(réponse originale à cette question)

Ce n'est pas une réponse, mais je voulais partager quelques informations supplémentaires et il est beaucoup trop long pour tenir dans un commentaire. J'étais capable de reproduire vos résultats, et ont un peu d'autres choses à ajouter:

Le générateur de profils SQL indique le délai entre l'exécution de la première requête (Main.Select) et le second Main.Where de la requête, donc je soupçonne que le problème était dans la génération et l'envoi d'une requête de cette taille (48,980 octets).

Cependant, la construction de la même instruction sql en T-SQL de manière dynamique prend moins de 1 seconde, et en prenant l' ids de votre Main.Select déclaration, la construction de la même instruction sql et l'exécution à l'aide d'un SqlCommand a pris 0.112 secondes, et c'est notamment le temps d'écrire le contenu de la console.

À ce stade, je pense que EF est en train de faire une analyse/traitement pour chacun des 10 000 ids , car il s'inspire de la requête. Souhaite que je pourrais donner une réponse définitive et solution :(.

Voici le code que j'ai essayé dans SSMS et LINQPad (merci de ne pas critiquer trop sévèrement, je suis pressé d'essayer de quitter le travail):

declare @sql nvarchar(max)

set @sql = 'SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Main] AS [Extent1]
WHERE [Extent1].[Id] IN ('

declare @count int = 0
while @count < 10000
begin
    if @count > 0 set @sql = @sql + ','
    set @count = @count + 1
    set @sql = @sql + cast(@count as nvarchar)
end
set @sql = @sql + ')'

exec(@sql)

---

var ids = Mains.Select(a => a.Id).ToArray();

var sb = new StringBuilder();
sb.Append("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN (");
for(int i = 0; i < ids.Length; i++)
{
    if (i > 0) 
        sb.Append(",");     
    sb.Append(ids[i].ToString());
}
sb.Append(")");

using (SqlConnection connection = new SqlConnection("server = localhost;database = Test;integrated security = true"))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = sb.ToString();
    connection.Open();
    using(SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0));
        }
    }
}

Answer 5

Je ne connais pas Entity Framework, mais la performance est-elle meilleure si vous procédez comme suit?

Au lieu de cela:

 var ids = Main.Select(a => a.Id).ToArray();
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();
 

que diriez-vous de cela (en supposant que l'ID est un int):

 var ids = new HashSet<int>(Main.Select(a => a.Id));
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();