🥇LINQ-lekérdezések optimalizálási módszerei C#.NET

Bevezetés

A cikkben Több optimalizációs módszert megvizsgáltunk LINQ-lekérdezések.
Itt bemutatunk további kódoptimalizálási megközelítéseket, amelyek kapcsolódnak a LINQ-lekérdezésekhez.

Tudjuk, hogy LINQ(Language-Integrated Query) — egy egyszerű és kényelmes lekérdezési nyelv az adatforráshoz.

A LINQ to SQL egy adat-hozzáférési technológia DBMS-ben. Ez egy erőteljes eszköz az adatok kezelésére, ahol deklaratív nyelven vannak megfogalmazva a lekérdezések, amelyeket aztán a SQL-lekérdezéseket platform átalakít és elküld a database servernek végrehajtásra. Az esetünkben a DBMS alatt MS SQL Server.

Azonban, LINQ-lekérdezések nem alakulnak át optimálisan megírt SQL-lekérdezéseket, amelyet egy tapasztalt DBA meg tudna írni az optimalizálás minden részletével SQL-lekérdezések:

optimális csatlakozások (JOIN) és az eredmények szűrése (AHOL)
számos részlet a kapcsolatok és a csoportos feltételek használatában
sok variáció a feltételek helyettesítésében IN Gyorsan emlékezzünk vissza, hogyan működik a moduláris aritmetika. Az óra már egy ismert koncepció. Az órák használata VÉGE;és NOT IN, -re VÉGE;
átmeneti eredmények köztes tárolása ideiglenes táblák, CTE-k, táblázatos változók segítségével
a zárójel használata (OPTION) utasításokkal és táblázatos hívásokkal WITH (…)
indexálható nézetek alkalmazása, mint egy eszköz, hogy megszabaduljunk a felesleges adatolvasásoktól a lekérdezésekben

A keletkező teljesítmény szűk keresztmetszetei SQL-lekérdezések a fordítás során LINQ-lekérdezések a következők:

az adatgyűjtési folyamat konszolidálása egy lekérdezésben
azonos blokkok duplikálása, ami végső soron többszörös felesleges adatolvasásokhoz vezet
többkomponensű feltételcsoportok (logikai 'és' és 'vagy') — ÉS és VAGY, amelyek összekapcsolva bonyolult feltételeket alkotnak, ahhoz vezet, hogy az optimalizáló, ha megfelelő nem klaszterezett indexek állnak rendelkezésre a szükséges mezőkön, végül mégis elkezdi a klaszterezett index (INDEX SCAN) átvizsgálását a feltételcsoportok alapján
mélyen beágyazott al-lekérdezések rendkívül problémásá teszik a SQL-utasítások elemzését és a lekérdezési terv elemzését a fejlesztők részéről, DBA

Optimalizálási módszerek

Most térjünk át az optimalizálási módszerekre.

1) További indexelés

A legjobban a lekérdezés alap tábláin a szűrőket érdemes figyelembe venni, mivel gyakran az egész lekérdezés egy-két fő tábla (kérések-emberek-műveletek) köré épül, a standard feltételsorozattal (IsClosed, Canceled, Enabled, Status). Fontos, hogy a kiválasztott lekérdezésekhez megfelelő indexeket hozzunk létre.

Ez a megoldás akkor van értelme, amikor ezeknek a mezőknek a kiválasztása lényegesen korlátozza a lekérdezés által visszaadott halmazt.

Például, van 500000 kérésünk. Azonban, az aktív kérések száma mindössze 2000 bejegyzés. Ekkor a megfelelően kiválasztott index megszabadít minket INDEX SCAN a nagy táblázatból és gyorsan lehetővé teszi az adatok kiválasztását nem klaszterezett indexeken keresztül.

Az indexek hiányát a lekérdezéstervek elemzésének vagy a rendszerszemléletek statisztikáinak gyűjtése révén is észlelhetjük. MS SQL Server:

A nézetek minden adata tartalmazza az eltérő indexekről szóló információkat, kivéve a térbeli indexeket.

Azonban az indexek és a gyorsítótárazás gyakran a rosszul megírt lekérdezések következményeinek ellensúlyozására szolgáló módszerek. LINQ-lekérdezések és SQL-lekérdezések.

A szigorú üzleti tapasztalatok azt mutatják, hogy gyakran fontos a biznisz funkciók megvalósítása meghatározott határidőkre. Ezért gyakran a nehéz lekérdezéseket háttérbe helyezik gyorsítótárazással.

Ez részben indokolt, mivel a felhasználónak nem mindig szükségesek a legfrissebb adatok, és elfogadható szintű válaszidő érhető el a felhasználói felületen.

Ez a megközelítés lehetővé teszi az üzleti követelések kezelését, azonban végső soron csökkenti az információs rendszer hatékonyságát, egyszerűen elhalasztva a problémák megoldását.

Azt is érdemes figyelembe venni, hogy az új indexek hozzáadásához szükségesek keresések során a javaslatok MS SQL az optimalizálásra vonatkozó javaslatok helytelenek lehetnek a következő helyzetekben is:

ha már léteznek indexek hasonló mezőcsoporttal
ha a táblázat mezői nem indexelhetők az indexelési korlátozások miatt (erről részletesebben itt olvashat) itt).

2) Az attribútumok egy új attribútumba való egyesítése

Néha egy feltételcsoporthoz tartozó egyes mezőket egy új mező bevezetésével lehet helyettesíteni.

Ez különösen aktuális az állapotmezők esetében, amelyek általában bit- vagy egész szám típusúak.

Példa:

IsClosed = 0 ÉS Canceled = 0 ÉS Enabled = 0 cserélve van Status = 1.

Itt egy egész számú attribútumot vezetünk be, amely a státuszok táblázatban való kitöltésével biztosított. Ezt követően ennek az új attribútumnak az indexelése történik.

Ez alapvető megoldása a teljesítmény problémájának, hiszen adatokhoz jutunk felesleges számítások nélkül.

3) A nézet anyagiasítása

Sajnálom, de a LINQ lekérdezésekben nem lehet közvetlenül használni ideiglenes táblákat, CTE-ket és táblázatos változókat.

Viszont van még egy optimalizálási mód erre az esetre – ez az indexált nézetek.

A feltételek csoportja (az előző példából) IsClosed = 0 ÉS Canceled = 0 ÉS Enabled = 0 (vagy más hasonló feltételek) kiváló lehetőség arra, hogy ezeket használják egy indexált nézetben, ami egy kis adatszeletet tárol egy nagy mennyiségből.

De van néhány korlátozás a nézet materializálása során:

a beágyazott lekérdezések, a javaslatok VÉGE; helyettesíteni kell a JOIN
nem lehet javaslatokat használni UNION, UNION ALL, KIVÉTEL, INTERSECT
nem lehet táblázatos címkéket és javaslatokat használni OPTION
nincs lehetőség ciklusokkal dolgozni
nem lehet adatokat egy nézetben különböző táblákból kihozni

Fontos emlékezni arra, hogy az indexált nézet használatából származó valódi előnyök gyakorlatilag csak akkor valósíthatók meg, ha azt indexálják.

De a nézet hívásakor ezek az indexek előfordulhat, hogy nem használódnak, és ezek explicit használatához meg kell adni WITH (NOEXPAND).

Mivel a a LINQ lekérdezésekben nem lehet táblázatos címkéket megadni, így egy új nézetet – "burkot" – kell létrehozni a következő formában:

CREATE VIEW NÉV_nézet AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Táblázatos függvények használata

Gyakran az a LINQ lekérdezésekben nagy beágyazott lekérdezésblokkok vagy komplex struktúrájú nézeteket használó blokkok végső lekérdezést alkotnak, amely nagyon bonyolult és nem optimális végrehajtási struktúrát képez.

