Запрос, сгенерированный EF, занимает слишком много времени для выполнения

Ответ 1

Я предполагаю, что WHERE row_number > 0 со временем изменится, когда вы попросите страницы 2, стр. 3 и т.д.

Как таковой, мне любопытно, поможет ли это создать этот индекс:

CREATE INDEX idx_links_SentDate_desc ON [dbo].[Links] ([SentDate] DESC)

Честно говоря, ЕСЛИ это работает, это в значительной степени бандажная помощь, и вам, вероятно, придется часто обновлять этот индекс, поскольку я предполагаю, что со временем он будет фрагментирован...

ОБНОВЛЕНИЕ: проверьте комментарии! Оказывается, DESC не имеет никакого эффекта и его следует избегать, если ваши данные поступают с низким и высоким!

Ответ 2

Мне никогда не приходило в голову, что у вас просто нет индекса. Извлеченный урок - всегда проверяйте основы, прежде чем рыть дальше.

Если вам не нужна разбивка на страницы, тогда запрос можно упростить до

SELECT TOP (10) 
    [Extent1].[LinkID] AS [LinkID], 
    [Extent1].[Title] AS [Title], 
    ...
FROM [dbo].[Links] AS [Extent1]
ORDER BY [Extent1].[SentDate] DESC

и он работает быстро, как вы подтвердили.

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

Причина, по которой ваша текущая версия медленная, потому что она сначала сканирует таблицу целый, вычисляет номер строки для каждой строки и только затем возвращает 10 строк. Я был здесь не так. Оптимизатор SQL Server довольно умный. Корень вашей проблемы находится где-то в другом месте. См. Мое обновление ниже.

Кстати, как отметили другие люди, эта разбивка на страницы будет корректно работать, только если столбец SentDate уникален. Если это не уникально, вам нужно ORDER BY SentDate и еще один уникальный столбец, например, некоторый ID, чтобы устранить двусмысленность.

Если вам не нужна возможность переходить прямо на определенную страницу, но, скорее, всегда начинайте со страницы 1, затем переходите к следующей странице, следующей странице и т.д., тогда надлежащий эффективный способ сделать такую ​​разбивку на страницы описан в этом отличная статья: http://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way Автор использует PostgreSQL для иллюстрации, но этот метод работает и для MS SQL Server. Это сводится к тому, чтобы запомнить ID последней строки на показанной странице, а затем использовать этот ID в предложении WHERE с соответствующим индексом поддержки для получения следующей страницы без сканирования всех предыдущих строк.

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

У вас будут эти переменные (PageSize, PageNumber) в коде С#. Я поставил их здесь, чтобы проиллюстрировать суть.

DECLARE @VarPageSize int = 10; -- number of rows in each page
DECLARE @VarPageNumber int = 3; -- page numeration is zero-based

SELECT TOP (@VarPageSize)
    [Extent1].[LinkID] AS [LinkID]
    ,[Extent1].[Title] AS [Title]
    ,[Extent1].[Url] AS [Url]
    ,[Extent1].[Description] AS [Description]
    ,[Extent1].[SentDate] AS [SentDate]
    ,[Extent1].[VisitCount] AS [VisitCount]
    ,[Extent1].[RssSourceId] AS [RssSourceId]
    ,[Extent1].[ReviewStatus] AS [ReviewStatus]
    ,[Extent1].[UserAccountId] AS [UserAccountId]
    ,[Extent1].[CreationDate] AS [CreationDate]
FROM
    (
        SELECT TOP((@VarPageNumber + 1) * @VarPageSize)
            [Extent1].[LinkID] AS [LinkID]
            ,[Extent1].[Title] AS [Title]
            ,[Extent1].[Url] AS [Url]
            ,[Extent1].[Description] AS [Description]
            ,[Extent1].[SentDate] AS [SentDate]
            ,[Extent1].[VisitCount] AS [VisitCount]
            ,[Extent1].[RssSourceId] AS [RssSourceId]
            ,[Extent1].[ReviewStatus] AS [ReviewStatus]
            ,[Extent1].[UserAccountId] AS [UserAccountId]
            ,[Extent1].[CreationDate] AS [CreationDate]
        FROM [dbo].[Links] AS [Extent1]
        ORDER BY [Extent1].[SentDate] DESC
    ) AS [Extent1]
