🥇Mekhoa ea ho ntlafatsa lipotso tsa LINQ ho C#.NET

Selelekela

В sehlooho sena ho ile ha shejoa mekhoa e meng ea ho ntlafatsa LINQ lipotso.
Mona re fana ka mekhoa e meng hape ea ho ntlafatsa khoutu e amanang le Lipotso tsa LINQ.

Hoa tsebahala hore LINQ(Language-Integrated Query) ke puo e bonolo le e bonolo bakeng sa ho botsa mohloli oa data.

А LINQ ho SQL ke theknoloji ea ho fumana data ho DBMS. Sena ke sesebelisoa se matla sa ho sebetsa ka data, moo lipotso li hahoang ka puo e hlalosang, e tla fetoleloa ho Lipotso tsa SQL sethaleng mme e rometsoe ho seva sa database bakeng sa ts'ebetso. Tabeng ea rona, ka DBMS re bolela Seva ea MS SQL.

Leha ho le joalo, LINQ lipotso ha li fetoleloe ho tse ngotsoeng hantle Lipotso tsa SQL, eo DBA e nang le boiphihlelo e ka e ngolang ka mefuta eohle ea ntlafatso Lipotso tsa SQL:

likamano tse ntle (ETSA) le ho sefa liphetho (HOBANE'NG)
li-nuances tse ngata mabapi le ho sebelisa likhokahano le maemo a sehlopha
mefuta e mengata ea ho fetola maemo IN mabapi le E TSOANGи SE KE UA, <> ho E TSOANG
ho boloka lipakeng tsa liphetho ka litafole tsa nakoana, CTE, mefuta ea litafole
tšebeliso ea polelo (KHETHO) ka litaelo le lintlha tsa tafole LE (...)
ho sebelisa lipono tse thathamisitsoeng e le e 'ngoe ea mekhoa ea ho tlosa ho baloa ha data ho sa hlokahaleng nakong ea likhetho

Litšitiso tse ka sehloohong tsa ts'ebetso ea sephetho Lipotso tsa SQL ha o hlophisa LINQ lipotso ke:

ho kopanngoa ha mokhoa oohle oa khetho ea data ka kopo e le 'ngoe
ho kopitsa li-blocks tse tšoanang tsa khoutu, tse qetellang li lebisa ho baloeng ha data ho sa hlokahaleng
lihlopha tsa maemo a likarolo tse ngata (a utloahalang "le" le "kapa") - LE и OR, ho kopana le maemo a rarahaneng, ho lebisa tabeng ea hore optimizer, e nang le li-index tse loketseng tse sa kopanngoeng bakeng sa masimo a hlokahalang, qetellong e qala ho hlahloba khahlanong le index e kopantsoeng (TS'ELISO SEKANANE) ka lihlopha tsa maemo
Nesting e tebileng ea li-subqueries e etsa hore ho arola ho be thata haholo Litlhaloso tsa SQL le tlhahlobo ea morero oa lipotso ka lehlakoreng la bahlahisi le dba

Mekhoa ea ho ntlafatsa

Joale ha re feteleng ka kotloloho mekhoeng ea ho ntlafatsa.

1) Tlatsetso indexing

Ho molemo ho nahana ka li-filters holim'a litafole tse kholo tsa khetho, kaha hangata potso eohle e hahiloe ho pota-pota tafole e le 'ngoe kapa tse peli tse kholo (lisebelisoa-ts'ebetso ea batho) le maemo a tloaelehileng (E Koaletsoe, E Hlakotsoe, E nolofalitsoe, Boemo). Ho bohlokoa ho etsa li-indices tse nepahetseng bakeng sa lisampole tse khethiloeng.

Tharollo ena ea utloahala ha u khetha libaka tsena e fokotsa haholo sete e khutlisitsoeng ho potso.

Mohlala, re na le likopo tse 500000. Leha ho le joalo, ho na le lits'ebetso tse 2000 feela tse sebetsang. Joale index e khethiloeng ka nepo e tla re pholosa TS'ELISO SEKANANE tafoleng e kholo 'me e tla u lumella ho khetha kapele data ka index e sa kopanngoeng.

Hape, khaello ea li-index e ka tsejoa ka likhothaletso bakeng sa meralo ea lipotso kapa ho bokella lipalo-palo tsa pono ea sistimi. Seva ea MS SQL:

Lintlha tsohle tsa pono li na le leseli mabapi le li-index tse sieo, ntle le li-index tsa sebaka.

Leha ho le joalo, li-index le caching hangata ke mekhoa ea ho loants'a litlamorao tsa ho ngola hampe LINQ lipotso и Lipotso tsa SQL.

Joalo ka ha mokhoa o thata oa bophelo o bontša, hangata ho bohlokoa hore khoebo e kenye tšebetsong likarolo tsa khoebo ka linako tse itseng. 'Me ka hona, likopo tse boima hangata li fetisetsoa ka morao ka caching.

Sena se nepahetse ka mokhoa o itseng, kaha ha se kamehla mosebelisi a hlokang data ea morao-rao mme ho na le boemo bo amohelehang ba karabelo ea sebopeho sa mosebelisi.

Mokhoa ona o lumella ho rarolla litlhoko tsa khoebo, empa qetellong o fokotsa ts'ebetso ea sistimi ea tlhahisoleseling ka ho lieha ho rarolla mathata.

Hape ke habohlokoa ho hopola hore ts'ebetsong ea ho batla li-index tse hlokahalang ho eketsa, litlhahiso MS SQL optimization e kanna ea fosahala, ho kenyeletsoa tlasa maemo a latelang:

haeba ho se ho na le li-index tse nang le lihlopha tse tšoanang tsa masimo
haeba masimo a tafoleng a ke ke a ngolisoa ka lebaka la lithibelo tsa indexing (e hlalositsoe ka botlalo mona).

2) Ho kopanya litšoaneleho ho semelo se le seng se secha

Ka linako tse ling masimo a mang a tsoang tafoleng e le ’ngoe, a sebeletsang e le motheo oa sehlopha sa maemo, a ka nkeloa sebaka ke ho kenya tšimo e le ’ngoe e ncha.

Sena ke 'nete haholo-holo bakeng sa likarolo tsa boemo, tseo hangata li leng nyenyane kapa tse feletseng ka mofuta.

Mohlala:

E Koetse = 0 LE E Hlakotsoe = 0 LE E Nolofalitsoe = 0 e nkeloa sebaka ke Boemo = 1.

Mona ke moo tšobotsi e felletseng ea Boemo e hlahisoang ho netefatsa hore maemo ana a behiloe lethathamong. Ka mor'a moo, tšobotsi ena e ncha ea indexed.

Ena ke tharollo ea mantlha bothateng ba ts'ebetso, hobane Re fihlella data ntle le lipalo tse sa hlokahaleng.

3) Tlhahiso ea lintho tse bonahalang