A táblázatos függvények használatának fő előnyei a LINQ lekérdezésekben:

Lehetőség, hasonlóan a nézetekhez, arra, hogy objektumként használjuk és megadjuk azt, de át lehet adni egy bemenet paraméterek halmazát:
FROM FUNCTION(@param1, @param2 …)
végül rugalmas adatlekérdezést érhetünk el
Táblázatos függvény használata esetén nincsenek olyan szigorú korlátozások, mint az indexált nézetek esetében, amelyeket fentebb leírtam:
1. Táblázatos címkék:
  -kérések, LINQ nem lehet megadni, hogy mely indexek használata szükséges, és meghatározni az adatlekérdezés izolációs szintjét.
  De a függvényben ezek a lehetőségek adottak.
  A függvénnyel elegendően állandó végrehajtási tervet érhetünk el, ahol szabályozva vannak az indexekkel való munka szabályai és az adatlekérdezés szintjei.
2. A függvény használata lehetővé teszi, hogy az indexált nézetekhez képest:
  - összetett adatszűrési logika (beleértve a ciklusok használatát is)
  - adatszűrés különböző táblákból
  - használat UNION és VÉGE;
Ajánlat OPTION nagyon hasznos, amikor biztosítanunk kell a párhuzamosság kezelését OPTION(MAXDOP N), a lekérdezés végrehajtási tervének sorrendjében. Például:
- meg lehet határozni a lekérdezési terv kényszerített újragenerálását OPTION (RECOMPILE)
- meg lehet határozni, hogy a lekérdezés terve kényszerítően használja a lekérdezésben megadott egyesítési sorrendet OPTION (FORCE ORDER)
Részletesebben a OPTION ami le van írva itt.
a legszűkebb és legszükségesebb adatréteg használata:
Nincs szükség nagy adathalmazok cache-ben tartására (mint az indexelhető nézetek esetén), amelyekből további paraméterek alapján kellene szűrni az adatokat.
Például van egy tábla, amelynek szűréséhez AHOL három mezőt használnak (a, b, c).
Feltételezzük, hogy minden lekérdezésnek van egy állandó feltétele a = 0 és b = 0.
Azonban a mezőre vonatkozó lekérdezés c változatosabb.
Tegyük fel, hogy a feltétel a = 0 és b = 0 valóban segít a szükséges adathalmaz korlátozásában ezer rekordra, de a feltétel által a készenlétben tartja a választást száz rekordra.
Itt a táblázatos funkció lehet a kedvezőbb választás.
A táblázatos funkció constansabb és kiszámíthatóbb is a végrehajtási idő tekintetében.

Példák

Nézzük meg a példát a Questions adatbázis példáján keresztül.

Van egy lekérdezés, SELECT, amely több táblát összekapcsol és egy nézetet (OperativeQuestions) használ, amelyben ellenőrizzük az email alapján a hovatartozást (keresztül VÉGE;) az „Aktív lekérdezések”-hez ([OperativeQuestions]):

Lekérdezés № 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])
));

A nézet viszonylag bonyolult felépítéssel bír: al-lekérdezések és rendezési művelet használatával, DISTINCTami általánosan elég erőforrással igényes művelet.

Az OperativeQuestions táblából körülbelül tízezer bejegyzés kiválasztása.

A lekérdezés fő problémája az, hogy a külső lekérdezésből származó bejegyzésekhez belső allekérdezést hajtunk végre az [OperativeQuestions] nézethez, amelynek [Email] = @p__linq__0 alapján kell korlátoznia a kimeneti választ (keresztül VÉGE;) száz bejegyzésre.

És úgy tűnhet, hogy az allekérdezés egyszer kellene, hogy kiszámolja a bejegyzéseket [Email] = @p__linq__0 alapján, majd ezeket a párszáz bejegyzést az Id szerint össze kellene kapcsolni a Questions-tel, és a lekérdezés gyors lenne.

