🥇Módszerek a LINQ-lekérdezések optimalizálására a C#.NET |-ben ProHoster

Bevezetés

В ezt a cikket néhány optimalizálási módszert figyelembe vettünk LINQ lekérdezések.
Itt is bemutatunk néhány további megközelítést a kódoptimalizáláshoz kapcsolódóan LINQ lekérdezések.

Ismert, hogy a LINQ(Language-Integrated Query) egy egyszerű és kényelmes nyelv egy adatforrás lekérdezéséhez.

А LINQ az SQL-hez egy DBMS-ben lévő adatok elérésére szolgáló technológia. Ez egy hatékony eszköz az adatokkal való munkavégzéshez, ahol a lekérdezések egy deklaratív nyelven keresztül jönnek létre, amelyeket ezután konvertál SQL lekérdezések platformon, és végrehajtásra elküldi az adatbázis-kiszolgálónak. Esetünkben DBMS alatt értjük MS SQL Server.

Azonban, LINQ lekérdezések nem alakítják át optimálisan írottakká SQL lekérdezések, amelyet egy tapasztalt DBA az optimalizálás minden árnyalatával meg tud írni SQL lekérdezések:

optimális kapcsolatok (JOIN) és az eredmények szűrése (AHOL)
sok árnyalat a kapcsolatok és a csoportfeltételek használatában
a cserefeltételek sokféle változata IN on LÉTEZIKи NEM BENT, <> bekapcsolva LÉTEZIK
az eredmények köztes gyorsítótárazása ideiglenes táblákon, CTE-n, táblaváltozókon keresztül
mondathasználat (OPTION) utasításokkal és táblázati tippekkel VAL VEL (...)
indexelt nézetek használata a kijelölések során előforduló redundáns adatleolvasások egyik eszközeként

A fő teljesítmény szűk keresztmetszetek a kapott SQL lekérdezések összeállításkor LINQ lekérdezések a következők:

a teljes adatkiválasztási mechanizmus összevonása egy kérelemben
azonos kódblokkok megkettőzése, ami végül több szükségtelen adatolvasáshoz vezet
többkomponensű feltételek csoportjai (logikai „és” és „vagy”) - ÉS и OR, összetett feltételekkel kombinálva azt a tényt eredményezi, hogy az optimalizáló, amely megfelelő nem fürtözött indexekkel rendelkezik a szükséges mezőkhöz, végül elkezd pásztázni a fürtözött indexhez (INDEX SCAN) feltételcsoportok szerint
Az allekérdezések mély egymásba ágyazása nagyon problémássá teszi az elemzést SQL utasítások és a lekérdezési terv elemzése a fejlesztők részéről és DBA

Optimalizálási módszerek

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

1) További indexelés

A legjobb, ha a fő kiválasztási táblákon szűrőket veszünk figyelembe, mivel nagyon gyakran a teljes lekérdezés egy vagy két fő tábla köré épül fel (alkalmazások-emberek-műveletek) és szabványos feltételekkel (Lezárva, Mégse, Engedélyezve, Állapot). Fontos, hogy az azonosított mintákhoz megfelelő indexeket hozzunk létre.

Ennek a megoldásnak akkor van értelme, ha ezeknek a mezőknek a kiválasztása jelentősen korlátozza a lekérdezéshez visszaadott halmazt.

Például 500000 2000 alkalmazásunk van. Azonban csak XNUMX aktív alkalmazás van. Ekkor egy helyesen kiválasztott index megment minket INDEX SCAN egy nagy táblán, és lehetővé teszi az adatok gyors kiválasztását egy nem fürtözött indexen keresztül.

Az indexek hiánya a lekérdezési tervek elemzésével vagy a rendszernézeti statisztikák gyűjtésével is azonosítható. MS SQL Server:

Minden nézetadat tartalmaz információt a hiányzó indexekről, a térbeli indexek kivételével.

Az indexek és a gyorsítótárazás azonban gyakran a rosszul írás következményei elleni küzdelem módszerei LINQ lekérdezések и SQL lekérdezések.

Amint azt az élet kemény gyakorlata mutatja, egy vállalkozás számára gyakran fontos, hogy bizonyos határidőkre bevezesse az üzleti funkciókat. Ezért a súlyos kérések gyakran a háttérbe kerülnek a gyorsítótárazással.

Ez részben indokolt, mivel a felhasználónak nem mindig van szüksége a legfrissebb adatokra, és a felhasználói felület reagálóképessége is elfogadható.

Ez a megközelítés lehetővé teszi az üzleti igények megoldását, de végső soron csökkenti az információs rendszer teljesítményét azáltal, hogy egyszerűen késlelteti a problémák megoldását.

Azt is érdemes megjegyezni, hogy a szükséges indexek keresése során javaslatokat kell tenni MS SQL az optimalizálás helytelen lehet, beleértve a következő feltételeket is:

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

2) Attribútumok egyesítése egy új attribútummá