Ka bomalimabe, ho Lipotso tsa LINQ Litafole tsa nakoana, li-CTE, le mefuta ea litafole li ke ke tsa sebelisoa ka kotloloho.

Leha ho le joalo, ho na le mokhoa o mong oa ho ntlafatsa bakeng sa nyeoe ena - maikutlo a indexed.

Sehlopha sa maemo (ho tloha mohlaleng o ka holimo) E Koetse = 0 LE E Hlakotsoe = 0 LE E Nolofalitsoe = 0 (kapa sete ea maemo a mang a tšoanang) e fetoha khetho e ntle ea ho li sebelisa ka pono e bonts'itsoeng, ho boloka selae se senyenyane sa data ho tloha sete e kholo.

Empa ho na le lithibelo tse 'maloa ha ho etsoa pono:

tšebeliso ea likarolo, likarolo E TSOANG e lokela ho nkeloa sebaka ke ho sebelisa ETSA
ha o khone ho sebelisa lipolelo UNION, MOKHOAHLELE TSOHLE, KEKETSO, KHOTHATSO
U ke ke ua sebelisa litlhahiso tsa tafole le litemana KHETHO
ha ho na monyetla oa ho sebetsa le li-cycle
Ha ho khonehe ho hlahisa data ka pono e le 'ngoe ho tsoa litafoleng tse fapaneng

Ke habohlokoa ho hopola hore molemo oa sebele oa ho sebelisa pono ea indexed o ka finyelloa feela ka ho o etsa indexed.

Empa ha o letsetsa pono, li-index tsena li kanna tsa se sebelisoe, 'me ho li sebelisa ka mokhoa o hlakileng, o tlameha ho hlakisa LE(NOEXPAND).

Ho tloha ka Lipotso tsa LINQ Ha ho khonehe ho hlalosa lintlha tsa tafole, kahoo o tlameha ho theha setšoantšo se seng - "wrapper" ea foromo e latelang:

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

4) Ho sebelisa mesebetsi ea tafole

