- PVSM.RU - https://www.pvsm.ru -
В этой статье [1] рассматривались некоторые методы оптимизации LINQ-запросов.
Здесь же приведем еще некоторые подходы по оптимизации кода, связанные с LINQ-запросами.
Известно, что LINQ(Language-Integrated Query) — это простой и удобный язык запросов к источнику данных.
А LINQ to SQL является технологией доступа к данным в СУБД. Это мощный инструмент работы с данными, где через декларативный язык конструируются запросы, которые затем будут преобразовываться в SQL-запросы платформой и отправляться на сервер баз данных уже на выполнение. В нашем случае под СУБД будем понимать MS SQL Server.
Однако, LINQ-запросы не преобразовываются в оптимально написанные SQL-запросы, которые смог бы написать опытный DBA со всеми нюансами оптимизации SQL-запросов:
Основными узкими местами производительности получающихся SQL-запросов при компиляции LINQ-запросов являются:
Теперь перейдем непосредственно к методам оптимизации.
Лучше всего рассматривать фильтры на основных таблицах выборки, поскольку очень часто весь запрос строится вокруг одной-двух основных таблиц (заявки-люди-операции) и со стандартным набором условий (IsClosed, Canceled, Enabled, Status). Важно для выявленных выборок создать соответствующие индексы.
Данное решение имеет смысл, когда выбор по этим полям существенно ограничивает возвращаемое множество запросом.
Например, у нас есть 500000 заявок. Однако, активных заявок всего 2000 записей. Тогда правильно подобранный индекс избавит нас от INDEX SCAN по большой таблице и позволит быстро выбрать данные через некластеризованный индекс.
Также нехватку индексов можно выявить через подсказки разбора планов запросов или сбора статиcтик системных представлений MS SQL Server:
Все данные представления содержат сведения об отсутствующих индексах, за исключением пространственных индексов.
Однако, индексы и кэширование часто являются методами борьбы последствий плохо написанных LINQ-запросов и SQL-запросов.
Как показывает суровая практика жизни для бизнеса часто важна реализация бизнес-фичей к определенным срокам. И потому часто тяжелые запросы переводят в фоновый режим с кэшированием.
Отчасти это оправдано, так как пользователю не всегда нужны самые свежие данные и происходит приемлемый уровень отклика пользовательского интерфейса.
Данный подход позволяет решать запросы бизнеса, но понижает в итоге работоспособность информационной системы, просто отсрочивая решения проблем.
Также стоит помнить о том, что в процессе поиска необходимых для добавления новых индексов, предложения MS SQL по оптимизации могут быть некорректными в том числе при следующих условиях:
Иногда некоторые поля из одной таблицы, по которым происходит группа условий, можно заменить введением одного нового поля.
Особенно это актуально для полей-состояний, которые по типу обычно являются либо битовыми, либо целочисленными.
Пример:
IsClosed = 0 AND Canceled = 0 AND Enabled = 0 заменяется на Status = 1.
Здесь вводится целочисленный атрибут Status, обеспечиваемый заполнением этих статусов в таблице. Далее проводится индексирование этого нового атрибута.
Это фундаментальное решение проблемы производительности, ведь Мы обращаемся за данными без лишних вычислений.
К сожалению, в LINQ-запросах нельзя напрямую использовать временные таблицы, CTE и табличные переменные.
Однако, есть еще один способ оптимизации на этот случай — это индексируемые представления.
Группа условий (из примера выше) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (или набор других схожих условий) становится хорошим вариантом для использования их в индексируемом представлении, кэшируя небольшой срез данных от большого множества.
Но есть ряд ограничений при материализации представления:
Важно помнить, что реальная польза от использования индексируемого представления может быть получена фактически только при его индексировании.
Но при вызове представления эти индексы могут не использоваться, а для явного их использования необходимо указывать WITH (NOEXPAND).
Поскольку в LINQ-запросах нельзя определять табличные хинты, так что приходится делать еще одно представление — «обертку» следующего вида:
CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);
Часто в LINQ-запросах большие блоки подзапросов или блоки, использующие представления со сложной структурой, образуют конечный запрос с очень сложной и не оптимальной структурой выполнения.
Основные преимущества использования табличных функций в LINQ-запросах:
Более детально про OPTION описано здесь [6].
Условно для всех запросов есть постоянное условие a = 0 and b = 0.
Однако, запрос к полю c более вариативный.
Пусть условие a = 0 and b = 0 нам действительно помогает ограничить требуемый получаемый набор до тысяч записей, но условие по с нам сужает выборку до сотни записей.
Здесь табличная функция может оказаться более выигрышным вариантом.
Также табличная функция более предсказуема и постоянна по времени выполнения.
Рассмотрим пример реализации на примере базы данных Questions.
Есть запрос SELECT, соединяющий в себе несколько таблиц и использующий одно представление (OperativeQuestions), в котором проверяется по email принадлежность (через EXISTS) к «Активным запросам»([OperativeQuestions]):
(@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:
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));
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
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, получаем обратно таблицу значений:
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:
(@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]);
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})");
}
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 является задачей, которую можно решить.
В данной работе очень важны внимательность и последовательность.
В начале процесса:
На следующей итерации оптимизации выявляются:
В итоге получившийся LINQ-запрос должен стать по структуре идентичным выявленному оптимальному SQL-запросу из пункта 3.
Огромное спасибо коллегам jobgemws [7] и alex_ozr [8] из компании Fortis за помощь в подготовке данного материала.
Автор: BP1988
Источник [9]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/c-2/347251
Ссылки в тексте:
[1] этой статье: https://habr.com/ru/post/459716/
[2] sys.dm_db_missing_index_groups: https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-groups-transact-sql?view=sql-server-ver15
[3] sys.dm_db_missing_index_group_stats: https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-ver15
[4] sys.dm_db_missing_index_details: https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql?view=sql-server-ver15
[5] здесь: https://docs.microsoft.com/ru-ru/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15
[6] здесь: https://docs.microsoft.com/ru-ru/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver15
[7] jobgemws: https://habr.com/ru/users/jobgemws/
[8] alex_ozr: https://habr.com/ru/users/alex_ozr/
[9] Источник: https://habr.com/ru/post/489226/?utm_campaign=489226&utm_source=habrahabr&utm_medium=rss
Нажмите здесь для печати.