🥇Mga pamamaraan para sa pag-optimize ng mga query sa LINQ sa C#.NET

Pagpapakilala

В artikulong ito ang ilang mga paraan ng pag-optimize ay isinasaalang-alang Mga query sa LINQ.
Dito ay nagpapakita kami ng ilan pang mga diskarte sa pag-optimize ng code na nauugnay sa Mga query sa LINQ.

Ito ay kilala na LINQ(Language-Integrated Query) ay isang simple at maginhawang wika para sa pag-query ng data source.

А LINQ sa SQL ay isang teknolohiya para sa pag-access ng data sa isang DBMS. Ito ay isang mahusay na tool para sa pagtatrabaho sa data, kung saan ang mga query ay binuo sa pamamagitan ng isang deklaratibong wika, na pagkatapos ay mako-convert sa Mga query sa SQL platform at ipinadala sa database server para sa pagpapatupad. Sa aming kaso, sa pamamagitan ng DBMS ang ibig naming sabihin Server ng MS SQL.

Gayunpaman, Mga query sa LINQ ay hindi na-convert sa mga mahusay na nakasulat Mga query sa SQL, na maaaring isulat ng isang bihasang DBA sa lahat ng mga nuances ng pag-optimize Mga query sa SQL:

pinakamainam na koneksyon (SUMALI) at pag-filter ng mga resulta (SAAN)
maraming mga nuances sa paggamit ng mga koneksyon at mga kondisyon ng grupo
maraming mga pagkakaiba-iba sa pagpapalit ng mga kondisyon IN sa MAY NAи WALA SA, <> sa MAY NA
intermediate na pag-cache ng mga resulta sa pamamagitan ng mga pansamantalang talahanayan, CTE, mga variable ng talahanayan
paggamit ng pangungusap (OPTION) na may mga tagubilin at mga pahiwatig sa talahanayan SA (...)
gamit ang mga naka-index na view bilang isa sa mga paraan upang maalis ang mga kalabisan na pagbabasa ng data sa panahon ng mga seleksyon

Ang pangunahing mga bottleneck ng pagganap ng resulta Mga query sa SQL kapag nag-compile Mga query sa LINQ ay:

pagsasama-sama ng buong mekanismo ng pagpili ng data sa isang kahilingan
pagdodoble ng magkaparehong mga bloke ng code, na sa huli ay humahantong sa maraming hindi kinakailangang pagbabasa ng data
mga pangkat ng mga kondisyon na may maraming bahagi (lohikal na "at" at "o") - AT и OR, pagsasama-sama sa mga kumplikadong kundisyon, ay humahantong sa katotohanan na ang optimizer, pagkakaroon ng angkop na mga di-clustered index para sa mga kinakailangang field, sa huli ay nagsisimulang mag-scan laban sa clustered index (INDEX SCAN) ayon sa mga pangkat ng mga kondisyon
Ang malalim na pugad ng mga subquery ay ginagawang napakaproblema ng pag-parse Mga pahayag ng SQL at pagsusuri ng query plan sa bahagi ng mga developer at DBA

Mga pamamaraan ng pag-optimize

Ngayon ay direktang lumipat tayo sa mga paraan ng pag-optimize.

1) Karagdagang pag-index

Pinakamainam na isaalang-alang ang mga filter sa mga pangunahing talahanayan ng pagpili, dahil kadalasan ang buong query ay binuo sa paligid ng isa o dalawang pangunahing talahanayan (mga application-people-operations) at may karaniwang hanay ng mga kundisyon (IsClosed, Cancelled, Enabled, Status). Mahalagang lumikha ng naaangkop na mga indeks para sa mga natukoy na sample.

Ang solusyon na ito ay may katuturan kapag ang pagpili sa mga field na ito ay makabuluhang nililimitahan ang ibinalik na hanay sa query.

Halimbawa, mayroon kaming 500000 aplikasyon. Gayunpaman, mayroon lamang 2000 aktibong aplikasyon. Pagkatapos ang isang tamang napiling index ay magliligtas sa amin mula sa INDEX SCAN sa isang malaking talahanayan at magbibigay-daan sa iyong mabilis na pumili ng data sa pamamagitan ng isang hindi naka-cluster na index.

Gayundin, ang kakulangan ng mga index ay maaaring matukoy sa pamamagitan ng mga senyas para sa pag-parse ng mga plano ng query o pagkolekta ng mga istatistika ng view ng system Server ng MS SQL:

Ang lahat ng data ng view ay naglalaman ng impormasyon tungkol sa mga nawawalang index, maliban sa mga spatial na index.

