LINQ-päringute optimeerimise meetodid C#.NET-is

Sissejuhatus

V selles artiklis vaatleme mitmeid optimeerimise meetodeid LINQ-päringutele.
Siin toome välja veel mõned koodi optimeerimise lähenemisviisid, mis on seotud LINQ-päringutega.

On teada, et LINQ(Language-Integrated Query) — see on lihtne ja mugav päringukeel andmeallikate jaoks.

A LINQ to SQL on andmete juurdepääsu tehnoloogia andmebaasisüsteemides. See on võimas tööriist andmetega töötamiseks, kus deklaratiivse keele kaudu konstrueeritakse päringud, mis seejärel muudetakse SQL-päringuteks platvormile ja saadetakse andmebaasiserverile täitmiseks. Meie puhul mõistame andmebaasisüsteeme MS SQL Server.

Siiski, LINQ-päringud ei muutu optimaalselt kirjutatud SQL-päringuteks, mille võiks koostada kogenud DBA, arvestades kõiki optimeerimise nüansse SQL-päringutest:

1. optimaalsetest ühendustest (JOIN) ja tulemuste filtreerimisest (WHERE)
2. palju nüansse ühenduste ja grupitingimuste kasutamisel
3. palju variatsioone tingimuste asendamisel IN järgnevaga EXISTSja NOT IN, <> asendamine EXISTS
4. vahepealne tulemuste vahemälu ajutiste tabelite, CTE-de, tabelimuutujate kaudu
5. kasutades lauset (OPTION) suuniste ja tabeli vihjete määratlemiseks WITH (…)
6. indekseeritavate vaadete kasutamine, et vähendada andmete ülemääraseid lugemisi päringute käigus

Tulevate rakenduste peamised jõudluskitked SQL-päringutest koostamisel LINQ-päringutele on:

1. kogu andmete valimise mehhanismi konsolideerimine ühes päringus
2. identsete koodiblokkide dubleerimine, mis toob lõpuks kaasa mitmekordsed tarbetud andmete lugemised
3. komplekssete tingimuste rühmad (loogilised 'ja' ja 'või') — AND ja OR, ühendades keerukate tingimuste põhjal, toob see kaasa, et optimeerija, omades sobivaid klasterdamata indekseid, vajalike väljade järgi, hakkab lõppkokkuvõttes ikkagi klasterindeksi skaneerimist tegema (INDEX SCAN) tingimuste rühmade kaupa
4. sügav allpäringute pesastamine muudab tõlgendamise väga problemaatiliseks SQL-käsud ja päringute plaani tõlgendamise arendajate ja DBA

Optimeerimise meetodid

Liigume nüüd otse optimeerimise meetodite juurde.

1) Täiendav indekseerimine

Parim on vaadata filtreid peamistel valimis tabelitel, kuna sageli põhineb kogu päring ühel või kahel peamisel tabelil (taotlused-inimesed-toimingud) ja tavapärasel tingimuste kogumil (IsClosed, Canceled, Enabled, Status). Oluline on, et tuvastatud valimitele luuakse vastavad indeksid.

See lahendus on mõistlik, kui valik nende väljade põhjal piirdub oluliselt tagastatava kogumi päringuga.

Näiteks, meil on 500000 taotlust. Siiski on aktiivseid taotlusi vaid 2000 kirjet. Siis õigesti valitud indeks vabastab meid INDEX SCAN suurest tabelist ja võimaldab kiiresti andmeid valida läbi klasterdamata indeksi.

Samuti saab indeksite puudust tuvastada päringute plaanide analüüsi või süsteemi vaadete statistika kogumise vihjete kaudu. MS SQL Server:

1. sys.dm_db_missing_index_groups
2. sys.dm_db_missing_index_group_stats
3. sys.dm_db_missing_index_details

Kõik vaateandmed sisaldavad teavet puuduvate indeksite kohta, välja arvatud ruumiindeksid.

Kuid indeksid ja vahemälu on sageli meetodid, et võidelda halvasti kirjutatud LINQ-päringutele ja SQL-päringutest.

Äärmuslikud elupraktikad näitavad, et äri jaoks on tihti tähtis äritegevuse funktsioonide elluviimine kindlate tähtaegadega. Seetõttu kantakse tihti raskeid päringuid taustale koos vahemällu salvestamisega.

Osaliselt on see põhjendatud, kuna kasutaja ei vaja alati kõige värskemaid andmeid ning kasutajaliidese vastamise tase on aktsepteeritav.

See lähenemine võimaldab lahendada äri päringuid, kuid lõpuks vähendab see infosüsteemi töövõimet, lihtsalt viibides probleemide lahendamise edasi.

Samuti tuleks meeles pidada, et uute indeksite lisamiseks vajalike otsingute käigus võivad pakkumised MS SQL optimeerimise kohta olla ebatäpsed, sealhulgas järgmistes tingimustes:

1. kui sarnaste väljade kombinatsiooniga indeksid juba eksisteerivad
2. kui tabeli väljad ei saa olla indekseeritud indeksimise piirangute tõttu (sellest on üksikasjalikumalt kirjeldatud siit).