Valójában minden táblát sorosan kapcsol össze: a Questions Id-jének ellenőrzése az OperativeQuestions Id-jével, és a szűrés az Email alapján.

Gyakorlatilag a lekérdezés az összes tízezer OperativeQuestions bejegyzéssel dolgozik, pedig csak a szükséges adatokat kellene megszerezni az Email alapján.

Az OperativeQuestions nézet szövege:

2. számú lekérdezés

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

Az OperativeQuestions nézet kezdeti leképezése a DbContext-ben (EF Core 2)

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

A kezdeti LINQ lekérdezés

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

Ebben a konkrét esetben a probléma megoldását vizsgáljuk infrastruktúra-változtatások nélkül, anélkül, hogy külön táblázatot hoznánk létre a kész eredményekkel („Aktív lekérdezések”), amelyhez adatfeltöltési mechanizmusra lenne szükség, és amelyet folyamatosan naprakészen kellene tartani.

Bár ez egy jó megoldás, létezik egy másik lehetőség is ennek a feladatnak az optimalizálására.

A fő cél az [Email] = @p__linq__0 lekérdezések előcache-elése az OperativeQuestions nézetből.

Bevezetjük a [dbo].[OperativeQuestionsUserMail] táblázatos funkciót az adatbázisba.

Amikor az Email-t bemeneti paraméterként küldjük, visszakapunk egy értéktáblát:

3. számú lekérdezés


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

Itt egy előre meghatározott adatszerkezettel rendelkező értéktábla tér vissza.

Ahhoz, hogy az OperativeQuestionsUserMail lekérdezések optimálisak legyenek, optimális lekérdezési tervekkel, szigorú struktúrára van szükség, nem pedig RETURNS TABLE AS RETURN…

Ebben az esetben a keresett 1. számú lekérdezés 4. lekérdezéssé alakul:

4. számú lekérdezés

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

A nézet és a funkció mappolása a DbContext-ben (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})");
}

A végső LINQ-lekérdezés

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

A végrehajtási időordere 200-800 ms-ról 2-20 ms-ra csökkent, stb., tehát tízszer gyorsabb.

Ha átlagszámítást végzünk, akkor a 350 ms helyett 8 ms-t kaptunk.

Nyilvánvaló előnyök közé tartozik:

a terhelés általános csökkentése az olvasás terén,
a blokkolások valószínűségének jelentős csökkentése.
a blokkolások átlagos idejének csökkentése elfogadható értékekre.

Következtetés

Az adatbázisra irányuló lekérdezések optimalizálása és finomhangolása MS SQL -kérések, LINQ olyan feladat, amelyet meg lehet oldani.

Ebben a munkában nagyon fontos a figyelem és a következetesség.

A folyamat elején:

ellenőrizni kell az adatokat, amelyekkel a lekérdezés dolgozik (értékek, kiválasztott adattípusok).
meg kell megfelelően indexelni ezeket az adatokat.
ellenőrizni kell a táblák közötti kapcsolati feltételek helyességét.

A következő optimalizálási iteráció során megállapítjuk:

a lekérdezés alapjául szolgáló részt és meghatározzuk a lekérdezés fő szűrőjét.
a lekérdezésben ismétlődő, hasonló blokkot és elemezzük a feltételek keresztmetszetét.
SSMS-ben vagy más GUI-ban. SQL Server optimalizáljuk magát. SQL lekérdezés (köztes tároló létrehozása, a végeredmény lekérdezés felépítése e tárolóval (több is lehet)).
az utolsó lépésben, a végeredményt alapul véve. SQL lekérdezés, újraépítjük a struktúrát. A LINQ-lekérdezés.

Végül az így kapott LINQ lekérdezés strukturálisan az optimális SQL-lekérdezéssel az 3. pontból.

Köszönetnyilvánítások

Hatalmas köszönet a kollégáknak, jobgemws, és alex_ozr, a Fortis cégtől, a támogatásért, amelyet a dokumentum elkészítéséhez nyújtottak.

Forrás: habr.com