Hangata ho Lipotso tsa LINQ Li-blocks tse kholo tsa li-subqueries kapa li-blocks tse sebelisang maikutlo a nang le sebopeho se rarahaneng li theha potso ea ho qetela e nang le sebopeho se rarahaneng haholo le se sa sebetseng hantle.

Melemo ea Bohlokoa ea ho Sebelisa Mesebetsi ea Tafole ka Lipotso tsa LINQ:

Bokhoni, joalo ka boemong ba maikutlo, bo ka sebelisoang le ho hlalosoa e le ntho, empa u ka fetisa sete ea liparamente tsa ho kenya:
HO TSOA MOSEBETSI (@param1, @param2 ...)
Ka lebaka leo, sampole ea data e feto-fetohang e ka finyelloa
Tabeng ea ho sebelisa ts'ebetso ea tafole, ha ho na lithibelo tse matla joalo ka tabeng ea lipono tse hlalositsoeng ka holimo:
1. Litlhahiso tsa tafole:
  ho pholletsa LINQ U ke ke ua hlakisa hore na ke li-index life tse lokelang ho sebelisoa le ho fumana boemo ba ho arola data ha u botsisisa.
  Empa ts'ebetso e na le lisebelisoa tsena.
  Ka ts'ebetso, o ka fihlela moralo oa potso oa phetisetso, moo melao ea ho sebetsa ka li-index le maemo a ho itšehla thajana a hlalosoang.
2. Ho sebelisa ts'ebetso ho lumella, ha ho bapisoa le maikutlo a indexed, ho fumana:
  - logic e rarahaneng ea sampole ea data (esita le ho sebelisa loops)
  - ho lata data ho tsoa litafoleng tse ngata tse fapaneng
  - tshebediso ya UNION и E TSOANG
Fana KHETHO e thusa haholo ha re hloka ho fana ka taolo ea concurrency KHETHO(MAXDOP N), tatellano ea moralo oa ts'ebetso ea potso. Ka mohlala:
- o ka hlakisa tokiso e qobelloang ea moralo oa lipotso KHETHO (RECOMPILE)
- o ka hlakisa hore na ho qobella moralo oa potso ho sebelisa taelo ea ho kopanya e boletsoeng potsong KHETHO (FORCE ORDER)
Lintlha tse ling mabapi le KHETHO hlalositsoe mona.
Ho sebelisa sekhechana sa data se patisaneng le se hlokahalang haholo:
Ha ho na tlhoko ea ho boloka li-data tse kholo ka har'a li-cache (joalo ka ha ho le joalo ka maikutlo a indexed), moo o ntseng o hloka ho sefa data ka paramente.
Ka mohlala, ho na le tafole eo sefe HOBANE'NG ho sebelisoa masimo a mararo (a, b, c).

Ka tloaelo, likopo tsohle li na le boemo bo sa feleng a = 0 le b = 0.

Leha ho le joalo, kopo ea tšimo c feto-fetoha ho feta.

Tlohela boemo a = 0 le b = 0 E hlile e re thusa ho fokotsa sephetho se hlokahalang ho likete tsa lirekoto, empa boemo bo le teng с e fokotsa khetho ho fihlela ho lirekoto tse lekholo.

Mona mosebetsi oa tafole e ka ba khetho e ntle.

Hape, ts'ebetso ea tafole e ka lebelloa le ho ts'oara nako ea ts'ebetso.

mehlala

Ha re shebeng mohlala oa ts'ebetsong re sebelisa database ea Lipotso joalo ka mohlala.

Ho na le kopo Khetha, e kopanyang litafole tse 'maloa' me e sebelisa pono e le 'ngoe (OperativeQuestions), moo kamano e hlahlojoang ka imeile (ka E TSOANG) ho "Lipotso tsa Ts'ebetso":

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

Pono e na le sebopeho se rarahaneng: e na le likaroloana tsa subquery mme e sebelisa ho hlopha QETHA, eo ka kakaretso e leng ts'ebetso e batlang e le matla haholo.

Mohlala ho tsoa ho OperativeQuestions ke lirekoto tse ka bang likete tse leshome.

Bothata bo boholo ka potso ena ke hore bakeng sa lirekoto tse tsoang potsong ea kantle, subquery e ka hare e etsoa ho [OperativeQuestions] pono, e lokelang bakeng sa [Email] = @p__linq__0 ho re lumella ho fokotsa khetho ea tlhahiso (ka tsela. E TSOANG) ho fihlela ho makholo a lirekoto.

