Les performances de la requête Entity Framework diffèrent extrêmement avec l'exécution SQL brute

J'ai une question sur les performances d'exécution des requêtes Entity Framework.

Schéma :

J'ai une structure de table comme celle-ci:

CREATE TABLE [dbo].[DataLogger] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [ProjectID] [bigint] NULL, CONSTRAINT [PrimaryKey1] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) CREATE TABLE [dbo].[DCDissortingbutionBox] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [DataLoggerID] [bigint] NOT NULL, CONSTRAINT [PrimaryKey2] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) ALTER TABLE [dbo].[DCDissortingbutionBox] ADD CONSTRAINT [FK_DCDissortingbutionBox_DataLogger] FOREIGN KEY([DataLoggerID]) REFERENCES [dbo].[DataLogger] ([ID]) CREATE TABLE [dbo].[DCSsortingng] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [DCDissortingbutionBoxID] [bigint] NOT NULL, [CurrentMPP] [decimal](18, 2) NULL, CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) ALTER TABLE [dbo].[DCSsortingng] ADD CONSTRAINT [FK_DCSsortingng_DCDissortingbutionBox] FOREIGN KEY([DCDissortingbutionBoxID]) REFERENCES [dbo].[DCDissortingbutionBox] ([ID]) CREATE TABLE [dbo].[SsortingngData] ( [DCSsortingngID] [bigint] NOT NULL, [TimeStamp] [datetime] NOT NULL, [DCCurrent] [decimal](18, 2) NULL, CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCSsortingngID] ASC) ) CREATE NONCLUSTERED INDEX [TimeStamp_DCCurrent-NonClusteredIndex] ON [dbo].[SsortingngData] ([DCSsortingngID] ASC, [TimeStamp] ASC) INCLUDE ([DCCurrent]) 

