🥇Методи оптимізації LINQ-запитів у C#.NET

Запровадження

В цієї статті розглядалися деякі методи оптимізації LINQ-запитів.
Тут же наведемо ще деякі підходи щодо оптимізації коду, пов'язані з LINQ-запитами.

Відомо, що LINQ(Language-Integrated Query) - це проста та зручна мова запитів до джерела даних.

А LINQ до SQL є технологією доступу до даних у СУБД. Це потужний інструмент роботи з даними, де через декларативну мову конструюються запити, які потім перетворюватимуться на SQL-запити платформою та відправлятися на сервер баз даних вже на виконання. У нашому випадку під СУБД розумітимемо MS SQL Server.

Однак, LINQ-запити не перетворюються на оптимально написані SQL-запити, які зміг би написати досвідчений DBA з усіма нюансами оптимізації SQL-запитів:

оптимальні з'єднання (РЕЄСТРАЦІЯ) та фільтрування результатів (ДЕ)
безліч нюансів у використанні сполук та групових умов
безліч варіацій у заміні умов IN на ІСНУЄТЬСЯи НЕ В, <> на ІСНУЄТЬСЯ
проміжне кешування результатів через тимчасові таблиці, CTE, табличні змінні
використання пропозиції (ВАРІАНТ) із вказівками та табличних хінтів З (...)
використання індексованих уявлень, як один із засобів позбавитися надмірних читань даних при вибірках

Основними вузькими місцями продуктивності виходять SQL-запитів при компіляції LINQ-запитів є:

консолідація всього механізму відбору даних в одному запиті
дублювання ідентичних блоків коду, що в результаті призводить до багаторазових зайвих читань даних
групи багатоскладових умов (логічних «і» та «або») І и OR, поєднуючись у складні умови, призводить до того, що оптимізатор, маючи відповідні некластеризовані індекси, за необхідними полями, зрештою все ж таки починає робити сканування за кластерним індексом (ІНДЕКС -СКАН) за групами умов
глибока вкладеність підзапитів робить дуже проблематичним розбір. SQL-інструкцій та розбір плану запитів з боку розробників та DBA

Методи оптимізації

Тепер перейдемо безпосередньо до методів оптимізації.

1) Додаткове індексування

Найкраще розглядати фільтри на основних таблицях вибірки, оскільки дуже часто весь запит будується навколо однієї-двох основних таблиць (заявки-люди-операції) та зі стандартним набором умов (IsClosed, Canceled, Enabled, Status). Важливо для виявлених вибірок створити відповідні індекси.

Дане рішення має сенс, коли вибір по цих полях істотно обмежує повертається безліч запитом.

Наприклад, у нас є 500000 2000 заявок. Проте, активних заявок лише XNUMX записів. Тоді правильно підібраний індекс позбавить нас від ІНДЕКС -СКАН по великій таблиці та дозволить швидко вибрати дані через некластеризований індекс.

Також брак індексів можна виявити через підказки розбору планів запитів або збору статистики системних уявлень MS SQL Server:

Всі дані подання містять відомості про відсутні індекси, за винятком просторових індексів.

Однак, індекси та кешування часто є методами боротьби наслідків погано написаних LINQ-запитів и SQL-запитів.

Як показує сувора практика життя для бізнесу, часто важлива реалізація бізнес-фічів до певних термінів. І тому часто важкі запити переводять у фоновий режим із кешуванням.

Частково це виправдано, тому що користувачу не завжди потрібні найсвіжіші дані і відбувається прийнятний рівень відгуку інтерфейсу користувача.

Цей підхід дозволяє вирішувати запити бізнесу, але знижує у результаті працездатність інформаційної системи, просто відстрочуючи вирішення проблем.

Також варто пам'ятати про те, що в процесі пошуку необхідних для додавання нових індексів пропозиції MS SQL з оптимізації можуть бути некоректними у тому числі за таких умов:

якщо вже існують індекси з подібним набором полів
якщо поля в таблиці не можуть бути проіндексовані через обмеження індексування (детальніше про це описано тут).

2) Об'єднання атрибутів в один новий атрибут

Іноді деякі поля з однієї таблиці, за якими відбувається група умов, можна замінити запровадженням одного нового поля.

Особливо це актуально для полів-станів, які на кшталт зазвичай є або бітовими, або цілими.

Приклад:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 замінюється на Статус = 1.

Тут вводиться цілий атрибут Status, що забезпечується заповненням цих статусів у таблиці. Далі проводиться індексування нового атрибута.

