🥇Menetelmät LINQ-kyselyjen optimointiin C#.NET

Esittely

В tässä artikkelissa joitain optimointimenetelmiä harkittiin LINQ-kyselyt.
Tässä esittelemme myös joitain muita lähestymistapoja koodin optimointiin liittyen LINQ-kyselyt.

On tunnettua, että LINQ(Language-Integrated Query) on yksinkertainen ja kätevä kieli tietolähteen kyselyyn.

А LINQ SQL:ään on tekniikka, jolla päästään käsiksi tietoihin DBMS:ssä. Tämä on tehokas työkalu tietojen kanssa työskentelyyn, jossa kyselyt muodostetaan deklaratiivisella kielellä, joka sitten muunnetaan SQL-kyselyt alusta ja lähetetään tietokantapalvelimelle suoritettavaksi. Meidän tapauksessamme tarkoitamme DBMS:llä MS SQL Server.

Kuitenkin, LINQ-kyselyt ei muunneta optimaalisesti kirjoitetuiksi SQL-kyselyt, jonka kokenut DBA voisi kirjoittaa kaikilla optimoinnin vivahteilla SQL-kyselyt:

optimaaliset liitännät (LIITY) ja suodattaa tulokset (MISTÄ)
monia vivahteita yhteyksien ja ryhmäehtojen käytössä
monia muunnelmia korvaavissa olosuhteissa IN päälle EXISTSи EI SISÄLLÄ, <> päällä EXISTS
tulosten välimuisti välimuistiin väliaikaisten taulukoiden, CTE:n ja taulukkomuuttujien kautta
lauseen käyttö (OPTION) ohjeineen ja taulukkovinkkeineen MEILLÄ ON (...)
käyttämällä indeksoituja näkymiä yhtenä keinona päästä eroon tarpeettomista datan lukemista valintojen aikana

Tärkeimmät suorituskyvyn pullonkaulat tuloksena SQL-kyselyt kootaessa LINQ-kyselyt Ne ovat:

koko tiedonvalintamekanismin yhdistäminen yhteen pyyntöön
identtisten koodilohkojen kopioiminen, mikä lopulta johtaa useisiin tarpeettomiin tietojen lukuihin
monikomponenttiehtojen ryhmät (looginen "ja" ja "tai") JA и OR, yhdistäminen monimutkaisiin olosuhteisiin johtaa siihen, että optimoija, jolla on sopivat klusteroimattomat indeksit tarvittaville kentille, alkaa lopulta skannata klusteroitua indeksiä vastaan (HAKEMISTO SKANNAUS) ehtoryhmien mukaan
alikyselyiden syvä sisäkkäisyys tekee jäsentämisestä erittäin ongelmallista SQL-lauseet ja kyselysuunnitelman analysointi kehittäjien ja DBA

Optimointimenetelmät

Siirrytään nyt suoraan optimointimenetelmiin.

1) Lisäindeksointi

On parasta harkita suodattimia päävalintataulukoissa, koska hyvin usein koko kysely on rakennettu yhden tai kahden päätaulukon ympärille (sovellukset-ihmiset-toiminnot) ja vakioehtojen kanssa (IsClosed, Canceled, Enabled, Status). On tärkeää luoda asianmukaiset indeksit tunnistetuille näytteille.

Tämä ratkaisu on järkevä, kun näiden kenttien valinta rajoittaa merkittävästi kyselyyn palautettua joukkoa.

Meillä on esimerkiksi 500000 2000 hakemusta. Aktiivisia sovelluksia on kuitenkin vain XNUMX. Sitten oikein valittu hakemisto säästää meidät HAKEMISTO SKANNAUS suuressa taulukossa ja antaa sinun valita nopeasti tietoja klusteroimattoman indeksin kautta.

Indeksien puute voidaan myös tunnistaa kehotteiden avulla jäsentämään kyselysuunnitelmia tai keräämällä järjestelmänäkymätilastoja. MS SQL Server:

Kaikki näkymätiedot sisältävät tietoja puuttuvista indekseistä, lukuun ottamatta spatiaalisia indeksejä.