Les index standard sur les foreign keys existent également (je ne veux pas les listr tous pour des raisons d'espace).

La table [SsortingngData] possède les statistics de stockage suivantes:

  • Espace de données: 26,901.86 MB
  • Nombre de lignes: 131 827 749
  • Partitionné: true
  • Nombre de partitions: 62

Utilisation :

Je veux maintenant regrouper datatables dans la table [SsortingngData] et faire une certaine agrégation.

J'ai créé une requête Entity Framework (des informations détaillées sur la requête peuvent être trouvées ici ):

 var compareData = model.SsortingngDatas .AsNoTracking() .Where(p => p.DCSsortingng.DCDissortingbutionBox.DataLogger.ProjectID == projectID && p.TimeStamp >= fromDate && p.TimeStamp < tillDate) .Select(d => new { TimeStamp = d.TimeStamp, DCCurrentMpp = d.DCCurrent / d.DCSsortingng.CurrentMPP }) .GroupBy(d => DbFunctions.AddMinutes(DateTime.MinValue, DbFunctions.DiffMinutes(DateTime.MinValue, d.TimeStamp) / minuteInterval * minuteInterval)) .Select(d => new { TimeStamp = d.Key, DCCurrentMppMin = d.Min(v => v.DCCurrentMpp), DCCurrentMppMax = d.Max(v => v.DCCurrentMpp), DCCurrentMppAvg = d.Average(v => v.DCCurrentMpp), DCCurrentMppStDev = DbFunctions.StandardDeviationP(d.Select(v => v.DCCurrentMpp)) }) .ToList(); 

La durée d'exécution est exceptionnelle!?

  • Résultat d'exécution: 92rows
  • Temps d'exécution: ~ 16000ms

Tentatives :

J'ai maintenant jeté un oeil dans la requête SQL générée Entity Framework et ressemble à ceci:

 DECLARE @p__linq__4 DATETIME = 0; DECLARE @p__linq__3 DATETIME = 0; DECLARE @p__linq__5 INT = 15; DECLARE @p__linq__6 INT = 15; DECLARE @p__linq__0 BIGINT = 20827; DECLARE @p__linq__1 DATETIME = '06.02.2016 00:00:00'; DECLARE @p__linq__2 DATETIME = '07.02.2016 00:00:00'; SELECT 1 AS [C1], [GroupBy1].[K1] AS [C2], [GroupBy1].[A1] AS [C3], [GroupBy1].[A2] AS [C4], [GroupBy1].[A3] AS [C5], [GroupBy1].[A4] AS [C6] FROM ( SELECT [Project1].[K1] AS [K1], MIN([Project1].[A1]) AS [A1], MAX([Project1].[A2]) AS [A2], AVG([Project1].[A3]) AS [A3], STDEVP([Project1].[A4]) AS [A4] FROM ( SELECT DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Project1].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) AS [K1], [Project1].[C1] AS [A1], [Project1].[C1] AS [A2], [Project1].[C1] AS [A3], [Project1].[C1] AS [A4] FROM ( SELECT [Extent1].[TimeStamp] AS [TimeStamp], [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1] FROM [dbo].[SsortingngData] AS [Extent1] INNER JOIN [dbo].[DCSsortingng] AS [Extent2] ON [Extent1].[DCSsortingngID] = [Extent2].[ID] INNER JOIN [dbo].[DCDissortingbutionBox] AS [Extent3] ON [Extent2].[DCDissortingbutionBoxID] = [Extent3].[ID] INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID] WHERE (([Extent4].[ProjectID] = @p__linq__0) OR (([Extent4].[ProjectID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2) ) AS [Project1] ) AS [Project1] GROUP BY [K1] ) AS [GroupBy1] 

J'ai copié cette requête SQL dans SSMS sur la même machine, connectée avec la même string de connection que Entity Framework.

Le résultat est une performance très améliorée:

  • Résultat d'exécution: 92rows
  • Temps d'exécution: 517ms

Je fais aussi un test de run loop et le résultat est étrange. Le test ressemble à ceci

 for (int i = 0; i < 50; i++) { DateTime begin = DateTime.UtcNow; [...query...] TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin; Debug.WriteLine("{0}th run: {1}", i, excecutionTimeSpan.ToSsortingng()); } 

Le résultat est très différent et semble random (?):

 0th run: 00:00:11.0618580 1th run: 00:00:11.3339467 2th run: 00:00:10.0000676 3th run: 00:00:10.1508140 4th run: 00:00:09.2041939 5th run: 00:00:07.6710321 6th run: 00:00:10.3386312 7th run: 00:00:17.3422765 8th run: 00:00:13.8620557 9th run: 00:00:14.9041528 10th run: 00:00:12.7772906 11th run: 00:00:17.0170235 12th run: 00:00:14.7773750 

Question :

Pourquoi l'exécution des requêtes Entity Framework est-elle si lente? Le nombre de lignes résultant est vraiment faible et la requête SQL brute affiche une performance très rapide.

Mise à jour 1 :

Je veille à ce que ce ne soit pas un retard de création MetaContext ou Model. Certaines autres requêtes sont exécutées sur la même instance Model juste avant avec de bonnes performances.

Mise à jour 2 (liée à la réponse de @ x0007me):

Merci pour l'indice, mais cela peut être éliminé en changeant les parameters du model comme ceci:

 modelContext.Configuration.UseDatabaseNullSemantics = true; 

L'EF généré SQL est maintenant:

 SELECT 1 AS [C1], [GroupBy1].[K1] AS [C2], [GroupBy1].[A1] AS [C3], [GroupBy1].[A2] AS [C4], [GroupBy1].[A3] AS [C5], [GroupBy1].[A4] AS [C6] FROM ( SELECT [Project1].[K1] AS [K1], MIN([Project1].[A1]) AS [A1], MAX([Project1].[A2]) AS [A2], AVG([Project1].[A3]) AS [A3], STDEVP([Project1].[A4]) AS [A4] FROM ( SELECT DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Project1].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) AS [K1], [Project1].[C1] AS [A1], [Project1].[C1] AS [A2], [Project1].[C1] AS [A3], [Project1].[C1] AS [A4] FROM ( SELECT [Extent1].[TimeStamp] AS [TimeStamp], [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1] FROM [dbo].[SsortingngData] AS [Extent1] INNER JOIN [dbo].[DCSsortingng] AS [Extent2] ON [Extent1].[DCSsortingngID] = [Extent2].[ID] INNER JOIN [dbo].[DCDissortingbutionBox] AS [Extent3] ON [Extent2].[DCDissortingbutionBoxID] = [Extent3].[ID] INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID] WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2) ) AS [Project1] ) AS [Project1] GROUP BY [K1] ) AS [GroupBy1] 

