🥇Metody optimalizace LINQ dotazů v C#.NET

Úvod

V tomto článku Byly zvažovány některé metody optimalizace dotazů LINQ.
Zde se také podívejme na další přístupy k optimalizaci kódu spojené s dotazy LINQ.

Je známo, že LINQ(Language-Integrated Query) – to je jednoduchý a pohodlný jazyk dotazů na zdroj dat.

A LINQ to SQL je technologie přístupu k datům v DBMS. Je to mocný nástroj pro práci s daty, kde jsou dotazy konstruovány pomocí deklarativního jazyka, které budou následně transformovány na SQL dotazy platformu a odesílány na server databází k vykonání. V našem případě pod DBMS rozumíme MS SQL Server.

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

optimální spojení (JOIN) a filtrování výsledků (není spolehlivý:)
mnoho nuancí při používání spojení a skupinových podmínek
mnoho variací při nahrazování podmínek IN na EXISTSa NOT IN, na EXISTS
meziukládání výsledků přes dočasné tabulky, CTE, tabulkové proměnné
použití klauzule (OPTION) s pokyny a tabulkovými hintami WITH (…)
použití indexovaných pohledů jako jednoho ze způsobů, jak eliminovat nadbytečné čtení dat při výběrech

Hlavní úzká místa výkonnosti vznikající dotazy SQL při kompilaci dotazů LINQ jsou:

konzolidace celého mechanismu výběru dat v jednom dotazu
duplicita identických bloků kódu, což nakonec vede k mnohonásobným zbytečným čtením dat
skupiny složených podmínek (logické „a“ a „nebo“) — A a Nebo, spojené do složitých podmínek, vede k tomu, že optimalizátor, má-li vhodné neklastrové indexy pro potřebná pole, přesto začne provádět skenování podle klastrového indexu (INDEX SCAN) podle skupin podmínek
hluboké zanoření poddotazů značně ztěžuje analýzu SQL pokynů a analýzu plánů dotazů z pohledu vývojářů a DBA

Metody optimalizace

Nyní se pojďme přímo na metody optimalizace.

1) Další indexování

Nejlépe je zvažovat filtry na hlavních výběrových tabulkách, protože často je celý dotaz postaven kolem jedné nebo dvou hlavních tabulek (přihlášky - lidé - operace) a se standardní sadou podmínek (IsClosed, Canceled, Enabled, Status). Je důležité vytvořit odpovídající indexy pro zjištěné výběry.

Toto řešení dává smysl, když výběr podle těchto polí výrazně omezuje vracenou množinu dotazem.

Například máme 500000 přihlášek. Aktivních přihlášek je však pouze 2000 záznamů. Správně zvolený index nás zbaví INDEX SCAN velké tabulky a umožní rychle vybrat data přes neklasifikovaný index.

Nedostatek indexů lze rovněž zjistit pomocí doporučení k analýze plánů dotazů nebo shromažďováním statistik systémových pohledů MS SQL Server:

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

Nicméně indexy a kešování jsou často metodami, jak se vypořádat s důsledky špatně napsaných dotazů LINQ a dotazy SQL.

Jak ukazuje krutá praxe života pro podnikání, je často důležitá implementace obchodních funkcí v určitém termínu. A proto se těžké dotazy často převádějí do pozadí s kešováním.

Částečně je to oprávněné, protože uživateli ne vždy záleží na nejnovějších datech a dochází k přijatelnému úrovni odezvy uživatelského rozhraní.

Tento přístup umožňuje vyřešit požadavky podnikání, ale nakonec snižuje funkčnost informačního systému, jednoduše odkladajíc řešení problémů.

Také je třeba mít na paměti, že při hledání potřebných nových indexů mohou být návrhy Grafový*, dokumentový na optimalizaci chybnými, a to zejména za následujících podmínek:

pokud již existují indexy se podobnou sadou polí
pokud polí v tabulce nemohou být indexována z důvodu omezení indexování (o tom více podrobně se popisuje zde).

2) Spojení atributů do jednoho nového atributu

Někdy lze některá pole z jedné tabulky, podle kterých dochází k souboru podmínek, nahradit zavedením jednoho nového pole.

To je obzvláště aktuální pro pole-stavy, které jsou obvykle buď bitové, nebo celočíselné.