Indeksit ja välimuisti ovat kuitenkin usein tapoja torjua huonosti kirjoitetun tekstin seurauksia LINQ-kyselyt и SQL-kyselyt.

Kuten elämän ankara käytäntö osoittaa, yritykselle on usein tärkeää ottaa bisnesominaisuudet käyttöön tiettyyn määräaikaan mennessä. Ja siksi raskaat pyynnöt siirretään usein taustalle välimuistin avulla.

Tämä on osittain perusteltua, koska käyttäjä ei aina tarvitse uusimpia tietoja ja käyttöliittymän reagointikyky on hyväksyttävä.

Tämä lähestymistapa mahdollistaa liiketoiminnan tarpeiden ratkaisemisen, mutta lopulta heikentää tietojärjestelmän suorituskykyä yksinkertaisesti viivyttämällä ongelmien ratkaisua.

On myös syytä muistaa, että kun etsit tarvittavia indeksejä lisättäväksi, ehdotuksia MS SQL optimointi voi olla virheellinen, myös seuraavissa olosuhteissa:

jos on jo indeksejä, joissa on samanlainen kenttäjoukko
jos taulukon kenttiä ei voida indeksoida indeksointirajoitusten vuoksi (kuvattu tarkemmin täällä).

2) Attribuuttien yhdistäminen yhdeksi uudeksi attribuutiksi

Joskus jotkin kentät yhdestä taulukosta, jotka toimivat perustana ehtoryhmälle, voidaan korvata lisäämällä yksi uusi kenttä.

Tämä pätee erityisesti tilakenttiin, jotka ovat yleensä joko bitti- tai kokonaislukutyyppiä.

Esimerkiksi:

IsClosed = 0 JA Peruutettu = 0 JA Käytössä = 0 korvataan Tila = 1.

Tässä otetaan käyttöön kokonaisluku Status -attribuutti sen varmistamiseksi, että nämä tilat täytetään taulukossa. Seuraavaksi tämä uusi määrite indeksoidaan.

Tämä on perustavanlaatuinen ratkaisu suorituskykyongelmaan, koska käytämme tietoja ilman tarpeettomia laskelmia.

3) Näkemyksen materialisointi

Valitettavasti sisään LINQ-kyselyt Väliaikaisia taulukoita, CTE:itä ja taulukkomuuttujia ei voi käyttää suoraan.

On kuitenkin olemassa toinen tapa optimoida tätä tapausta varten - indeksoidut näkymät.

Ehtoryhmä (yllä olevasta esimerkistä) IsClosed = 0 JA Peruutettu = 0 JA Käytössä = 0 (tai joukko muita samankaltaisia ehtoja) on hyvä vaihtoehto käyttää niitä indeksoidussa näkymässä, jolloin välimuistiin tallennetaan pieni osa dataa suuresta joukosta.

Mutta näkymän toteutumiseen liittyy useita rajoituksia:

alikyselyjen, lausekkeiden käyttö EXISTS tulee korvata käyttämällä LIITY
et osaa käyttää lauseita UNIONIN, UNIONIN KAIKKI, EXCEPTION, RISTEKSI
Et voi käyttää taulukon vihjeitä ja lausekkeita OPTION
ei mahdollisuutta työskennellä pyörien kanssa
On mahdotonta näyttää tietoja yhdessä näkymässä eri taulukoista

On tärkeää muistaa, että indeksoidun näkymän käytön todellinen hyöty voidaan saavuttaa vain indeksoimalla se.

Mutta kun kutsut näkymää, näitä indeksejä ei saa käyttää, ja sinun on määritettävä, jotta voit käyttää niitä nimenomaisesti WITH (NOEXPAND).

Vuodesta lähtien LINQ-kyselyt Taulukkovihjeitä on mahdotonta määritellä, joten sinun on luotava toinen esitys - seuraavan muotoinen "kääre":

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

4) Taulukkofunktioiden käyttäminen

Usein sisään LINQ-kyselyt Suuret alikyselylohkot tai lohkot, jotka käyttävät monimutkaisen rakenteen omaavia näkymiä, muodostavat lopullisen kyselyn, jolla on erittäin monimutkainen ja epäoptimaalinen suoritusrakenne.