'Me ho ka bonahala eka subquery e lokela ho bala litlaleho hang ka [Email] = @p__linq__0, ebe litlaleho tsena tse makholo a mabeli li lokela ho hokahanngoa ke Id le Lipotso, 'me potso e tla potlaka.

Ebile, ho na le khokahano e latellanang ea litafole tsohle: ho lekola ngollano ea Lipotso tsa Id ka Id ho tsoa ho Lipotso tsa Ts'ebetso, le ho sefa ka Imeile.

Ha e le hantle, kopo e sebetsa le mashome a likete a lirekoto tsa OperativeQuestions, empa ho hlokahala feela lintlha tsa thahasello ka Email.

Potso ea ho sebetsa sheba mongolo:

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

Ponahalo ea pele ea 'mapa ho 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");
    }
}

Potso ea pele ea LINQ

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

Tabeng ena, re nahana ka tharollo ea bothata bona ntle le liphetoho tsa meralo, ntle le ho hlahisa tafole e arohaneng e nang le liphetho tse lokiselitsoeng ("Active Queries"), e neng e tla hloka mokhoa oa ho e tlatsa ka data le ho e boloka e ntse e le teng. .

Leha ena e le tharollo e ntle, ho na le khetho e 'ngoe ea ho ntlafatsa bothata bona.

Morero o ka sehloohong ke ho boloka li-cache ka [Email] = @p__linq__0 ho tsoa ponong ea OperativeQuestions.

Hlahisa tšebetso ea tafole [dbo].[OperativeQuestionsUserMail] polokelongtshedimosetso.

Ka ho romella lengolo-tsoibila joalo ka paramente ea ho kenya, re khutlisa tafole ea boleng:

Kopo 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

Sena se khutlisa tafole ea boleng ka sebopeho sa data se boletsoeng esale pele.

E le hore lipotso ho OperativeQuestionsUserMail li sebetse hantle 'me li be le merero e nepahetseng ea ho botsa, ho hlokahala sebopeho se thata, eseng. RETS'ELISITSOE TAFOLE JOALOKA...

Tabeng ena, Potso ea 1 e hlokahalang e fetoleloa ho Query 4:

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

Maikutlo a 'mapa le mesebetsi ho 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})");
}

Potso ea ho qetela ea 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();

Taelo ea nako ea ho bolaoa e theohile ho tloha 200-800 ms, ho ea ho 2-20 ms, joalo-joalo, ke hore makhetlo a mashome ka potlako.

Haeba re e nka ka karolelano, joale sebakeng sa 350 ms re fumane 8 ms.

Ho tsoa ho melemo e totobetseng re boetse re fumana:

phokotso e akaretsang ya boima ba ho bala,
phokotso e kholo ea monyetla oa ho thibela
ho fokotsa nako e tloaelehileng ea ho thibela ho ea ho litekanyetso tse amohelehang

fihlela qeto e

Ntlafatso le tokiso e ntle ea mehala ea database MS SQL ho pholletsa LINQ ke bothata bo ka rarolloang.

Ho ela hloko le ho tsitsa ho bohlokoa haholo mosebetsing ona.

Qalong ea ts'ebetso:

hoa hlokahala ho hlahloba lintlha tseo kopo e sebetsang ka tsona (litekanyetso, mefuta e khethiloeng ea data)
etsa indexing e nepahetseng ea data ena
hlahloba ho nepahala ha ho kopanya maemo pakeng tsa litafole

Phetolelo e latelang ea optimization e bontša:

motheo oa kopo le e hlalosa ka sehloohong kopo filthara
ho pheta-pheta li-block tsa lipotso le ho sekaseka mateano a maemo
ho SSMS kapa GUI e 'ngoe bakeng sa SQL Server e ntlafatsa ka boyona Potso ea SQL (ho abela polokelo ea data ea mahareng, ho aha potso e hlahisoang ka polokelo ena (ho ka ba le tse 'maloa))
mothating oa ho qetela, ho nka e le motheo oa sephetho Potso ea SQL, mohaho o ntse o tsosolosoa LINQ potso

Sephetho LINQ potso e lokela ho tšoana ka sebopeho ho ea boleng bo holimo Potso ea SQL ho tloha ntlheng ea 3.

Liteboho

Ke leboha haholo basebetsi-'moho mesebetsi и alex_ozr ho tsoa ho khampani Fortis bakeng sa thuso ea ho lokisetsa boitsebiso bona.

Source: www.habr.com

Mekhoa ea ho ntlafatsa lipotso tsa LINQ ho C#.NET