Gayunpaman, ang mga index at pag-cache ay kadalasang mga paraan ng paglaban sa mga kahihinatnan ng hindi magandang pagkakasulat Mga query sa LINQ и Mga query sa SQL.

Gaya ng ipinapakita ng malupit na kasanayan sa buhay, kadalasang mahalaga para sa isang negosyo na ipatupad ang mga feature ng negosyo sa ilang partikular na mga deadline. At samakatuwid, ang mga mabibigat na kahilingan ay madalas na inililipat sa background na may caching.

Ito ay bahagyang nabibigyang katwiran, dahil ang user ay hindi palaging nangangailangan ng pinakabagong data at mayroong isang katanggap-tanggap na antas ng pagtugon ng user interface.

Ang diskarte na ito ay nagbibigay-daan sa paglutas ng mga pangangailangan ng negosyo, ngunit sa huli ay binabawasan ang pagganap ng sistema ng impormasyon sa pamamagitan lamang ng pagkaantala ng mga solusyon sa mga problema.

Ito rin ay nagkakahalaga ng pag-alala na sa proseso ng paghahanap para sa mga kinakailangang index upang idagdag, mga mungkahi MS SQL Maaaring hindi tama ang pag-optimize, kabilang ang sa ilalim ng mga sumusunod na kondisyon:

kung mayroon nang mga index na may katulad na hanay ng mga patlang
kung ang mga patlang sa talahanayan ay hindi ma-index dahil sa mga paghihigpit sa pag-index (inilarawan nang mas detalyado dito).

2) Pagsasama-sama ng mga katangian sa isang bagong katangian

Minsan ang ilang mga patlang mula sa isang talahanayan, na nagsisilbing batayan para sa isang pangkat ng mga kundisyon, ay maaaring palitan sa pamamagitan ng pagpapakilala ng isang bagong field.

Ito ay totoo lalo na para sa mga patlang ng katayuan, na karaniwang bit o integer sa uri.

Halimbawa:

IsClosed = 0 AT Kinansela = 0 AT Pinagana = 0 ay pinalitan ng Katayuan = 1.

Dito ipinakilala ang katangian ng integer na Status upang matiyak na ang mga status na ito ay na-populate sa talahanayan. Susunod, na-index ang bagong katangiang ito.

Ito ay isang pangunahing solusyon sa problema sa pagganap, dahil ina-access namin ang data nang walang hindi kinakailangang mga kalkulasyon.

3) Materialization ng view

Sa kasamaang palad sa Mga query sa LINQ Ang mga pansamantalang talahanayan, CTE, at mga variable ng talahanayan ay hindi maaaring gamitin nang direkta.

Gayunpaman, may isa pang paraan upang mag-optimize para sa kasong ito - mga naka-index na view.

Pangkat ng kundisyon (mula sa halimbawa sa itaas) IsClosed = 0 AT Kinansela = 0 AT Pinagana = 0 (o isang hanay ng iba pang katulad na kundisyon) ay nagiging isang magandang opsyon na gamitin ang mga ito sa isang naka-index na view, na nag-cache ng isang maliit na slice ng data mula sa isang malaking set.

Ngunit mayroong ilang mga paghihigpit kapag nagsasagawa ng isang view:

paggamit ng mga subquery, sugnay MAY NA dapat palitan ng paggamit SUMALI
hindi ka maaaring gumamit ng mga pangungusap Unyon, UNION LAHAT, KALIGTASAN, INTERSECT
Hindi ka maaaring gumamit ng mga pahiwatig at sugnay sa talahanayan OPTION
walang posibilidad na magtrabaho sa mga cycle
Imposibleng magpakita ng data sa isang view mula sa iba't ibang mga talahanayan

Mahalagang tandaan na ang tunay na benepisyo ng paggamit ng naka-index na view ay maaari lamang makamit sa pamamagitan ng aktwal na pag-index nito.

Ngunit kapag tumatawag ng view, maaaring hindi gamitin ang mga index na ito, at para tahasang gamitin ang mga ito, dapat mong tukuyin MAY(NOEXPAND).

Mula noong sa Mga query sa LINQ Imposibleng tukuyin ang mga pahiwatig ng talahanayan, kaya kailangan mong lumikha ng isa pang representasyon - isang "wrapper" ng sumusunod na form:

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

4) Paggamit ng mga function ng talahanayan

Madalas sa Mga query sa LINQ Ang malalaking bloke ng mga subquery o bloke na gumagamit ng mga view na may kumplikadong istraktura ay bumubuo ng panghuling query na may napakakomplikado at suboptimal na istraktura ng pagpapatupad.