Taulukkofunktioiden käytön tärkeimmät edut LINQ-kyselyt:

Mahdollisuus käyttää ja määrittää objektina, kuten näkymien tapauksessa, mutta voit välittää joukon syöttöparametreja:
FROM FUNCTION(@param1, @param2...)
Tuloksena voidaan saavuttaa joustava datanäytteenotto
Taulukkofunktiota käytettäessä ei ole niin voimakkaita rajoituksia kuin yllä kuvattujen indeksoitujen näkymien tapauksessa:
1. Taulukon vinkkejä:
  kautta LINQ Et voi määrittää, mitä indeksejä tulee käyttää ja määrittää tietojen eristystasoa kyselyn aikana.
  Mutta toiminnolla on nämä ominaisuudet.
  Toiminnolla voit saavuttaa melko vakion suorituksen kyselysuunnitelman, jossa määritellään säännöt indeksien kanssa työskentelylle ja tietojen eristystasot
2. Toiminnon käyttäminen mahdollistaa indeksoituihin näkymiin verrattuna:
  - monimutkainen datanäytteenottologiikka (jopa silmukoita käyttäen)
  - tietojen hakeminen useista eri taulukoista
  - käyttö UNIONIN и EXISTS
Ehdotus OPTION erittäin hyödyllinen, kun tarvitsemme samanaikaisuuden hallintaa VAIHTOEHTO (MAXDOP N), kyselyn suoritussuunnitelman järjestys. Esimerkiksi:
- voit määrittää kyselysuunnitelman pakotetun uudelleenluomisen VAIHTOEHTO (KOMPILE UUDELLEEN)
- voit määrittää, pakotetaanko kyselysuunnitelma käyttämään kyselyssä määritettyä liitosjärjestystä VAIHTOEHTO (PAKOTILAUS)
Lisätietoja aiheesta OPTION kuvattu täällä.
Kapeimman ja vaadituimman datalohkon käyttäminen:
Suuria tietojoukkoja ei tarvitse tallentaa välimuistiin (kuten indeksoiduissa näkymissä), joista sinun on silti suodatettava tiedot parametrien mukaan.
Esimerkiksi on taulukko, jonka suodatin MISTÄ käytetään kolmea kenttää (a, b, c).

Perinteisesti kaikilla kyselyillä on vakioehto a = 0 ja b = 0.

Kuitenkin pyyntö alalla c vaihtelevampaa.

Olkoon ehto a = 0 ja b = 0 Se todella auttaa meitä rajoittamaan vaaditun tuloksen joukon tuhansiin tietueisiin, mutta ehto on päällä с supistaa valikoiman sataan tietueeseen.

Tässä taulukkotoiminto voi olla parempi vaihtoehto.

Lisäksi taulukkofunktio on ennakoitavampi ja johdonmukaisempi suoritusajassa.

Примеры

Tarkastellaan esimerkkitoteutusta käyttämällä esimerkkinä Questions-tietokantaa.

On pyyntö VALITSE, joka yhdistää useita taulukoita ja käyttää yhtä näkymää (OperativeQuestions), jossa kuuluvuus tarkistetaan sähköpostitse (välillä EXISTS) kohtaan "Operatiiviset kysymykset":

Pyyntö nro 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])
));

Näkymän rakenne on melko monimutkainen: siinä on alikyselyliitoksia ja se käyttää lajittelua DISTINCT, joka on yleisesti ottaen melko resursseja vaativa toimenpide.

OperativeQuestionsin näyte on noin kymmenen tuhatta tietuetta.

Tämän kyselyn suurin ongelma on, että ulkoisen kyselyn tietueille suoritetaan sisäinen alikysely [OperativeQuestions]-näkymässä, jonka [Sähköposti] = @p__linq__0 kohdalla pitäisi sallia meidän rajoittaa tulosten valintaa ( EXISTS) jopa satoja tietueita.

Ja saattaa vaikuttaa siltä, että alikyselyn pitäisi laskea tietueet kerran muodossa [Sähköposti] = @p__linq__0, ja sitten nämä parisataa tietuetta pitäisi yhdistää Id:llä Kysymyksiin, ja kyselystä tulee nopea.

