🥇Metody pro optimalizaci LINQ dotazů v C#.NET

úvod

В tento článek byly zváženy některé optimalizační metody LINQ dotazy.
Zde také představujeme některé další související přístupy k optimalizaci kódu LINQ dotazy.

To je známo LINQ(Language-Integrated Query) je jednoduchý a pohodlný jazyk pro dotazování na zdroj dat.

А LINQ to SQL je technologie pro přístup k datům v DBMS. Jedná se o výkonný nástroj pro práci s daty, kde jsou dotazy konstruovány prostřednictvím deklarativního jazyka, který bude následně převeden na SQL dotazy platformě a odeslány na databázový server ke spuštění. V našem případě máme na mysli DBMS MS SQL Server.

Nicméně, LINQ dotazy nejsou převedeny na optimálně napsané SQL dotazy, který by zkušený DBA mohl napsat se všemi nuancemi optimalizace SQL dotazy:

optimální spojení (REGISTRACE) a filtrování výsledků (KDE)
mnoho nuancí při používání spojení a skupinových podmínek
mnoho variací v náhradních podmínkách IN na EXISTUJEи NE V, <> zapnuto EXISTUJE
mezipaměť výsledků prostřednictvím dočasných tabulek, CTE, tabulkových proměnných
použití věty (VOLBA) s pokyny a tabulkovými radami S (...)
použití indexovaných pohledů jako jednoho z prostředků, jak se zbavit nadbytečných čtení dat během výběrů

Hlavní překážky výkonu výsledného SQL dotazy při sestavování LINQ dotazy jsou:

konsolidace celého mechanismu výběru dat v jedné žádosti
duplikování identických bloků kódu, což nakonec vede k mnoha zbytečným čtením dat
skupiny vícesložkových podmínek (logické „a“ a „nebo“) - A AUTOMATIZACI и OR, kombinování do složitých podmínek, vede k tomu, že optimalizátor, který má vhodné neseskupené indexy pro potřebná pole, nakonec začne skenovat proti shlukovanému indexu (INDEX SCAN) podle skupin podmínek
hluboké vnoření poddotazů činí analýzu velmi problematickou SQL příkazy a analýza plánu dotazů ze strany vývojářů a DBA

Metody optimalizace

Nyní přejděme přímo k optimalizačním metodám.

1) Dodatečné indexování

Nejlepší je zvážit filtry na hlavních výběrových tabulkách, protože velmi často je celý dotaz postaven na jedné nebo dvou hlavních tabulkách (aplikace-lidé-operace) a se standardní sadou podmínek (IsClosed, Canceled, Enabled, Status). Je důležité vytvořit vhodné indexy pro identifikované vzorky.

Toto řešení má smysl, když výběr těchto polí výrazně omezuje vrácenou sadu dotazu.

Máme například 500000 2000 žádostí. Aktivních aplikací je však pouze XNUMX. Pak nás zachrání správně zvolený index INDEX SCAN na velké tabulce a umožní vám rychle vybrat data prostřednictvím indexu bez klastrů.

Nedostatek indexů lze také identifikovat pomocí výzev pro analýzu plánů dotazů nebo shromažďování statistik zobrazení systému MS SQL Server:

Všechna data zobrazení obsahují informace o chybějících indexech, s výjimkou prostorových indexů.

Indexy a ukládání do mezipaměti jsou však často metodami boje proti důsledkům špatného zápisu LINQ dotazy и SQL dotazy.

Jak ukazuje krutá praxe života, pro firmu je často důležité implementovat obchodní funkce do určitých termínů. A proto jsou těžké požadavky často přenášeny na pozadí pomocí ukládání do mezipaměti.

To je částečně oprávněné, protože uživatel ne vždy potřebuje nejnovější data a existuje přijatelná úroveň odezvy uživatelského rozhraní.

Tento přístup umožňuje řešení obchodních potřeb, ale v konečném důsledku snižuje výkon informačního systému pouhým oddalováním řešení problémů.

Je také třeba připomenout, že v procesu hledání potřebných indexů k přidání návrhy MS SQL optimalizace může být nesprávná, a to i za následujících podmínek:

pokud již existují indexy s podobnou sadou polí
pokud pole v tabulce nelze indexovat z důvodu omezení indexování (popsáno podrobněji zde).

2) Sloučení atributů do jednoho nového atributu

Někdy lze některá pole z jedné tabulky, která slouží jako základ pro skupinu podmínek, nahradit zavedením jednoho nového pole.

