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

Sissejuhatus

В see artikkel kaaluti mõningaid optimeerimismeetodeid LINQ päringud.
Siin tutvustame ka veel mõnda koodi optimeerimise lähenemisviisi LINQ päringud.

On teada, et LINQ(Language-Integrated Query) on lihtne ja mugav keel andmeallika päringute tegemiseks.

А LINQ SQL-ile on DBMS-is andmetele juurdepääsu tehnoloogia. See on võimas tööriist andmetega töötamiseks, kus päringud koostatakse deklaratiivse keele kaudu, mis seejärel teisendatakse SQL päringud platvormi ja saadetakse täitmiseks andmebaasiserverisse. Meie puhul peame DBMS-i all silmas MS SQL Server.

Kuid LINQ päringud ei teisendata optimaalselt kirjutatud SQL päringud, mida kogenud DBA võiks kirjutada kõigi optimeerimise nüanssidega SQL päringud:

optimaalsed ühendused (LIITU) ja tulemuste filtreerimine (KUS)
palju nüansse seoste ja rühmatingimuste kasutamisel
palju variatsioone asendustingimustes IN edasi OLEMASи EI SISSE, <> sisse OLEMAS
tulemuste vahemällu salvestamine ajutiste tabelite, CTE, tabelimuutujate kaudu
lause kasutamine (VALIK) koos juhiste ja tabelivihjetega KOOS (...)
indekseeritud vaadete kasutamine ühe vahendina, et vabaneda üleliigsetest andmelugemistest valikute ajal

Tulemuslikkuse peamised kitsaskohad SQL päringud koostamisel LINQ päringud on:

kogu andmevaliku mehhanismi koondamine ühes päringus
identsete koodiplokkide dubleerimine, mis viib lõpuks mitme tarbetu andmete lugemiseni
mitmekomponentsete tingimuste rühmad (loogilised "ja" ja "või") JA и OR, ühendades keeruliste tingimustega, viib selleni, et optimeerija, millel on vajalike väljade jaoks sobivad rühmitamata indeksid, hakkab lõpuks skannima rühmitatud indeksit (INDEKSI SKANNI) tingimuste rühmade kaupa
alampäringute sügav pesastamine muudab sõelumise väga problemaatiliseks SQL-laused ja päringuplaani analüüs arendajate poolt ja DBA

Optimeerimismeetodid

Liigume nüüd otse optimeerimismeetodite juurde.

1) Täiendav indekseerimine

Parim on kaaluda filtreid peamistes valikutabelites, kuna sageli on kogu päring üles ehitatud ühe või kahe põhitabeli ümber (rakendused-inimesed-toimingud) ja standardsete tingimuste komplektiga (IsClosed, Canceled, Enabled, Status). Oluline on luua tuvastatud proovide jaoks sobivad indeksid.

See lahendus on mõttekas, kui nende väljade valimine piirab oluliselt päringule tagastatavat komplekti.

Meil on näiteks 500000 2000 rakendust. Aktiivseid rakendusi on aga vaid XNUMX. Siis päästab meid õigesti valitud indeks INDEKSI SKANNI suurel tabelis ja võimaldab teil kiiresti andmeid rühmitamata indeksi kaudu valida.

Samuti saab indeksite puudumist tuvastada päringuplaanide sõelumise või süsteemivaate statistika kogumise viipade kaudu MS SQL Server:

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

Indeksid ja vahemällu salvestamine on aga sageli meetodid halva kirjutamise tagajärgede vastu võitlemiseks LINQ päringud и SQL päringud.

Nagu elu karm praktika näitab, on ettevõtte jaoks sageli oluline ärifunktsioonide rakendamine teatud tähtaegadeks. Seetõttu kantakse rasked taotlused vahemällu salvestamisega sageli taustale.

See on osaliselt õigustatud, kuna kasutaja ei vaja alati uusimaid andmeid ja kasutajaliidese reageerimisvõime on vastuvõetaval tasemel.

Selline lähenemine võimaldab lahendada ärivajadusi, kuid lõppkokkuvõttes vähendab infosüsteemi jõudlust, lihtsalt viivitades probleemide lahendamisega.

Samuti tasub meeles pidada, et lisamiseks vajalike indeksite otsimise protsessis on soovitused MS SQL optimeerimine võib olla vale, sealhulgas järgmistel tingimustel:

kui juba on sarnase väljakomplektiga indeksid
kui tabeli välju ei saa indekseerimispiirangute tõttu indekseerida (kirjeldatud üksikasjalikumalt siin).

2) Atribuutide ühendamine üheks uueks atribuudiks

