Методи за оптимизация на LINQ заявки в C#.NET

Въведение

В тази статия Разглеждали са някои методи за оптимизация заявки на LINQ.
Тук ще представим и други подходи за оптимизация на кода, свързани с заявките на LINQ.

Известно е, че LINQ(Language-Integrated Query) — това е прост и удобен език за заявки към източник на данни.

А LINQ to SQL е технология за достъп до данни в СУБД. Това е мощен инструмент за работа с данни, където чрез декларативен език се конструират заявки, които след това ще бъдат преобразувани в SQL-заявки платформата и изпратени на сървъра на базата данни за изпълнение. В нашия случай ще разбираме под СУБД MS SQL Server.

Но заявки на LINQ не се преобразуват в оптимално написани SQL-заявки, които би могъл да напише опитен DBA с всички нюанси на оптимизацията SQL-заявки:

1. оптимални съединения (JOIN) и филтриране на резултати (WHERE)
2. множество нюанси в използването на съединения и групови условия
3. множество вариации при замяната на условия IN на EXISTSи NOT IN, на EXISTS
4. промеждутъчно кеширане на резултатите чрез временни таблици, CTE, таблицови променливи
5. използване на предложението (OPTION) с указания и таблицови хинтове WITH (…)
6. използване на индексируеми представления, като един от средствата за избавяне от излишни четения на данни при извличане

Основните узки места на производителността при компилация SQL-заявки консолидиране на целия механизъм за извличане на данни в една заявка заявки на LINQ са:

1. дублиране на идентични блокове код, което води до многократни ненужни четения на данни
2. групи от сложни условия (логически „и“ и „или“) —
3. OR И и , свързвайки се в сложни условия, води до това, че оптимизаторът, разполагащ с подходящи некластеризирани индекси по необходимите полета, в крайна сметка все пак започва да прави сканиране по кластерен индекс (INDEX SCAN) по групи условиядълбоката вложеност на подзаявки прави много проблематично разбиране
4. SQL-инструкции и анализ на плана за заявки от страна на разработчиците и DBA Методи за оптимизация

Сега ще преминем директно към методите за оптимизация.

1) Допълнително индексиране

1) Допълнително индексиране

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

Това решение е разумно, когато селекцията по тези полета значително ограничава връщаното множество от запитването.

Например, имаме 500000 заявки. Въпреки това, активните заявки са само 2000 записа. В този случай, правилно подбраният индекс ще ни освободи от ) по групи условия голямата таблица и ще ни позволи бързо да извлечем данни чрез некластеризиран индекс.

Също така, липсата на индекси може да бъде идентифицирана чрез подсказки от анализа на плана на запитванията или чрез събиране на статистики от системните представления MS SQL Server:

1. sys.dm_db_missing_index_groups
2. sys.dm_db_missing_index_group_stats
3. sys.dm_db_missing_index_details

Всички данни в представленията съдържат информация за липсващи индекси, с изключение на пространствените индекси.

Въпреки това, индексите и кеширането често са методи за справяне с последиците от лошо написани заявки на LINQ и SQL-заявки.

Както показва суровата практика в бизнеса, често е важно реализирането на бизнес функции в определени срокове. И затова често тежки запитвания се поставят в режим на фоново изпълнение с кеширане.

Отчасти това е оправдано, тъй като на потребителя не винаги са му нужни най-актуалните данни, и се достига приемливо ниво на отговор на потребителския интерфейс.

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

Също така, е важно да се помни, че при търсенето на нови индекси, предложенията MS SQL за оптимизация могат да бъдат неточни, особено при следните условия:

1. ако вече съществуват индекси с подобен набор от полета
2. ако полетата в таблицата не могат да бъдат индексирани поради ограничения при индексите (по-подробно е описано тук.).

2) Обединение на атрибути в един нов атрибут

Понякога някои полета от една таблица, по които се базират групи условия, могат да бъдат заменени с въвеждането на едно ново поле.

Особено важно е за полета-състояния, които обикновено са или битови, или цели числа.

Пример:

IsClosed = 0 И Canceled = 0 И Enabled = 0 се заменя с Status = 1.

Тук се въвежда цяло число атрибут Status, осигурено чрез попълване на тези статути в таблицата. След това се извършва индексиране на този нов атрибут.

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

3) Материализиране на представление

За съжаление, в LINQ запитванията не могат да се използват директно временни таблици, CTE и таблични променливи.

Обаче, има и друг начин за оптимизация в този случай — индексируеми представления.

Група условия (от примера по-горе) IsClosed = 0 И Canceled = 0 И Enabled = 0 (или набор от други подобни условия) е добро решение за използване в индексируемо представление, кеширайки малък изрез от данни от голямо множество.

