🥇Metódy na optimalizáciu LINQ dotazov v C#.NET

Úvod

В v tomto článku zvažovali sa niektoré optimalizačné metódy LINQ dotazy.
Tu uvádzame niekoľko ďalších prístupov k optimalizácii kódu LINQ dotazy.

Je známe, že LINQ(Language-Integrated Query) je jednoduchý a pohodlný jazyk na dopytovanie zdroja údajov.

А LINQ to SQL je technológia na prístup k údajom v DBMS. Ide o výkonný nástroj na prácu s údajmi, kde sa dopyty vytvárajú prostredníctvom deklaratívneho jazyka, ktorý sa potom prevedie na SQL dotazy platforme a odoslaný na databázový server na vykonanie. V našom prípade máme na mysli DBMS MS SQL Server.

Avšak, LINQ dotazy nie sú prevedené na optimálne napísané SQL dotazy, ktorý by skúsený DBA mohol napísať so všetkými nuansami optimalizácie SQL dotazy:

optimálne spojenia (REGISTRÁCIA) a filtrovanie výsledkov (KDE)
veľa nuancií pri používaní spojení a skupinových podmienok
mnoho variácií v podmienkach výmeny IN na EXISTUJEи NIE V, <> zapnuté EXISTUJE
prechodné ukladanie výsledkov do vyrovnávacej pamäte prostredníctvom dočasných tabuliek, CTE, tabuľkových premenných
použitie vety (OPTION) s pokynmi a tabuľkovými radami S (...)
používanie indexovaných zobrazení ako jedného z prostriedkov, ako sa zbaviť nadbytočných údajov počas výberov

Hlavné výkonnostné prekážky výsledného SQL dotazy pri zostavovaní LINQ dotazy Sú to:

konsolidácia celého mechanizmu výberu údajov v jednej žiadosti
duplikovanie identických blokov kódu, čo v konečnom dôsledku vedie k viacnásobnému zbytočnému čítaniu údajov
skupiny viaczložkových podmienok (logické „a“ a „alebo“) - A и ORskombinovaním do zložitých podmienok vedie k tomu, že optimalizátor, ktorý má vhodné nezhlukované indexy pre potrebné polia, nakoniec začne skenovať proti zhlukovanému indexu (INDEX SCAN) podľa skupín podmienok
hlboké vnorenie poddotazov robí analýzu veľmi problematickou SQL príkazy a analýza plánu dopytov zo strany vývojárov a DBA

Optimalizačné metódy

Teraz prejdime priamo k metódam optimalizácie.

1) Dodatočné indexovanie

Najlepšie je zvážiť filtre na hlavných výberových tabuľkách, pretože veľmi často je celý dotaz postavený na jednej alebo dvoch hlavných tabuľkách (aplikácie-ľudia-operácie) a so štandardnou sadou podmienok (IsClosed, Canceled, Enabled, Status). Je dôležité vytvoriť vhodné indexy pre identifikované vzorky.

Toto riešenie má zmysel, keď výber týchto polí výrazne obmedzuje vrátenú množinu dotazu.

Napríklad máme 500000 2000 žiadostí. Aktívnych aplikácií je však len XNUMX. Potom nás zachráni správne zvolený index INDEX SCAN na veľkej tabuľke a umožní vám rýchly výber údajov prostredníctvom indexu bez klastrov.

Nedostatok indexov možno identifikovať aj pomocou výziev na analýzu plánov dotazov alebo zhromažďovania štatistík systémového zobrazenia MS SQL Server:

Všetky údaje zobrazenia obsahujú informácie o chýbajúcich indexoch s výnimkou priestorových indexov.

Indexy a ukladanie do vyrovnávacej pamäte sú však často metódami boja proti následkom zle napísaného textu LINQ dotazy и SQL dotazy.

Ako ukazuje drsná životná prax, pre firmu je často dôležité implementovať obchodné funkcie do určitých termínov. Preto sa ťažké požiadavky často prenášajú na pozadie pomocou vyrovnávacej pamäte.

To je čiastočne opodstatnené, pretože používateľ nepotrebuje vždy najnovšie údaje a existuje prijateľná úroveň odozvy používateľského rozhrania.

Tento prístup umožňuje riešenie obchodných potrieb, no v konečnom dôsledku znižuje výkon informačného systému jednoduchým oddialením riešenia problémov.

Je tiež potrebné pripomenúť, že v procese hľadania potrebných indexov na pridanie návrhov MS SQL optimalizácia môže byť nesprávna, a to aj za nasledujúcich podmienok:

ak už existujú indexy s podobnou množinou polí
ak polia v tabuľke nemožno indexovať z dôvodu obmedzení indexovania (podrobnejšie popísané tu).

2) Zlúčenie atribútov do jedného nového atribútu

Niekedy je možné niektoré polia z jednej tabuľky, ktoré slúžia ako základ pre skupinu podmienok, nahradiť zavedením jedného nového poľa.

