C#.NET LINQ-päringute optimeerimise meetodid

Sissejuhatus

Uues selles artiklis kaalus mõtleme erinevatele optimeerimise meetoditele LINQ-päringud.
Siin on veel mõned koodide optimeerimise lähenemised, mis on seotud LINQ-päringutega.

On teada, et LINQ(Language-Integrated Query) on lihtne ja mugav keelepäringute süsteem andmeallikatele.

A LINQ to SQL on andmebaasidele juurdepääsu tehnoloogia. See on võimas tööriist andmetega töötamiseks, kus deklaratiivse keele kaudu konstrueeritakse päringud, mis seejärel muudetakse SQL-päringuid platvormi poolt ja saadetakse andmebaasi serverisse täitmiseks. Meie kontekstis mõistame andmebaasina MS SQL Server.

Kuid LINQ-päringud ei muutu optimaalselt kirjutatuks SQL-päringuid, mida oskab kirjutada kogenud DBA kõigi optimeerimise nüanssidega SQL-päringud:

1. optimaalsed ühendused (JOIN) ja tulemuste filtreerimine (KUS)
2. palju nüansse ühenduste ja grupi tingimuste kasutamisel
3. palju variatsioone tingimuste asendamisel IN . Tundub, et EXISTSja NOT IN, EXISTS
4. vahepealne tulemuste vahemälu ajutiste tabelite, CTE-de, tabelite muutujate kaudu
5. kasutades lauseid (OPTION) juhiste ja tabeli vihjete määrangutega WITH (…)
6. kasutades indekseeritud vaateid, kui üht vahendit liigsete andmelugemiste vältimiseks valimite korral

Peamised kitsaskohad tekkivatel SQL-päringud kompileerimise käigus LINQ-päringud on:

1. konsolideerimine kogu andmete valimise mehhanismi ühte päringusse
2. identsete koodiblokkide dubleerimine, mis toob lõpuks kaasa mitmekordseid liigseid andmelugemisi
3. komplekssete tingimuste rühmad (loogilised „ja” ja „või”) — JA ja OR, keerukatesse tingimustesse ühendades toob see kaasa, et optimeerija, omades sobivaid mitteklastrilisi indekseid vajalikel väliadel, hakkab lõppkokkuvõttes ikkagi klastrisse indeksi skaneerimist tegema (INDEX SCAN) tingimusgruppide järgi
4. sügav alampäringute pesitsus muudab SQL-käskude ja päringute plaani analüüsi arendajate poolt väga probleemseks Optimeerimise meetodid DBA

Liigume nüüd otse optimeerimise meetoditesse.

1) Täiendav indekseerimine

Parim on kaaluda filtreid peamistel valimistabelitel, kuna väga sageli ehitatakse kogu päring ühe või kahe peamise tabeli (taotlused-inimesed-operatsioonid) ümber ja tavalise tingimuste kogumiga (IsClosed, Canceled, Enabled, Status). Oluline on luua vastavad indeksid tuvastatud valimitele.

Parima on filtreid vaadelda põhiväljundite tabelites, kuna tihti ehitatakse kogu päring ühele või kahele põhiteabele (taotlused-isikud-tegevused) ja tavalise tingimuste kogumiga (IsClosed, Canceled, Enabled, Status). Oluline on luua vastavad indeksid leitud väljavõtete jaoks.

See raisi on lahendusel mõte, kui valik nende väljade järgi piirab oluliselt tagastatavaid andmeid.

Näiteks, meil on 500000 taotlust. Siiski, aktiivseid taotlusi on ainult 2000 kirjet. Siis õigesti valitud indeks päästab meid INDEX SCAN suurustabelist ning võimaldab kiirelt andmeid läbi mitteklasterdatud indeksi valida.

Samuti saab indeksite puudumise tuvastada päringute plaanide analüüsi vihjete või süsteemsete vaadete statistika kogumise 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 ruumilised indeksid.

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

Karm praktika näitab, et äri jaoks on sageli oluline äriomaduste rakendamine kindlate tähtaegadega. Seetõttu tõlgitakse sageli keerulised päringud taustsüsteemi koos vahemäluga.

Osaliselt on see õigustatud, kuna kasutaja ei vaja alati kõige värskemaid andmeid ning kasutajaliidese vastamise tase on vastuvõetav.

See lähenemine võimaldab lahendada äri päringud, kuid alandab lõpuks infotehnoloogilise süsteemi tõhusust, lihtsalt probleemide lahendamise edasi lükates.

