🥇C#.NET | LINQ kontsultak optimizatzeko metodoak ProHoster

Sarrera

В Artikulu honetan optimizazio metodo batzuk kontuan hartu ziren LINQ kontsultak.
Honekin erlazionatutako kodeen optimizaziorako hurbilketa gehiago ere aurkezten ditugu LINQ kontsultak.

Ez da askorik ezagutzen duten LINQ(Language-Integrated Query) datu-iturburu bat kontsultatzeko hizkuntza sinple eta erosoa da.

А LINQ to SQL DBMS batean datuak atzitzeko teknologia bat da. Datuekin lan egiteko tresna indartsua da hau, non kontsultak deklarazio-lengoaia baten bidez eraikitzen diren, gero bihurtuko dena. SQL kontsultak plataforma eta datu-basearen zerbitzarira bidalita exekutatzeko. Gure kasuan, DBMS esan nahi dugu MS SQL zerbitzaria.

Hala eta guztiz ere, LINQ kontsultak ez dira modu optimoan idatzitakoak bihurtzen SQL kontsultak, DBA esperientziadun batek optimizazioaren ñabardura guztiekin idatz zezakeena SQL kontsultak:

konexio optimoak (ELKARTU) eta emaitzak iragazten (NON)
ñabardura ugari konexioak eta talde-baldintzak erabiltzean
aldakuntza asko ordezkatzeko baldintzak IN on EXISTEAKи EZ SARTU, <> aktibatuta EXISTEAK
emaitzen tarteko cachea aldi baterako taulen bidez, CTE, taula aldagaien bidez
esaldiaren erabilera (OPTION) argibideekin eta mahai-iradokizunekin CON (...)
indexatutako ikuspegiak hautapenetan zehar datu-irakurketa erredundanteak kentzeko bitarteko gisa erabiltzea

Emaitzen den errendimenduaren oztopo nagusiak SQL kontsultak konpilatzean LINQ kontsultak hauek dira:

datuak aukeratzeko mekanismo osoa eskaera bakarrean finkatzea
kode-bloke berdinak bikoiztuz, eta horrek, azken finean, alferrikako datuen irakurketa anitz eragiten du
Osagai anitzeko baldintza-taldeak («eta» logikoa eta «edo») - ETA и OR, baldintza konplexuetan konbinatuz, optimizatzaileak, beharrezko eremuetarako klusteratutako indize egokiak izanik, azken finean indize multzokatuaren aurka eskaneatzen hasten dela dakar (AURKIBIDEA ESKANEA) baldintza taldeen arabera
Azpikontsulten habiatze sakonak analizatzea oso arazotsua da SQL adierazpenak eta kontsulta-planaren azterketa garatzaileen aldetik eta DBA

Optimizazio metodoak

Orain joan gaitezen zuzenean optimizazio metodoetara.

1) Indize gehigarria

Hobe da hautapen-taula nagusietako iragazkiak kontuan hartzea, izan ere, sarritan kontsulta osoa taula nagusi baten edo biren inguruan (aplikazioak-pertsonak-eragiketak) eta baldintza multzo estandar batekin (Itxita, Ezeztatuta, Gaituta, Egoera) eraikitzen da. Garrantzitsua da identifikatutako laginetarako indize egokiak sortzea.

Irtenbide honek zentzua du eremu hauek hautatzean itzultzen den multzoa kontsultara nabarmen mugatzen denean.

Adibidez, 500000 eskaera ditugu. Hala ere, 2000 aplikazio aktibo daude soilik. Ondoren, behar bezala hautatutako indize batek salbatuko gaitu AURKIBIDEA ESKANEA taula handi batean eta klusteratutako indize baten bidez datuak azkar hautatzeko aukera emango dizu.

Gainera, indizeen falta kontsulta-planak analizatzeko edo sistemaren ikuspegien estatistikak biltzeko galderen bidez identifikatu daiteke. MS SQL zerbitzaria:

Ikuspegiko datu guztiek falta diren indizeei buruzko informazioa dute, indize espazialak izan ezik.

Dena den, indizeak eta caching-a maiz gaizki idatzitako ondorioei aurre egiteko metodoak dira LINQ kontsultak и SQL kontsultak.

Bizitzaren praktika gogorrak erakusten duenez, askotan garrantzitsua da negozio batek negozio-eginbideak epe jakin batzuetan ezartzea. Hori dela eta, eskaera handiak atzeko planora pasatzen dira maiz cachearekin.

Hori neurri batean justifikatuta dago, erabiltzaileak ez baititu beti azken datuak behar eta erabiltzaile-interfazearen erantzun-maila onargarria baitago.

Ikuspegi honek negozio-beharrak konpontzea ahalbidetzen du, baina azken finean informazio-sistemaren errendimendua murrizten du arazoen konponbideak atzeratuz besterik gabe.

Era berean, gogoratzea komeni da gehitzeko beharrezko indizeak bilatzeko prozesuan, iradokizunak MS SQL optimizazioa okerra izan daiteke, baldintza hauetan barne:

antzeko eremu multzoa duten indizeak badaude jada
taulako eremuak ezin badira indexatu indexatzeko murrizketen ondorioz (xehetasun gehiagoz deskribatuta Hemen).

2) Atributuak atributu berri batean bateratzea

Batzuetan, taula bateko eremu batzuk, baldintza talde baten oinarri gisa balio dutenak, ordezka daitezke eremu berri bat sartuz.

Hau bereziki egia da egoera-eremuetan, normalean bit edo osoko motakoak izan ohi direnak.

Adibidea:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 ordezkatzen du Egoera = 1.

Hor sartzen da osoko Egoera atributua, egoera horiek taulan betetzen direla ziurtatzeko. Ondoren, atributu berri hau indexatzen da.

Hau errendimendu-arazoaren oinarrizko irtenbidea da, alferrikako kalkulurik gabe sartzen baikara datuetara.

3) Ikuspegiaren materializazioa

Zoritxarrez barruan LINQ kontsultak Aldi baterako taulak, CTEak eta taula-aldagaiak ezin dira zuzenean erabili.

Hala ere, kasu honetarako optimizatzeko beste modu bat dago: indexatutako ikuspegiak.

Baldintza taldea (goiko adibidetik) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (edo antzeko beste baldintza batzuen multzoa) aukera ona bihurtzen da indexatutako ikuspegi batean erabiltzeko, multzo handi bateko datu zati txiki bat cachean gordez.

Baina hainbat murrizketa daude ikuspegi bat gauzatzean:

azpikontsulten erabilera, klausulak EXISTEAK erabiliz ordezkatu behar da ELKARTU
ezin dituzu esaldiak erabili UNION, BATASUN GUZTIAK, EXCEPTION, INTERSEKTUA
Ezin duzu taulako aholkuak eta klausulak erabili OPTION
zikloekin lan egiteko aukerarik ez
Ezinezkoa da taula ezberdinetako datuak ikuspegi batean bistaratzea

Garrantzitsua da gogoratzea indexatutako ikuspegi bat erabiltzearen benetako onura benetan indexatuz soilik lor daitekeela.

Baina ikuspegi bati deitzean, baliteke indize hauek ez erabiltzea, eta esplizituki erabiltzeko, zehaztu behar duzu GAIN (EZANDATZEN).

urtean geroztik LINQ kontsultak Ezinezkoa da taula-aholkuak definitzea, beraz, beste irudikapen bat sortu behar duzu - forma honetako "bilgarri" bat:

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

4) Taularen funtzioak erabiltzea

Sarritan LINQ kontsultak Azpikontsulten bloke handiek edo egitura konplexua duten ikuspegiak erabiltzen dituzten blokeek exekuzio-egitura oso konplexua eta ezin hobea duen azken kontsulta osatzen dute.

Taularen funtzioak erabiltzearen abantaila nagusiak LINQ kontsultak:

Ikuspegien kasuan bezala, objektu gisa erabiltzeko eta zehazteko gaitasuna, baina sarrerako parametro multzo bat pasa dezakezu:
FUNTZIOtik(@param1, @param2...)
Ondorioz, datuen laginketa malgua lor daiteke
Taula-funtzioa erabiltzearen kasuan, ez dago goian deskribatutako ikuspegi indexatuen kasuan bezalako murrizketa sendorik:
1. Taularen aholkuak:
  bidez LINQ Ezin duzu zehaztu zein indize erabili behar diren eta datuen isolamendu maila zehaztu kontsultak egiterakoan.
  Baina funtzioak gaitasun hauek ditu.
  Funtzioarekin, nahiko etengabeko exekuzio-kontsulta-plan bat lor dezakezu, non indizeekin lan egiteko arauak eta datuen isolamendu-mailak definitzen diren.
2. Funtzioa erabiltzeak aukera ematen du, indexatutako ikuspegiekin alderatuta:
  - datuen laginketa logika konplexua (nahiz eta begiztak erabiliz)
  - hainbat taulatako datuak eskuratzea
  - erabilera UNION и EXISTEAK
eskaintza OPTION oso erabilgarria aldibereko kontrola eman behar dugunean AUKERA (MAXDOP N), kontsultak egiteko planaren ordena. Adibidez:
- kontsulta-planaren birsorkuntza behartu bat zehaztu dezakezu AUKERA (BERRORKETA)
- kontsulta-planak kontsultan zehaztutako batu-ordena erabiltzera behartu nahi duzun zehaztu dezakezu AUKERA (BORTZEKO ORDENA)
buruzko xehetasun gehiago OPTION deskribatu Hemen.
Datu-zatirik estuena eta beharrezkoena erabiliz:
Ez dago datu-multzo handiak cacheetan gorde beharrik (indexatutako ikuspegietan gertatzen den bezala), eta, oraindik, datuak parametroen arabera iragazi behar dituzu.
Adibidez, iragazkia duen taula bat dago NON hiru eremu erabiltzen dira (a, b, c).

Ohikoki, kontsulta guztiek baldintza konstantea dute a = 0 eta b = 0.

Hala ere, eremuaren eskaera c aldakorragoa.

Utzi baldintza a = 0 eta b = 0 Benetan laguntzen digu beharrezko emaitza multzoa milaka erregistrotara mugatzen, baina baldintza с hautaketa ehun erregistrora murrizten du.

Hemen taula funtzioa aukera hobea izan daiteke.

Gainera, taula funtzio bat aurreikusgarriagoa eta koherenteagoa da exekuzio denboran.

Примеры

Ikus dezagun inplementazio adibide bat Galderak datu-basea adibide gisa erabiliz.

Eskaera bat dago AUKERATU, hainbat taula konbinatzen dituena eta ikuspegi bat erabiltzen duena (Galdera Operatiboak), zeinetan afiliazioa posta elektronikoz egiaztatzen den (bidez EXISTEAK) "Galdera operatiboak"ra:

Eskaera 1. zenbakia

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

Ikuspegiak egitura konplexu samarra du: azpikontsulten elkarketak ditu eta ordenatzea erabiltzen du desberdin, orokorrean baliabide nahiko intentsiboa den eragiketa da.

OperativeQuestions-en lagin bat hamar mila erregistro ingurukoa da.

Kontsulta honen arazo nagusia da kanpoko kontsultako erregistroetarako, barneko azpikontsulta bat exekutatzen dela [OperativeQuestions] ikuspegian, eta horrek [E-posta] = @p__linq__0 aukera eman beharko luke irteera hautaketa mugatzeko (bidez). EXISTEAK) ehunka erregistroraino.

Eta badirudi azpikontsultak erregistroak behin kalkulatu behar dituela [E-mail] = @p__linq__0 bidez, eta, ondoren, ehun erregistro pare horiek Id-en bidez konektatu behar dira Galderekin, eta kontsulta azkarra izango da.

