🥇Nzira dzekugadzirisa LINQ mibvunzo muC#.NET

Nhanganyaya

В ichi chinyorwa dzimwe nzira dzekugadzirisa dzakatariswa LINQ mibvunzo.
Pano tinopa dzimwe nzira dzekugadzirisa kodhi dzine chekuita ne LINQ mibvunzo.

Zvinozivikanwa izvozvo LINQ(Mutauro-Integrated Query) mutauro wakapfava uye wakanakira kubvunza kunobva data.

А LINQ kuSQL inyanzvi yekuwana data muDBMS. Ichi chishandiso chine simba chekushanda nedata, uko mibvunzo inovakwa kuburikidza nemutauro unozivisa, unozoshandurwa kuita SQL mibvunzo chikuva uye chakatumirwa kune database server kuti iitwe. Muchiitiko chedu, neDBMS tinoreva MS SQL Server.

Zvisinei, LINQ mibvunzo haashandurwe kuita zvakanyorwa zvakanyatsonaka SQL mibvunzo, iyo DBA ine ruzivo yaigona kunyora nemanuances ese ekugadzirisa SQL mibvunzo:

optimal connections (ONA) uye kusefa zvabuda (WHERE)
nuances dzakawanda pakushandisa kubatana uye mamiriro eboka
misiyano yakawanda mukutsiva mamiriro IN pamusoro VAPOи NOT IN, <> on VAPO
yepakati caching yemhedzisiro kuburikidza nematafura enguva pfupi, CTE, tafura dzakasiyana
kushandiswa kwechirevo (CHINHU) nemirayiridzo uye mazano etafura NA (...)
kushandisa maonerwo akaiswa indexed seimwe yenzira dzekubvisa kusaverengeka kwedata rekuverenga panguva yesarudzo

Iyo huru yekuita mabhodhoro ezvakaguma SQL mibvunzo pakugadzira LINQ mibvunzo nde:

kubatanidzwa kwese kusarudzwa kwedata muchikumbiro chimwe
kudzokorora zvidhinha zvakafanana zvekodhi, izvo zvinozotungamira kune akawanda asina kufanira data kuverenga
mapoka ezvakasiyana-siyana mamiriro ezvinhu (zvinonzwisisika "uye" uye "kana") - AND и OR, kusanganisa mumamiriro ezvinhu akaoma, zvinotungamira kune chokwadi chekuti iyo optimizer, ine yakakodzera isina-yakaunganidzwa indexes yeminda inodiwa, inopedzisira yatanga kuongorora inopesana neiyo clustered index (INDEX SCAN) nemapoka emamiriro
kudzika kwakadzika kwema subqueries kunoita kuti parsing inonetsa SQL statements uye kuongororwa kwechirongwa chemubvunzo kune chikamu chevagadziri uye DBA

Optimization nzira

Zvino ngatifambei takananga kune optimization nzira.

1) Kuwedzera indexing

Zvakanakisa kufunga mafirita pamatafura makuru ekusarudza, nekuti kazhinji mubvunzo wese unovakwa kutenderedza tafura imwe chete kana maviri makuru (maapplication-people-operations) uye neyakajairwa seti yemamiriro (IsClosed, Canceded, Enbled, Status). Zvakakosha kugadzira indices dzakakodzera dzemasampuli akaonekwa.

Iyi mhinduro ine musoro kana uchisarudza minda iyi inodzika zvakanyanya iyo yakadzoserwa seti kumubvunzo.

Semuenzaniso, tine 500000 maapplication. Nekudaro, kune 2000 chete inoshanda maapplication. Ipapo indekisi yakasarudzwa nemazvo ichatiponesa kubva INDEX SCAN patafura hombe uye ichakubvumidza iwe kukurumidza kusarudza data kuburikidza neiyo isiri-clustered index.

Zvakare, kushomeka kwemaindekisi kunogona kuzivikanwa kuburikidza nekukurudzira kurongedza hurongwa hwemibvunzo kana kuunganidza system yekutarisa manhamba. MS SQL Server:

Yese yekuona data ine ruzivo nezve yakashaikwa indexes, kunze kwe spatial indexes.

Nekudaro, indexes uye caching kazhinji inzira dzekurwisa mhedzisiro yekusanyorwa zvakanaka LINQ mibvunzo и SQL mibvunzo.