ORDER BY [Extent1].[SentDate] ASC
;

Первая страница - это строки с 1 по 10, вторая страница - от 11 до 20 и т.д. Посмотрите, как работает этот запрос, когда мы пытаемся получить четвертую страницу, то есть строки с 31 по 40. PageSize=10, PageNumber=3. Во внутреннем запросе мы выбираем первые 40 строк. Обратите внимание, что мы не просматриваем всю таблицу здесь, мы сканируем только первые 40 строк. Нам даже не нужен явный ROW_NUMBER(). Затем нам нужно выбрать последние 10 строк из найденных 40, поэтому внешний запрос выбирает TOP(10) с ORDER BY в противоположном направлении. Так как это будет возвращать строки с 40 по 31 в обратном порядке. Вы можете отсортировать их в правильном порядке на клиенте или добавить еще один внешний запрос, который просто сортирует их снова SentDate DESC. Вот так:

SELECT
    [Extent1].[LinkID] AS [LinkID]
    ,[Extent1].[Title] AS [Title]
    ,[Extent1].[Url] AS [Url]
    ,[Extent1].[Description] AS [Description]
    ,[Extent1].[SentDate] AS [SentDate]
    ,[Extent1].[VisitCount] AS [VisitCount]
    ,[Extent1].[RssSourceId] AS [RssSourceId]
    ,[Extent1].[ReviewStatus] AS [ReviewStatus]
    ,[Extent1].[UserAccountId] AS [UserAccountId]
    ,[Extent1].[CreationDate] AS [CreationDate]
FROM
    (
        SELECT TOP (@VarPageSize)
            [Extent1].[LinkID] AS [LinkID]
            ,[Extent1].[Title] AS [Title]
            ,[Extent1].[Url] AS [Url]
            ,[Extent1].[Description] AS [Description]
            ,[Extent1].[SentDate] AS [SentDate]
            ,[Extent1].[VisitCount] AS [VisitCount]
            ,[Extent1].[RssSourceId] AS [RssSourceId]
            ,[Extent1].[ReviewStatus] AS [ReviewStatus]
            ,[Extent1].[UserAccountId] AS [UserAccountId]
            ,[Extent1].[CreationDate] AS [CreationDate]
        FROM
            (
                SELECT TOP((@VarPageNumber + 1) * @VarPageSize)
                    [Extent1].[LinkID] AS [LinkID]
                    ,[Extent1].[Title] AS [Title]
                    ,[Extent1].[Url] AS [Url]
                    ,[Extent1].[Description] AS [Description]
                    ,[Extent1].[SentDate] AS [SentDate]
                    ,[Extent1].[VisitCount] AS [VisitCount]
                    ,[Extent1].[RssSourceId] AS [RssSourceId]
                    ,[Extent1].[ReviewStatus] AS [ReviewStatus]
                    ,[Extent1].[UserAccountId] AS [UserAccountId]
                    ,[Extent1].[CreationDate] AS [CreationDate]
                FROM [dbo].[Links] AS [Extent1]
                ORDER BY [Extent1].[SentDate] DESC
            ) AS [Extent1]
        ORDER BY [Extent1].[SentDate] ASC
    ) AS [Extent1]
ORDER BY [Extent1].[SentDate] DESC

Этот запрос (как исходный запрос) будет работать всегда корректно, только если SentDate является уникальным. Если он не уникален, добавьте уникальный столбец в ORDER BY. Например, если LinkID уникален, то в самом внутреннем запросе используйте ORDER BY SentDate DESC, LinkID DESC. Во внешнем запросе обратный порядок: ORDER BY SentDate ASC, LinkID ASC.