Izan ere, taula guztien konexio sekuentziala dago: Id Galderek Id-ekin duten korrespondentzia egiaztatzea OperativeQuestions-en, eta Email bidez iragaztea.

Izan ere, eskaerak OperativeQuestions-en hamarnaka mila erregistro guztiekin funtzionatzen du, baina intereseko datuak soilik behar dira Posta elektronikoaren bidez.

Operative Questions ikusi testua:

Eskaera 2. zenbakia

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

Hasierako ikuspegiaren mapak DbContext-en (EF Core 2)

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

Hasierako LINQ kontsulta

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

Kasu zehatz honetan, azpiegitura aldaketarik gabe arazo honi irtenbidea ematea aztertzen ari gara, prest egindako emaitzekin (“Kontsulta Aktiboak”) taula bereizirik sartu gabe, eta horrek datuez bete eta eguneratuta mantentzeko mekanismo bat beharko luke. .

Irtenbide ona den arren, arazo hau optimizatzeko beste aukera bat dago.

Helburu nagusia [Email] = @p__linq__0 sarrerak cachean jartzea da OperativeQuestions ikuspegitik.

Sartu taula-funtzioa [dbo].[OperativeQuestionsUserMail] datu-basean.

Posta elektronikoa sarrerako parametro gisa bidaliz, balioen taula bat berreskuratuko dugu:

Eskaera 3. zenbakia


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

Honek balioen taula bat itzultzen du aurrez zehaztutako datu-egitura batekin.

OperativeQuestionsUserMail-i egindako kontsultak optimoak izan daitezen eta kontsulta-plan optimoak izan ditzaten, egitura zorrotza behar da, eta ez. ITZULKETAK TAULA ITZULKETA GISA...

Kasu honetan, beharrezko 1. kontsulta 4. kontsultara bihurtzen da:

Eskaera 4. zenbakia

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

Ikuspegiak eta funtzioak mapatzea DbContext-en (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})");
}

LINQ azken kontsulta

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

Exekuzio-denbora 200-800 ms-tik, 2-20 ms-ra, etab.era jaitsi da, hau da, hamar aldiz azkarrago.

Batez bestekoa gehiago hartzen badugu, 350 ms-ren ordez 8 ms lortuko ditugu.

Abantail nabarietatik ere lortzen dugu:

irakurketa-kargaren murrizketa orokorra,
blokeatzeko probabilitatearen murrizketa nabarmena
batez besteko blokeo-denbora balio onargarrietara murriztea

Irteera

Datu-baseen deien optimizazioa eta doikuntza MS SQL bidez LINQ konpondu daitekeen arazoa da.

Arreta eta koherentzia oso garrantzitsuak dira lan honetan.

Prozesuaren hasieran:

beharrezkoa da eskaerak funtzionatzen duen datuak egiaztatu behar dira (balioak, hautatutako datu motak)
datu horien indexazio egokia egitea
egiaztatu taulen arteko elkartze-baldintzen zuzena

Hurrengo optimizazio-iterazioa agerian uzten du:

eskaeraren oinarria eta eskaeraren iragazki nagusia definitzen du
antzeko kontsulta-blokeak errepikatuz eta baldintzen elkargunea aztertuz
SSMS edo beste GUI batean SQL Server bere burua optimizatzen du SQL kontsulta (tarteko datuen biltegiratze bat esleitu, ondoriozko kontsulta biltegiratze hori erabiliz eraiki (hainbat egon daitezke))
azken fasean, oinarritzat emaitza hartuta SQL kontsulta, egitura berreraikitzen ari da LINQ kontsulta

Ondorioz LINQ kontsulta Egituraz identifikatutako optimoaren berdina bihurtu behar da SQL kontsulta 3 puntutik.

Eskerrak

Mila esker lankideei jobgemws и alex_ozr konpainiatik Fortis material hau prestatzen laguntzeko.

Iturria: www.habr.com

C#.NET-en LINQ kontsultak optimizatzeko metodoak