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

Вовед

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

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

А LINQ на SQL е технологија за пристап до податоци во DBMS. Ова е моќна алатка за работа со податоци, каде што барањата се конструираат преку декларативен јазик, кој потоа ќе се конвертира во SQL прашања платформа и испратен до серверот на базата на податоци за извршување. Во нашиот случај, под DBMS мислиме MS SQL сервер.

Сепак, Прашања за LINQ не се претвораат во оптимално напишани SQL прашања, кој искусен DBA би можел да го напише со сите нијанси на оптимизација SQL прашања:

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

Главните перформанси тесни грла на добиената SQL прашања при составувањето Прашања за LINQ се:

консолидација на целиот механизам за избор на податоци во едно барање
дуплирање на идентични блокови од код, што на крајот доведува до повеќекратно непотребно читање на податоци
групи на повеќекомпонентни услови (логички „и“ и „или“) - И и OR, комбинирајќи се во сложени услови, доведува до фактот дека оптимизаторот, имајќи соодветни не-групирани индекси за потребните полиња, на крајот почнува да скенира во однос на групираниот индекс (СКЕНИРАЊЕ НА ИНДЕКС) по групи услови
длабокото вгнездување на подпрашања го прави парсирањето многу проблематично SQL изјави и анализа на планот за барање од страна на програмерите и Велика Британија

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

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

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

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

Ова решение има смисла кога изборот на овие полиња значително го ограничува вратениот сет на барањето.

На пример, имаме 500000 апликации. Сепак, има само 2000 активни апликации. Тогаш правилно избраниот индекс ќе не спаси од СКЕНИРАЊЕ НА ИНДЕКС на голема табела и ќе ви овозможи брзо избирање податоци преку индекс што не е кластер.

Исто така, недостатокот на индекси може да се идентификува преку потсетници за парсирање на планови за пребарување или собирање статистика за преглед на системот MS SQL сервер:

Сите податоци за прегледот содржат информации за индексите што недостасуваат, со исклучок на просторните индекси.

Сепак, индексите и кеширањето често се методи за борба против последиците од лошо напишаното Прашања за LINQ и SQL прашања.

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

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

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

Исто така, вреди да се запамети дека во процесот на пребарување на потребните индекси за додавање, предлози MS SQL оптимизацијата може да биде неточна, вклучително и под следниве услови:

ако веќе има индекси со сличен сет на полиња
ако полињата во табелата не можат да се индексираат поради ограничувањата за индексирање (подетално опишани тука).

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

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

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

Пример:

Дали е затворено = 0 И откажано = 0 И овозможено = 0 се заменува со Статус = 1.

Овде се воведува атрибутот Цел број Статус за да се осигура дека овие статуси се пополнети во табелата. Следно, овој нов атрибут е индексиран.

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

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

За жал, во Прашања за LINQ Привремените табели, CTE и променливите на табелата не можат директно да се користат.

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

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

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

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

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

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

Од во Прашања за LINQ Невозможно е да се дефинираат совети за табели, па затоа треба да креирате друга претстава - „обвивка“ од следнава форма:

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

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

Често во Прашања за LINQ Големи блокови од подпрашања или блокови кои користат погледи со сложена структура формираат финално барање со многу сложена и неоптимална структура за извршување.

Главните придобивки од користењето на функциите на табелата во Прашања за LINQ:

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

Конвенционално, сите барања имаат постојана состојба a = 0 и b = 0.

Сепак, барањето за терен c повеќе променлива.

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

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

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

примери

Ајде да погледнеме пример за имплементација користејќи ја базата на податоци Прашања како пример.

Постои барање ИЗБЕРЕТЕ, кој комбинира неколку табели и користи еден поглед (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])
));

Погледот има прилично сложена структура: има подпрашачки спојувања и користи сортирање РАЗЛИЧНИ, што генерално е операција која бара прилично ресурси.

Примерок од OperativeQuestions е околу десет илјади записи.

Главниот проблем со ова барање е што за записите од надворешното барање, внатрешно подпрашање се извршува на приказот [OperativeQuestions], што треба за [Email] = @p__linq__0 да ни овозможи да го ограничиме изборниот излез (преку ПОСТОИ) до стотици записи.

И може да изгледа дека подпрашањето треба да ги пресмета записите еднаш со [Email] = @p__linq__0, а потоа овие неколку стотини записи треба да се поврзат со Id со Прашања и барањето ќе биде брзо.

Всушност, постои секвенцијално поврзување на сите табели: проверка на кореспонденцијата на Id Questions со Id од 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 преку ЛИНК е проблем што може да се реши.

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

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

потребно е да се проверат податоците со кои работи барањето (вредности, избрани типови на податоци)
спроведете соодветно индексирање на овие податоци
проверете ја исправноста на условите за спојување помеѓу табелите

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

основа на барањето и го дефинира главниот филтер за барање
повторување на слични блокови за пребарување и анализа на пресекот на условите
во SSMS или друг GUI за SQL Server, се оптимизира SQL барање (доделување на средно складирање податоци, градење на добиеното барање користејќи го ова складирање (може да има неколку))
во последната фаза, земајќи ги како основа добиените SQL барање, конструкцијата се обновува LINQ барање

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

Благодарници

Голема благодарност до колегите jobgemws и alex_ozr од компанијата Фортис за помош при подготовката на овој материјал.

Извор: www.habr.com

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