Введение
В этой статье рассматривались некоторые методы оптимизации LINQ-запросов.
Здесь же приведем еще некоторые подходы по оптимизации кода, связанные с LINQ-запросами.
Известно, что LINQ(Language-Integrated Query) — это простой и удобный язык запросов к источнику данных.
А LINQ to SQL является технологией доступа к данным в СУБД. Это мощный инструмент работы с данными, где через декларативный язык конструируются запросы, которые затем будут преобразовываться в SQL-запросы платформой и отправляться на сервер баз данных уже на выполнение. В нашем случае под СУБД будем понимать MS SQL Server.
Однако, LINQ-запросы не преобразовываются в оптимально написанные SQL-запросы, которые смог бы написать опытный DBA со всеми нюансами оптимизации SQL-запросов:
- оптимальные соединения (JOIN) и фильтрование результатов (WHERE)
- множество нюансов в использовании соединений и групповых условий
- множество вариаций в замене условий IN на EXISTSи NOT IN, <> на EXISTS
- промежуточное кэширование результатов через временные таблицы, CTE, табличные переменные
- использование предложения (OPTION) с указаниями и табличных хинтов WITH (...)
- использование индексируемых представлений, как одно из средств избавиться от избыточных чтений данных при выборках
Основными узкими местами производительности получающихся SQL-запросов при компиляции LINQ-запросов являются:
- консолидация всего механизма отбора данных в одном запросе
- дублирование идентичных блоков кода, что в итоге приводит к многократным лишним чтениям данных
- группы многосоставных условий (логических «и» и «или») — AND и OR, соединяясь в сложные условия, приводит к тому, что оптимизатор, имея подходящие некластеризованные индексы, по необходимым полям, в конечном итоге все же начинает делать сканирование по кластерному индексу (INDEX SCAN) по группам условий
- глубокая вложенность подзапросов делает очень проблематичным разбор SQL-инструкций и разбор плана запросов со стороны разработчиков и DBA
Методы оптимизации
Теперь перейдем непосредственно к методам оптимизации.
1) Дополнительное индексирование
Лучше всего рассматривать фильтры на основных таблицах выборки, поскольку очень часто весь запрос строится вокруг одной-двух основных таблиц (заявки-люди-операции) и со стандартным набором условий (IsClosed, Canceled, Enabled, Status). Важно для выявленных выборок создать соответствующие индексы.
Данное решение имеет смысл, когда выбор по этим полям существенно ограничивает возвращаемое множество запросом.
Например, у нас есть 500000 заявок. Однако, активных заявок всего 2000 записей. Тогда правильно подобранный индекс избавит нас от INDEX SCAN по большой таблице и позволит быстро выбрать данные через некластеризованный индекс.
Также нехватку индексов можно выявить через подсказки разбора планов запросов или сбора статиcтик системных представлений MS SQL Server:
Все данные представления содержат сведения об отсутствующих индексах, за исключением пространственных индексов.
Однако, индексы и кэширование часто являются методами борьбы последствий плохо написанных LINQ-запросов и SQL-запросов.
Как показывает суровая практика жизни для бизнеса часто важна реализация бизнес-фичей к определенным срокам. И потому часто тяжелые запросы переводят в фоновый режим с кэшированием.
Отчасти это оправдано, так как пользователю не всегда нужны самые свежие данные и происходит приемлемый уровень отклика пользовательского интерфейса.
Данный подход позволяет решать запросы бизнеса, но понижает в итоге работоспособность информационной системы, просто отсрочивая решения проблем.
Также стоит помнить о том, что в процессе поиска необходимых для добавления новых индексов, предложения MS SQL по оптимизации могут быть некорректными в том числе при следующих условиях:
- если уже существуют индексы с подобным набором полей
- если поля в таблице не могут быть проиндексированы по причине ограничений индексирования (более детально об этом описано здесь).
2) Объединение атрибутов в один новый атрибут
Иногда некоторые поля из одной таблицы, по которым происходит группа условий, можно заменить введением одного нового поля.
Особенно это актуально для полей-состояний, которые по типу обычно являются либо битовыми, либо целочисленными.
Пример:
IsClosed = 0 AND Canceled = 0 AND Enabled = 0 заменяется на Status = 1.
Здесь вводится целочисленный атрибут Status, обеспечиваемый заполнением этих статусов в таблице. Далее проводится индексирование этого нового атрибута.
Это фундаментальное решение проблемы производительности, ведь Мы обращаемся за данными без лишних вычислений.
3) Материализация представления
К сожалению, в LINQ-запросах нельзя напрямую использовать временные таблицы, CTE и табличные переменные.
Однако, есть еще один способ оптимизации на этот случай — это индексируемые представления.
Группа условий (из примера выше) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (или набор других схожих условий) становится хорошим вариантом для использования их в индексируемом представлении, кэшируя небольшой срез данных от большого множества.
Но есть ряд ограничений при материализации представления:
- использование подзапросов, предложений EXISTS должны заменяться использованием JOIN
- нельзя использовать предложения UNION, UNION ALL, EXCEPTION, INTERSECT
- нельзя использовать табличные хинты и предложения OPTION
- нет возможности работы с циклами
- невозможно выводить данные в одном представлении из разных таблиц
Важно помнить, что реальная польза от использования индексируемого представления может быть получена фактически только при его индексировании.
Но при вызове представления эти индексы могут не использоваться, а для явного их использования необходимо указывать WITH (NOEXPAND).
Поскольку в LINQ-запросах нельзя определять табличные хинты, так что приходится делать еще одно представление — «обертку» следующего вида:
CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);
4) Использование табличных функций
Часто в LINQ-запросах большие блоки подзапросов или блоки, использующие представления со сложной структурой, образуют конечный запрос с очень сложной и не оптимальной структурой выполнения.
Основные преимущества использования табличных функций в LINQ-запросах:
- Возможность, как и в случае с представлениями, использовать и указывать как объект, но можно передать набор входных параметров:
FROM FUNCTION(@param1, @param2 ...)
в итоге можно добиться гибкой выборки данных - В случае использования табличной функции нет таких сильных ограничений, как в случае с индексируемыми представлениями, описанных выше:
- Табличные хинты:
через LINQ нельзя указывать какие индексы необходимо использовать и определять уровень изоляции данных при запросе.
Но в функции эти возможности есть.
С функцией можно добиться достаточно постоянного плана запроса выполнения, где определенны правила работы с индексами и уровни изоляции данных - Использование функции позволяет, по сравнению с индексируемыми представлениями, получить:
- сложную логику выборки данных (вплоть до использования циклов)
- выборки данных из множества разных таблиц
- использование UNION и EXISTS
- Табличные хинты:
- Предложение OPTION очень полезно, когда нам надо обеспечить управление параллелизмом OPTION(MAXDOP N), порядком плана выполнения запроса. Например:
- можно указать принудительное пересоздание плана запроса OPTION (RECOMPILE)
- можно указать необходимость обеспечить принудительное использование планом запроса порядка соединения, указанного в запросе OPTION (FORCE ORDER)
Более детально про OPTION описано здесь.
- Использование самого узкого и требуемого среза данных:
Нет необходимости держать большие наборы данных в кэшах (как в случае с индексируемыми представлениями), из которых еще необходимо по параметру до фильтровать данные.
Например, есть таблица, у которой для фильтра WHERE используются три поля (a, b, c).
Условно для всех запросов есть постоянное условие a = 0 and b = 0.
Однако, запрос к полю c более вариативный.
Пусть условие a = 0 and b = 0 нам действительно помогает ограничить требуемый получаемый набор до тысяч записей, но условие по с нам сужает выборку до сотни записей.
Здесь табличная функция может оказаться более выигрышным вариантом.
Также табличная функция более предсказуема и постоянна по времени выполнения.
Примеры
Рассмотрим пример реализации на примере базы данных Questions.
Есть запрос SELECT, соединяющий в себе несколько таблиц и использующий одно представление (OperativeQuestions), в котором проверяется по email принадлежность (через EXISTS) к «Активным запросам»([OperativeQuestions]):
Запрос № 1
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL)
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));
Представление имеет довольно сложное строение: в нем есть соединения подзапросов и использование сортировка DISTINCT, которая в общем случае является достаточно ресурсоемкой операцией.
Выборка из OperativeQuestions порядка десяти тысяч записей.
Основная проблема этого запроса в том, что для записей из внешнего запроса выполняется внутренний подзапрос на представлении [OperativeQuestions], который должен для [Email] = @p__linq__0 нам ограничить выводимую выборку (через EXISTS) до сотен записей.
И может показаться, что подзапрос должен один раз рассчитать записи по [Email] = @p__linq__0, а потом эти пару сотен записей должны соединяться по Id c Questions, и запрос будет быстрым.
На самом же деле происходит последовательное соединение всех таблиц: и проверка соответствия Id Questions с Id из OperativeQuestions, и фильтрование по Email.
По сути запрос работает со всеми десятками тысяч записей OperativeQuestions, а ведь нужны только интересующие данные по Email.
Текст представления OperativeQuestions:
Запрос № 2
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM [dbo].Questions AS Q INNER JOIN
[dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id
OUTER APPLY
(SELECT 1 AS HasNoObjects
WHERE NOT EXISTS
(SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU
WHERE BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
[dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL
AND (BO.HasNoObjects = 1 OR
EXISTS (SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU INNER JOIN
[dbo].ObjectQuestions AS QBO
ON QBO.[Object_Id] =BOU.ObjectId
WHERE BOU.ProcessUserAccessId = BPU.Id
AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));
Исходный маппинг представления в DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
Исходный LINQ-запрос
var businessObjectsData = await context
.OperativeQuestions
.Where(x => x.Email == Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
В данном конкретном случае рассматривается решение данной проблемы без инфраструктурных изменений, без введения отдельной таблицы с готовыми результатами («Активные запросы»), под которую необходим был бы механизм наполнения ее данными и поддержания ее в актуальном состоянии.
Хотя это и хорошее решение, есть и другой вариант оптимизации данной задачи.
Основная цель — закэшировать записи по [Email] = @p__linq__0 из представления OperativeQuestions.
Вводим табличную функцию [dbo].[OperativeQuestionsUserMail] в базу данных.
Отправляя как входной параметр Email, получаем обратно таблицу значений:
Запрос № 3
CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
@Email nvarchar(4000)
)
RETURNS
@tbl TABLE
(
[Id] uniqueidentifier,
[Email] nvarchar(4000)
)
AS
BEGIN
INSERT INTO @tbl ([Id], [Email])
SELECT Id, @Email
FROM [OperativeQuestions] AS [x] WHERE [x].[Email] = @Email;
RETURN;
END
Здесь возвращается таблица значений с заранее определенной структурой данных.
Чтобы запросы к OperativeQuestionsUserMail были оптимальны, имели оптимальные планы запросов, необходима строгая структура, а не RETURNS TABLE AS RETURN…
В данном случае искомый Запрос 1 преобразуется в Запрос 4:
Запрос № 4
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);
Маппинг представления и функции в DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
public static class FromSqlQueries
{
public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
=> source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}
Итоговый LINQ-запрос
var businessObjectsData = await context
.OperativeQuestions
.GetByUserEmail(Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
Порядок времени выполнения понизился с 200-800 мс, до 2-20 мс., и т. д., т е в десятки раз быстрее.
Если более усреднено брать, то вместо 350 мс получили 8 мс.
Из очевидных плюсов также получаем:
- общее снижение нагрузки по чтением,
- значительное уменьшение вероятности блокировок
- уменьшение среднего времени блокировки до приемлемых значений
Вывод
Оптимизация и тонкая настройка обращений к БД MS SQL через LINQ является задачей, которую можно решить.
В данной работе очень важны внимательность и последовательность.
В начале процесса:
- необходимо проверить данные, с которыми работает запрос (значения, выбранные типы данных)
- провести правильное индексирование этих данных
- проверить корректность соединительных условий между таблицами
На следующей итерации оптимизации выявляются:
- основа запроса и определяется основной фильтр запроса
- повторяющиеся схожие блоки запроса и анализируется пересечение условий
- в SSMS или другом GUI для SQL Server оптимизируется сам SQL-запрос (выделение промежуточного хранилища данных, построение результирующего запроса с использованием этого хранилища (может быть несколько))
- на последнем этапе, беря за основу результирующий SQL-запрос, перестраивается структура LINQ-запроса
В итоге получившийся LINQ-запрос должен стать по структуре идентичным выявленному оптимальному SQL-запросу из пункта 3.
Благодарности
Огромное спасибо коллегам jobgemws и alex_ozr из компании Fortis за помощь в подготовке данного материала.
Illivion
Спасибо за полезную информацию! Правда из своего опыта работы с БД из .NET, используя ORM пришел к выводу, что проще и эффективнее либо писать чистый SQL с маппингом результатов (Dapper, EF .SqlQuery), либо организовывать хранилище данных таким образом, чтобы для «боевых» выборок никогда не требовалось бы что-то сложнее простого select + where и несложных group by.
Sing
Расскажите, как обновляете БД? Пишете всю раскладку со сравнениями изменённых данных для всего графа?
Illivion
Немного не понял вопрос. Если вы о об изменении данных в таблицах, то зачастую все эти операции можно доверить ORM. Простые insert, update by id транслируются в адекватный SQL. Сложных запросов, даже типа 'update where', где условие включает в себя нечто большее чем match по индексируемому полю я так же стараюсь избегать. Вообще сейчас, когда есть возможность использовать объемные и надежные СХД все больше update становятся insert (event sourcing), а о delete лучше вообще забыть.
Если про схему, то механизм миграций в EF — отличная вещь. Если чего-то не хватает в штатном генераторе — всегда можно дописать SQL в миграции. Если хочется отказаться от EF, то нужен отдельный скрипт под VCS, позволяющий создать/обновить базу. Хорошо, если в таком случае ведением таких скриптов будет заниматься отдельный сотрудник, который в случае чего сможет и написать скрипт под сложную миграцию базы с данными.
Sing
Да, я про изменение данных.
Ясно, по первому комментарию решил, что вы отказались от полноценной ORM и перешли на чистый SQL, а вы используете сразу и то, и то.