Производительность запросов в платформе Entity Framework сильно отличается от необработанного выполнения SQL

У меня вопрос о производительности выполнения запросов Entity Framework.

Схема

У меня есть такая структура таблицы, как это:

CREATE TABLE [dbo].[DataLogger]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ProjectID] [bigint] NULL,
    CONSTRAINT [PrimaryKey1] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

CREATE TABLE [dbo].[DCDistributionBox]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DataLoggerID] [bigint] NOT NULL,
    CONSTRAINT [PrimaryKey2] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCDistributionBox]
    ADD CONSTRAINT [FK_DCDistributionBox_DataLogger] 
    FOREIGN KEY([DataLoggerID]) REFERENCES [dbo].[DataLogger] ([ID])

CREATE TABLE [dbo].[DCString] 
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCDistributionBoxID] [bigint] NOT NULL,
    [CurrentMPP] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCString]
    ADD CONSTRAINT [FK_DCString_DCDistributionBox] 
    FOREIGN KEY([DCDistributionBoxID]) REFERENCES [dbo].[DCDistributionBox] ([ID])

CREATE TABLE [dbo].[StringData]
(
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCStringID] ASC)
)

CREATE NONCLUSTERED INDEX [TimeStamp_DCCurrent-NonClusteredIndex] 
ON [dbo].[StringData] ([DCStringID] ASC, [TimeStamp] ASC)
INCLUDE ([DCCurrent])

Стандартные индексы внешних ключей также существуют (я не хочу перечислять их все по соображениям пространства).

Таблица [StringData] имеет следующую статистику хранения:

Информационное пространство: 26 901,86 МБ
Количество строк: 131,827,749
Разделено: true
Количество разделов: 62

Использование

Теперь я хочу сгруппировать данные в таблице [StringData] и сделать некоторую агрегацию.

Я создал запрос Entity Framework (подробные сведения к запросу можно найти здесь):

var compareData = model.StringDatas
    .AsNoTracking()
    .Where(p => p.DCString.DCDistributionBox.DataLogger.ProjectID == projectID && p.TimeStamp >= fromDate && p.TimeStamp < tillDate)
    .Select(d => new
    {
        TimeStamp = d.TimeStamp,
        DCCurrentMpp = d.DCCurrent / d.DCString.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();

Временной интервал исключения является исключительным долго!?

Результат выполнения: 92rows
Время выполнения: ~ 16000мс

Попытки

Теперь я рассмотрел созданный SQL-запрос Entity Framework и выглядел так:

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].[StringData] AS [Extent1]
            INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
            INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [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]

Я скопировал этот SQL-запрос в SSMS на том же компьютере, связанный с той же строкой соединения, что и Entity Framework.

Результат - очень улучшенная производительность:

Результат выполнения: 92rows
Время выполнения: 517мс

Я также выполняю тест цикла, и результат странный. Тест выглядит следующим образом

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

Результат очень отличается и выглядит случайным (?):

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

Вопрос

Почему выполнение запросов Entity Framework так медленно? В результате число строк действительно низкое, а исходный SQL-запрос показывает очень высокую производительность.

Обновление 1:

Я забочусь о том, что это не задержка создания MetaContext или Model. Некоторые другие запросы выполняются на одном экземпляре модели непосредственно с хорошей производительностью.

Обновление 2 (связанное с ответом @x0007me):

Спасибо за подсказку, но это можно устранить, изменив параметры модели следующим образом:

modelContext.Configuration.UseDatabaseNullSemantics = true;

Созданный EF SQL теперь:

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].[StringData] AS [Extent1]
            INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
            INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [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]

Итак, вы можете видеть, что описанная вами проблема решена, но время выполнения не изменяется.

Кроме того, как вы можете видеть в схеме и необработанном времени выполнения, я использовал оптимизированную структуру с высоким оптимизированным индексом.

Обновление 3 (связанное с ответом @Владимира Баранова):

Я не понимаю, почему это может быть связано с кэшированием плана запроса. Поскольку в MSDN четко указано, что EF6 использует кэширование плана запроса.

Простое тестовое доказательство того, что огромные различия времени исключения не связаны с кешированием плана запроса (phseudo code):

using(var modelContext = new ModelContext())
{
    modelContext.Query(); //1th run activates caching

    modelContext.Query(); //2th used cached plan
}

В результате оба запроса выполняются с тем же временем исключения.

Обновление 4 (связанное с ответом @bubi):

Я попытался запустить запрос, который генерируется EF, когда вы его описали:

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].[StringData] AS [Extent1]
                INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
                INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [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));

Результат выполнения: 92
Время выполнения: ~ 16000мс

Требуется точное до тех пор, пока обычный EF-запрос!?