Но има редица ограничения при материализацията на представлението:

1. използване на подзапитвания, предложения EXISTS трябва да се заменят с използване на JOIN
2. не могат да се използват предложения UNION, UNION ALL, EXCEPTION, INTERSECT
3. не могат да се използват таблични подсказки и предложения OPTION
4. няма възможност за работа с цикли
5. невъзможно е да се извеждат данни в едно представление от различни таблици

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

Но при извикването на представлението тези индекси може да не се използват, а за явното им използване трябва да се посочи WITH (NOEXPAND).

Тъй като в LINQ запитванията не могат да се определят таблични подсказки, така че се налага да се направи още едно представление — 'обвивка' от следния вид:

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

4) Използване на таблични функции

Често в LINQ запитванията големи блокове подзапитвания или блокове, използващи представления със сложна структура, оформят крайно запитване с много сложна и не оптимална структура на изпълнение.

Основните предимства на използването на таблични функции в LINQ запитванията:

1. Възможността, както и в случая с представленията, да се използва и да се указва като обект, но може да се предаде набор от входящи параметри:
   FROM FUNCTION(@param1, @param2 …)
   в крайна сметка може да се постигне гъвкаво извлечение на данни
2. В случай на използване на таблична функция няма толкова строги ограничения, както в случая с индексируемите представления, описани по-горе:
   1. Таблични подсказки:
      чрез LINQ Невъзможно е да се задават кой индекси да се използват и да се определя нивото на изолация на данните при заявка.
      Но в функцията тези възможности са налични.
      С функцията може да се постигне достатъчно постоянен план за изпълнение на заявките, където са определени правилата за работа с индексите и нивата на изолация на данните.
   2. Използването на функция позволява, в сравнение с индексируемите представления, да се получи:
      • сложна логика за извличане на данни (включително използване на цикли)
      • извличане на данни от множество различни таблици
      • използване UNION и EXISTS

3. Предложение OPTION много полезно, когато трябва да осигурим управление на паралелизма OPTION(MAXDOP N), порядъка на плана за изпълнение на заявката. Например:
   • може да се зададе принудително възстановяване на плана за заявка OPTION (RECOMPILE)
   • може да се зададе необходимостта от принудително използване на плана за заявка в реда на свързване, посочен в заявката OPTION (FORCE ORDER)

   По-подробно за OPTION е описано тук..

4. Използването на най-тесния и необходим срез от данни:
   Няма нужда да се съхраняват големи набори от данни в кеша (както в случая с индексируемите представления), от които също трябва да се филтрират данни по параметър.
   Например, има таблица, за която за филтър WHERE се използват три полета (a, b, c).

   Условно за всички заявки има постоянно условие a = 0 и b = 0.

   Обаче, заявката към полето с е по-вариативна.

   Нека условието a = 0 и 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 с 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 OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().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)

публичен клас QuestionsDbContext : DbContext
{
    //...
    публично DbQuery<OperativeQuestion> ОперативниВъпроси { get; set; }
    //...
    защитенoverride void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
публичен статичен клас FromSqlQueries
{
    публично статично IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Итоговият LINQ-запрос

var businessObjectsData = await context
    .ОперативниВъпроси
    .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 мс.

От очевидните плюсове получаваме:

1. общо намаление на натоварването при четене,
2. значително намаляване на вероятността от блокирания
3. намаление на средното време за блокиране до приемливи стойности

Извод

Оптимизацията и прецизното настройване на достъпите до БД MS SQL чрез LINQ е задача, която може да се реши.

В тази работа много важни са вниманието и последователността.

В началото на процеса:

1. необходимо е да се проверят данните, с които работи запитването (стойности, избрани типове данни)
2. да се проведе правилна индексация на тези данни
3. да се провери коректността на условията за свързване между таблиците

На следващата итерация на оптимизацията се идентифицират:

1. основата на запитването и се определя основният филтър на запитването
2. повтарящи се сходни блокове на запитването и се анализират пресечените условия
3. в SSMS или друг GUI за SQL Server оптимизира се самият SQL-запитване (изолиране на междинно хранилище от данни, построяване на резултатно запитване с използване на това хранилище (може да има няколко))
4. на последния етап, вземайки за основа резултатния SQL-запитване, се преустроява структурата на LINQ-запитването

В крайна сметка полученото LINQ-запитване трябва да стане по структура идентично на идентифицираното оптимално SQL-запитване от точка 3.

Благодарности

Огромно благодаря на колегите jobgemws и alex_ozr от компанията Fortis за помощта при подготовката на този материал.

Източник: habr.com