🥇Метады аптымізацыі LINQ-запытаў у C#.NET

Увядзенне

В гэтым артыкуле разглядаліся некаторыя метады аптымізацыі LINQ-запытаў.
Тутака ж прывядзем яшчэ некаторыя падыходы па аптымізацыі кода, злучаныя з LINQ-запытамі.

Вядома, што LINQ(Language-Integrated Query) - гэта простая і зручная мова запытаў да крыніцы дадзеных.

А LINQ to SQL з'яўляецца тэхналогіяй доступу да дадзеных у СКБД. Гэта магутны інструмент працы з дадзенымі, дзе праз дэкларатыўны мову канструююцца запыты, якія затым будуць пераўтварацца ў SQL-запыты платформай і адпраўляцца на сервер баз даных ужо на выкананне. У нашым выпадку пад СКБД будзем разумець MS SQL Server.

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

аптымальныя злучэнні (РЭГІСТРАЦЫЯ) і фільтраванне вынікаў (ДЗЕ)
мноства нюансаў у выкарыстанні злучэнняў і групавых умоў
мноства варыяцый у замене ўмоў IN на EXISTSи НЕ Ў, <> на EXISTS
прамежкавае кэшаванне вынікаў праз часовыя табліцы, CTE, таблічныя зменныя
выкарыстанне прапановы (ВАРЫЯНТ) з указаннямі і таблічных хінтоў З (...)
выкарыстанне індэксаваных уяўленняў, як адно са сродкаў пазбавіцца ад залішніх чытанняў дадзеных пры выбарках

Асноўнымі вузкімі месцамі прадукцыйнасці якія атрымліваюцца SQL-запытаў пры кампіляцыі LINQ-запытаў з'яўляюцца:

кансалідацыя ўсяго механізму адбору дадзеных у адным запыце
дубляванне ідэнтычных блокаў кода, што ў выніку прыводзіць да шматразовых лішніх чытанняў дадзеных
групы шматскладовых умоў (лагічных "і" і "або") - І и OR, Злучаючыся ў складаныя ўмовы, прыводзіць да таго, што аптымізатар, маючы прыдатныя некластэрызаваныя індэксы, па неабходных палях, у канчатковым выніку ўсё ж пачынае рабіць сканаванне па кластарным індэксе (INDEX SCAN) па групах умоў
глыбокая ўкладзенасць подзапросов робіць вельмі праблематычным разбор SQL-інструкцый і разбор плана запытаў з боку распрацоўшчыкаў і адміністратар базы дадзеных

Метады аптымізацыі

Цяпер пяройдзем непасрэдна да метадаў аптымізацыі.

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

Лепш за ўсё разглядаць фільтры на асноўных табліцах выбаркі, паколькі вельмі часта ўвесь запыт будуецца вакол адной-двух асноўных табліц (заяўкі-людзі-аперацыі) і са стандартным наборам умоў (IsClosed, Canceled, Enabled, Status). Важна для выяўленых выбарак стварыць адпаведныя азначнікі.

Дадзенае рашэнне мае сэнс, калі выбар па гэтых палях істотна абмяжоўвае якое вяртаецца мноства запытам.

Напрыклад, у нас ёсць 500000 заявак. Аднак, актыўных заявак за ўсё 2000 запісаў. Тады правільна падабраны індэкс пазбавіць нас ад INDEX SCAN па вялікай табліцы і дазволіць хутка абраць дадзеныя праз некластэрызаваны азначнік.

Таксама недахоп індэксаў можна выявіць праз падказкі разбору планаў запытаў або збору статыстык сістэмных уяўленняў. 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 (або набор іншых падобных умоў) становіцца добрым варыянтам для выкарыстання іх у индексируемом уяўленні, кэшуючы невялікі зрэз дадзеных ад вялікага мноства.

Але ёсць шэраг абмежаванняў пры матэрыялізацыі ўяўлення:

выкарыстанне падзапытаў, прапаноў EXISTS павінны замяняцца выкарыстаннем РЭГІСТРАЦЫЯ
нельга выкарыстоўваць прапановы САЮЗ, UNION ALL, ВЫКЛЮЧЭННЕ, ІНТЭРКСТ
нельга выкарыстоўваць таблічныя хінты і прапановы ВАРЫЯНТ
няма магчымасці працы з цыкламі
немагчыма выводзіць дадзеныя ў адным паданні з розных табліц

Важна памятаць, што рэальная карысць ад выкарыстання індэксуемага прадстаўлення можа быць атрымана фактычна толькі пры яго індэксаванні.

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

паколькі ў LINQ-запытах нельга вызначаць таблічныя хінты, так што прыходзіцца рабіць яшчэ адно паданне - «абгортку» наступнага выгляду:

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

4) Выкарыстанне таблічных функцый

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

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

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

Умоўна для ўсіх запытаў ёсць пастаянная ўмова a = 0 and b = 0.

Аднак, запыт да поля c больш варыятыўны.

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

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

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

прыклады

Разгледзім прыклад рэалізацыі на прыкладзе базы даных Questions.

Ёсць запыт ВЫБАР, які злучае ў сабе некалькі табліц і выкарыстоўвае адно ўяўленне (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 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 з кампаніі Fortis за дапамогу ў падрыхтоўцы дадзенага матэрыялу.

Крыніца: habr.com

Метады аптымізацыі LINQ-запытаў у C#.NET