🥇 Методе за оптимизацију ЛИНК упита у Ц#.НЕТ

Увод

В Овај чланак разматране су неке методе оптимизације ЛИНК упити.
Овде представљамо још неке приступе оптимизацији кода у вези са ЛИНК упити.

Познато је да ЛИНК(Лангуаге-Интегратед Куери) је једноставан и згодан језик за испитивање извора података.

А ЛИНК на СКЛ је технологија за приступ подацима у ДБМС. Ово је моћан алат за рад са подацима, где се упити конструишу путем декларативног језика, који ће се затим конвертовати у СКЛ упити платформи и послат серверу базе података на извршење. У нашем случају, под ДБМС подразумевамо МС СКЛ Сервер.

Међутим, ЛИНК упити не претварају се у оптимално написане СКЛ упити, који би искусни ДБА могао да напише са свим нијансама оптимизације СКЛ упити:

оптималне везе (ЈОИН) и филтрирање резултата (ГДЕ)
многе нијансе у коришћењу веза и групних услова
многе варијације у замени услова IN на ПОСТОЈИи НЕ У, <> укључено ПОСТОЈИ
интермедијарно кеширање резултата преко привремених табела, ЦТЕ, променљивих табела
употреба реченице (ОПЦИЈА) са упутствима и саветима у табели Витх (...)
коришћење индексираних приказа као једног од начина да се ослободите сувишних очитавања података током селекције

Главна уска грла перформанси резултирају СКЛ упити при састављању ЛИНК упити су:

обједињавање целокупног механизма селекције података у једном захтеву
дуплирање идентичних блокова кода, што на крају доводи до вишеструког читања непотребних података
групе вишекомпонентних услова (логичко „и“ и „или“) - И и OR, комбиновањем у сложене услове, доводи до чињенице да оптимизатор, који има одговарајуће не-кластерисане индексе на потребним пољима, на крају почиње да скенира према груписаном индексу (ИНДЕКС СЦАН) по групама услова
дубоко угнежђење потупита чини рашчлањивање веома проблематичним СКЛ искази и анализа плана упита од стране програмера и ДБА

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

Сада пређимо директно на методе оптимизације.

1) Додатно индексирање

Најбоље је размотрити филтере на главним табелама селекције, пошто је врло често цео упит изграђен око једне или две главне табеле (апликације-људи-операције) и са стандардним скупом услова (ИсЦлосед, Цанцеллед, Енаблед, Статус). Важно је креирати одговарајуће индексе за идентификоване узорке.

Ово решење има смисла када селектовање ових поља значајно ограничава враћени скуп на упит.

На пример, имамо 500000 пријава. Међутим, постоји само 2000 активних апликација. Тада ће нас исправно одабран индекс спасити ИНДЕКС СЦАН на великој табели и омогућиће вам да брзо изаберете податке преко некластера индекса.

Такође, недостатак индекса се може идентификовати кроз упите за рашчлањивање планова упита или прикупљање статистике приказа система МС СКЛ Сервер:

Сви подаци приказа садрже информације о индексима који недостају, са изузетком просторних индекса.

Међутим, индекси и кеширање су често методе за борбу против последица лошег писања ЛИНК упити и СКЛ упити.

Као што показује сурова животна пракса, за посао је често важно да у одређеним роковима имплементира пословне карактеристике. Због тога се тешки захтеви често преносе у позадину уз кеширање.

Ово је делимично оправдано, јер кориснику нису увек потребни најновији подаци и постоји прихватљив ниво одзива корисничког интерфејса.

Овај приступ омогућава решавање пословних потреба, али на крају смањује перформансе информационог система једноставним одлагањем решења проблема.

Такође је вредно запамтити да у процесу тражења потребних индекса за додавање, предлози МС СКЛ оптимизација може бити нетачна, укључујући под следећим условима:

ако већ постоје индекси са сличним скупом поља
ако се поља у табели не могу индексирати због ограничења индексирања (описано детаљније овде).

2) Спајање атрибута у један нови атрибут

Понекад се нека поља из једне табеле, која служе као основа за групу услова, могу заменити увођењем једног новог поља.