Vous pouvez donc voir que le problème que vous avez décrit est maintenant résolu, mais le time d'exécution ne change pas.

De plus, comme vous pouvez le voir dans le schéma et le time d'exécution brut, j'ai utilisé une structure optimisée avec un indexeur hautement optimisé.

Mise à jour 3 (liée à la réponse de Vladimir Baranov):

Je ne vois pas pourquoi cela peut être lié à la caching du plan de requête. Parce que dans le MSDN est clairement décrit que l'EF6 utilise la caching de plan de requête.

Une preuve de test simple que l'énorme différence de time d'exécution n'est pas liée à la caching du plan de requête (code phseudo):

 using(var modelContext = new ModelContext()) { modelContext.Query(); //1th run activates caching modelContext.Query(); //2th used cached plan } 

Par conséquent, les deux requêtes s'exécutent avec le même time d'exécution.

Mise à jour 4 (liée à la réponse de @bubi):

J'ai essayé d'exécuter la requête qui est générée par l'EF comme vous l'avez décrit:

 int result = model.Database.ExecuteSqlCommand(@"SELECT 1 AS [C1], [GroupBy1].[K1] AS [C2], [GroupBy1].[A1] AS [C3], [GroupBy1].[A2] AS [C4], [GroupBy1].[A3] AS [C5], [GroupBy1].[A4] AS [C6] FROM ( SELECT [Project1].[K1] AS [K1], MIN([Project1].[A1]) AS [A1], MAX([Project1].[A2]) AS [A2], AVG([Project1].[A3]) AS [A3], STDEVP([Project1].[A4]) AS [A4] FROM ( SELECT DATEADD (minute, ((DATEDIFF (minute, 0, [Project1].[TimeStamp])) / @p__linq__5) * @p__linq__6, 0) AS [K1], [Project1].[C1] AS [A1], [Project1].[C1] AS [A2], [Project1].[C1] AS [A3], [Project1].[C1] AS [A4] FROM ( SELECT [Extent1].[TimeStamp] AS [TimeStamp], [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1] FROM [dbo].[SsortingngData] AS [Extent1] INNER JOIN [dbo].[DCSsortingng] AS [Extent2] ON [Extent1].[DCSsortingngID] = [Extent2].[ID] INNER JOIN [dbo].[DCDissortingbutionBox] AS [Extent3] ON [Extent2].[DCDissortingbutionBoxID] = [Extent3].[ID] INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID] WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2) ) AS [Project1] ) AS [Project1] GROUP BY [K1] ) AS [GroupBy1]", new SqlParameter("p__linq__0", 20827), new SqlParameter("p__linq__1", fromDate), new SqlParameter("p__linq__2", tillDate), new SqlParameter("p__linq__5", 15), new SqlParameter("p__linq__6", 15)); 
  • Résultat d'exécution: 92
  • Temps d'exécution: ~ 16000ms

Cela a duré aussi longtime que la requête EF normale!

Mise à jour 5 (liée à la réponse de @vittore):

Je crée un tree d'appel tracé, peut-être cela aide:

appel trace de l'arbre

Mise à jour 6 (liée à la réponse de @usr):

J'ai créé deux showplan XML via SQL Server Profiler.

Exécution rapide (SSMS) .SQLPlan

Slow run (EF) .SQLPlan

Mise à jour 7 (liée aux commentaires de @VladimirBaranov):

Je cours maintenant un autre cas de test lié à vos commentaires.

Premièrement, j'élimine le time en prenant des opérations de command en utilisant une nouvelle colonne calculée et un INDEXER correspondant. Cela réduit le retard de performance lié à DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [TimeStamp] ) / 15* 15, 0) . Détail pour comment et pourquoi vous pouvez find ici .

Le résultat ressemble à ceci:

Requête Pure EntityFramework:

 for (int i = 0; i < 3; i++) { DateTime begin = DateTime.UtcNow; var result = model.SsortingngDatas .AsNoTracking() .Where(p => p.DCSsortingng.DCDissortingbutionBox.DataLogger.ProjectID == projectID && p.TimeStamp15Minutes >= fromDate && p.TimeStamp15Minutes < tillDate) .Select(d => new { TimeStamp = d.TimeStamp15Minutes, DCCurrentMpp = d.DCCurrent / d.DCSsortingng.CurrentMPP }) .GroupBy(d => d.TimeStamp) .Select(d => new { TimeStamp = d.Key, DCCurrentMppMin = d.Min(v => v.DCCurrentMpp), DCCurrentMppMax = d.Max(v => v.DCCurrentMpp), DCCurrentMppAvg = d.Average(v => v.DCCurrentMpp), DCCurrentMppStDev = DbFunctions.StandardDeviationP(d.Select(v => v.DCCurrentMpp)) }) .ToList(); TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin; Debug.WriteLine("{0}th run pure EF: {1}", i, excecutionTimeSpan.ToSsortingng()); } 

0ème course EF pur: 00: 00: 12.6460624

1ère course EF pur: 00: 00: 11.0258393

2ème course pure EF: 00: 00: 08.4171044

J'ai maintenant utilisé le SQL généré par EF en tant que requête SQL:

 for (int i = 0; i < 3; i++) { DateTime begin = DateTime.UtcNow; int result = model.Database.ExecuteSqlCommand(@"SELECT 1 AS [C1], [GroupBy1].[K1] AS [TimeStamp15Minutes], [GroupBy1].[A1] AS [C2], [GroupBy1].[A2] AS [C3], [GroupBy1].[A3] AS [C4], [GroupBy1].[A4] AS [C5] FROM ( SELECT [Project1].[TimeStamp15Minutes] AS [K1], MIN([Project1].[C1]) AS [A1], MAX([Project1].[C1]) AS [A2], AVG([Project1].[C1]) AS [A3], STDEVP([Project1].[C1]) AS [A4] FROM ( SELECT [Extent1].[TimeStamp15Minutes] AS [TimeStamp15Minutes], [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1] FROM [dbo].[SsortingngData] AS [Extent1] INNER JOIN [dbo].[DCSsortingng] AS [Extent2] ON [Extent1].[DCSsortingngID] = [Extent2].[ID] INNER JOIN [dbo].[DCDissortingbutionBox] AS [Extent3] ON [Extent2].[DCDissortingbutionBoxID] = [Extent3].[ID] INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID] WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp15Minutes] >= @p__linq__1) AND ([Extent1].[TimeStamp15Minutes] < @p__linq__2) ) AS [Project1] GROUP BY [Project1].[TimeStamp15Minutes] ) AS [GroupBy1];", new SqlParameter("p__linq__0", 20827), new SqlParameter("p__linq__1", fromDate), new SqlParameter("p__linq__2", tillDate)); TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin; Debug.WriteLine("{0}th run: {1}", i, excecutionTimeSpan.ToSsortingng()); } 

0ème passage: 00: 00: 00.8381200

1ère course: 00: 00: 00.6920736

2ème course: 00: 00: 00.7081006

et avec OPTION(RECOMPILE) :

 for (int i = 0; i < 3; i++) { DateTime begin = DateTime.UtcNow; int result = model.Database.ExecuteSqlCommand(@"SELECT 1 AS [C1], [GroupBy1].[K1] AS [TimeStamp15Minutes], [GroupBy1].[A1] AS [C2], [GroupBy1].[A2] AS [C3], [GroupBy1].[A3] AS [C4], [GroupBy1].[A4] AS [C5] FROM ( SELECT [Project1].[TimeStamp15Minutes] AS [K1], MIN([Project1].[C1]) AS [A1], MAX([Project1].[C1]) AS [A2], AVG([Project1].[C1]) AS [A3], STDEVP([Project1].[C1]) AS [A4] FROM ( SELECT [Extent1].[TimeStamp15Minutes] AS [TimeStamp15Minutes], [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1] FROM [dbo].[SsortingngData] AS [Extent1] INNER JOIN [dbo].[DCSsortingng] AS [Extent2] ON [Extent1].[DCSsortingngID] = [Extent2].[ID] INNER JOIN [dbo].[DCDissortingbutionBox] AS [Extent3] ON [Extent2].[DCDissortingbutionBoxID] = [Extent3].[ID] INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID] WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp15Minutes] >= @p__linq__1) AND ([Extent1].[TimeStamp15Minutes] < @p__linq__2) ) AS [Project1] GROUP BY [Project1].[TimeStamp15Minutes] ) AS [GroupBy1] OPTION(RECOMPILE);", new SqlParameter("p__linq__0", 20827), new SqlParameter("p__linq__1", fromDate), new SqlParameter("p__linq__2", tillDate)); TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin; Debug.WriteLine("{0}th run: {1}", i, excecutionTimeSpan.ToSsortingng()); } 

0ème passage avec RECOMPILE: 00: 00: 00.8260932

1ère course avec RECOMPILE: 00: 00: 00.9139730

2ème manche avec RECOMPILE: 00: 00: 01.0680665

Même requête SQL exécutée dans SSMS (sans RECOMPILE):

00: 00: 01.105

Même requête SQL exécutée dans SSMS (avec RECOMPILE):

00: 00: 00.902

J'espère que ce sont toutes les valeurs dont vous avez besoin.

Dans cette réponse, je me concentre sur l'observation originale: la requête générée par EF est lente, mais lorsque la même requête est exécutée dans SSMS, elle est rapide.

Une explication possible de ce comportement est le reniflage des parameters .

SQL Server utilise un process appelé reniflage de parameters lorsqu'il exécute des procédures stockées avec des parameters. Lorsque la procédure est compilée ou recompilée, la valeur transmise au paramètre est évaluée et utilisée pour créer un plan d'exécution. Cette valeur est ensuite stockée avec le plan d'exécution dans le cache du plan. Lors des exécutions suivantes, cette même valeur – et même plan – est utilisée.

Ainsi, EF génère une requête qui a peu de parameters. La première fois que vous exécutez cette requête, le server crée un plan d'exécution pour cette requête en utilisant les valeurs des parameters qui étaient en vigueur lors de la première exécution. Ce plan est généralement plutôt bon. Mais, plus tard, vous exécutez la même requête EF en utilisant d'autres valeurs pour les parameters. Il est possible que pour de nouvelles valeurs de parameters, le plan généré précédemment ne soit pas optimal et la requête soit lente. Le server continue d'utiliser le plan précédent, car il s'agit toujours de la même requête, seules les valeurs des parameters sont différentes.

Si à ce moment vous prenez le text de la requête et que vous essayez de l'exécuter directement dans le SSMS, le server créera un nouveau plan d'exécution, car techniquement, ce n'est pas la même requête qui est émise par l'application EF. Même une différence de caractère suffit, toute modification des parameters de session est également suffisante pour que le server traite la requête comme une nouvelle. En conséquence, le server a deux plans pour la même requête dans son cache. Le premier plan "lent" est lent pour les nouvelles valeurs de parameters, car il a été construit à l'origine pour différentes valeurs de parameters. Le deuxième plan "rapide" est construit pour les valeurs de parameters actuelles, il est donc rapide.

L'article Slow in the Application, Rapide dans SSMS par Erland Sommarskog explique ceci et d'autres domaines connexes dans beaucoup plus de détails.

Il existe plusieurs façons de supprimer les plans mis en cache et de forcer le server à les régénérer. Changer la table ou changer les index de table devrait le faire – il devrait rejeter tous les plans qui sont liés à cette table, à la fois "lent" et "rapide". Ensuite, vous exécutez la requête dans l'application EF avec de nouvelles valeurs de parameters et obtenez un nouveau plan "rapide". Vous exécutez la requête dans SSMS et obtenez un deuxième plan "rapide" avec de nouvelles valeurs de parameters. Le server génère toujours deux plans, mais les deux plans sont rapides maintenant.

Une autre variante consiste à append OPTION(RECOMPILE) à la requête. Avec cette option, le server ne stockera pas le plan généré dans son cache. Ainsi, chaque fois que la requête s'exécute, le server utilise des valeurs de parameters réelles pour générer le plan qui (pense-t-il) serait optimal pour les valeurs de paramètre données. L'inconvénient est un surcoût de la génération de plan.

Rappelez-vous, le server pourrait encore choisir un «mauvais» plan avec cette option en raison de statistics obsolètes, par exemple. Mais, au less, le reniflage des parameters ne serait pas un problème.

Je sais que je suis un peu en retard ici, mais depuis que j'ai participé à la construction de la question en question, je me sens obligé de prendre des mesures.

Le problème général que je vois avec les requêtes Linq to Entities est que la façon typique de les build introduit des parameters inutiles, qui peuvent affecter le plan de requête de la database en cache (ce qu'on appelle le problème de sniffing des parameters Sql Server ).

Laissez jeter un oeil à votre groupe de requête par expression

 d => DbFunctions.AddMinutes(DateTime.MinValue, DbFunctions.DiffMinutes(DateTime.MinValue, d.TimeStamp) / minuteInterval * minuteInterval) 

Puisque minuteInterval est une variable (ie non constante), elle introduit un paramètre. Idem pour DateTime.MinValue (notez que les types primitifs exposent des choses semblables à des constantes s, mais pour DateTime , decimal etc. ce sont des champs statiques en lecture seule qui font une grande différence sur la façon dont ils sont traités dans les expressions).

Mais indépendamment de la façon dont il est représenté dans le système CLR, DateTime.MinValue est logiquement une constante. Qu'en est-il de minuteInterval , cela dépend de votre utilisation.

Ma tentative pour résoudre le problème serait d'éliminer tous les parameters liés à cette expression. Puisque nous ne pouvons pas faire cela avec l'expression générée par le compilateur, nous devons le build manuellement en utilisant System.Linq.Expressions . Ce dernier n'est pas intuitif, mais heureusement, nous pouvons utiliser une approche hybride.

Tout d'abord, nous avons besoin d'une méthode d'assistance qui nous permet de replace les parameters d'expression:

 public static class ExpressionUtils { public static Expression ReplaceParemeter(this Expression expression, ParameterExpression source, Expression target) { return new ParameterReplacer { Source = source, Target = target }.Visit(expression); } class ParameterReplacer : ExpressionVisitor { public ParameterExpression Source; public Expression Target; protected override Expression VisitParameter(ParameterExpression node) { return node == Source ? Target : base.VisitParameter(node); } } } 

Maintenant nous avons tout ce dont nous avons besoin. Laissez encapsuler la logique dans une méthode personnalisée:

 public static class QueryableUtils { public static IQueryable<IGrouping<DateTime, T>> GroupBy<T>(this IQueryable<T> source, Expression<Func<T, DateTime>> dateSelector, int minuteInterval) { Expression<Func<DateTime, DateTime, int, DateTime>> expr = (date, baseDate, interval) => DbFunctions.AddMinutes(baseDate, DbFunctions.DiffMinutes(baseDate, date) / interval).Value; var selector = Expression.Lambda<Func<T, DateTime>>( expr.Body .ReplaceParemeter(expr.Parameters[0], dateSelector.Body) .ReplaceParemeter(expr.Parameters[1], Expression.Constant(DateTime.MinValue)) .ReplaceParemeter(expr.Parameters[2], Expression.Constant(minuteInterval)) , dateSelector.Parameters[0] ); return source.GroupBy(selector); } } 

Enfin, remplacez

 .GroupBy(d => DbFunctions.AddMinutes(DateTime.MinValue, DbFunctions.DiffMinutes(DateTime.MinValue, d.TimeStamp) / minuteInterval * minuteInterval)) 

avec

 .GroupBy(d => d.TimeStamp, minuteInterval * minuteInterval) 

et la requête SQL générée serait comme ceci (pour minuteInterval = 15 ):

 SELECT 1 AS [C1], [GroupBy1].[K1] AS [C2], [GroupBy1].[A1] AS [C3], [GroupBy1].[A2] AS [C4], [GroupBy1].[A3] AS [C5], [GroupBy1].[A4] AS [C6] FROM ( SELECT [Project1].[K1] AS [K1], MIN([Project1].[A1]) AS [A1], MAX([Project1].[A2]) AS [A2], AVG([Project1].[A3]) AS [A3], STDEVP([Project1].[A4]) AS [A4] FROM ( SELECT DATEADD (minute, (DATEDIFF (minute, convert(datetime2, '0001-01-01 00:00:00.0000000', 121), [Project1].[TimeStamp])) / 225, convert(datetime2, '0001-01-01 00:00:00.0000000', 121)) AS [K1], [Project1].[C1] AS [A1], [Project1].[C1] AS [A2], [Project1].[C1] AS [A3], [Project1].[C1] AS [A4] FROM ( SELECT [Extent1].[TimeStamp] AS [TimeStamp], [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1] FROM [dbo].[SsortingngDatas] AS [Extent1] INNER JOIN [dbo].[DCSsortingngs] AS [Extent2] ON [Extent1].[DCSsortingngID] = [Extent2].[ID] INNER JOIN [dbo].[DCDissortingbutionBoxes] AS [Extent3] ON [Extent2].[DCDissortingbutionBoxID] = [Extent3].[ID] INNER JOIN [dbo].[DataLoggers] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID] WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2) ) AS [Project1] ) AS [Project1] GROUP BY [K1] ) AS [GroupBy1] 