Mõnikord saab ühe tabelis olevad väljad, mis on tingimuste rühma aluseks, asendada ühe uue väljaga.

See kehtib eriti olekuväljade kohta, mis on tavaliselt kas biti- või täisarvu tüüpi.

Näide:

IsClosed = 0 JA Tühistatud = 0 JA Lubatud = 0 asendatakse Olek = 1.

Siin võetakse kasutusele atribuut täisarv Status, et tagada nende olekute tabelisse sisestamine. Järgmisena indekseeritakse see uus atribuut.

See on jõudlusprobleemi põhilahendus, sest pääseme andmetele juurde ilma tarbetute arvutusteta.

3) Vaate materialiseerimine

Kahjuks sisse LINQ päringud Ajutisi tabeleid, CTE-sid ja tabelimuutujaid ei saa otse kasutada.

Selle juhtumi jaoks on aga optimeerimiseks veel üks võimalus – indekseeritud vaated.

Tingimuste rühm (ülaltoodud näitest) IsClosed = 0 JA Tühistatud = 0 JA Lubatud = 0 (või muude sarnaste tingimuste kogum) muutub heaks võimaluseks kasutada neid indekseeritud vaates, salvestades vahemällu väikese andmelõike suurest komplektist.

Kuid vaate realiseerimisel on mitmeid piiranguid:

alampäringute, klauslite kasutamine OLEMAS tuleks asendada kasutades LIITU
sa ei saa lauseid kasutada LIIDU, LIIT KÕIK, VÄLJASTAMINE, LÕPETA
Tabeli vihjeid ja klausleid kasutada ei saa VALIK
tsiklitega töötamise võimalus puudub
Erinevate tabelite andmeid ühes vaates kuvada on võimatu

Oluline on meeles pidada, et tegelikku kasu indekseeritud vaate kasutamisest saab saavutada ainult selle indekseerimisega.

Kuid vaate kutsumisel ei tohi neid indekseid kasutada ja nende selgesõnaliseks kasutamiseks peate määrama WITH (EI LAIENDA).

Alates aastast LINQ päringud Tabelivihjeid on võimatu määratleda, seega peate looma teise esituse - järgmise vormi "ümbrise":

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

4) Tabelifunktsioonide kasutamine

Sageli sisse LINQ päringud Suured alampäringute plokid või keeruka struktuuriga vaateid kasutavad plokid moodustavad väga keerulise ja mitteoptimaalse täitmisstruktuuriga lõpliku päringu.

Tabelifunktsioonide kasutamise peamised eelised LINQ päringud:

Võimalus, nagu vaadete puhul, kasutada ja määrata objektina, kuid saate edastada sisendparameetrite komplekti:
FROM FUNCTION(@param1, @param2 ...)
Selle tulemusel on võimalik saavutada paindlik andmete valim
Tabelifunktsiooni kasutamisel pole nii tugevaid piiranguid kui ülalkirjeldatud indekseeritud vaadete puhul:
1. Tabeli näpunäited:
  läbi LINQ Te ei saa määrata, milliseid indekseid tuleks kasutada, ega määrata päringute tegemisel andmete eraldatuse taset.
  Kuid funktsioonil on need võimalused.
  Funktsiooniga saate saavutada üsna konstantse täitmise päringuplaani, kus on määratletud reeglid indeksitega töötamiseks ja andmete eraldamise tasemed
2. Funktsiooni kasutamine võimaldab võrreldes indekseeritud vaadetega saada:
  - keeruline andmete diskreetimisloogika (isegi silmuseid kasutades)
  - andmete toomine paljudest erinevatest tabelitest
  - kasutamise LIIDU и OLEMAS
Ettepanek VALIK väga kasulik, kui peame pakkuma samaaegsuse kontrolli VALIK (MAXDOP N), päringu täitmise plaani järjekord. Näiteks:
- saate määrata päringuplaani sunniviisilise uuesti loomise VALIK (ÜLEKOMPILEERIDA)
- saate määrata, kas sundida päringuplaani kasutama päringus määratud liitumisjärjekorda VALIK (JUNDUMENDUS)
Lisateavet selle kohta VALIK kirjeldatud siin.
Kasutades kõige kitsamat ja nõutavamat andmelõiku:
Vahemällu pole vaja salvestada suuri andmekogumeid (nagu indekseeritud vaadete puhul), kust tuleb andmeid ikkagi parameetrite järgi filtreerida.
Näiteks on tabel, mille filter KUS kasutatakse kolme välja (a, b, c).

Tavaliselt on kõigil taotlustel konstantne tingimus a = 0 ja b = 0.

Küll aga taotlus väljale c muutlikum.