Очевидно, что если вы хотите перейти на страницу 1000, тогда внутренний запрос должен будет прочитать 10 000 строк, так что чем дальше вы идете, тем медленнее получится.

В любом случае вам нужно иметь индекс на SentDate (или SentDate, LinkID), чтобы он работал. Без индекса запрос снова сканирует всю таблицу.

Я не говорю вам здесь, как перевести этот запрос в EF, потому что я не знаю. Я никогда не использовал EF. Может быть и так. Кроме того, по-видимому, вы можете просто заставить его использовать фактический SQL, а не пытаться играть с кодом С#.

Update

Сравнение планов выполнения

В моей базе данных у меня есть таблица EventLogErrors с 29,477,859 строк, и я сравнил на SQL Server 2008 запрос с ROW_NUMBER, который генерирует EF и что я предложил здесь с помощью TOP. Я попытался получить четвертую страницу длиной 10 строк. В обоих случаях оптимизатор был достаточно умен, чтобы читать только 40 строк, как вы можете видеть из планов выполнения. Я использовал столбец первичного ключа для упорядочения и разбивки на страницы для этого теста. Когда я использовал другой индексированный столбец для результатов разбивки на страницы, были одинаковыми, т.е. Оба варианта читали только 40 строк. Излишне говорить, что оба варианта возвращали результаты за долю секунды.

Вариант с TOP

Вариант с ROW_NUMBER

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

• Проверить план выполнения.
• Убедитесь, что у вас есть индекс.
• Убедитесь, что индекс не сильно фрагментирован, а статистика не устарела.
• В SQL Server есть функция Автопараметризация. Кроме того, у него есть функция Параметр Sniffing. Кроме того, у него есть функция кэширование плана выполнения. Когда все три функции работают вместе, это может привести к использованию неоптимального плана выполнения. Существует замечательная статья Эрланда Соммарскана, подробно объясняющая это: http://www.sommarskog.se/query-plan-mysteries.html В этой статье объясняется, как подтвердить, что проблема действительно связана с параметром sniffing проверив план выполнения кэширования и что можно сделать для устранения проблемы.

Ответ 3

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

Ясно, что в этом случае это так. Одним из способов является использование собственного запроса с помощью ExecuteStoreQuery. Что-то вроде этого:

int takeNo = 20;
int skipNo = 100;

var results = db.ExecuteStoreQuery<Link>(
    "SELECT LinkID, Title, Url, Description, SentDate, VisitCount, RssSourceId, ReviewStatus, UserAccountId, CreationDate FROM Links", 
    null);

results = results.OrderBy(x=> x.SentDate).Skip(skipNo).Take(takeNo);

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

Ответ 4

Это выглядит как стандартный запрос подкачки. Я бы предположил, что у вас нет индекса в SentDate. Если это так, первое, что нужно попробовать, это добавить индекс в SentDate и посмотреть, какое влияние это оказывает на производительность. Предполагая, что вы не всегда хотите сортировать/на странице SentDate и индексировать каждый столбец, который вы хотите отсортировать/на странице, не произойдет, посмотрите fooobar.com/questions/383274/.... В некоторых случаях операция SQL Server "Собрать потоки" parallelism может переполняться в TempDb. Когда это происходит, производительность идет в туалет. Как говорит другой ответ, индексирование столбца может помочь, так как может отключить parallelism. Ознакомьтесь с планом запроса и посмотрите, похоже ли, что это может быть проблемой.

Ответ 5

Я не очень хорош в EF, но могу дать вам подсказки. Прежде всего, вы должны проверить, есть ли у вас некластеризованный индекс в [Extent1]. [SentDate]. Второй, если нет, создайте, если существует, затем воссоздайте или переустановите его.

Третий изменить свой запрос следующим образом. Поскольку ваш исходный SQL - это просто написанный ненужный комплекс, и он будет таким же, как и этот, который я показываю здесь. Попробуйте написать простые вещи, они будут работать быстрее, а обслуживание также будет легким.