2) Atribuutide ühendamine uue atribuudina

Mõnikord on mõningaid välju ühest tabelist, mille alusel toimub tingimuste rühm, võimalik asendada ühe uue välja sisseviimisega.

See on eriti oluline olekute väljade jaoks, mis on tüübilt tavaliselt kas bite või täisarv.

Näide:

IsClosed = 0 JA Canceled = 0 JA Enabled = 0 asendatakse Status = 1.

Siin sisestatakse täisarvuline atribuut Status, täites need olekud tabelis. Seejärel indekseeritakse see uus atribuut.

See on fundamentaalne lahendus jõudlusprobleemile, kuna me küsime andmeid ilma liigsete arvutusteta.

3) Vaate materialiseerimine

Kahjuks LINQ-i päringutes ei saa kasutada ajutisi tabeleid, CTE ja tabelimuutujaid.

Siiski on veel üks optimeerimise viis — indekseeritavad vaated.

Konditsioneerimiste rühm (ülevaltoodud näites) IsClosed = 0 JA Canceled = 0 JA Enabled = 0 (või muud sarnased tingimused) on hea valik nende kasutamiseks indekseeritavas vaates, mis talletab väikese osa andmetest suurest hulgast.

Kuid vaate materialiseerimisel on mitmeid piiranguid:

1. alamkonstruktsioonide, ettepanekute EXISTS peavad asendama kasutamise JOIN
2. ei saa kasutada ettepanekuid UNION, UNION ALL, ERAND, INTERSECT
3. ei saa kasutada tabeli vihjeid ja ettepanekuid OPTION
4. tsüklitega töötamine ei ole võimalik
5. teavet ei saa kuvada ühelt vaatepunktilt erinevatest tabelitest

Oluline on meeles pidada, et indeksitelt lähtuvate vaadete tegelik kasu saab tõeliselt saavutada ainult nende indekseerimise korral.

Kuid vaate kutsumisel ei pruugi neid indekseid kasutada, ja nende selgeks kasutamiseks tuleb märgata WITH (NOEXPAND).

Kuna LINQ-i päringutes tabeli vihjeid määrata ei saa, tuleb luua veel üks vaade - 'ümbris', järgmise struktuuriga:

CREATE VIEW VAATE_NIMI AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Tabelifunktsioonide kasutamine

Sageli LINQ-i päringutes suured alampäringud või struktuurselt keerukate vaadetega plokid moodustavad lõpppäringu väga keerulise ja optimeerimata täitmisstruktuuriga.

Tabelifunktsioonide kasutamise peamised eelised LINQ-i päringutes:

1. Võimalus, nagu ka vaadete puhul, kasutada ja määrata objektina, kuid saab edastada rikka sisendi parameetrite komplekti:
   FROM FUNCTION(@param1, @param2 …)
   lõpptulemuseks on paindlik andmevalik
2. Tabeli funktsiooni kasutamisel ei ole nii tugevaid piiranguid kui ülaltoodud indekseeritud vaadete puhul:
   1. Tabeli vihjed:
      kaudu LINQ ei saa määrata, milliseid indekse tuleb kasutada ja andmete isolatsiooni taset päringus.
      Kuid funktsioonis on need võimalused olemas.
      Funktsiooniga saab saavutada piisavalt stabiilse päringuplaani, kus on kehtestatud reeglid indeksitega töötamiseks ja andmete isolatsiooni tasemed.
   2. Funktsiooni kasutamine võimaldab, võrreldes indekseeritud vaadetega, saada:
      • keerulist andmete valikuloogikat (kuni tsüklite kasutamiseni)
      • andmete valikuid paljusid erinevaid tabeleid.
      • kasutamine UNION ja EXISTS

3. Pakkumine OPTION on väga kasulik, kui peame tagama paralleelsuse juhtimise. OPTION(MAXDOP N), paarilise päringu plaani korras. Näiteks:
   • saab määrata päringu plaani sundreteerimise. OPTION (RECOMPILE)
   • saab määrata vajaduse tagada päringu plaani sundkasutamine ühendamise järjekorras, mis on päringus märgitud. OPTION (FORCE ORDER)

   Täpsemini teema kohta OPTION detailsemalt siit.

4. Kasutades kõige kitsamat ja vajalikku andmepiiri:
   Ei ole vajalik hoida suuri andmekogusid vahemä caches (näiteks indeksiseeritud vaadete korral), millest tuleb veel andmeid filtrite kaudu eraldada.
   Näiteks on tabel, mille puhul WHERE kasutatakse kolme väljakutset (a, b, c).

   Konditsioneerimise kohaselt on kõigil päringutel pidev tingimus a = 0 ja b = 0.

   Kuid päring c on mitmekesisem.

   Oletame, et tingimus a = 0 ja b = 0 aitab meil tõeliselt piirata vajalikku saadud komplekti tuhandete teadeteni, kuid tingimus koos piirab meie valikut sajani.

   Siin võib tabelifunktsioon osutuda kasulikuks valikuks.

   Samuti on tabelifunktsioon ennustatav ja ühtlane täitmise ajal.