Lase tingimusel a = 0 ja b = 0 See tõesti aitab meil piirata nõutavat tulemuskomplekti tuhandete kirjetega, kuid tingimus on sees с ahendab valikut saja rekordini.

Siin võib tabelifunktsioon olla parem valik.

Samuti on tabelifunktsioon täitmisaja osas prognoositavam ja ühtlasem.

näited

Vaatame näidisrakendust, kasutades näitena küsimuste andmebaasi.

On palve SELECT, mis ühendab mitu tabelit ja kasutab ühte vaadet (OperativeQuestions), milles seotust kontrollitakse meili teel (läbi OLEMAS) jaotisesse "Operatiivsed küsimused":

Taotlus 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 on üsna keerulise ülesehitusega: sellel on alampäringu liitumised ja see kasutab sorteerimist DISTINCT, mis üldiselt on üsna ressursimahukas tegevus.

OperativeQuestionsi näidis on umbes kümme tuhat kirjet.

Selle päringu peamine probleem seisneb selles, et välise päringu kirjete jaoks käivitatakse vaates [OperativeQuestions] sisemine alampäring, mis peaks [Email] = @p__linq__0 puhul võimaldama meil piirata väljundi valikut (läbi OLEMAS) kuni sadu kirjeid.

Ja võib tunduda, et alampäring peaks kirjed üks kord arvutama [Email] = @p__linq__0 ja siis need paarsada kirjet tuleks Id abil küsimustega ühendada ja päring on kiire.

Tegelikult on kõik tabelid järjestikku ühendatud: ID-küsimuste vastavuse kontrollimine OperativeQuestionsi ID-ga ja filtreerimine e-posti teel.

Tegelikult töötab päring kõigi kümnete tuhandete OperativeQuestionsi kirjetega, kuid meili teel on vaja ainult huvipakkuvaid andmeid.

OperativeQuestionsi vaate tekst:

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

Esialgse vaate 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");
    }
}

Esialgne 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 juhul kaalume selle probleemi lahendamist ilma infrastruktuuri muudatusteta, ilma valmistulemustega eraldi tabelit (“Aktiivsed päringud”) kasutusele võtmata, mis eeldaks andmete täitmise ja ajakohasena hoidmise mehhanismi. .

Kuigi see on hea lahendus, on selle probleemi optimeerimiseks veel üks võimalus.

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

Sisestage andmebaasi tabelifunktsioon [dbo].[OperativeQuestionsUserMail].

Kui saadate sisendparameetrina meili, saame tagasi väärtuste tabeli:

Taotlus 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

See tagastab väärtuste tabeli koos eelmääratletud andmestruktuuriga.

Selleks, et OperativeQuestionsUserMaili päringud oleksid optimaalsed ja neil oleks optimaalsed päringuplaanid, on vaja ranget struktuuri ja mitte TAGASTUSTABEL TAGASTUSEKS...

Sel juhul teisendatakse nõutav päring 1 päringuks 4:

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

Vaated ja funktsioonid 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();

Täitmisaja järjekord on langenud 200-800 ms-lt, 2-20 ms-ni jne, ehk kümneid kordi kiiremini.

Kui võtta keskmisemalt, siis 350 ms asemel saime 8 ms.

Ilmsetest eelistest saame ka:

üldine lugemiskoormuse vähenemine,
blokeerimise tõenäosuse märkimisväärne vähenemine
keskmise blokeerimisaja vähendamine vastuvõetavate väärtusteni

Väljund

Andmebaasikõnede optimeerimine ja peenhäälestus MS SQL läbi LINQ on probleem, mida saab lahendada.

Tähelepanelikkus ja järjekindlus on selles töös väga olulised.

Protsessi alguses:

on vaja kontrollida andmeid, millega päring töötab (väärtused, valitud andmetüübid)
viima läbi nende andmete nõuetekohase indekseerimise
kontrollida tabelitevaheliste liitmistingimuste õigsust

Järgmine optimeerimise iteratsioon näitab:

päringu alusel ja määratleb peamise päringu filtri
sarnaste päringuplokkide kordamine ja tingimuste ristumiskoha analüüsimine
SSMS-is või muus GUI-s SQL Server optimeerib ennast SQL päring (vahepealse andmesalvestusruumi eraldamine, saadud päringu koostamine selle salvestusruumi abil (neid võib olla mitu))
viimases etapis, võttes aluseks tulemuse SQL päring, konstruktsiooni ehitatakse ümber LINQ päring

Saadud LINQ päring peaks saama ülesehituselt identseks tuvastatud optimaalsega SQL päring punktist 3.

Tänusõnad

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

Allikas: www.habr.com

Meetodid LINQ-päringute optimeerimiseks C#.NET-is