Příklad:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 se nahrazuje Status = 1.

Zde se zadává celočíselný atribut Status, který je zajištěn vyplněním těchto stavů v tabulce. Následně se provádí indexování tohoto nového atributu.

To je zásadní řešení problémů s výkonností, protože se obracíme pro data bez zbytečných výpočtů.

3) Materializace pohledu

Bohužel v dotazech LINQ nelze přímo používat dočasné tabulky, CTE a tabulkové proměnné.

Existuje však ještě jeden způsob optimalizace v tomto případě — jsou to indexované pohledy.

Skupina podmínek (z výše uvedeného příkladu) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (nebo soubor jiných podobných podmínek) se stává dobrým řešením pro jejich použití v indexovaném pohledu, který uchovává malý segment dat z velkého množství.

Existují však určité omezení při materializaci pohledu:

použití poddotazů, návrhy EXISTS musí být nahrazeny použitím JOIN
nelze používat návrhy UNION, UNION ALL, VYJÍMKY, INTERSECT
nelze používat tabulkové hinty a návrhy OPTION
neexistuje možnost práce s cykly
není možné zobrazovat data v jednom pohledu z různých tabulek

Je důležité si pamatovat, že skutečný přínos z použití indexovaných pohledů může být fakticky získán pouze jejich indexováním.

Při volání pohledu však tyto indexy nemusí být použity, a pro jejich explicitní použití je nutné uvést WITH (NOEXPAND).

Jelikož v dotazech LINQ nelze definovat tabulkové hinty, takže je nutné vytvořit další pohled - "obal" následujícího typu:

CREATE VIEW JMÉNO_pohledu AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Použití tabulkových funkcí

Často v dotazech LINQ velké bloky poddotazů nebo bloky využívající pohledy se složitou strukturou vytvářejí konečný dotaz s velmi složitou a neoptimální strukturou vykonávání.

Hlavní výhody používání tabulkových funkcí v dotazech LINQ:

Možnost, stejně jako v případě pohledů, používat a specifikovat jako objekt, ale lze předat sadu vstupních parametrů:
FROM FUNCTION(@param1, @param2 …)
nakonec lze dosáhnout flexibilního výběru dat.
Při použití tabulkové funkce nejsou tak silná omezení, jako v případě indexovaných pohledů, jak bylo popsáno výše:
1. Tabulkové hinty:
  přes LINQ Není možné specifikovat, které indexy je třeba použít a určit úroveň izolace dat při dotazu.
  Ale ve funkci tyto možnosti existují.
  S funkcí lze dosáhnout dostatečně konstantního plánu dotazování, kde jsou definována pravidla pro práci s indexy a úrovně izolace dat.
2. Použití funkce umožňuje ve srovnání s indexovanými pohledy získat:
  - složitou logiku výběru dat (až po použití cyklů)
  - výběr dat z různých tabulek
  - použití UNION a EXISTS
Návrh OPTION je velmi užitečný, když potřebujeme zajistit řízení paralelismu. OPTION(MAXDOP N), pořadí plánu provedení dotazu. Například:
- můžeme uvést povinné přegenerování plánu dotazu. OPTION (RECOMPILE)
- můžeme uvést nutnost zajistit povinné použití plánu dotazu pořadí spojení, uvedeného v dotazu. OPTION (FORCE ORDER)
Podrobněji o OPTION je popsáno zde.
Použití nejužšího a požadovaného datového řezu:
Není třeba uchovávat velké sady dat v cache (jak je tomu v případě s indexovanými pohledy), ze kterých je třeba ještě filtrovat data podle parametru.
Například existuje tabulka, která pro filtr není spolehlivý: používá tři pole (a, b, c).
Podmínkově pro všechny dotazy je zde trvalá podmínka a = 0 a b = 0.
Nicméně dotaz na pole s je variabilnější.
Předpokládejme, že podmínka a = 0 a b = 0 nám skutečně pomáhá omezit požadovanou sadu na tisíce záznamů, ale podmínka podle s nám zúží výběr na stovky záznamů.
Zde se může tabulková funkce ukázat jako výhodnější varianta.
Také tabulková funkce je předvídatelnější a konstantní co se týče doby provádění.

Příklady

Zvažme příklad realizace na databázi Otázky.