Néha egy tábla egyes mezői, amelyek egy feltételcsoport alapjául szolgálnak, helyettesíthetők egy új mező bevezetésével.

Ez különösen igaz az állapotmezőkre, amelyek általában bites vagy egész típusúak.

Példa:

IsClosed = 0 ÉS törölve = 0 ÉS Engedélyezve = 0 helyébe a Állapot = 1.

Itt kerül bevezetésre az integer Status attribútum, amely biztosítja, hogy ezek az állapotok megjelenjenek a táblázatban. Ezután ezt az új attribútumot indexeli.

Ez egy alapvető megoldás a teljesítményproblémára, mert felesleges számítások nélkül férünk hozzá az adatokhoz.

3) A nézet materializálása

Sajnos be LINQ lekérdezések Az ideiglenes táblák, CTE-k és táblaváltozók nem használhatók közvetlenül.

Van azonban egy másik módja is az optimalizálásnak erre az esetre – az indexelt nézetek.

Feltételcsoport (a fenti példából) IsClosed = 0 ÉS törölve = 0 ÉS Engedélyezve = 0 (vagy más hasonló feltételek halmaza) jó lehetőséggé válik indexelt nézetben való használatukra, gyorsítótárazva egy kis adatszeletet egy nagy halmazból.

A nézet megvalósítása során azonban számos korlátozás érvényesül:

részlekérdezések, záradékok használata LÉTEZIK használatával kell cserélni JOIN
nem használhat mondatokat UNION, UNIÓ MINDEN, KIVÉTEL, METSZÉS
Nem használhat táblázati tippeket és záradékokat OPTION
nincs lehetőség ciklusokkal dolgozni
Lehetetlen egy nézetben megjeleníteni az adatokat különböző táblázatokból

Fontos megjegyezni, hogy az indexelt nézet használatának valódi előnye csak akkor érhető el, ha ténylegesen indexeli.

De egy nézet meghívásakor ezek az indexek nem használhatók, és kifejezetten használatukhoz meg kell adni WITH (NO EXPAND).

Óta LINQ lekérdezések A táblázat tippjeit nem lehet definiálni, ezért létre kell hoznia egy másik reprezentációt - a következő formájú "burkolót":

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

4) Táblázatfüggvények használata

Gyakran be LINQ lekérdezések Az allekérdezések nagy blokkjai vagy az összetett szerkezetű nézeteket használó blokkok egy nagyon összetett és szuboptimális végrehajtási struktúrájú végső lekérdezést alkotnak.

A táblafüggvények használatának legfontosabb előnyei LINQ lekérdezések:

A nézetekhez hasonlóan objektumként használható és megadható, de átadhat egy sor bemeneti paramétert:
FROM FUNCTION(@param1, @param2...)
Ennek eredményeként rugalmas adatmintavétel érhető el
Táblázatfüggvény használata esetén nincsenek olyan erős korlátozások, mint a fent leírt indexelt nézetek esetében:
1. Táblázat tippek:
  keresztül LINQ Lekérdezéskor nem határozhatja meg, hogy mely indexeket kell használni, és nem határozhatja meg az adatok elkülönítési szintjét.
  De a funkció rendelkezik ezekkel a képességekkel.
  A funkcióval egy meglehetősen állandó végrehajtási lekérdezési tervet érhet el, ahol az indexekkel való munkavégzés szabályai és az adatok elkülönítési szintjei vannak meghatározva.
2. A függvény használata az indexelt nézetekkel összehasonlítva lehetővé teszi a következők elérését:
  - összetett adatmintavételezési logika (még hurkok használatával is)
  - adatok lekérése sok különböző táblából
  - használat UNION и LÉTEZIK
Предложение OPTION nagyon hasznos, ha egyidejű vezérlést kell biztosítanunk OPCIÓ (MAXDOP N), a lekérdezés végrehajtási tervének sorrendje. Például:
- megadhatja a lekérdezési terv kényszerített újralétrehozását OPCIÓ (ÚJRABEÁLLÍTÁS)
- megadhatja, hogy kényszerítse-e a lekérdezési tervet a lekérdezésben megadott összekapcsolási sorrend használatára OPCIÓ (KÉNYSZERÍTÉS)
További részletek a OPTION leírta itt.
A legszűkebb és leginkább szükséges adatszelet használata:
Nem kell nagy adathalmazokat gyorsítótárban tárolni (mint az indexelt nézetek esetében), ahonnan továbbra is paraméterenként kell szűrni az adatokat.
Például van egy táblázat, amelynek szűrője AHOL három mezőt használnak (a, b, c).

Hagyományosan minden kérésnek állandó feltétele van a = 0 és b = 0.

Azonban a kérés a területen c változóbb.

Legyen a feltétel a = 0 és b = 0 Valóban segít abban, hogy a szükséges eredményhalmazt több ezer rekordra korlátozzuk, de a feltétel be van kapcsolva с száz rekordra szűkíti a választékot.

Itt a táblázat funkció jobb választás lehet.

Ezenkívül a táblafüggvények kiszámíthatóbbak és konzisztensebbek a végrehajtási időben.