To platí zejména pro stavová pole, která mají obvykle bitový nebo celočíselný typ.

Příklad:

IsClosed = 0 A Zrušeno = 0 A Povoleno = 0 nahrazuje Stav = 1.

Zde je zaveden atribut Integer Status, aby bylo zajištěno, že tyto stavy budou v tabulce vyplněny. Dále je tento nový atribut indexován.

Toto je zásadní řešení problému s výkonem, protože k datům přistupujeme bez zbytečných výpočtů.

3) Zhmotnění pohledu

Bohužel v LINQ dotazy Dočasné tabulky, CTE a proměnné tabulky nelze použít přímo.

Existuje však další způsob optimalizace pro tento případ – indexovaná zobrazení.

Skupina podmínek (z výše uvedeného příkladu) IsClosed = 0 A Zrušeno = 0 A Povoleno = 0 (nebo sada jiných podobných podmínek) se stává dobrou volbou pro jejich použití v indexovaném zobrazení, přičemž se do mezipaměti ukládá malá část dat z velké sady.

Při zhmotňování pohledu však existuje řada omezení:

použití poddotazů, klauzulí EXISTUJE by měl být nahrazen použitím REGISTRACE
neumíš používat věty UNION, UNION ALL, VÝJIMKA, PROSÍT
Nemůžete používat tabulkové rady a klauzule VOLBA
žádná možnost práce s cykly
Není možné zobrazit data v jednom pohledu z různých tabulek

Je důležité si pamatovat, že skutečný přínos používání indexovaného zobrazení lze ve skutečnosti získat pouze jeho indexováním.

Při volání pohledu však tyto indexy nelze použít a chcete-li je použít explicitně, musíte je zadat S (NOEXPAND).

Od r LINQ dotazy Není možné definovat nápovědu tabulky, takže musíte vytvořit jinou reprezentaci – „obal“ v následujícím tvaru:

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

4) Použití tabulkových funkcí

Často v LINQ dotazy Velké bloky poddotazů nebo bloky využívající pohledy se složitou strukturou tvoří finální dotaz s velmi složitou a neoptimální prováděcí strukturou.

Klíčové výhody používání tabulkových funkcí v LINQ dotazy:

Schopnost, jako v případě pohledů, používat a specifikovat jako objekt, ale můžete předat sadu vstupních parametrů:
Z FUNKCE(@param1, @param2 ...)
V důsledku toho lze dosáhnout flexibilního vzorkování dat
V případě použití tabulkové funkce neexistují tak silná omezení jako v případě indexovaných pohledů popsaných výše:
1. Rady ke stolu:
  přes LINQ Při dotazování nemůžete určit, které indexy se mají použít, a určit úroveň izolace dat.
  Ale funkce má tyto schopnosti.
  Pomocí funkce můžete dosáhnout poměrně konstantního plánu dotazů na provádění, kde jsou definována pravidla pro práci s indexy a úrovněmi izolace dat
2. Použití funkce umožňuje ve srovnání s indexovanými pohledy získat:
  - komplexní logika vzorkování dat (i pomocí smyček)
  - načítání dat z mnoha různých tabulek
  - použití UNION и EXISTUJE
Návrh VOLBA velmi užitečné, když potřebujeme zajistit kontrolu souběžnosti MOŽNOST (MAXDOP N), pořadí plánu provádění dotazu. Například:
- můžete zadat vynucené opětovné vytvoření plánu dotazů MOŽNOST (REKOMPILOVAT)
- můžete určit, zda má plán dotazů vynutit použití pořadí spojení zadaného v dotazu MOŽNOST (VYNUCOVAT)
Další podrobnosti o VOLBA popsáno zde.
Použití nejužšího a nejžádanějšího datového segmentu:
Není potřeba ukládat velké datové sady do mezipamětí (jako je tomu u indexovaných pohledů), ze kterých je stále potřeba filtrovat data podle parametrů.
Existuje například tabulka, jejíž filtr KDE používají se tři pole (a, b, c).

Všechny požadavky mají obvykle konstantní podmínku a = 0 a b = 0.

Nicméně požadavek na obor c variabilnější.

Nechte podmínku a = 0 a b = 0 Opravdu nám pomáhá omezit požadovaný výsledný soubor na tisíce záznamů, ale podmínka zapnuta с zužuje výběr na sto záznamů.

Zde může být lepší volbou funkce tabulky.

Funkce tabulky je také předvídatelnější a konzistentnější v době provádění.