Existuje dotaz nebude prováděn při nepravdivosti první podmínky. Opravy pomocí, který spojuje několik tabulek a používá jeden pohled (OperativníOtázky), v němž je kontrolováno podle emailu členství (přes EXISTS) k „Aktivním dotazům“ ([OperativníOtázky]):

Dotaz č. 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])
));

Představění má poměrně složitou strukturu: obsahuje poddotazy a používá třídění. DISTINCT, které je obecně považováno za operaci náročnou na zdroje.

Výběr z OperativeQuestions obsahuje přibližně deset tisíc záznamů.

Hlavní problém tohoto dotazu spočívá v tom, že pro záznamy z externího dotazu se provádí vnitřní poddotaz na pohledu [OperativeQuestions], který by měl omezit zobrazované výsledky pro [Email] = @p__linq__0 (přes EXISTS) na stovky záznamů.

Může se zdát, že poddotaz by měl jednou spočítat záznamy podle [Email] = @p__linq__0, a poté by se tyto stovky záznamů měly spojovat podle Id s Questions, což by mělo urychlit dotaz.

Ve skutečnosti však probíhá sekvenční spojení všech tabulek: jak se kontroluje shoda Id Questions s Id z OperativeQuestions, tak i filtrování podle Email.

V podstatě dotaz pracuje se všemi desítkami tisíc záznamů v OperativeQuestions, přičemž potřebné jsou pouze relevantní údaje podle Email.

Text pohledu OperativeQuestions:

Dotaz č. 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í pohledu v DbContext (EF Core 2)

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

Původní LINQ dotaz

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ě se zvažuje řešení tohoto problému bez změn v infrastruktuře, bez zavádění samostatné tabulky s hotovými výsledky („Aktivní dotazy“), pro kterou by byl potřebný mechanismus naplňování daty a udržování její aktuálnosti.

I když jde o dobré řešení, existuje i jiná možnost optimalizace tohoto úkolu.

Hlavním cílem je uložit záznamy podle [Email] = @p__linq__0 z pohledu OperativeQuestions.

Zavádíme tabulkovou funkci [dbo].[OperativeQuestionsUserMail] do databáze.

Zasláním jako vstupní parametr Email, obdržíme zpět tabulku hodnot:

Dotaz č. 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

Zde se vrací tabulka hodnot s předem definovanou strukturou dat.

Aby dotazy na OperativeQuestionsUserMail byly optimální, měly optimální plány dotazů, je potřebná přísná struktura, nikoli RETURNS TABLE AS RETURN…

V tomto případě se hledaný Dotaz 1 převádí na Dotaz 4:

Dotaz č. 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í pohledu a funkce v DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable GetByUserEmail(this DbQuery source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Konečný LINQ dotaz

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

Doba vykonávání poklesla z 200-800 ms na 2-20 ms, atd., tedy desítkykrát rychlejší.

Pokud to bereme více průměrně, místo 350 ms jsme dostali 8 ms.

Mezi zjevnými výhodami také získáváme:

celkové snížení zátěže při čtení,
významné snížení pravděpodobnosti zablokování
snížení průměrného času zablokování na přijatelnou úroveň

Výstup

Optimalizace a jemné ladění dotazů na databázi Grafový*, dokumentový přes LINQ je úkol, který lze vyřešit.

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

Na začátku procesu:

je nutné zkontrolovat data, se kterými dotaz pracuje (hodnoty, vybrané typy dat)
provést správné indexování těchto dat
zkontrolovat správnost podmínky spojení mezi tabulkami

Při další iteraci optimalizace se odhalují:

základ dotazu a určuje se hlavní filtr dotazu
opakující se podobné bloky dotazu a analyzuje se překrývání podmínek
v SSMS nebo jiném GUI pro SQL Server optimalizuje se samotné SQL dotaz (vytvoření mezilehlého úložiště dat, sestavení výsledného dotazu s využitím tohoto úložiště (může jich být více))
v poslední fázi, na základě výsledného SQL dotaz, se přestavuje struktura LINQ dotazu

V konečném důsledku musí získaný LINQ dotaz být strukturou identický s nalezeným optimálním SQL dotazem z bodu 3.

Poděkování

Obrovské díky kolegům jobgemws a alex_ozr z firmy Fortis za pomoc při přípravě tohoto materiálu.

Zdroj: habr.com