Примеры

Nézzünk meg egy példa megvalósítást a Questions adatbázis használatával.

Van egy kérés SELECT, amely több táblát egyesít és egy nézetet használ (OperativeQuestions), amelyben a hovatartozás ellenőrzése e-mailben történik (a LÉTEZIK) az „Operatív kérdések” részhez:

1. számú kéré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 [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 meglehetősen összetett felépítésű: vannak benne részlekérdezés csatlakozások, és rendezést használ KÜLÖNBÖZŐ, ami általában meglehetősen erőforrás-igényes művelet.

Az OperativeQuestions egy mintája körülbelül tízezer rekord.

A fő probléma ezzel a lekérdezéssel az, hogy a külső lekérdezés rekordjaihoz egy belső segédlekérdezés fut le az [OperativeQuestions] nézetben, aminek az [Email] = @p__linq__0 esetén lehetővé kell tennie a kimenet kiválasztásának korlátozását (a LÉTEZIK) akár több száz rekordot.

És úgy tűnhet, hogy az allekérdezésnek egyszer ki kell számítania a rekordokat a következővel: [Email] = @p__linq__0, majd ezt a pár száz rekordot az Id-vel össze kell kapcsolni a Kérdésekkel, és a lekérdezés gyors lesz.

Valójában az összes tábla szekvenciális kapcsolata van: az Id Questions és az OperativeQuestions azonosítójának megfelelőségének ellenőrzése, valamint az e-mail szerinti szűrés.

Valójában a kérés működik mind a több tízezer OperativeQuestions rekorddal, de csak az érdeklő adatokra van szükség e-mailben.

OperativeQuestions nézet szövege:

2. számú kéré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));

Kezdeti nézet leképezése DbContextben (EF Core 2)

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

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 ennek a problémának a megoldását fontolgatjuk infrastrukturális változtatások nélkül, külön táblázat bevezetése nélkül a kész eredményekkel („Active Queries”), amihez szükség lenne egy olyan mechanizmusra, amely azt adatokkal kitölti és naprakészen tartja. .

Bár ez jó megoldás, van egy másik lehetőség a probléma optimalizálására.

A fő cél az [Email] = @p__linq__0 bejegyzések gyorsítótárazása az OperativeQuestions nézetből.

Vezesse be az adatbázisba a [dbo].[OperativeQuestionsUserMail] táblafüggvényt.

Ha bemeneti paraméterként e-mailt küldünk, akkor visszakapunk egy értéktáblázatot:

3. számú kéré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

Ez egy előre meghatározott adatszerkezettel rendelkező értéktáblázatot ad vissza.

Ahhoz, hogy az OperativeQuestionsUserMail lekérdezései optimálisak legyenek, és optimális lekérdezési tervei legyenek, szigorú szerkezetre van szükség, és nem VISSZATÉRÉSI TÁBLÁZAT VISSZATÉRÍTÉSKÉNT...

Ebben az esetben a szükséges 1. lekérdezés 4. lekérdezéssé alakul:

4. számú kéré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]);

Nézetek és funkciók leképezése a DbContextben (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})");
}

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ő sorrendje 200-800 ms-ról lecsökkent 2-20 ms-ra stb., azaz több tízszer gyorsabb.

Ha átlagosabban vesszük, akkor 350 ms helyett 8 ms-ot kaptunk.

A nyilvánvaló előnyökből a következőket kapjuk:

az olvasási terhelés általános csökkenése,
jelentősen csökkenti az elzáródás valószínűségét
az átlagos blokkolási idő csökkentése elfogadható értékekre

Teljesítmény

Adatbázishívások optimalizálása és finomhangolása MS SQL keresztül LINQ egy megoldható probléma.

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

A folyamat elején:

ellenőrizni kell, hogy milyen adatokkal működik a kérés (értékek, kiválasztott adattípusok)
végezze el ezen adatok megfelelő indexelését
ellenőrizze a táblák közötti összekapcsolási feltételek helyességét

A következő optimalizálási iteráció felfedi:

a kérés alapján, és meghatározza a fő kérésszűrőt
hasonló lekérdezési blokkok ismétlése és a feltételek metszéspontjának elemzése
SSMS-ben vagy más grafikus felhasználói felületen SQL Server optimalizálja magát SQL lekérdezés (köztes adattár kiosztása, a kapott lekérdezés felépítése ezzel a tárolóval (több is lehet))
az utolsó szakaszban, a kapott eredményt alapul véve SQL lekérdezés, a szerkezet átépítés alatt áll LINQ lekérdezés

A kapott LINQ lekérdezés szerkezetében azonosnak kell lennie az azonosított optimálistal SQL lekérdezés 3. ponttól.

Köszönetnyilvánítás

Nagyon köszönöm a kollégáknak jobgemws и alex_ozr a cégtől Fortis segítségért az anyag elkészítésében.

Forrás: will.com

A LINQ-lekérdezések optimalizálásának módszerei C#.NET-ben