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

въведение

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

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

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

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

оптимални връзки (ПРИСЪЕДИНЕТЕ СЕ КЪМ) и филтриране на резултатите (КЪДЕ)
много нюанси при използването на връзки и групови условия
много вариации в условията на замяна IN на СЪЩЕСТВУВАи НЕ В, <> включено СЪЩЕСТВУВА
междинно кеширане на резултати чрез временни таблици, CTE, променливи на таблици
използване на изречение (ОПЦИЯ) с инструкции и таблични съвети С (...)
използване на индексирани изгледи като едно от средствата за премахване на излишните показания на данни по време на селекции

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

консолидиране на целия механизъм за избор на данни в една заявка
дублиране на идентични блокове код, което в крайна сметка води до множество ненужни четения на данни
групи от многокомпонентни условия (логически „и“ и „или“) - И и OR, комбинирайки се в сложни условия, води до факта, че оптимизаторът, имащ подходящи неклъстерирани индекси за необходимите полета, в крайна сметка започва да сканира срещу клъстерирания индекс (ИНДЕКС СКАНИРАНЕ) по групи условия
дълбокото влагане на подзаявки прави анализирането много проблематично SQL изрази и анализ на плана за заявка от страна на разработчиците и DBA

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

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

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

Най-добре е да обмислите филтри в основните таблици за избор, тъй като много често цялата заявка е изградена около една или две основни таблици (приложения-хора-операции) и със стандартен набор от условия (Затворено е, Отменено, Активирано, Състояние). Важно е да се създадат подходящи индекси за идентифицираните проби.

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

Например имаме 500000 2000 приложения. Има обаче само XNUMX активни приложения. Тогава правилно избран индекс ще ни спаси от ИНДЕКС СКАНИРАНЕ на голяма таблица и ще ви позволи бързо да изберете данни чрез неклъстъриран индекс.

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

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

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

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

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

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

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

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

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

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

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

Пример:

IsClosed = 0 И Отменено = 0 И Активирано = 0 се заменя с Състояние = 1.

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

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

3) Материализиране на гледката

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

Има обаче и друг начин за оптимизация за този случай - индексирани изгледи.

Група условия (от горния пример) IsClosed = 0 И Отменено = 0 И Активирано = 0 (или набор от други подобни условия) се превръща в добър вариант да ги използвате в индексиран изглед, кеширайки малка част от данни от голям набор.

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

използване на подзапитвания, клаузи СЪЩЕСТВУВА трябва да се замени с използване ПРИСЪЕДИНЕТЕ СЕ КЪМ
не можете да използвате изречения СЪЮЗ, СЪЮЗ ВСИЧКИ, ИЗКЛЮЧЕНИЕ, ПРЕКРЕСТИ
Не можете да използвате таблични съвети и клаузи ОПЦИЯ
няма възможност за работа с цикли
Невъзможно е да се показват данни в един изглед от различни таблици

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

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

Тъй като през LINQ заявки Невъзможно е да се дефинират съвети за таблица, така че трябва да създадете друго представяне - „обвивка“ със следната форма:

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

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

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

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

Възможността, както в случая с изгледите, да се използва и указва като обект, но можете да подадете набор от входни параметри:
ОТ ФУНКЦИЯ(@param1, @param2 ...)
В резултат на това може да се постигне гъвкаво вземане на проби от данни
В случай на използване на таблична функция няма толкова строги ограничения, както в случая на индексирани изгледи, описани по-горе:
1. Съвети за таблицата:
  през LINQ Не можете да посочите кои индекси да се използват и да определите нивото на изолация на данните при заявка.
  Но функцията има тези възможности.
  С функцията можете да постигнете сравнително постоянен план за заявка за изпълнение, където се дефинират правила за работа с индекси и нива на изолация на данни
2. Използването на функцията позволява, в сравнение с индексираните изгледи, да се получи:
  - сложна логика за вземане на проби от данни (дори при използване на цикли)
  - извличане на данни от много различни таблици
  - използването на СЪЮЗ и СЪЩЕСТВУВА
оферта ОПЦИЯ много полезно, когато трябва да осигурим контрол на паралелността ОПЦИЯ (MAXDOP N), реда на плана за изпълнение на заявката. Например:
- можете да зададете принудително повторно създаване на плана на заявката ОПЦИЯ (ПРЕКОМПИЛИРАНЕ)
- можете да посочите дали да принудите плана на заявката да използва реда на свързване, указан в заявката ОПЦИЯ (СИЛОВА ПОРЪЧКА)
Повече подробности за ОПЦИЯ описано тук.
Използване на най-тесния и най-необходим отрязък от данни:
Няма нужда да съхранявате големи набори от данни в кешове (какъвто е случаят с индексираните изгледи), от които все още трябва да филтрирате данните по параметър.
Например, има таблица, чийто филтър КЪДЕ се използват три полета (а, б, в).

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

Въпреки това, искането за полето c по-променлива.

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

Тук функцията таблица може да е по-добър вариант.

Също така табличната функция е по-предсказуема и последователна във времето за изпълнение.

Примеры

Нека разгледаме примерна реализация, използвайки базата данни Questions като пример.

Има молба МАРКИРАЙ, който комбинира няколко таблици и използва един изглед (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 да ни позволи да ограничим избора на изход (чрез СЪЩЕСТВУВА) до стотици записи.

И може да изглежда, че подзаявката трябва да изчисли записите веднъж чрез [Email] = @p__linq__0 и след това тези няколкостотин записа трябва да бъдат свързани чрез Id с Въпроси и заявката ще бъде бърза.

Всъщност има последователна връзка на всички таблици: проверка на съответствието на Id Questions с Id от OperativeQuestions и филтриране по имейл.

Всъщност заявката работи с всичките десетки хиляди записи на OperativeQuestions, но само данните, които представляват интерес, са необходими чрез имейл.

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] в базата данни.

Изпращайки имейл като входен параметър, получаваме обратно таблица със стойности:

Заявка №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 оптимални и да имат оптимални планове за заявки, е необходима строга структура, а не ТАБЛИЦА ЗА ВРЪЩАНЕ КАТО ВРЪЩАНЕ...

В този случай необходимата заявка 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 ms, на 2-20 ms и т.н., т.е. десетки пъти по-бързо.

Ако го вземем по-средно, тогава вместо 350 ms получаваме 8 ms.

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

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

Продукция

Оптимизиране и фина настройка на извикванията на базата данни MS SQL през LINQ е проблем, който може да бъде решен.

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

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

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

Следващата оптимизационна итерация разкрива:

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

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

Признания

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

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

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