Comme vous pouvez le voir, nous avons réussi à éliminer certains des parameters de la requête. Cela aidera-t-il? Eh bien, comme avec tout réglage de requête de database, il pourrait ou non. Vous devez essayer et voir.

Le moteur DB détermine le plan pour chaque requête en fonction de la façon dont il est appelé. Dans le cas de votre requête EF Linq, le plan est préparé de telle sorte que chaque paramètre d'input est traité comme un inconnu (puisque vous n'avez aucune idée de ce qui arrive). Dans votre requête actuelle, vous avez tous vos parameters dans le cadre de la requête afin qu'elle s'exécute sous un plan différent de celui pour un paramètre paramétré. Une des pièces touchées que je vois immédiatement est

… (@ p__linq__0 EST NUL) ..

Ceci est FALSE depuis p_linq_0 = 20827 et n'est pas NULL, donc votre première moitié de WHERE est FAUX pour commencer et n'a pas besoin d'être regardé plus. Dans le cas de requêtes LINQ, la database n'a aucune idée de ce qui arrive et évalue tout de toute façon.

Vous aurez besoin de voir si vous pouvez utiliser des indices ou d'autres techniques pour que cela fonctionne plus vite.

Lorsque EF exécute la requête, il l'enveloppe et l'exécute avec sp_executesql, ce qui signifie que le plan d'exécution sera mis en cache dans le cache du plan d'exécution de la procédure stockée. En raison des différences (reniflage des parameters, etc.) dans la façon dont l'instruction SQL brute par rapport à la version SP a leurs plans d'exécution construits, les deux peuvent différer.

Lors de l'exécution de la version EF (sp wrapped), le server SQL utilise probablement un plan d'exécution plus générique qui couvre une plage d'horodatages plus étendue que les valeurs réellement transmises.

Cela dit, pour réduire le risque que le server SQL essaie quelque chose de "drôle" avec des jointures de hachage, etc, les premières choses que je ferais sont:

1) Indexer les colonnes utilisées dans la clause where et dans les jointures

 create index ix_DataLogger_ProjectID on DataLogger (ProjectID); create index ix_DCDissortingbutionBox_DataLoggerID on DCDissortingbutionBox (DataLoggerID); create index ix_DCSsortingng_DCDissortingbutionBoxID on DCSsortingng (DCDissortingbutionBoxID); 

2) Faire des jointures explicites dans la requête Linq pour éliminer le ou ProductID est une partie nulle