Sezvinoratidzwa nemaitiro akaomarara ehupenyu, kazhinji zvakakosha kuti bhizinesi rishandise maficha ebhizinesi nemamwe mazuva ekupedzisira. Uye saka, zvikumbiro zvinorema zvinowanzoendeswa kumashure ne caching.

Izvi zvine zvimwe zvinoruramiswa, sezvo mushandisi haagare achida data razvino uye kune nhanho inogamuchirika yekupindura kweiyo mushandisi interface.

Iyi nzira inobvumira kugadzirisa zvinodikanwa zvebhizinesi, asi pakupedzisira inoderedza mashandiro ehurongwa hweruzivo nekungononoka kugadzirisa matambudziko.

Izvo zvakakoshawo kuyeuka kuti mukuita kutsvaga inodiwa indexes yekuwedzera, mazano MS SQL optimization inogona kunge isiriyo, kusanganisira pasi pezvinotevera mamiriro:

kana paine ma indexes ane seti yeminda yakafanana
kana minda iri patafura isingagone kunyoreswa nekuda kwezvirambidzo zve indexing (inotsanangurwa zvakadzama pano).

2) Kubatanidza hunhu kuita humwe hunhu hutsva

Dzimwe nguva mimwe minda kubva patafura imwe, iyo inoshanda sehwaro hweboka remigariro, inogona kutsiviwa nokusuma munda mumwe mutsva.

Izvi ndezvechokwadi kunyanya kundima dzechimiro, dzinowanzova bhiti kana kuti nhamba mumhando.

Muenzaniso:

Yakavharwa = 0 UYE Yakamiswa = 0 UYE Yakagoneswa = 0 inotsiviwa ne Status = 1.

Apa ndipo panosumwa nhamba yeChimiro chechimiro kuitira kuona kuti aya mastatus akazara mutafura. Tevere, hunhu hutsva uhu hunoiswa indexed.

Iyi ndiyo mhinduro yakakosha kudambudziko rekuita, nekuti Isu tinowana data pasina maverengero asina kufanira.

3) Materialization yemaonero

Zvinosuruvarisa, mu LINQ mibvunzo Matafura enguva pfupi, CTEs, uye tafura dzakasiyana hazvigone kushandiswa zvakananga.

Nekudaro, pane imwe nzira yekukwiridzira iyi kesi - indexed maonero.

Condition group (kubva pamuenzaniso uri pamusoro) Yakavharwa = 0 UYE Yakamiswa = 0 UYE Yakagoneswa = 0 (kana seti yemamwe mamiriro akafanana) inova sarudzo yakanaka yekuishandisa mune indexed view, caching diki diki data kubva kune yakakura seti.

Asi pane akati wandei zvirambidzo pakuita maonero:

kushandisa subqueries, clauses VAPO inofanira kutsiviwa nekushandisa ONA
haugone kushandisa mitsara UNION, UNION ZVESE, EXCEPTION, KUSVIRA
Iwe haugone kushandisa tafura mazano uye clauses CHINHU
hapana mukana wekushanda nema cycles
Hazvibviri kuratidza data mune imwe maonero kubva pamatafura akasiyana

Kunokosha kuyeuka kuti betsero chaiyoiyo yokushandisa maonere ane indexed inogona bedzi kuwanwa kupfurikidza nokuiratidza chaizvoizvo.

Asi kana uchidaidza maonero, aya ma index anogona kusashandiswa, uye kuti uashandise zvakajeka, unofanirwa kutsanangura NA(NOEXPAND).

Kubva muna LINQ mibvunzo Hazvibviri kutsanangura mazano etafura, saka unofanirwa kugadzira imwe inomiririra - "wrapper" yefomu rinotevera:

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

4) Kushandisa tafura mabasa

Kazhinji mu LINQ mibvunzo Zvivharo zvakakura zvema subqueries kana mabhuroko anoshandisa maonero ane chimiro chakaomarara anoumba mubvunzo wekupedzisira une yakanyanya kuoma uye suboptimal execution chimiro.

Mabhenefiti Akakosha Ekushandisa Tafura Mabasa mu LINQ mibvunzo:

Iko kugona, senge muchiitiko chemaonero, kushandiswa uye kutsanangurwa sechinhu, asi iwe unogona kupfuudza seti yekuisa paramita:
KUBVA KUBASA(@param1, @param2 ...)
Nekuda kweizvozvo, flexible data sampling inogona kuwanikwa
Muchiitiko chekushandisa tafura basa, hapana zvirambidzo zvakasimba zvakadaro sezviri munyaya yemaonero akaiswa akatsanangurwa pamusoro apa:
1. Tafura mazano:
  kuburikidza LINQ Iwe haugone kutsanangura kuti ndeapi ma indexes anofanirwa kushandiswa uye kuona iyo data yekuparadzanisa nhanho paunenge uchibvunza.
  Asi basa rine zvikwanisiro izvi.
  Nebasa racho, unogona kuwana chirongwa chemubvunzo wenguva dzose, uko mitemo yekushanda nema indexes uye mazinga ekuparadzanisa data anotsanangurwa.
2. Kushandisa basa rinobvumira, mukuenzanisa ne indexed maonero, kuwana:
  - yakaoma data sampling logic (kunyangwe kushandisa zvishwe)
  - kutora data kubva kumatafura akawanda akasiyana
  - kushandiswa UNION и VAPO
Offer CHINHU inobatsira zvikuru kana tichida kupa concurrency control SARUDZO(MAXDOP N), kurongeka kwechirongwa chekuita mubvunzo. Semuyenzaniso:
- iwe unogona kutsanangura kumanikidzwa kusikazve kwechirongwa chemubvunzo OPTION (RECOMPILE)
- unogona kutsanangura kana kumanikidza chirongwa chemubvunzo kushandisa iyo yekujoinha yakatsanangurwa mumubvunzo OPTION (FORCE ORDER)
Mamwe mashoko pamusoro CHINHU akatsanangura pano.
Uchishandisa yakatetepa uye inonyanya kudiwa data chidimbu:
Iko hakuna chikonzero chekuchengeta yakakura data seti mumacache (sezvazvakaita ne indexed maonero), kubva kwauchiri kuda kusefa data neparameter.
Semuenzaniso, pane tafura ine sefa WHERE minda mitatu inoshandiswa (a,b,c).

Conventionally, zvikumbiro zvose ane anogara mamiriro a = 0 uye b = 0.

Zvisinei, chikumbiro chemunda c zvimwe zvakasiyana.

Rega mamiriro acho a = 0 uye b = 0 Zvinonyatso kutibatsira kudzikamisa zvinodiwa zvinokonzeresa kuzviuru zvemarekodhi, asi mamiriro aripo с inoderedza kusarudzwa kusvika kuzana rekodhi.

Pano basa retafura rinogona kunge riri sarudzo iri nani.

Zvakare, basa retafura rinonyanya kufanotaura uye rinoenderana munguva yekuuraya.

mienzaniso

Ngatitarisei muenzaniso kuita uchishandisa iyo Mibvunzo dhatabhesi semuenzaniso.

Pane chikumbiro SELECT, iyo inosanganisa matafura akati wandei uye inoshandisa imwe maonero (OperativeQuestions), umo kubatanidzwa kunotariswa neemail (kuburikidza VAPO) ku "Mibvunzo Yekushanda":

Chikumbiro Nha

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

Maonero ane chimiro chakaoma kunzwisisa: ine subquery inojoinha uye inoshandisa kurongedza DISTINCT, iyo kazhinji ibasa rakasimba rekushandisa.

Muenzaniso kubva kuOperativeQuestions ungangoita zviuru gumi zvinyorwa.

Dambudziko guru nemubvunzo uyu nderekuti kune marekodhi kubva kumubvunzo wekunze, subquery yemukati inoitwa pa[OperativeQuestions] maonero, ayo anofanirwa ku [Email] = @p__linq__0 kutibvumira kudzikamisa sarudzo yekubuda (kuburikidza VAPO) kusvika kumazana ezvinyorwa.

Uye zvingaite sekuti subquery inofanira kuverenga marekodhi kamwe chete ne [Email] = @p__linq__0, uyezve aya mazana marekodhi anofanirwa kubatana neId neMibvunzo, uye mubvunzo uchakurumidza.

Muchokwadi, kune inoteedzana yekubatanidza yematafura ese: kutarisa kunyoreswa kweId Mibvunzo neId kubva kuOperativeQuestions, uye kusefa neEmail.