Näited

Vaatame rakenduse näidet küsimuste andmebaasis.

On päring SELECT, mis ühendab mitu tabelit ja kasutab ühte vaadet (OperativeQuestions), kus kontrollitakse e-posti kaudu kuuluvust (kaudu EXISTS) "Aktiivsed päringud"([OperativeQuestions]):

Päring nr 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])
));

Kujundus on üsna keeruline: see sisaldab alavalduste ühendusi ja sorteerimist. DISTINCT, mis on üldiselt piisavalt ressursimahukas operatsioon.

Andmete valimine OperativeQuestions'ist, umbes kümme tuhat kirjet.

Selle päringu peamine probleem on see, et välise päringu kirjed käivitavad sisemise alavaldusselektiivi [OperativeQuestions] üle, mis peaks [Email] = @p__linq__0 kaudu väljundi valikut piirama (kaudu EXISTS) sadade kirjeteni.

Ja võib tunduda, et alampäring peaks üks kord arvutama kirjed, mille [Email] = @p__linq__0, ja seejärel peaks need paar sada kirjet Id järgi küsimustega ühendama, muutes päringu kiireks.

Tegelikult toimub kõigi tabelite järjestikune ühendamine: ja Id Questions-i vastavuse kontrollimine OperativeQuestions-i Id-dega ning filtreerimine Email'i järgi.

Sisuliselt töötab päring kõigi kütte kümnete tuhandete OperativeQuestions kirjadega, kuigi tegelikult on vajalikud ainult huvipakkuvad andmed Email'i järgi.

OperativeQuestions-i esituse tekst:

Päring nr 2

 
LOOMINE VAATE [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));

Algne esitus kaardistamine DbContextis (EF Core 2)

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

Algne LINQ-päring

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

Käesolevas konkreetses olukorras käsitletakse probleemilahendust ilma infrastruktuuri muudatusteta, ilma eraldi tabeli loomata valmis tulemustega („Aktiivsed päringud“), mille jaoks oleks vajalik selle andmete täiendamise mehhanism ning selle ajakohasena hoidmine.

Kuigi see on hea lahendus, on ka teine võimalus selle ülesande optimeerimiseks.

Peamine eesmärk on salvestada kirjed, mille puhul [Email] = @p__linq__0 vaates OperativeQuestions.

Sisestame tabelifunktsiooni [dbo].[OperativeQuestionsUserMail] andmebaasi.

Sisendparameetrina Emaili saades saame tagasi väärtustetabeli:

Päring nr 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

Siin tagastatakse väärtuste tabel, millel on eelnevalt määratletud andmestruktuur.

Et OperativeQuestionsUserMail'i päringud oleksid optimaalsed ja neil oleksid optimaalsed päringukavad, on vajalik rangem struktuur, mitte TAGASTAB TABELINA KUI TAGASTUS…

Antud juhul muudetakse otsitav Päring 1 Päringuks 4:

Päring nr 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]);

Vaate ja funktsiooni kaardistamine DbContextis (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})");
}

Lõplik LINQ päring

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

Täideviimise aeg vähenes 200-800 ms, kuni 2-20 ms, jne, see tähendab, et see on kümneid kordi kiirem.

Keskelt arvestades, saime 350 ms asemel 8 ms.

Ilmselt saame ka järgmised eelised:

1. üldine koormuse vähenemine lugemisel,
2. tunduvalt väiksem tõenäosus lukustuste tekkeks
3. keskmise lukustuse aja vähendamine vastuvõetavatesse väärtustesse

Kokkuvõte

Andmebaasi töötlemise optimeerimine ja häälestamine MS SQL kaudu LINQ on ülesanne, mille saab lahendada.

Selles töös on äärmiselt oluline tähelepanu ja järjepidevus.

Protsessi alguses:

1. on vajalik kontrollida andmeid, millega päring töötab (väärtused, valitud andmetüübid)
2. teha nende andmete õige indekseerimine
3. kontrollida ühendustingimuste õigsust tabelite vahel

Järgmise optimeerimise iteratsiooni käigus tuvastatakse:

1. päringu alus ja määratakse päringu põhifilter
2. korratud sarnased päringu osad ja analüüsitakse tingimuste kattuvust
3. SSMS-is või muus GUI-s SQL Server optimeeritakse ise SQL-päring (vaheandmete salvestamise esitlemine, lõpppäringu koostamine selle salvestuse abil (võivad olla mitu))
4. viimasel etapil, võttes aluseks lõpppäringu SQL-päring, muudetakse struktuuri LINQ-päring

Lõppude lõpuks peab saadud LINQ-päring struktuurilt olema identne kindlaks tehtud optimaalsele SQL-päringule punktist 3.

Tänud

Suur tänu kolleegidele jobgemws ja alex_ozr ettevõttest Fortis abi eest selle materjali ettevalmistamisel.

Allikas: habr.com