SELECT TOP (10) 
[Extent1].[LinkID] AS [LinkID], 
[Extent1].[Title] AS [Title], 
[Extent1].[Url] AS [Url], 
[Extent1].[Description] AS [Description], 
[Extent1].[SentDate] AS [SentDate], 
[Extent1].[VisitCount] AS [VisitCount], 
[Extent1].[RssSourceId] AS [RssSourceId], 
[Extent1].[ReviewStatus] AS [ReviewStatus], 
[Extent1].[UserAccountId] AS [UserAccountId], 
[Extent1].[CreationDate] AS [CreationDate]
FROM [dbo].[Links] AS [Extent1]
ORDER BY [Extent1].[SentDate] DESC

или измените этот бит немного, если это произойдет иначе.

select top 10 A.* from (
SELECT * from
[Extent1].[LinkID] AS [LinkID], 
[Extent1].[Title] AS [Title], 
[Extent1].[Url] AS [Url], 
[Extent1].[Description] AS [Description], 
[Extent1].[SentDate] AS [SentDate], 
[Extent1].[VisitCount] AS [VisitCount], 
[Extent1].[RssSourceId] AS [RssSourceId], 
[Extent1].[ReviewStatus] AS [ReviewStatus], 
[Extent1].[UserAccountId] AS [UserAccountId], 
[Extent1].[CreationDate] AS [CreationDate]
FROM [dbo].[Links] AS [Extent1] ) A
ORDER BY A.[SentDate] DESC 

Я на 99% уверен, что он сработает.

Ответ 6

Вы пробовали цепочки в методе?

        IQueryable<TEntity> query = _dbSet;
        return query.Where(x => (filter != null ? filter : x)
                    .Where(x => (orderBy != null ? orderBy : x));

Мне интересно, изменит ли этот запрос запрос, созданный EF.

Ответ 7

Я столкнулся с подобными проблемами до того, как EF решит украсить SQL, который он решает запустить очень неэффективно.

В любом случае, чтобы обеспечить возможное решение вашего вопроса:

В случаях, когда мне не нравится то, что EF делает с моим кодом для генерации операторов SQL, я в конечном итоге записываю хранимую процедуру, импортирую ее в свой EDMX как функцию и использую ее для извлечения моих данных. Это дает мне контроль над тем, как сформулировать SQL, и я точно знаю, какой индекс мне нужен, чтобы получить максимальную производительность. Я думаю, вы знаете, как писать хранимую процедуру и импортировать ее как функцию в EF, поэтому я оставлю эти данные. Надеюсь, это поможет вам.

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

Ответ 8

Ваш код выглядит несколько неясным для меня, и это первый раз, когда я сталкиваюсь с таким запросом. Как вы сказали, иногда требуется слишком много времени для выполнения, поэтому он сообщает, что запрос может быть интерпретирован по-другому, возможно, игнорируя соображения производительности EF в некоторых случаях So пытается изменить условия запроса/выборки и считать ленивую загрузку в вашей программной логике.

Ответ 9

Неужели вы не укушены проблемой обновления статистики в SQL-сервере?

ALTER DATABASE YourDBName SET AUTO_UPDATE_STATISTICS_ASYNC ON

Значение по умолчанию: OFF, поэтому ваш SQL-сервер будет останавливаться, когда 20% ваших данных изменились, - ожидая обновления статистики перед запуском запроса.

Ответ 10

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

if (orderBy != null)
{
    query = orderBy(query);
}

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

Попробуйте комментировать фрагмент query = orderBy(query) вашего кода и посмотрите, все ли медленнее. Я уверен, что вы этого не сделаете.

Кроме того, вы можете упростить свой код, используя Dynamic LINQ. В основном это позволяет вам сортировать с именем строки поля (.orderby("somefield")) вместо того, чтобы пытаться передать метод, который, как мне показалось, намного проще. Я использую это в приложениях MVC для обработки сортировки по любому полю, которое пользователи нажимают на сетку.

Ответ 11

Попробуйте добавить некластеризованный индекс в SentDate