Muchokwadi, chikumbiro chinoshanda nemakumi ezviuru zveOperativeQuestions marekodhi, asi chete iyo data yekufarira inodiwa kuburikidza neEmail.

OperativeQuestions ona mavara:

Chikumbiro Nha

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

Yekutanga kuona mepu muDbContext (EF Core 2)

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

Chekutanga LINQ mubvunzo

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

Panyaya iyi, tiri kufunga nezvemhinduro yedambudziko iri pasina shanduko yezvivakwa, tisinga suma tafura yakaparadzana ine mhinduro dzakagadzirirwa ("Active Queries"), izvo zvingada nzira yekuizadza nedata uye kuichengeta ichienderana. .

Kunyangwe iyi iri mhinduro yakanaka, pane imwe sarudzo yekugadzirisa dambudziko iri.

Chinangwa chikuru ndechekuchengetedza zvinyorwa ne [Email] = @p__linq__0 kubva paOperativeQuestions maonero.

Sumai basa retafura [dbo].[OperativeQuestionsUserMail] mudura re database.

Nekutumira Imeyili seyekupinza parameter, tinodzosera tafura yezvakakosha:

Chikumbiro Nha


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

Izvi zvinodzosa tafura yezvakakosha ine predefined data chimiro.

Kuti mibvunzo kuOperativeQuestionsUserMail ive yakakwana uye ive nehurongwa hwemibvunzo yakakwana, chimiro chakasimba chinodiwa, uye kwete. ANODZOSERA TAFURA SEKUDZOKERA...

Mune ino kesi, inodiwa Query 1 inoshandurwa kuita Query 4:

Chikumbiro Nha

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

Mepu maonero uye mabasa muDbContext (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})");
}

Yekupedzisira LINQ mubvunzo

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

Kurongeka kwenguva yekuuraya kwakaderera kubva ku200-800 ms, kusvika ku2-20 ms, nezvimwewo, i.e. makumi enguva nekukurumidza.

Kana tikaitora zvakaenzana, saka pachinzvimbo che350 ms takawana 8 ms.

Kubva pane zviri pachena zvakanakira isu zvakare tinowana:

kuderera kwehuwandu hwekuverenga mutoro,
kuderera kwakanyanya mukana wekuvhara
kuderedza avhareji yekuvharira nguva kune zvinogamuchirika tsika

mhedziso

Kugonesa uye kugadzirisa zvakanaka kwedatabase mafoni MS SQL kuburikidza LINQ idambudziko rinogona kugadziriswa.

Kuteerera uye kuenderana kunokosha zvikuru mubasa iri.

Pakutanga kwemaitiro:

zvinodikanwa kuti utarise iyo data iyo chikumbiro chinoshanda nacho (tsika, dzakasarudzwa data mhando)
ita indexing chaiyo yeiyi data
tarisa kurongeka kwekubatanidza mamiriro pakati pematafura

Iyo inotevera optimization iteration inoratidza:

hwaro hwechikumbiro uye inotsanangura iyo huru yekukumbira sefa
kudzokorora zvakafanana mabhuroki emibvunzo uye kuongorora mharadzano yemamiriro
muSSMS kana imwe GUI ye SQL Server inozvinatsiridza SQL mubvunzo (kugovera yepakati data kuchengetedza, kuvaka mhedzisiro yemubvunzo uchishandisa iyi yekuchengetedza (panogona kunge paine akati wandei))
padanho rekupedzisira, kutora sehwaro mhedzisiro SQL mubvunzo, chivako chacho chiri kuvakwazve LINQ mubvunzo

The result LINQ mubvunzo inofanira kufanana muchimiro kune yakaonekwa yakakwana SQL mubvunzo kubva papoint 3.

Kutenda

Kutenda zvikuru kune vatinoshanda navo jobgemws и alex_ozr kubva kukambani Fortis nokuda kwebetsero mukugadzirira chinyorwa ichi.

Source: www.habr.com

Nzira dzekugadzirisa LINQ mibvunzo muC#.NET

Nhanganyaya

Optimization nzira

1) Kuwedzera indexing

2) Kubatanidza hunhu kuita humwe hunhu hutsva

3) Materialization yemaonero

4) Kushandisa tafura mabasa

mienzaniso

mhedziso

Kutenda

Voeg kukanzura mhinduro