Це фундаментальне вирішення проблеми продуктивності, адже ми звертаємося за даними без зайвих обчислень.

3) Матеріалізація уявлення

На жаль, у LINQ-запитах не можна безпосередньо використовувати часові таблиці, CTE та табличні змінні.

Однак, є ще один спосіб оптимізації на цей випадок — це уявлення, що індексуються.

Група умов (на прикладі вище) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (або набір інших схожих умов) стає хорошим варіантом для використання їх в поданні, що індексується, кешуючи невеликий зріз даних від великої множини.

Але є низка обмежень при матеріалізації уявлення:

використання підзапитів, пропозицій ІСНУЄТЬСЯ повинні замінюватись використанням РЕЄСТРАЦІЯ
не можна використовувати пропозиції СОЮЗ, UNION ALL, ИСКЛЮЧЕНИЕ, ІНТЕРСЕКТ
не можна використовувати табличні хінти та пропозиції ВАРІАНТ
немає можливості роботи з циклами
неможливо виводити дані в одному поданні з різних таблиць

Важливо пам'ятати, що реальна користь від використання уявлення, що індексується, може бути отримана фактично тільки при його індексуванні.

Але при виклику подання ці індекси можуть не використовуватись, а для явного їх використання необхідно вказувати WITH (NOEXPAND).

оскільки в LINQ-запитах не можна визначати табличні хінти, так що доводиться робити ще одне уявлення - «обгортку» наступного виду:

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Використання табличних функцій

Часто в LINQ-запитах великі блоки підзапитів або блоки, що використовують уявлення зі складною структурою, утворюють кінцевий запит з дуже складною і оптимальною структурою виконання.

Основні переваги використання табличних функцій у LINQ-запитах:

Можливість, як і у випадку з уявленнями, використовувати та вказувати як об'єкт, але можна передати набір вхідних параметрів:
FROM FUNCTION(@param1, @param2 …)
в результаті можна досягти гнучкої вибірки даних
У разі використання табличної функції немає таких сильних обмежень, як у випадку з індексованими уявленнями, описаними вище:
1. Табличні хінти:
  через LINQ не можна вказувати, які індекси необхідно використовувати і визначати рівень ізоляції даних при запиті.
  Але у функції ці можливості є.
  З функцією можна досягти достатньо постійного плану запиту виконання, де визначені правила роботи з індексами та рівні ізоляції даних
2. Використання функції дозволяє, порівняно з індексованими уявленнями, отримати:
  - складну логіку вибірки даних (до використання циклів)
  - вибірки даних з різних таблиць
  - використання СОЮЗ и ІСНУЄТЬСЯ
Пропозиція ВАРІАНТ дуже корисно, коли нам треба забезпечити керування паралелізмом OPTION(MAXDOP N), порядок плану виконання запиту. Наприклад:
- можна вказати примусове перестворення плану запиту OPTION (RECOMPILE)
- можна вказати на необхідність забезпечити примусове використання планом запиту порядку з'єднання, зазначеного у запиті OPTION (FORCE ORDER)
Більш детально про ВАРІАНТ описано тут.
Використання найвужчого та необхідного зрізу даних:
Немає необхідності тримати великі набори даних у кешах (як у випадку з уявленнями, що індексуються), з яких ще необхідно за параметром до фільтрувати дані.
Наприклад, є таблиця, яка має для фільтра ДЕ використовуються три поля (a, b, c).

Умовно для всіх запитів є постійна умова a = 0 і b = 0.

Однак, запит до поля c варіативніший.

Нехай умова a = 0 і b = 0 нам дійсно допомагає обмежити необхідний набір до тисяч записів, але умова по с нам звужує вибірку до сотні записів.

Тут таблична функція може бути більш виграшним варіантом.

Також таблична функція більш передбачувана та постійна за часом виконання.

Приклади

Розглянемо приклад реалізації з прикладу бази даних Questions.

Є запит ВИБІР, що з'єднує кілька таблиць і використовує одне уявлення (OperativeQuestions), у якому перевіряється по email приналежність (через ІСНУЄТЬСЯ) до «Активних запитів»([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], який повинен для [Email] = @p__linq__0 нам обмежити вибірку, що виводиться (через ІСНУЄТЬСЯ) до сотень записів.

І може здатися, що підзапит повинен один раз розрахувати записи [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 з компанії Фортіс за допомогу у підготовці даного матеріалу.

Джерело: habr.com

Методи оптимізації LINQ-запитів у C#.NET