Обновить 5 (связанный с ответом @vittore):

Я создаю отслеживаемое дерево вызовов, возможно, это помогает:

Обновить 6 (связанный с ответом @usr):

Я создал два showplan XML через SQL Server Profiler.

Быстрый запуск (SSMS).SQLPlan

Медленный запуск (EF).SQLPlan

Обновление 7 (связано с комментариями @VladimirBaranov):

Теперь я запускаю еще несколько тестовых примеров, связанных с вашими комментариями.

Сначала я выполняю операции с порядковым номером, используя новый вычисляемый столбец и соответствующий INDEXER. Это уменьшает задержку производительности, связанную с DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [TimeStamp] ) / 15* 15, 0). Подробно о том, как и почему вы можете найти здесь.

Результат выглядит так:

Запрос Pure EntityFramework:

for (int i = 0; i < 3; i++)
{
    DateTime begin = DateTime.UtcNow;
    var result = model.StringDatas
        .AsNoTracking()
        .Where(p => p.DCString.DCDistributionBox.DataLogger.ProjectID == projectID && p.TimeStamp15Minutes >= fromDate && p.TimeStamp15Minutes < tillDate)
        .Select(d => new
        {
            TimeStamp = d.TimeStamp15Minutes,
            DCCurrentMpp = d.DCCurrent / d.DCString.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.ToString());
}

0-й запуск чистого EF: 00: 00: 12.6460624

1-й запуск чистого EF: 00: 00: 11.0258393

2-й запуск чистого EF: 00: 00: 08.4171044

Теперь я использовал созданный EF SQL как 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].[StringData] AS [Extent1]
                INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
                INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [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.ToString());
}

0-й прогон: 00: 00: 00.8381200

1-й прогон: 00: 00: 00.6920736

2-й прогон: 00: 00: 00.7081006

и 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].[StringData] AS [Extent1]
                INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
                INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [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.ToString());
}

0-й запуск с помощью RECOMPILE: 00: 00: 00.8260932

1-й запуск с помощью RECOMPILE: 00: 00: 00.9139730

2-й запуск с помощью RECOMPILE: 00: 00: 01.0680665

Тот же SQL-запрос, экспрессивный в SSMS (без RECOMPILE):

00: 00: 01,105

Тот же SQL-запрос, выделенный в SSMS (с помощью RECOMPILE):

00: 00: 00,902

Надеюсь, это все нужные вам ценности.

Ответ 1

В этом ответе я сосредоточусь на исходном наблюдении: запрос, сгенерированный EF, медленный, но когда тот же запрос выполняется в SSMS, он быстрый.

Одним из возможных объяснений этого поведения является анализ параметров.

SQL Server использует процесс, называемый сниффинг параметров, когда он выполняет хранимые процедуры, которые имеют параметры. Когда процедура компилируется или перекомпилируется, значение, переданное в параметр, оценивается и используется для создания плана выполнения. Это значение затем сохраняется вместе с планом выполнения в кэше планов. При последующих казнях используется то же значение и тот же план.

Итак, EF генерирует запрос с несколькими параметрами. При первом запуске этого запроса сервер создает план выполнения для этого запроса, используя значения параметров, которые действовали при первом запуске. Этот план обычно довольно хорош. Но позже вы запустите тот же EF-запрос, используя другие значения для параметров. Возможно, что для новых значений параметров ранее сгенерированный план не является оптимальным, и запрос становится медленным. Сервер продолжает использовать предыдущий план, потому что это все тот же запрос, просто значения параметров разные.

Если в этот момент вы берете текст запроса и пытаетесь запустить его непосредственно в SSMS, сервер создаст новый план выполнения, поскольку технически это не тот запрос, который выдается приложением EF. Достаточно даже одного различия символов, любое изменение настроек сеанса также достаточно, чтобы сервер воспринял запрос как новый. В результате у сервера есть два плана для, казалось бы, одного и того же запроса в его кеше. Первый "медленный" план является медленным для новых значений параметров, потому что он изначально был построен для разных значений параметров. Второй "быстрый" план построен для текущих значений параметров, поэтому он быстрый.

Erland Sommarskog в статье " Медленно в приложении, быстро в SSMS" объясняет эту и другие смежные области гораздо более подробно.

Существует несколько способов отменить кэшированные планы и заставить сервер их восстановить. Изменение таблицы или изменение индексов таблицы должны сделать это - она должна отбросить все планы, связанные с этой таблицей, как "медленные", так и "быстрые". Затем вы запускаете запрос в приложении EF с новыми значениями параметров и получаете новый "быстрый" план. Вы запускаете запрос в SSMS и получаете второй "быстрый" план с новыми значениями параметров. Сервер по-прежнему генерирует два плана, но оба плана теперь быстрые.

