Методи за оптимизация на 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. групи многосъставни условия (логически «и» и «или») — AND и OR, съединявайки се в сложни условия, води до това, че оптимизаторът, имайки подходящи неклъстеризовани индекси, по необходимите полета, в крайна сметка все пак започва да прави сканиране по кластерен индекс (INDEX SCAN) по групи условия
4. дълбоката вложеност на подзаявките прави много проблематично разшифроването SQL инструкции и разборът на плана за заявки от страна на разработчиците и DBA

Методи за оптимизация

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

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

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

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

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

Също така, недостигът на индекси може да бъде идентифициран чрез подсказки при анализ на плановете на запитвания или събиране на статистики от системни представления. 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) Мateralизация на представление

За съжаление, в 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 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 с 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)

публичен клас QuestionsDbContext : DbContext
{
    \/\/...
    публично DbQuery<OperativeQuestion> ОперативниВъпроси { получаване; задаване; }
    \/\/...
    защитен override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("ОперативниВъпроси");
    }
}

Изходен LINQ запит

var businessObjectsData = await context
    .ОперативниВъпроси
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => нов
                {
                    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 от представянето ОперативниВъпроси.

Въвеждаме табличната функция [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 [ОперативниВъпроси]  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 ОперативниВъпроси { get; set; }
    //...
    защитен изменение на void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("ОперативниВъпроси");
    }
}

публичен статичен клас FromSqlQueries
{
    публичен статичен IQueryable GetByUserEmail(тази DbQuery източник, низ Email)
        => източник.FromSql($"SELECT Id, Email FROM [dbo].[ОперативниВъпросиПотребителПоща] ({Email})");
}

Краен LINQ-запрос

var businessObjectsData = await context
    .ОперативниВъпроси
    .GetByUserEmail(Email)
    .Include(x => x.Въпрос)
    .Select(x => x.Въпрос)
    .SelectMany(x => x.ОбектВъпроси,
                (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