🥇Metode optimizacije LINQ poizvedb v C#.NET

Uvod

V this article raziskovali smo nekatere metode optimizacije LINQ-izvedbe.
Tukaj bomo predstavili še nekatere pristopi za optimizacijo kode, povezane z LINQ-izvedbami.

Znano je, da LINQ(Language-Integrated Query) — to je preprost in udoben jezik za poizvedovanje po viru podatkov.

A LINQ to SQL je tehnologija dostopa do podatkov v DBMS. To je močno orodje za delo s podatki, kjer se preko deklarativnega jezika konstruirajo povpraševanja, ki se nato pretvorijo v SQL-poizvedbe platformo in se pošljejo na strežnik podatkov za izvajanje. V našem primeru pod DBMS razumemo MS SQL Server.

Vendar pa LINQ-izvedbe niso pretvorjene v optimalno napisane SQL-poizvedbe, ki bi jih lahko napisal izkušen DBA z vsemi podrobnostmi optimizacije SQL-poizvedb:

optimalne povezave (JOIN) in filtriranje rezultatov (WHERE)
mnogo podrobnosti pri uporabi povezav in grupnih pogojev
mnogo variacij pri zamenjavi pogojev IN na EXISTSin NOT IN, > na EXISTS
posredno predpomnjenje rezultatov preko začasnih tabel, CTE, tabelskih spremenljivk
uporaba stavka (OPTION) s usmeritvami in tabelnimi namigi WITH (…)
uporaba indeksiranih predstavitev, kot enega od sredstev za odpravljanje odvečnih branj podatkov pri izbirah

Glavne ožje točke zmogljivosti, ki nastanejo SQL-poizvedb med kompilacijo LINQ-izvedbe so:

konsolidacija celega mehanizma iskanja podatkov v eni poizvedbi
podvajanje identičnih blokov kode, kar nazadnje vodi do večkratnih odvečnih branj podatkov
skupine večkomponentnih pogojev (logični 'in' in 'ali') — AND in ILI, povezanih v zapletene pogoje, povzroči, da optimizator, ki ima primerne nekClustersne indekse, na potrebnih poljih, nazadnje vseeno začne izvajati skeniranje po clusterskem indeksu (INDEX SCAN) po skupinah pogojev
globokost podvprašanj zelo otežuje analiziranje SQL-navodil in analizo načrta poizvedb s strani razvijalcev in DBA

Metode optimizacije

Prejdimo zdaj neposredno na metode optimizacije.

1) Dodatno indeksiranje

Najbolje je razmotriti indekse na glavnim tabelama upita, budući da se često ceo zahtev gradi oko jedne ili dve glavne tabele (zahtevi-ljudi-operacije) i sa standardnim skupom uslova (IsClosed, Canceled, Enabled, Status). Važno je za identifikovane rezultate napraviti odgovarajuće indekse.

Ovo rešenje ima smisla kada odabir po ovim poljima značajno ograničava vraćeni skup podataka.

Na primer, imamo 500000 zahteva. Međutim, aktivnih zahteva je samo 2000 unosa. Tada pravilno odabran indeks će nas osloboditi INDEX SCAN od velikih tabela i omogućiti brzo preuzimanje podataka preko nekategorizovanog indeksa.

Takođe, nedostatak indeksa može se identifikovati kroz savete analiziranja planova upita ili prikupljanje statistike sistemskih prikaza MS SQL Server:

Svi podaci prikaza sadrže informacije o nedostajućim indeksima, osim prostornog indeksa.

Međutim, indeksi i keširanje često su metode borbe protiv posledica loše pisanih LINQ-izvedbe in SQL-poizvedb.

Kao što pokazuje surova praksa, za poslovanje je često važna realizacija poslovnih funkcija u određenim rokovima. Stoga se često teški zahtevi prebacuju u pozadinski režim sa keširanjem.

Delimično je to opravdano, jer korisniku ne trebaju uvek najnoviji podaci, a ostvaruje se prihvatljiv nivo odziva korisničkog interfejsa.

Ovaj pristup omogućava ispunjavanje zahteva poslovanja, ali na kraju smanjuje efikasnost informacionog sistema, jednostavno odlažući rešenja problema.

Takođe, treba imati na umu da tokom pretrage potrebnih za dodavanje novih indeksa, predlozi Grafični*, dokumentni za optimizaciju mogu biti netačni, posebno u sledećim uslovima:

ako već postoje indeksi sa sličnim skupom polja
ako se polja u tabeli ne mogu indeksirati zbog ograničenja indeksiranja (detaljnije o tome je opisano ovde).

2) Spajanje atributa u jedan novi atribut