Другой вариант - добавление OPTION(RECOMPILE) к запросу. С этой опцией сервер не будет сохранять сгенерированный план в своем кэше. Таким образом, каждый раз, когда выполняется запрос, сервер будет использовать фактические значения параметров для генерации плана, который (он считает) будет оптимальным для данных значений параметров. Недостатком является добавленная накладная стоимость генерации плана.

Имейте в виду, что сервер все еще может выбрать "плохой" план с этой опцией, например, из-за устаревшей статистики. Но, по крайней мере, сниффинг параметров не будет проблемой.

Те, кому интересно, как добавить подсказку OPTION (RECOMPILE) в запрос, сгенерированный EF, взглянут на этот ответ:

fooobar.com/questions/76237/...

Ответ 2

Я знаю, что я немного опаздываю здесь, но так как я участвовал в создании данного вопроса, я чувствую себя обязанным принять какое-то действие.

Общая проблема, которую я вижу с запросами Linq to Entities, заключается в том, что типичный способ их создания вводит ненужные параметры, которые могут повлиять на план запросов кэшированных баз данных (так называемая проблема с параметром snqing сервера Sql).

Посмотрите на свою группу запросов по выражению

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

Так как minuteInterval - переменная (т.е. не константа), она вводит параметр. То же самое для DateTime.MinValue (обратите внимание, что примитивные типы выставляют похожие вещи как константа s, но для DateTime, decimal и т.д. Они являются статическими полями readonly, что делает большое различие, как они рассматриваются внутри выражений).

Но независимо от того, как он представлен в системе CLR, DateTime.MinValue логически является константой. Что насчет minuteInterval, это зависит от вашего использования.

Моя попытка решить проблему будет заключаться в устранении всех параметров, связанных с этим выражением. Поскольку мы не можем сделать это с помощью генерируемого компилятором выражения, нам нужно его вручную создать, используя System.Linq.Expressions. Позже это неинтуитивно, но, к счастью, мы можем использовать гибридный подход.

Во-первых, нам нужен вспомогательный метод, который позволяет нам заменить параметры выражения:

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

Теперь у нас есть все необходимое. Пусть инкапсулирует логику внутри настраиваемого метода:

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

Наконец, замените

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

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

и сгенерированный SQL-запрос будет таким (для 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].[StringDatas] AS [Extent1]
                INNER JOIN [dbo].[DCStrings] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
                INNER JOIN [dbo].[DCDistributionBoxes] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [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]

Как вы можете видеть, мы успешно устранили некоторые параметры запроса. Это поможет? Ну, как и при любой настройке запроса базы данных, это может быть или не быть. Вам нужно попытаться посмотреть.

Ответ 3

Механизм БД определяет план для каждого запроса на основе того, как он вызывается. В случае вашего запроса EF Linq план подготовлен таким образом, что каждый входной параметр рассматривается как неизвестный (поскольку вы не представляете, что входит). В вашем фактическом запросе у вас есть все параметры как часть запроса, поэтому он будет работать под другим планом, чем для параметризованного. Одна из пораженных частей, которую я вижу сразу, -

... (@p__linq__0 IS NULL)..

Это ЛОЖЬ с p_linq_0 = 20827 и не является NULL, поэтому ваша первая половина WHERE является FALSE для начала, и ее больше не нужно искать. В случае запросов LINQ, БД не имеет представления о том, что происходит, поэтому все равно оценивает все.

Вам нужно будет увидеть, можете ли вы использовать индексы или другие методы, чтобы сделать этот запуск быстрее.

Ответ 4

Когда EF запускает запрос, он завершает его и запускает его с помощью sp_executesql, что означает, что план выполнения будет кэшироваться в кеше плана выполнения хранимой процедуры. Из-за различий (sniffing параметров и т.д.) В том, как строгий SQL-оператор и версия SP имеют свои планы выполнения, они могут отличаться.

При запуске версии EF (sp wrapped) сервер SQL, скорее всего, использует более общий план выполнения, который охватывает более широкий диапазон временных меток, чем значения, которые вы фактически передаете.

Тем не менее, чтобы уменьшить вероятность того, что SQL-сервер попробует что-то "смешное" с хэш-соединениями и т.д., первые вещи, которые я бы сделал, это:

1) Индексируйте столбцы, используемые в предложении where, и в соединениях

create index ix_DataLogger_ProjectID on DataLogger (ProjectID);
create index ix_DCDistributionBox_DataLoggerID on DCDistributionBox (DataLoggerID);
create index ix_DCString_DCDistributionBoxID on DCString (DCDistributionBoxID);

2) Явные соединения в запросе Linq для устранения или ProductID являются нулевой частью