Mga Pangunahing Benepisyo ng Paggamit ng Mga Function ng Table sa Mga query sa LINQ:

Ang kakayahan, tulad ng sa kaso ng mga view, na gamitin at tinukoy bilang isang bagay, ngunit maaari kang magpasa ng isang set ng mga parameter ng input:
MULA SA FUNCTION(@param1, @param2 ...)
Bilang resulta, maaaring makamit ang flexible sampling ng data
Sa kaso ng paggamit ng function ng talahanayan, walang mga mahigpit na paghihigpit tulad ng sa kaso ng mga naka-index na view na inilarawan sa itaas:
1. Mga pahiwatig sa talahanayan:
  sa pamamagitan ng LINQ Hindi mo maaaring tukuyin kung aling mga index ang dapat gamitin at matukoy ang antas ng paghihiwalay ng data kapag nagtatanong.
  Ngunit ang function ay may ganitong mga kakayahan.
  Gamit ang function, makakamit mo ang isang medyo pare-pareho na plano sa query sa pagpapatupad, kung saan ang mga panuntunan para sa pagtatrabaho sa mga index at mga antas ng paghihiwalay ng data ay tinukoy
2. Ang paggamit ng function ay nagbibigay-daan, kumpara sa mga naka-index na view, na makakuha ng:
  - kumplikadong data sampling logic (kahit na gumagamit ng mga loop)
  - pagkuha ng data mula sa maraming iba't ibang mga talahanayan
  - ang paggamit ng Unyon и MAY NA
Alok OPTION lubhang kapaki-pakinabang kapag kailangan naming magbigay ng concurrency control OPTION(MAXDOP N), ang pagkakasunud-sunod ng plano ng pagpapatupad ng query. Halimbawa:
- maaari mong tukuyin ang isang sapilitang muling paglikha ng query plan OPTION (RECOMPILE)
- maaari mong tukuyin kung ipipilit ang query plan na gamitin ang join order na tinukoy sa query OPTION (PILITIN ANG ORDER)
Higit pang mga detalye tungkol sa OPTION inilarawan dito.
Gamit ang pinakamakitid at pinakakinakailangang hiwa ng data:
Hindi na kailangang mag-imbak ng malalaking data set sa mga cache (tulad ng kaso sa mga naka-index na view), kung saan kailangan mo pa ring i-filter ang data ayon sa parameter.
Halimbawa, mayroong isang talahanayan na ang filter SAAN tatlong field ang ginagamit (a, b, c).

Karaniwan, ang lahat ng mga kahilingan ay may pare-parehong kondisyon a = 0 at b = 0.

Gayunpaman, ang kahilingan para sa patlang c mas variable.

Hayaan ang kondisyon a = 0 at b = 0 Talagang nakakatulong ito sa amin na limitahan ang kinakailangang resultang set sa libu-libong talaan, ngunit naka-on ang kundisyon с pinapaliit ang pagpili hanggang sa isang daang talaan.

Narito ang function ng talahanayan ay maaaring isang mas mahusay na pagpipilian.

Gayundin, ang isang function ng talahanayan ay mas predictable at pare-pareho sa oras ng pagpapatupad.

Mga halimbawa

Tingnan natin ang isang halimbawa ng pagpapatupad gamit ang database ng Mga Tanong bilang isang halimbawa.

May hiling Piliin, na pinagsasama ang ilang mga talahanayan at gumagamit ng isang view (OperativeQuestions), kung saan ang kaakibat ay sinusuri sa pamamagitan ng email (sa pamamagitan ng MAY NA) sa "Mga Tanong sa Operatibo":

Kahilingan No. 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])
));

Ang view ay may medyo kumplikadong istraktura: mayroon itong mga subquery na sumali at gumagamit ng pag-uuri DISTINCT, na sa pangkalahatan ay isang medyo resource-intensive na operasyon.

Ang isang sample mula sa OperativeQuestions ay humigit-kumulang sampung libong talaan.

Ang pangunahing problema sa query na ito ay para sa mga talaan mula sa panlabas na query, ang isang panloob na subquery ay isinasagawa sa view ng [OperativeQuestions], na dapat para sa [Email] = @p__linq__0 ay nagpapahintulot sa amin na limitahan ang pagpili ng output (sa pamamagitan ng MAY NA) hanggang sa daan-daang talaan.

At maaaring mukhang dapat kalkulahin ng subquery ang mga tala nang isang beses sa pamamagitan ng [Email] = @p__linq__0, at pagkatapos ang ilang daang record na ito ay dapat ikonekta ng Id na may Mga Tanong, at magiging mabilis ang query.