Itse asiassa kaikki taulukot ovat peräkkäin kytkettyjä: Id-kysymysten ja OperativeQuestions-tunnuksen vastaavuuden tarkistaminen ja sähköpostin suodatus.

Itse asiassa pyyntö toimii kaikkien kymmenien tuhansien OperativeQuestions-tietueiden kanssa, mutta vain kiinnostavat tiedot tarvitaan sähköpostitse.

OperativeQuestions-näkymän teksti:

Pyyntö nro 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));

Alkuperäisen näkymän kartoitus DbContextissa (EF Core 2)

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

Ensimmäinen LINQ-kysely

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

Tässä nimenomaisessa tapauksessa harkitsemme ratkaisua tähän ongelmaan ilman infrastruktuurimuutoksia, ilman erillistä valmiiden tulosten taulukkoa (”Active Queries”), mikä vaatisi mekanismin sen täyttämiseksi tiedoilla ja ajan tasalla pitämiseksi. .

Vaikka tämä on hyvä ratkaisu, on olemassa toinen vaihtoehto tämän ongelman optimoimiseksi.

Päätarkoitus on tallentaa välimuistiin merkinnät [Sähköposti] = @p__linq__0 OperativeQuestions-näkymästä.

Ota tietokantaan taulukkofunktio [dbo].[OperativeQuestionsUserMail].

Lähettämällä sähköpostin syöttöparametrina saamme takaisin arvotaulukon:

Pyyntö nro 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

Tämä palauttaa arvotaulukon ennalta määritetyllä tietorakenteella.

Jotta OperativeQuestionsUserMailin kyselyt olisivat optimaalisia ja niillä olisi optimaaliset kyselysuunnitelmat, tarvitaan tiukka rakenne, eikä PALAUTUSTAULUKKO PALAUTUKSENA...

Tässä tapauksessa vaadittu kysely 1 muunnetaan kyselyksi 4:

Pyyntö nro 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]);

Näkymien ja toimintojen kartoitus DbContextissa (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})");
}

Lopullinen LINQ-kysely

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

Suoritusajan järjestys on pudonnut 200-800 ms:sta, 2-20 ms:iin jne., eli kymmeniä kertoja nopeampi.

Jos otamme sen keskimääräisemmin, niin 350 ms sijaan saimme 8 ms.

Ilmeisistä eduista saamme myös:

yleinen lukukuormituksen väheneminen,
tukkeutumisen todennäköisyys vähenee merkittävästi
keskimääräisen estoajan vähentäminen hyväksyttäviin arvoihin

johtopäätös

Tietokantakutsujen optimointi ja hienosäätö MS SQL kautta LINQ on ongelma, joka voidaan ratkaista.

Tarkkailu ja johdonmukaisuus ovat erittäin tärkeitä tässä työssä.

Prosessin alussa:

on tarpeen tarkistaa tiedot, joiden kanssa pyyntö toimii (arvot, valitut tietotyypit)
suorittaa näiden tietojen asianmukainen indeksointi
Tarkista taulukoiden välisten liitosehtojen oikeellisuus

Seuraava optimointiiteraatio paljastaa:

pyynnön perusteella ja määrittää pääpyyntösuodattimen
toistamalla samanlaisia kyselylohkoja ja analysoimalla ehtojen leikkauskohtaa
SSMS:ssä tai muussa graafisessa käyttöliittymässä SQL Server optimoi itsensä SQL-kysely (varaamalla välimuistin, muodostamaan tuloksena olevan kyselyn tämän tallennustilan avulla (saattaa olla useita))
viimeisessä vaiheessa ottaen pohjaksi tuloksen SQL-kysely, rakennetta rakennetaan uudelleen LINQ-kysely

Tuloksena oleva LINQ-kysely rakenteeltaan tulisi olla identtinen tunnistetun optimaalisen kanssa SQL-kysely kohdasta 3.

Kiitokset

Suuri kiitos kollegoille jobgemws и alex_ozr yrityksestä Fortis saadaksesi apua tämän materiaalin valmistelussa.

Lähde: will.com

Menetelmät LINQ-kyselyjen optimointiin C#.NETissä