Příklady

Podívejme se na příklad implementace pomocí databáze Questions jako příkladu.

Existuje žádost SELECT, který kombinuje více tabulek a používá jeden pohled (OperativeQuestions), ve kterém se kontroluje příslušnost emailem (přes EXISTUJE) na „Operativní otázky“:

Žádost č. 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])
));

Pohled má poměrně složitou strukturu: má spojení poddotazů a používá řazení DISTINCT, což je obecně operace poměrně náročná na zdroje.

Ukázka z OperativeQuestions je asi deset tisíc záznamů.

Hlavním problémem tohoto dotazu je, že pro záznamy z vnějšího dotazu se provede interní poddotaz v pohledu [OperativeQuestions], což by nám pro [Email] = @p__linq__0 mělo umožnit omezit výběr výstupu (přes EXISTUJE) až stovky záznamů.

A může se zdát, že poddotaz by měl vypočítat záznamy jednou pomocí [Email] = @p__linq__0 a pak by těchto pár stovek záznamů mělo být propojeno pomocí Id s otázkami a dotaz bude rychlý.

Ve skutečnosti existuje sekvenční propojení všech tabulek: kontrola shody Id Questions s Id z OperativeQuestions a filtrování e-mailem.

Ve skutečnosti žádost pracuje se všemi desetitisíci záznamy OperativeQuestions, ale prostřednictvím e-mailu jsou potřebná pouze data, která vás zajímají.

Text zobrazení Operativních otázek:

Žádost č. 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));

Počáteční mapování zobrazení 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čáteční dotaz 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étním případě zvažujeme řešení tohoto problému bez infrastrukturních změn, bez zavádění samostatné tabulky s hotovými výsledky („Active Queries“), která by vyžadovala mechanismus pro její naplnění daty a jejich aktualizaci. .

Přestože je to dobré řešení, existuje další možnost, jak tento problém optimalizovat.

Hlavním účelem je ukládat záznamy do mezipaměti pomocí [Email] = @p__linq__0 z pohledu OperativeQuestions.

Zaveďte do databáze tabulkovou funkci [dbo].[OperativeQuestionsUserMail].

Odesláním e-mailu jako vstupního parametru získáme zpět tabulku hodnot:

Žádost č. 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

Tím se vrátí tabulka hodnot s předdefinovanou datovou strukturou.

Aby byly dotazy na OperativeQuestionsUserMail optimální a měly optimální plány dotazů, je vyžadována přísná struktura, nikoli TABULKA VRÁCENÍ JAKO VRÁCENÍ...

V tomto případě se požadovaný Dotaz 1 převede na Dotaz 4:

Žádost č. 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]);

Mapování zobrazení a funkcí 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})");
}

Závěrečný dotaz 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();

Pořadí doby provádění kleslo z 200-800 ms na 2-20 ms atd., tedy desítkykrát rychleji.

Když to vezmeme průměrněji, tak místo 350 ms nám vyšlo 8 ms.

Ze zřejmých výhod také získáváme:

celkové snížení zátěže při čtení,
výrazné snížení pravděpodobnosti zablokování
snížení průměrné doby blokování na přijatelné hodnoty

Výkon

Optimalizace a doladění databázových volání MS SQL přes LINQ je problém, který lze vyřešit.

V této práci je velmi důležitá pozornost a důslednost.

Na začátku procesu:

je nutné zkontrolovat data, se kterými požadavek pracuje (hodnoty, vybrané datové typy)
provést řádnou indexaci těchto dat
zkontrolujte správnost podmínek spojení mezi tabulkami

Další iterace optimalizace odhaluje:

základ požadavku a definuje hlavní filtr požadavku
opakování podobných bloků dotazů a analyzování průniku podmínek
v SSMS nebo jiném GUI pro SQL Server se sám optimalizuje SQL dotaz (přidělení mezilehlého úložiště dat, vytvoření výsledného dotazu pomocí tohoto úložiště (může jich být několik))
v poslední fázi, přičemž za základ se vezme výsledný SQL dotaz, konstrukce se rekonstruuje LINQ dotaz

Výsledná LINQ dotaz by měly být strukturou identické s identifikovaným optimálním SQL dotaz z bodu 3.

Poděkování

Mnohokrát děkuji kolegům jobgemws и alex_ozr od společnosti Fortis za pomoc při přípravě tohoto materiálu.

Zdroj: www.habr.com

Metody pro optimalizaci LINQ dotazů v C#.NET