Samuti tuleks meeles pidada, et uute indeksite lisamiseks vajalike leidmine, ettepanekud MS SQL optimeerimise osas võivad olla ebatäpsed, sealhulgas järgmistes tingimustes:

1. kui juba eksisteerivad sarnaste väljadega indeksid
2. kui väljad tabelis ei saa indeksit moodustada indeksimise piirangute tõttu (täiendav teave on kirjeldatud siin).

2) Atributide ühendamine uue atribuudina

Mõnikord saab mõningaid tingimuste grupina toimivaid välju asendada uue välja loomisega.

See on eriti aktuaalne olekuväljade puhul, mis on tavaliselt kas bitilised või täisarvulised.

Näide:

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

Siin tutvustatakse täisarvulist atribuuti Status, mis tagatakse nende staatuste täitmisega tabelis. Edasi toimub selle uue atribuudi indekseerimine.

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

3) Vaate materiaaliseerimine

Kahjuks ei LINQ päringutes ei saa otse kasutada ajutisi tabeleid, CTE-d ja tabelimuutujad.

Siiski on olemas veel üks optimeerimise viis selle juhtumi jaoks — indeksit põhinevad vaated.

Klauslite rühm (ülevalt toodud näites) IsClosed = 0 JA Canceled = 0 JA Enabled = 0 (või kogum teisi sarnaseid klausi) saab hästi kasutada indeksit põhinevas vaates, hoidudes väikese andmeosa suurtest kogustest.

Kuid vaate materialiseerimisel on mitmeid piiranguid:

1. ala päringute kasutamine, klausid EXISTS peavad olema asendatud kasutamisega JOIN
2. ei saa kasutada klausi kogumi operaator, toetavad, UNION ALL, ERAND, TABLESAMPLE
3. ei saa kasutada tabelihindeid ega klausi OPTION
4. pole võimalust töötada tsüklitega
5. pole võimalik andmeid ühes vaates erinevatest tabelitest väljendada

Oluline on meeles pidada, et indeksit põhinevast vaate kasutamise tegelik kasu saab tegelikult ainult siis, kui see on indekseeritud.

Kuid vaate nimetamisel ei pruugi neid indekseid kasutatud olla, ja nende selgeks kasutamiseks tuleb märkida WITH (NOEXPAND).

mrkaran LINQ päringutes ei saa määrata tabelihindeid, nii et tuleb teha veel üks vaade — „ümbris” järgmises vormis:

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

4) Tabelifunktsioonide kasutamine

Tihti on LINQ päringutes suured ala päringute plokid või plokid, mis kasutavad keerulise struktuuriga vaateid, lõpuks moodustavad päringu, millel on väga keeruline ja ebaefektiivne täitmistruktuur.

Tabelifunktsioonide kasutamise peamised eelised LINQ päringutes:

1. Võimalus, nagu ka vaadete puhul, kasutada ja märgata kui objekti, kuid saab edastada sisendparameetrite kogumi:
   FROM FUNCTION(@param1, @param2 …)
   lõpuks on võimalik saavutada andmete paindlik valik
2. Tabelifunktsiooni kasutamisel pole selliseid rangeid piiranguid nagu eespool kirjeldatud indeksit põhinevates vaadetes:
   1. Tabelihinded:
      läbi LINQ ei saa märkida, milliseid indekseid peab kasutama ja määrata andmete isolatsioonitaset päringus.
      Kuid funktsioonis on need võimalused olemas.
      Funktsiooni abil on võimalik saavutada piisavalt püsiv päringute täitmisplaan, kus on määratletud reeglid indeksite jaoks ja andmete isolatsioonitasemed
   2. Funktsiooni kasutamine võimaldab võrreldes indeksit põhinevate vaadetega saada:
      • keerulise andmevaliku loogika (sealhulgas tsüklite kasutamine)
      • andmete valimine erinevatest tabelitest
      • omandi resize. kogumi operaator, toetavad ja EXISTS

3. Pakkumine OPTION on väga kasulik, kui peame tagama paralleelsuse juhtimise OPTION(MAXDOP N), päringu täitmisplaani järjekord. Näiteks:
   • võib määrata sunniviisilise päringu plaani loomise OPTION (RECOMPILE)
   • võib määrata vajaduse tagada päringu plaanile sunduslik kasutamine ühendusjärjekorrast, mis on määratletud päringus OPTION (FORCE ORDER)

   Detailsemalt OPTION kirjeldatud siin.