Ponekad se neka polja iz jedne tabele, prema kojima se primenjuju uslovi grupisanja, mogu zameniti uvođenjem jednog novog polja.

Ovo je posebno aktuelno za polja-stanja, koja su obično po tipu ili bitna ili celobrojna.

Primer:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 zamenjuje se sa Status = 1.

Tukaj vnesete celoštevilski atribut Status, zagotavljajoč te statuse s polnjenjem v tabeli. Nato se indeksira ta nov atribut.

To je temeljna rešitev za težave z zmogljivostjo, saj pridobivamo podatke brez odvečnih izračunov.

3) Materializacija prikaza

Žal v LINQ poizvedbah ni mogoče neposredno uporabiti začasnih tabel, CTE in tabelarnih spremenljivk.

Vendar obstaja še en način optimizacije za ta primer — indeksirani prikazi.

Skupina pogojev (iz zgornjega primera) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (ali niz drugih podobnih pogojev) postane dobra izbira za njihovo uporabo v indeksiranem prikazu, saj shranjujejo majhen del podatkov iz velike množice.

Vendar obstajajo nekatera omejitev pri materializaciji prikaza:

uporaba podpoizvedb, stavkov EXISTS morajo biti nadomeščeni z uporabo JOIN
ni mogoče uporabiti stavkov UNION, UNION ALL, EXCEPTION, INTERSECT
ni mogoče uporabiti tabelarnih namigov in stavkov OPTION
ni možnosti dela z zankami
ni mogoče prikazati podatkov iz različnih tabel v enem prikazu

Pomembno je zapomniti, da je dejanska korist uporabe indeksiranega prikaza lahko dosežena le z njegovo indeksacijo.

Toda ob klicu prikaza se ti indeksi morda ne uporabljajo, za njihovo eksplicitno uporabo pa je potrebno navesti WITH (NOEXPAND).

Ker v LINQ poizvedbah ni mogoče določiti tabelarnih namigov, zato je potrebno ustvariti še en prikaz — "ovitek" naslednje oblike:

USTVARI POGLED IME_prikaza AS IZBERI * IZ MAT_VIEW WITH (NOEXPAND);

4) Uporaba tabelarnih funkcij

Pogosto v LINQ poizvedbah veliki bloki podpoizvedb ali bloki, ki uporabljajo prikaze s kompleksno strukturo, oblikujejo končno poizvedbo z zelo zapleteno in neoptimalno strukturo izvajanja.

Glavne prednosti uporabe tabelarnih funkcij v LINQ poizvedbah:

Možnost, kot pri prikazih, uporabiti in navesti kot objekt, vendar je mogoče posredovati tudi niz vhodnih parametrov:
IZ OD FUNKCIJE(@param1, @param2 …)
na koncu lahko dosežete prilagodljivo izbiro podatkov
V primeru uporabe tabelarne funkcije ni takšnih stroge omejitve, kot pri indeksiranih prikazih, opisanih zgoraj:
1. Tabelarni namigi:
  preko LINQ Ni mogoče je določiti, kateri indeksi se morajo uporabljati in določiti raven izolacije podatkov pri poizvedbi.
  Toda v funkciji so te možnosti na voljo.
  S funkcijo lahko dosežemo dovolj dosleden načrt izvajanja poizvedbe, kjer so opredeljena pravila za delo z indeksi in ravni izolacije podatkov.
2. Uporaba funkcije omogoča v primerjavi z indekseranimi predstavitvami pridobiti:
  - zapleteno logiko izbire podatkov (vključno z uporabo zank)
  - izbiro podatkov iz več različnih tabel
  - uporaba UNION in EXISTS
Oblika OPTION je zelo koristna, ko moramo zagotoviti upravljanje paralelizma. OPTION(MAXDOP N), vrsto načrta izvajanja poizvedbe. Na primer:
- lahko določite prisilno ponovno ustvarjanje načrta poizvedbe. OPTION (RECOMPILE)
- lahko določite potrebo po prisilni uporabi vrste združitve, ki je navedena v poizvedbi, v načrtu poizvedbe. OPTION (FORCE ORDER)
Podrobneje o OPTION opisano je ovde.
Uporabi najožjega in potrebnega izreza podatkov:
Ni potrebe po ohranjanju velikih nizov podatkov v predpomnilniku (kot pri indeksiranih predstavitvah), iz katerih je potrebno podatke še filtrirati po parametru.
Na primer, imamo tabelo, kjer se za filtriranje WHERE uporabljajo tri polja (a, b, c).
Pogojno za vse poizvedbe velja stalni pogoj a = 0 in b = 0..
Vendar je poizvedba za polje c bolj spremenljiva.
Recimo, da pogoj a = 0 in b = 0. v resnici pomaga omejiti zahtevani nabor na tisoče zapisov, vendar pogoj z omejuje izbor na sto zapisov.
Tukaj je tabelarna funkcija lahko boljša izbira.
Tudi tabelarna funkcija je bolj predvidljiva in dosledna glede časa izvajanja.