To platí najmä pre stavové polia, ktoré majú zvyčajne bitový alebo celočíselný typ.

Príklad:

IsClosed = 0 A Zrušené = 0 A Povolené = 0 sa nahrádza výrazom Stav = 1.

Tu je zavedený celočíselný atribút Status, aby sa zabezpečilo, že tieto stavy budú vyplnené v tabuľke. Ďalej sa tento nový atribút indexuje.

Toto je zásadné riešenie problému s výkonom, pretože k údajom pristupujeme bez zbytočných výpočtov.

3) Zhmotnenie pohľadu

Bohužiaľ v LINQ dotazy Dočasné tabuľky, CTE a premenné tabuľky nemožno použiť priamo.

Existuje však aj iný spôsob optimalizácie pre tento prípad – indexované zobrazenia.

Skupina podmienok (z vyššie uvedeného príkladu) IsClosed = 0 A Zrušené = 0 A Povolené = 0 (alebo množina iných podobných podmienok) sa stáva dobrou voľbou na ich použitie v indexovanom zobrazení, pričom sa do vyrovnávacej pamäte ukladá malá časť údajov z veľkej množiny.

Pri zhmotňovaní pohľadu však existuje niekoľko obmedzení:

použitie poddotazov, doložiek EXISTUJE by sa mali nahradiť používaním REGISTRÁCIA
nevieš použiť vety UNION, VŠETKO ÚNIA, EXCEPTION, PRIŤAŽTE sa
Nemôžete použiť tabuľky a vety OPTION
žiadna možnosť práce s cyklami
Nie je možné zobraziť údaje v jednom zobrazení z rôznych tabuliek

Je dôležité si zapamätať, že skutočný prínos používania indexovaného zobrazenia možno dosiahnuť iba jeho skutočným indexovaním.

Pri volaní zobrazenia však tieto indexy nemožno použiť a na ich explicitné použitie musíte zadať S (NOEXPAND).

Od r LINQ dotazy Nie je možné definovať rady tabuliek, takže musíte vytvoriť inú reprezentáciu - „obal“ nasledujúceho tvaru:

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

4) Používanie tabuľkových funkcií

Často v LINQ dotazy Veľké bloky poddotazov alebo bloky využívajúce pohľady so zložitou štruktúrou tvoria finálny dotaz s veľmi zložitou a suboptimálnou štruktúrou vykonávania.

Kľúčové výhody používania tabuľkových funkcií v LINQ dotazy:

Schopnosť, ako v prípade pohľadov, byť použitá a špecifikovaná ako objekt, ale môžete odovzdať sadu vstupných parametrov:
Z FUNKCIE(@param1, @param2 ...)
Výsledkom je flexibilné vzorkovanie údajov
V prípade použitia tabuľkovej funkcie neexistujú také silné obmedzenia ako v prípade indexovaných zobrazení popísaných vyššie:
1. Rady k tabuľke:
  cez LINQ Pri dotazovaní nemôžete určiť, ktoré indexy sa majú použiť, a určiť úroveň izolácie údajov.
  Ale funkcia má tieto schopnosti.
  Pomocou funkcie môžete dosiahnuť pomerne konštantný plán vykonávania dotazov, kde sú definované pravidlá pre prácu s indexmi a úrovňami izolácie údajov
2. Použitie funkcie umožňuje v porovnaní s indexovanými zobrazeniami získať:
  - komplexná logika vzorkovania údajov (aj pomocou slučiek)
  - získavanie údajov z mnohých rôznych tabuliek
  - použitie UNION и EXISTUJE
Návrh OPTION veľmi užitočné, keď potrebujeme zabezpečiť kontrolu súbežnosti MOŽNOSŤ (MAXDOP N), poradie plánu vykonávania dotazu. Napríklad:
- môžete zadať vynútené opätovné vytvorenie plánu dotazov MOŽNOSŤ (REKOMPILOVAŤ)
- môžete určiť, či má plán dotazov vynútiť použitie poradia spojenia špecifikovaného v dotaze MOŽNOSŤ (VNÚTIŤ OBJEDNÁVKU)
Viac podrobností o OPTION popísané tu.
Použitie najužšieho a najžiadanejšieho dátového segmentu:
Nie je potrebné ukladať veľké množiny údajov do vyrovnávacích pamätí (ako je to v prípade indexovaných pohľadov), z ktorých je stále potrebné filtrovať údaje podľa parametrov.
Napríklad existuje tabuľka, ktorá má filter pre KDE používajú sa tri polia (a, b, c).

Všetky požiadavky majú zvyčajne konštantnú podmienku a = 0 a b = 0.

Žiadosť však do poľa c variabilnejšie.

Nechajte podmienku a = 0 a b = 0 Naozaj nám pomáha obmedziť požadovanú výslednú množinu na tisíce záznamov, ale podmienka zapnutá с zužuje výber na sto záznamov.

Tu môže byť lepšou voľbou funkcia tabuľky.

Funkcia tabuľky je tiež predvídateľnejšia a konzistentnejšia v čase vykonávania.