4. Kasutades kõige kitsamat ja vajaliku andmeosa:
   Ei ole vaja hoida suuri andmekogumeid cache'ides (nagu indekseeritud vaadetega), millest veel tuleks filtri järgi andmeid valida.
   Näiteks on tabel, mille filtreerimiseks KUS kasutatakse kolme välja (a, b, c).

   Eeldatakse, et kõikidel päringutel on konstantne tingimus a = 0 ja b = 0.

   Siiski on päring väljale c rohkem varieeruv.

   Olgu tingimus a = 0 ja b = 0 tõeliselt abiks, et piirata vajaliku tulemuse kogumit tuhandete rekordite juurde, kuid tingimus jot piirab valiku saja rekordiga.

   Siin võib tabelifunktsioon osutuda kasulikumaks valikuks.

   Lisaks on tabelifunktsioon ennustatav ja stabiilne täitmise ajas.

Näited

Vaatame rakenduse näidet andmebaasis Questions.

On päring SELECT, mis ühendab mitmeid tabeleid ja kasutab ühte vaadet (OperativeQuestions), kus kontrollitakse e-posti kaudu seotust (kasutades EXISTS) "Aktiivsete päringute" ([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])
));

Vaade omab üsna keerulist struktuuri: seal on alampäringute ühendusi ja kasutamine sorteerimist STDEV, mis on üldiselt piisavalt ressursimahukas operatsioon.

Valik OperativeQuestions'i umbes kümne tuhande kirje ulatuses.

Selle päringu peamine probleem on see, et välisest päringust saadud kirjeid piiratakse sisemise alampäringuga vaates [OperativeQuestions], mis peab meie väljundit piirama [Email] = @p__linq__0 (läbi EXISTS) sadade kirjeteni.

Ja võib tunduda, et alampäring peaks ühel korral arvestama kirjeid [Email] = @p__linq__0, ning siis peaks need paar sada kirjet ühendama Id järgi Questions-iga, ja päring oleks kiire.

Tegelikult toimub kõigi tabelite järjestikune ühendamine: nii Id Questions'ite vastavuse kontrollimine Id-dega OperativeQuestions'is, kui ka filtreerimine Email'i järgi.

Põhimõtteliselt töötab päring kõigi kümnete tuhandete kirje üle OperativeQuestions'is, kuigi vajalikud on ainult huvitatud andmed Email'i kohta.

OperativeQuestions'i vaate tekst:

Päring nr 2

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

Algne vaate kaardistus 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();

Antud konkreetses olukorras kaalutakse selle probleemi lahendamist ilma infrastruktuurimuudatusteta, ilma eraldi tabeli loomata valmis tulemustega («Aktiivsed päringud»), mille jaoks oleks vajalik mehhanism selle andmete täitmiseks ja ajakohasena hoidmiseks.

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

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

Introduseeritakse tabelifunktsioon [dbo].[OperativeQuestionsUserMail] andmebaasi.

Saates sisendparameetrina Email, 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äärtustetabel eelnevalt määratletud andmestruktuuriga.

Kuna päringud OperativeQuestionsUserMail'i suhtes peaksid olema optimaalsed, omama optimaalseid päringute plaane, on vajalik rangelt määratletud struktuur, mitte RETURNS TABLE AS RETURN…

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 mappimine 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");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> 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();

Ajava täitmise aeg vähenes 200-800 ms-lt 2-20 ms-ni, st kümneid kordi kiiremini.

Keskelt võttes saime 350 ms asemel 8 ms.

Mõningad ilmsed eelised on ka:

1. üldine koormuse vähenemine lugemisel,
2. märkimisväärne blokeeringute tõenäosuse vähenemine.
3. keskmise blokeeringu aja vähendamine vastuvõetavatesse väärtustesse.

Kokkuvõte

Andmebaasi päringute optimeerimine ja häälestamine MS SQL läbi LINQ on ülesanne, mida on võimalik lahendada.

Selles protsessis on äärmiselt oluline tähelepanelikkus 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 tabelite vahelisi ühendustingimuste õigsust.

Järgmiste optimeerimisetappide jooksul selguvad:

1. päringu alus ja määratakse päringu põhiline filter.
2. korduvad sarnased päringu plokid ja analüüsitakse tingimuste ristumist.
3. SSMS-is või muus GUI-s. SQL Server optimeeritakse seejärel ise. SQL-päring (vaheandmete salvestuse eraldamine, tulemuse päringu koostamine selle salvestuse kasutamisel (võib olla mitu)).
4. viimases etapis, võttes aluseks saadud tulemuse SQL-päring, rebuild the structure LINQ-päringust.

Kokkuvõttes peaks saadud LINQ-päring struktuur olema identne tuvastatud optimaalsega SQL-päring. punktist 3.

Tänud

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

Allikas: habr.com