Primeri

Poglejmo primer realizacije na primeru baze podatkov Questions.

Imamo poizvedbo, SELECTki povezuje več tabel in uporablja eno predstavitev (OperativeQuestions), pri kateri se preverja po e-pošti pripadnost (prek EXISTS) "Aktivnim poizvedbam" ([OperativeQuestions]):

Poizvedba št. 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])
));

Predstava ima prilično složenu strukturu: uključuje spajanje podupita i upotrebu sortiranja DISTINCT, što je u opštem slučaju prilično resursno zahtevna operacija.

Izbor iz OperativeQuestions s redosledom od desetak hiljada zapisa.

Glavni problem ovog upita je u tome što se za zapise iz spoljnog upita izvršava unutrašnji podupit na predstavi [OperativeQuestions], koji bi trebao za [Email] = @p__linq__0 da ograniči izlaznu selekciju (kroz EXISTS) na stotine zapisa.

Može se učiniti da bi podupit trebao jednom da izračuna zapise za [Email] = @p__linq__0, a zatim bi tih nekoliko stotina zapisa trebali da se spoje po Id sa Questions, i upit bi bio brz.

U stvarnosti se dešava sekvencijalno spajanje svih tabela: i provera usklađenosti Id Questions sa Id iz OperativeQuestions, i filtriranje po Email.

Suštinski, upit radi sa svim desetinama hiljada zapisa iz OperativeQuestions, dok su potrebni samo relevantni podaci za Email.

Tekst predstave OperativeQuestions:

Upit br. 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));

Izvorna mapa predstave u DbContext (EF Core 2)

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

Izvorni LINQ upit

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

U ovom konkretnom slučaju razmatra se rješenje ovog problema bez infrastrukturnih promjena, bez uvođenja posebne tablice s gotovim rezultatima ("Aktivni upiti"), za koju bi bio potreban mehanizam za punjenje podacima i održavanje u ažurnom stanju.

Iako je ovo dobro rješenje, postoji i druga opcija optimizacije ovog zadatka.

Osnovna svrha je keširati zapise po [Email] = @p__linq__0 iz prikaza OperativeQuestions.

Uvodimo tabelarnu funkciju [dbo].[OperativeQuestionsUserMail] u bazu podataka.

Šaljemo kao ulazni parametar Email, dobijamo nazad tabelu vrijednosti:

Upit br. 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

Ovdje se vraća tabela vrijednosti s unaprijed definiranim strukturom podataka.

Da bi upiti prema OperativeQuestionsUserMail bili optimalni, imali optimalne planove upita, potrebna je stroga struktura, a ne RETURNS TABLE AS RETURN…

U ovom slučaju traženi Upit 1 se pretransformira u Upit 4:

Upit br. 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]);

Mapiranje prikaza i funkcije u 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})");
}

Zaključni LINQ upit

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

Vreme izvršenja je smanjeno sa 200-800 ms na 2-20 ms, tj. desetinama puta brže.

U opštem uzimanju, umesto 350 ms dobili smo 8 ms.

Od očiglednih prednosti takođe dobijamo:

opšte smanjenje opterećenja pri čitanju,
značajno smanjenje verovatnoće zaključavanja
smanjenje prosečnog vremena zaključavanja na prihvatljive vrednosti

Izlaz

Optimizacija i fino podešavanje pristupa bazi podataka Grafični*, dokumentni preko LINQ je zadatak koji se može rešiti.

U ovom radu su vrlo važni pažnja i doslednost.

Na početku procesa:

potrebno je proveriti podatke sa kojima radi upit (vrednosti, ponuđeni tipovi podataka)
provesti ispravno indeksiranje tih podataka
proveriti tačnost uslova povezivanja između tabela

U sledećoj iteraciji optimizacije utvrđuje se:

osnova upita i određuje se glavni filter upita
ponavljajući slični blokovi upita i analizira se preklapanje uslova
u SSMS ili nekom drugom GUI za SQL Server optimizuje se sam SQL upit (izdvajanje intermedijalne skladišne jedinice podataka, izgradnja rezultantnog upita koristeći ovo skladište (može ih biti više))
u poslednjem koraku, uzimajući kao osnovu rezultantni SQL upit, menja se struktura LINQ upita

Na kraju, dobijeni LINQ upit treba postati po strukturi identičan utvrđenom optimalnom SQL upitu iz tačke 3.