Sa katunayan, mayroong sunud-sunod na koneksyon ng lahat ng mga talahanayan: pagsuri sa mga sulat ng Id Questions na may Id mula sa OperativeQuestions, at pag-filter sa pamamagitan ng Email.

Sa katunayan, gumagana ang kahilingan sa lahat ng sampu-sampung libong tala ng OperativeQuestions, ngunit ang data lang ng interes ang kailangan sa pamamagitan ng Email.

Tingnan ang text ng OperativeQuestions:

Kahilingan No. 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));

Paunang view ng pagmamapa sa DbContext (EF Core 2)

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

Paunang LINQ query

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

Sa partikular na kaso na ito, isinasaalang-alang namin ang isang solusyon sa problemang ito nang walang mga pagbabago sa imprastraktura, nang hindi nagpapakilala ng isang hiwalay na talahanayan na may mga handa na resulta ("Mga Aktibong Query"), na mangangailangan ng mekanismo para sa pagpuno nito ng data at pagpapanatiling napapanahon. .

Bagama't ito ay isang magandang solusyon, may isa pang opsyon upang ma-optimize ang problemang ito.

Ang pangunahing layunin ay i-cache ang mga entry sa pamamagitan ng [Email] = @p__linq__0 mula sa OperativeQuestions view.

Ipakilala ang table function [dbo].[OperativeQuestionsUserMail] sa database.

Sa pamamagitan ng pagpapadala ng Email bilang isang parameter ng input, nakakakuha kami ng isang talahanayan ng mga halaga:

Kahilingan No. 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

Nagbabalik ito ng talahanayan ng mga halaga na may paunang natukoy na istraktura ng data.

Upang ang mga query sa OperativeQuestionsUserMail ay maging optimal at magkaroon ng pinakamainam na query plan, isang mahigpit na istraktura ang kinakailangan, at hindi IBABALIK ANG TABLE BILANG PAGBABALIK...

Sa kasong ito, ang kinakailangang Query 1 ay na-convert sa Query 4:

Kahilingan No. 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]);

Pagmamapa ng mga view at function sa DbContext (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})");
}

Panghuling query sa LINQ

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

Ang pagkakasunud-sunod ng oras ng pagpapatupad ay bumaba mula 200-800 ms, hanggang 2-20 ms, atbp., ibig sabihin, sampu-sampung beses na mas mabilis.

Kung kukunin natin ito nang mas karaniwan, sa halip na 350 ms ay nakakuha tayo ng 8 ms.

Mula sa malinaw na mga pakinabang ay nakukuha rin natin:

pangkalahatang pagbawas sa pag-load ng pagbabasa,
makabuluhang pagbawas sa posibilidad ng pagharang
binabawasan ang average na oras ng pagharang sa mga katanggap-tanggap na halaga

Pagbubuhos

Pag-optimize at fine-tuning ng mga tawag sa database MS SQL sa pamamagitan ng LINQ ay isang problema na maaaring malutas.

Ang pagiging maasikaso at pagkakapare-pareho ay napakahalaga sa gawaing ito.

Sa simula ng proseso:

kinakailangang suriin ang data kung saan gumagana ang kahilingan (mga halaga, mga napiling uri ng data)
isagawa ang wastong pag-index ng data na ito
suriin ang kawastuhan ng mga kondisyon ng pagsasama sa pagitan ng mga talahanayan

Ang susunod na pag-ulit ng pag-optimize ay nagpapakita ng:

batayan ng kahilingan at tinutukoy ang pangunahing filter ng kahilingan
pag-uulit ng mga katulad na bloke ng query at pagsusuri sa intersection ng mga kundisyon
sa SSMS o iba pang GUI para sa SQL Server ino-optimize ang sarili SQL query (paglalaan ng isang intermediate na storage ng data, pagbuo ng resultang query gamit ang storage na ito (maaaring marami))
sa huling yugto, na ginagawang batayan ang resulta SQL query, muling itinatayo ang istraktura Tanong ng LINQ

Ang resulta Tanong ng LINQ dapat maging magkapareho sa istraktura sa natukoy na pinakamainam SQL query mula sa punto 3.

Mga Pasasalamat

Maraming salamat sa mga kasamahan jobgemws и alex_ozr mula sa kumpanya Fortis para sa tulong sa paghahanda ng materyal na ito.

Pinagmulan: www.habr.com

Mga pamamaraan para sa pag-optimize ng mga query sa LINQ sa C#.NET