Ово посебно важи за статусна поља, која су обично или битног или целобројног типа.

Пример:

Затворено = 0 И Отказано = 0 И Омогућено = 0 замењује се са Статус = 1.

Овде се уводи целобројни атрибут Статус да би се осигурало да су ови статуси попуњени у табели. Затим се овај нови атрибут индексира.

Ово је фундаментално решење за проблем перформанси, јер ми приступамо подацима без непотребних прорачуна.

3) Материјализација погледа

Нажалост, у ЛИНК упити Привремене табеле, ЦТЕ-ови и променљиве табеле се не могу директно користити.

Међутим, постоји још један начин оптимизације за овај случај - индексирани прикази.

Група услова (из примера изнад) Затворено = 0 И Отказано = 0 И Омогућено = 0 (или скуп других сличних услова) постаје добра опција за њихово коришћење у индексираном приказу, кеширајући мали део података из великог скупа.

Али постоје бројна ограничења када се материјализује поглед:

коришћење потупита, клаузула ПОСТОЈИ треба заменити коришћењем ЈОИН
не можете користити реченице УНИЈА, УНИОН АЛЛ, ИЗУЗЕТАК, ИНТЕРСЕЦТ
Не можете користити табеле наговештаја и клаузуле ОПЦИЈА
нема могућности за рад са циклусима
Немогуће је приказати податке у једном приказу из различитих табела

Важно је запамтити да се права корист од коришћења индексираног приказа може постићи само његовим стварним индексирањем.

Али када позивате поглед, ови индекси се можда неће користити, а да бисте их експлицитно користили, морате навести ВИТХ(НОЕКСПАНД).

Од у ЛИНК упити Немогуће је дефинисати наговештаје табеле, тако да морате да креирате још једну репрезентацију - „омотач“ следећег облика:

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

4) Коришћење функција табеле

Често у ЛИНК упити Велики блокови подупита или блокови који користе погледе са сложеном структуром формирају коначни упит са веома сложеном и субоптималном структуром извршења.

Кључне предности коришћења функција табеле у ЛИНК упити:

Могућност, као у случају погледа, да се користи и специфицира као објекат, али можете проследити скуп улазних параметара:
ФРОМ ФУНЦТИОН(@парам1, @парам2 ...)
Као резултат, може се постићи флексибилно узорковање података
У случају коришћења функције табеле, не постоје тако јака ограничења као у случају индексираних погледа описаних изнад:
1. Табеларни савети:
  кроз ЛИНК Не можете одредити које индексе треба користити и одредити ниво изолације података приликом постављања упита.
  Али функција има ове могућности.
  Помоћу ове функције можете постићи прилично константан план за извршење упита, где су дефинисана правила за рад са индексима и нивои изолације података
2. Коришћење функције омогућава, у поређењу са индексираним приказима, да се добије:
  - сложена логика узорковања података (чак и коришћењем петљи)
  - преузимање података из много различитих табела
  - употреба УНИЈА и ПОСТОЈИ
Понуда ОПЦИЈА веома корисно када треба да обезбедимо контролу истовремености ОПЦИЈА (МАКСДОП Н), редослед плана извршења упита. На пример:
- можете одредити присилно поновно креирање плана упита ОПЦИЈА (ПРЕКОМПИЛАЦИЈА)
- можете одредити да ли да присилите план упита да користи редослед придруживања наведен у упиту ОПЦИЈА (НАРУЧЕЊЕ ПРИСИЛНОМ)
Више детаља о ОПЦИЈА opisano овде.
Коришћење најужег и најпотребнијег дела података:
Нема потребе да складиштите велике скупове података у кеш меморије (као што је случај са индексираним приказима), из којих и даље треба да филтрирате податке по параметру.
На пример, постоји табела чији филтер ГДЕ користе се три поља (а, б, ц).

Конвенционално, сви захтеви имају сталан услов а = 0 и б = 0.

Међутим, захтев за терен c променљивији.

Нека услов а = 0 и б = 0 Заиста нам помаже да ограничимо тражени резултујући скуп на хиљаде записа, али услов је укључен с сужава избор на стотину записа.

