Úvod
V Boli posúdené niektoré metódy optimalizácie dotazov LINQ.
Tu uvedieme ďalšie prístupy k optimalizácii kódu, ktoré súvisia s dotazmi LINQ.
Je známe, že LINQ(Jazyk integrovaných dotazov) — to je jednoduchý a pohodlný jazyk na dotazy k zdroju údajov.
A LINQ to SQL je technológia prístupu k údajom v DBMS. Je to výkonný nástroj na prácu s údajmi, kde sa prostredníctvom deklaratívneho jazyka konštruujú dotazy, ktoré budú následne pretransformované do SQL dopyty platformy a odoslané na server databáz na vykonanie. V našom prípade pod DBMS rozumieme MS SQL Server.
Avšak, dotazy LINQ sa neprevádzajú na optimálne napísané SQL dopyty, ktoré by mohol napísať skúsený DBA so všetkými nuancami optimalizácie dotazov SQL:
- optimalizované spojenia (JOIN) a filtrovanie výsledkov (WHERE)
- množstvo nuáns v používaní spojení a skupinových podmienok
- množstvo variácií pri nahradzovaní podmienok IN na EXISTSa NOT IN, na EXISTS
- medzičasu cachovanie výsledkov prostredníctvom dočasných tabuliek, CTE, tabuľkových premenných
- použitie klauzuly (OPTION) s pokynmi a tabuľkovými hintmi WITH (…)
- použitie indexovateľných pohľadov ako jedného z prostriedkov na zbavenie sa nadbytočných čítaní údajov pri výberoch
Hlavnými úzkymi miestami výkonu sú dotazov SQL pri kompilácii dotazov LINQ sú:
- konsolidácia celého mechanizmu výberu údajov do jedného dotazu
- duplicita identických blokov kódu, čo má za následok viacnásobné zbytočné čítania údajov
- skupiny zložených podmienok (logické „a“ a „alebo“) — A a OR, spájajúce sa do zložitých podmienok, vedie k tomu, že optimalizátor, majúc vhodné neklastrované indexy na potrebné polia, nakoniec začína vykonávať skenovanie podľa klastrovaného indexu (INDEX SCAN) podľa skupín podmienok
- hĺbková vnorenosť poddotazov robí veľmi problematickým rozbor SQL príkazov a rozbor plánu dotazov zo strany vývojárov a DBA
Metódy optimalizácie
Teraz prejdeme priamo k metódam optimalizácie.
1) Dodatočné indexovanie
Najlepšie je zvážiť filtre na hlavných tabuľkách výberu, pretože veľmi často je celý dotaz postavený okolo jednej alebo dvoch hlavných tabuliek (žiadosti-ľudia- operácie) a so štandardnou sadou podmienok (IsClosed, Canceled, Enabled, Status). Je dôležité pre identifikované výberky vytvoriť zodpovedajúce indexy.
Toto riešenie má zmysel, keď výber v týchto poliach zásadne obmedzuje množstvo vracaných výsledkov dopytom.
Napríklad, máme 500000 žiadostí. Avšak, aktívnych žiadostí je len 2000 záznamov. Potom správne zvolený index nás zbaví INDEX SCAN veľkej tabuľky a umožní rýchlo vybrať údaje cez neklasterizovaný index.
Nedostatok indexov sa dá taktiež identifikovať pomocou návrhov analýzy plánov dopytov alebo zberom štatistík systémových zobrazení MS SQL Server:
Všetky údaje zobrazenia obsahujú informácie o chýbajúcich indexoch, okrem priestorových indexov.
Napriek tomu, indexy a caching sú často metodami boja proti následkom zle napísaných dotazov LINQ a dotazov SQL.
Ako ukazuje krutá prax života v podnikaní, implementácia obchodných funkcií do stanovených termínov je často dôležitá. Preto sa často ťažké dopyty presúvajú do pozadia s cachingom.
Časť tohto prístupu je ospravedlniteľná, pretože používateľovi nie vždy záleží na najnovších údajoch a dosahuje sa akceptovateľná úroveň spätnej odozvy používateľského rozhrania.
Tento prístup umožňuje riešiť požiadavky podnikania, ale nakoniec znižuje účinnosť informačného systému, jednoducho odkladá riešenie problémov.
Takisto je potrebné pamätať na to, že pri hľadaní potrebných nových indexov, návrhy Grafový*, dokumentový na optimalizáciu môžu byť nesprávne, a to najmä v nasledujúcich podmienkach:
- ak už existujú indexy s podobnou sadou polí
- ak polia v tabuľke nemôžu byť indexované z dôvodu obmedzení indexovania (o tomto je podrobnejšie popísané ).
2) Zlúčenie atribútov do jedného nového atribútu
Niekedy je možné nahradiť niektoré polia z jednej tabuľky, podľa ktorých sú skupiny podmienok, zavedením jedného nového poľa.
Obzvlášť to platí pre polia-stavy, ktoré sú zvyčajne buď bitové, alebo celové.
Príklad:
IsClosed = 0 A Canceled = 0 A Enabled = 0 sa nahrádza za Status = 1.
Tu je zavedený celočíselný atribút Status, zabezpečený vyplnením týchto stavov v tabuľke. Následne sa indexuje tento nový atribút.
Toto je fundamentálne riešenie problému výkonnosti, pretože sa obraciame na údaje bez zbytočných výpočtov.
3) Materializácia zobrazenia
Žiaľ, v LINQ dotazoch neživíme priamo používať dočasné tabuľky, CTE a tabuľkové premenné.
Avšak je tu ešte jeden spôsob optimalizácie na tento účel — indexovateľné pohľady.
Skupina podmienok (z vyššie uvedeného príkladu) IsClosed = 0 A Canceled = 0 A Enabled = 0 (alebo súbor iných podobných podmienok) sa stáva dobrým variantom na ich použitie v indexovateľnom pohľade, ktorý cache-uje malý úsek údajov z veľkého množstva.
Ale existuje niekoľko obmedzení pri materializácii pohľadu:
- použitie poddotazov, návrhy EXISTS musí byť nahradené použitím JOIN
- nie je možné používať návrhy operátor podporujú, UNION ALL, VÝNIMKA, TABUĽOVÝ VZOR
- nie je možné používať tabuľkové hinty a návrhy OPTION
- nie je možná práca s cyklami
- nie je možné vyťažovať údaje v jednom pohľade z rôznych tabuliek
Je dôležité pamätať, že skutočná výhoda z použití indexovateľného pohľadu môže byť skutočne dosiahnutá len pri jeho indexovaní.
Ale pri vyvolávaní pohľadu tieto indexy nemusia byť používané, a pre ich explicitné použitie je potrebné uviesť WITH (NOEXPAND).
Keďže v LINQ dotazoch nie je možné definovať tabuľkové hinty, takže je potrebné vytvoriť ešte jeden pohľad — "obal" nasledujúceho druhu:
CREATE VIEW NÁZOV_pohľadu AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);
4) Použitie tabuľkových funkcií
Často v LINQ dotazoch veľké bloky poddotazov alebo bloky, ktoré používajú pohľady so zložitou štruktúrou, tvoria konečný dotaz s veľmi zložitou a neoptimálnou štruktúrou vykonávania.
Hlavné výhody použitia tabuľkových funkcií v LINQ dotazoch:
- Možnosť, ako aj v prípade pohľadov, použiť a uvádzať ako objekt, ale može sa poslať súbor vstupných parametrov:
FROM FUNCTION(@param1, @param2 …)
výsledkom je flexibilný výber dát - Pri použití tabuľkovej funkcie nie sú také silné obmedzenia, ako v prípade indexovateľných pohľadov, ktoré sú popísané vyššie:
- Tabuľkové hinty:
cez LINQ nie je možné uviesť, ktoré indexy je potrebné použiť a definovať úroveň izolácie dát pri dotaze.
Ale vo funkcii tieto možnosti existujú.
S funkciou je možné dosiahnuť dosť konzistentný plán vykonávania požiadaviek, kde sú definované pravidlá práce s indexmi a úrovne izolácie dát. - Použitie funkcie umožňuje, v porovnaní s indexovateľnými pohľadmi, získať:
- zložitú logiku výberu údajov (až po použitie cyklov)
- výber údajov z množstva rôznych tabuliek
- používanie operátor podporujú a EXISTS
- Tabuľkové hinty:
- Návrh OPTION je veľmi užitočný, keď potrebujeme zabezpečiť riadenie paralelizmu OPTION(MAXDOP N), poradie plánu vykonania dopytu. Napríklad:
- je možné určiť nútené pregenerovanie plánu dopytu OPTION (RECOMPILE)
- môže určiť potrebu zabezpečiť nútené využitie plánom dopytu poradia spojenia uvedeného v dopyte OPTION (FORCE ORDER)
Podrobnejšie o OPTION popísané .
- Použitie najúzkeho a potrebného rezu údajov:
Nie je potrebné udržiavať veľké množstvá údajov v cache (ako v prípade indexovaných zobrazení), z ktorých je ešte potrebné filtrovať údaje podľa parametra.
Napríklad, existuje tabuľka, ktorá má pre filtrovanie WHERE používané tri polia (a, b, c).Podmienečne pre všetky dopyty existuje trvalá podmienka a = 0 a b = 0.
Avšak, dopyt na pole c je variabilnejší.
Nech podmienka a = 0 a b = 0 nám naozaj pomôže obmedziť požadovanú množinu na tisíce záznamov, ale podmienka s nám zúži výber na stovky záznamov.
Tu sa tabuľková funkcia môže ukázať ako výhodnejšia voľba.
Tabuľková funkcia je tiež predvídateľnejšia a stabilnejšia z hľadiska času vykonania.
Príklady
Zvážme príklad implementácie na databáze Questions.
Existuje dopyt SELECT, ktorý spája niekoľko tabuliek a používa jedno zobrazenie (OperativeQuestions), v ktorom sa kontroluje podľa emailu príslušnosť (cez EXISTS) k „Aktívnym dopytom“([OperativeQuestions]):
Dopyt č. 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])
));
Zobrazenie má pomerne zložitú štruktúru: obsahuje spojenia poddotazov a použitie triedenia STDEV, ktoré je vo všeobecnosti dosť výpočtovo náročnou operáciou.
Výber z OperativeQuestions s približne desiatimi tisíc záznamami.
Hlavný problém tohto dotazu spočíva v tom, že pre záznamy z externého dotazu sa vykonáva vnútorný poddotaz na zobrazení [OperativeQuestions], ktorý by mal pre [Email] = @p__linq__0 obmedziť zobrazovanú výber (cez EXISTS) na stovky záznamov.
A môže sa zdať, že poddotaz by mal raz vypočítať záznamy podľa [Email] = @p__linq__0, a potom by sa tieto pár stovák záznamov mali spájať podľa Id s Questions, a dotaz by bol rýchly.
V skutočnosti však dochádza k sekvenčnému spojeniu všetkých tabuliek: a kontrole zhodnosti Id Questions s Id z OperativeQuestions, a filtrovaniu podľa Email.
V podstate dotaz pracuje so všetkými desiatkami tisíc záznamov OperativeQuestions, a predsa potrebujeme iba zaujímavé údaje podľa Email.
Text zobrazenia 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));
Pôvodné mapovanie zobrazenia 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étnom prípade sa zvažuje riešenie tohto problému bez zmien v infraštruktúre, bez zavedenia samostatnej tabuľky s hotovými výsledkami („Aktívne požiadavky“), pod ktorú by bol potrebný mechanizmus na napĺňanie jej údajmi a udržiavanie jej v aktuálnom stave.
Hoci je to dobré riešenie, existuje aj iná možnosť optimalizácie tejto úlohy.
Hlavným cieľom je vyrovnať záznamy podľa [Email] = @p__linq__0 z pohľadu OperativeQuestions.
Zavádzame tabuľkovú funkciu [dbo].[OperativeQuestionsUserMail] do databázy.
Posielaním ako vstupného parametra Email získavame späť tabuľku hodnôt:
Dotaz č. 3
VYTVORIŤ FUNKCIU [dbo].[OperativeQuestionsUserMail]
(
@Email nvarchar(4000)
)
VRÁTI
@tbl TABUĽA
(
[Id] uniqueidentifier,
[Email] nvarchar(4000)
)
AS
ZAČNITE
VLOŽIŤ DO @tbl ([Id], [Email])
VYBERTE Id, @Email
Z [OperativeQuestions] AS [x] KDE [x].[Email] = @Email;
NÁVRAT;
KONEC
Tu sa vracia tabuľka hodnôt s vopred určitou štruktúrou údajov.
Aby boli dotazy na OperativeQuestionsUserMail optimálne a mali optimálne plány dotazov, je potrebná prísna štruktúra, nie VRÁTI TABUĽU AKO NÁVRAT…
V tomto prípade sa hľadaný Dotaz 1 transformuje na Dotaz 4:
Dotaz č. 4
(@p__linq__0 nvarchar(4000))VYBERTE
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]
Z (
VYBERTE Id, Email Z [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
VNÚTORNE PRIPOJTE [dbo].[Questions] AS [Extent1] NA([Extent0].Id=[Extent1].Id)
VNÚTORNE PRIPOJTE (VYBERTE [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]
Z [dbo].[ObjectQuestions] AS [Extent2]
VNÚTORNE PRIPOJTE [dbo].[Objects] AS [Extent3] NA [Extent2].[Object_Id] = [Extent3].[Id]
ĽAVÉ VONKAJŠIE PRIPOJTE [dbo].[ObjectTypes] AS [Extent4]
NA [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
NA [Extent1].[Id] = [Join2].[Question_Id]
KDE ([Extent1].[AnswerId] JE NULL) A (0 = [Extent1].[Exp]);
Mapovanie pohľadu a funkcie v DbContext (EF Core 2)
verejná trieda QuestionsDbContext : DbContext
{
\/\/...
verejná DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
\/\/...
chránené predefinované void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().DoView("OperativeQuestions");
}
}
verejná statická trieda FromSqlQueries
{
verejná statická IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
=> source.FromSql($"VYBERTE Id, Email Z [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ávania sa znížila z 200-800 ms na 2-20 ms, a tak ďalej, t.j. desiatky ráz rýchlejšie.
Ak to vezmeme z pohľadu priemeru, namiesto 350 ms sme dostali 8 ms.
Medzi očividnými výhodami získavame tiež:
- celkové zníženie záťaže pri čítaní,
- výrazné zníženie pravdepodobnosti zablokovania
- zníženie priemerného času zablokovania na prijateľné hodnoty
Záver
Optimalizácia a ladění prístupov k DB Grafový*, dokumentový cez LINQ je úloha, ktorú je možné vyriešiť.
V tejto práci sú veľmi dôležité pozornosť a sekvencia.
Na začiatku procesu:
- je potrebné skontrolovať údaje, s ktorými pracuje dotaz (hodnoty, vybrané typy údajov)
- vykonať správne indexovanie týchto údajov
- skontrolovať správnosť spojovacích podmienok medzi tabuľkami
Na ďalšej iterácii optimalizácie sa identifikujú:
- základ dotazu a určuje sa hlavný filter dotazu
- opakujúce sa podobné bloky dotazu a analyzuje sa prekrytie podmienok
- v SSMS alebo inom GUI pre SQL Server optimalizuje sa samotný SQL dotaz (vyčlenenie medziúložiska údajov, zostavenie výsledného dotazu s použitím tohto úložiska (môže ich byť viac))
- v poslednej fáze, berúc za základ výsledný SQL dotaz, prestavuje sa štruktúra LINQ-dotazu
Na konci by mal vzniknúť LINQ dotaz štrukturálne identický zistenému optimálnemu SQL-dotazu z bodu 3.
Poďakovanie
Veľká vďaka kolegom a z firmy Fortis za pomoc pri príprave tohto materiálu.
Zdroj: habr.com