príklady

Pozrime sa na príklad implementácie s použitím databázy Questions ako príkladu.

Existuje žiadosť SELECT, ktorý kombinuje viacero tabuliek a využíva jeden pohľad (OperativeQuestions), v ktorom sa príslušnosť kontroluje emailom (cez EXISTUJE) na „Operatívne otázky“:

Žiadosť č.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])
));

Pohľad má pomerne zložitú štruktúru: má spojenia poddotazov a používa triedenie DISTINCT, čo je vo všeobecnosti operácia pomerne náročná na zdroje.

Ukážka z OperativeQuestions je asi desaťtisíc záznamov.

Hlavným problémom tohto dotazu je, že pre záznamy z vonkajšieho dotazu sa vykoná interný poddotaz v zobrazení [OperativeQuestions], čo by nám pre [Email] = @p__linq__0 malo umožniť obmedziť výber výstupu (cez EXISTUJE) až stovky záznamov.

A môže sa zdať, že poddotaz by mal vypočítať záznamy raz pomocou [Email] = @p__linq__0 a potom by sa týchto pár stoviek záznamov malo spojiť pomocou Id s otázkami a dopyt bude rýchly.

V skutočnosti existuje sekvenčné prepojenie všetkých tabuliek: kontrola zhody Id otázok s Id z OperativeQuestions a filtrovanie e-mailom.

Žiadosť v skutočnosti funguje so všetkými desiatkami tisíc záznamov OperativeQuestions, ale prostredníctvom e-mailu sú potrebné iba údaje, ktoré vás zaujímajú.

Text zobrazenia Operatívnych otázok:

Žiadosť č.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));

Mapovanie počiatočného zobrazenia v 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");
    }
}

Počiatočný dopyt 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();

V tomto konkrétnom prípade zvažujeme riešenie tohto problému bez zmien v infraštruktúre, bez zavádzania samostatnej tabuľky s hotovými výsledkami („Aktívne dotazy“), čo by si vyžadovalo mechanizmus na jej napĺňanie údajmi a ich aktuálnosť. .

Aj keď je to dobré riešenie, existuje aj iná možnosť, ako tento problém optimalizovať.

Hlavným účelom je ukladať do vyrovnávacej pamäte položky [Email] = @p__linq__0 z pohľadu OperativeQuestions.

Zaveďte do databázy tabuľkovú funkciu [dbo].[OperativeQuestionsUserMail].

Odoslaním e-mailu ako vstupného parametra získame späť tabuľku hodnôt:

Žiadosť č.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

Toto vráti tabuľku hodnôt s preddefinovanou dátovou štruktúrou.

Aby boli dotazy na OperativeQuestionsUserMail optimálne a mali optimálne plány dotazov, vyžaduje sa prísna štruktúra a nie TABUĽKA VRÁTENIA AKO VRÁTENIE...

V tomto prípade sa požadovaný dotaz 1 skonvertuje na dotaz 4:

Žiadosť č.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]);

Mapovanie zobrazení a funkcií v 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})");
}

Posledný dopyt 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();

Poradie času vykonávania kleslo z 200-800 ms na 2-20 ms atď., t.j. desaťkrát rýchlejšie.

Ak to vezmeme priemernejšie, tak namiesto 350 ms nám vyšlo 8 ms.

Zo zrejmých výhod tiež získame:

všeobecné zníženie zaťaženia pri čítaní,
výrazné zníženie pravdepodobnosti zablokovania
zníženie priemerného času blokovania na prijateľné hodnoty

Výkon

Optimalizácia a dolaďovanie databázových volaní MS SQL cez LINQ je problém, ktorý sa dá vyriešiť.

Pri tejto práci je veľmi dôležitá pozornosť a dôslednosť.

Na začiatku procesu:

je potrebné skontrolovať údaje, s ktorými požiadavka pracuje (hodnoty, vybrané typy údajov)
vykonať správne indexovanie týchto údajov
skontrolujte správnosť podmienok spojenia medzi tabuľkami

Ďalšia iterácia optimalizácie odhalí:

základ požiadavky a definuje hlavný filter požiadaviek
opakovanie podobných blokov dopytov a analýza priesečníkov podmienok
v SSMS alebo inom GUI pre SQL Server sa sám optimalizuje SQL dotaz (pridelenie medziľahlého úložiska údajov, vytvorenie výsledného dotazu pomocou tohto úložiska (môže ich byť niekoľko))
v poslednej fáze, pričom sa vychádza z výsledného SQL dotaz, konštrukcia sa prestavuje LINQ dotaz

Výsledný LINQ dotaz by sa mali zhodovať v štruktúre s identifikovaným optimálnym SQL dotaz z bodu 3.

Poďakovanie

Veľká vďaka patrí kolegom jobgemws и alex_ozr od spoločnosti Fortis za pomoc pri príprave tohto materiálu.

Zdroj: hab.com

Metódy na optimalizáciu LINQ dotazov v C#.NET