Овде функција табеле може бити боља опција.

Такође, функција табеле је предвидљивија и доследнија у времену извршења.

Примери

Погледајмо пример имплементације користећи базу података Куестионс као пример.

Постоји захтев СЕЛЕЦТ, који комбинује неколико табела и користи један приказ (ОперативеКуестионс), у којем се припадност проверава путем е-поште (преко ПОСТОЈИ) до „Оперативна питања“:

Захтев бр.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])
));

Поглед има прилично сложену структуру: има спојеве подупита и користи сортирање ДИСТИНЦТ, што је генерално прилично интензивна операција.

Узорак из ОперативеКуестионс је око десет хиљада записа.

Главни проблем са овим упитом је тај што се за записе из спољашњег упита извршава унутрашњи потупит у приказу [ОперативеКуестионс], што би требало да нам за [Емаил] = @п__линк__0 омогући да ограничимо избор излаза (преко ПОСТОЈИ) до стотине записа.

И може се чинити да потупит треба да израчуна записе једном по [Емаил] = @п__линк__0, а затим ових пар стотина записа треба повезати помоћу ИД-а са питањима, и упит ће бити брз.

У ствари, постоји секвенцијална веза свих табела: провера кореспонденције Ид питања са ИД-ом из ОперативеКуестионс и филтрирање путем е-поште.

У ствари, захтев функционише са свим десетинама хиљада ОперативеКуестионс записа, али су потребни само подаци од интереса путем е-поште.

ОперативеКуестионс погледајте текст:

Захтев бр.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));

Почетно мапирање приказа у ДбЦонтект (ЕФ Цоре 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

Почетни ЛИНК упит

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();

У конкретном случају, разматрамо решење овог проблема без инфраструктурних промена, без увођења посебне табеле са готовим резултатима („Активни упити“), што би захтевало механизам за попуњавање подацима и ажурирање. .

Иако је ово добро решење, постоји још једна опција за оптимизацију овог проблема.

Главна сврха је кеширање уноса помоћу [Емаил] = @п__линк__0 из приказа ОперативеКуестионс.

Увести функцију табеле [дбо].[ОперативеКуестионсУсерМаил] у базу података.

Слањем е-поште као улазног параметра, добијамо табелу вредности:

Захтев бр.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

Ово враћа табелу вредности са унапред дефинисаном структуром података.

Да би упити за ОперативеКуестионсУсерМаил били оптимални и имали оптималне планове упита, потребна је строга структура, а не ТАБЕЛА ПОВРАТАКА КАО ПОВРАТАК...

У овом случају, тражени упит 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]);

Мапирање приказа и функција у ДбЦонтект (ЕФ Цоре 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})");
}

Коначни ЛИНК упит

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 мс.

Од очигледних предности добијамо и:

опште смањење оптерећења читањем,
значајно смањење вероватноће блокирања
смањење просечног времена блокирања на прихватљиве вредности

Излаз

Оптимизација и фино подешавање позива базе података МС СКЛ кроз ЛИНК је проблем који се може решити.

Пажња и доследност су веома важни у овом послу.

На почетку процеса:

потребно је проверити податке са којима ради захтев (вредности, изабрани типови података)
извршити одговарајуће индексирање ових података
проверити исправност услова спајања између табела

Следећа итерација оптимизације открива:

основу захтева и дефинише главни филтер захтева
понављање сличних блокова упита и анализирање пресека услова
у ССМС или другом ГУИ за СКЛ Сервер оптимизује себе СКЛ упит (додељивање средњег складишта података, прављење резултујућег упита користећи ово складиште (може их бити неколико))
у последњој фази, узимајући као основу добијену СКЛ упит, структура се обнавља ЛИНК упит

Резултати ЛИНК упит треба да по структури постане идентичан идентификованом оптималном СКЛ упит из тачке 3.

Захвалнице

Велико хвала колегама јобгемвс и алек_озр из компаније Фортис за помоћ у припреми овог материјала.

Извор: ввв.хабр.цом

Методе за оптимизацију ЛИНК упита у Ц#.НЕТ