🥇LINQ Mekhoa ea ho Kopanya Lipotso ho C#.NET

Selelekela

В sehlooho sena ho ile ha shejoa mekhoa e meng ea ho ntlafatsa Lipotso tsa LINQ.
Mona re tla boela re hlahise mekhoa e meng 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. Ke sesebelisoa se matla sa ho sebetsa ka data, moo lipotso li hahoang ka puo e hlalosang, e tla fetoloa hore e be. 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, Lipotso tsa LINQ ha li fetoleloe ho ngoloa 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 liphetho tsa ho sefa (HOBANE'NG)
li-nuances tse ngata ts'ebelisong ea likhokahano le maemo a sehlopha
mefuta e mengata ea ho fetola maemo IN mabapi le E TSOANGи HA HO KENA, <> 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 khetho

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

ho kopanngoa ha mokhoa oohle oa khetho ea data ka kopo e le 'ngoe
phetisetso ea li-block tsa khoutu tse ts'oanang, tseo qetellong li lebisang ho baloeng ha data ho sa hlokahaleng
lihlopha tsa maemo a likarolo tse ngata (tse 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 ntse e qala ho hlahloba index ea lihlopha (TS'ELISO SEKANANE) ka lihlopha tsa maemo
Nesting e tebileng ea li-subqueries e etsa hore ho arola ho be thata haholo Litaelo tsa SQL le tlhahlobo ea moralo oa kopo ho tsoa ho baetsi 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 (likopo-ts'ebetso ea batho) le ka maemo a tloaelehileng (E Koaletsoe, E Hlakotsoe, E nolofalitsoe, Boemo). Ho bohlokoa ho etsa li-index tse nepahetseng bakeng sa likhetho tse khethiloeng.

Tharollo ena ea utloahala ha u khetha ka mekhahlelo ena e fokotsa haholo sete e khutlisitsoeng ke 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, ho haella ha li-index ho ka bonoa ka lintlha tsa tlhahlobo ea meralo ea lipotso kapa pokello ea lipalo-palo tsa maikutlo a sistimi. Seva ea MS SQL:

Lipono tsohle tsa data li na le leseli mabapi le li-indices tse sieo, ntle le li-indices tsa sebaka.

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

Joalo ka ha mokhoa o thata oa bophelo o bontša, hangata ho bohlokoa hore likhoebo li kenye tšebetsong likarolo tsa khoebo ka linako tse itseng. 'Me ka hona, likopo tse boima hangata li fetisetsoa ho "background mode" 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 ho tsoa ho sebopeho sa mosebelisi.

Mokhoa ona o lumella ho rarolla litlhoko tsa khoebo, empa qetellong o fokotsa ts'ebetso ea tsamaiso ea tlhahisoleseding, feela ho chechisa tharollo ea mathata.

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

haeba li-index tse nang le likarolo tse tšoanang li se li le teng
haeba masimo a tafoleng a ke ke a ngolisoa ka lebaka la lithibelo tsa indexing (sena se hlalosoa ka botlalo mona).

2) Ho kopanya litšobotsi ho ba tšobotsi e le 'ngoe e ncha

Ka linako tse ling masimo a mang a tsoang tafoleng e le 'ngoe, moo sehlopha sa maemo se hlahang teng, se ka nkeloa sebaka ke ho hlahisa tšimo e le' ngoe e ncha.

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

Mohlala:

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

Mona, ho hlahisoa boemo bo felletseng ba Boemo, bo fanoang ka ho tlatsa maemo ana tafoleng. Joale, indexing ea tšobotsi ena e ncha ea etsoa.

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

3) Lintho tse bonahalang tsa boemeli

Ka bomalimabe, ho Lipotso tsa LINQ U ke ke ua sebelisa litafole tsa nakoana, li-CTE, le mefuta ea litafole 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 ts'oanang) e fetoha khetho e ntle ea ho li sebelisa ka pono e bonts'itsoeng, ho boloka selae se senyenyane sa data ho tsoa sehlopheng se seholo.

Empa ho na le mefokolo e mengata ha ho etsoa moemeli:

ho sebelisa subqueries, clauses E TSOANG lokela ho nkeloa sebaka ke tshebediso ya ETSA
u ke ke ua sebelisa litlhahiso UNION, KOPANO TSOHLE, KEKETSO, PHAKANE
Litlhahiso tsa tafole le litlhahiso li ke ke tsa sebelisoa KHETHO
ha ho na monyetla oa ho sebetsa ka li-cycle
ha ho khonehe ho hlahisa data ponong e le 'ngoe ho tsoa litafoleng tse fapaneng

Ke habohlokoa ho hopola hore molemo oa sebele oa ho sebelisa pono e thathamisitsoeng e ka fumanoa feela ka ho e bala hantle.

Empa ha o bitsa 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 o khone ho hlalosa lintlha tsa tafole, ka hona o tlameha ho theha setšoantšo se seng - "wrapper" ea mofuta o 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-block tse sebelisang maikutlo a nang le sebopeho se rarahaneng li hlahisa potso ea ho qetela e nang le sebopeho se rarahaneng haholo le se sa sebetseng hantle.

Melemo e ka sehloohong ea ho sebelisa mesebetsi ea tafole ho Lipotso tsa LINQ:

Bokhoni, joalo ka lipono, bo ka sebelisoang le ho hlalosoa e le ntho, empa ho ka fetisoa sete ea liparamente tsa ho kenya:
HO TSOA MOSEBETSI (@param1, @param2 ...)
Ka lebaka leo, sampole ea data e feto-fetohang e ka finyelloa
Ha u sebelisa ts'ebetso ea tafole, ha ho na lithibelo tse matla joalo ka maemong a lipono tse hlalositsoeng kaholimo:
1. Litlhahiso tsa tafole:
  ho pholletsa LINQ U ke ke ua hlakisa hore na ke li-index life tseo u ka li sebelisang kapa ua tseba boemo ba ho aroloa ha data ha u botsa.
  Empa ts'ebetso e na le lisebelisoa tsena.
  Ka ts'ebetso, o ka fihlela moralo oa ts'ebetso oa lipotso o sa feleng, 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:
  - mokhoa o rarahaneng oa ho khutlisa data (ho kenyeletsoa le ts'ebeliso ea lipotoloho)
  - likhetho tsa 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 ho tsosolosoa ho qobelloa ha 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 se senyenyane le se hlokahalang haholo sa data:
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 ho ntseng ho hlokahala ho sefa data ka parameter.
Ka mohlala, ho na le tafole e nang le sefe HOBANE'NG ho sebelisoa masimo a mararo (a, b, c).
Ho ea ka maemo, ho na le boemo bo sa feleng bakeng sa likopo tsohle a = 0 le b = 0.
Leha ho le joalo, potso ea ho ea tšimong c tse fapa-fapaneng.
Tlohela boemo a = 0 le b = 0 e re thusa ho fokotsa sephetho se hlokehang se behiloeng ho likete tsa lirekoto, empa maemo a teng с e fokotsa khetho ea rona ho fihlela lirekoto tse lekholo.
Mona mosebetsi oa tafole e ka ba khetho e ntle.
Hape, ts'ebetso ea tafole e tsebahala haholoanyane ebile e lula e le teng nakong ea ts'ebetso.

mehlala

Ha re nahaneng ka mohlala oa ts'ebetsong ka ho 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 lengolo-tsoibila le hlahlojoang (ka E TSOANG) ho ea ho "Lipotso tse sebetsang"([Lipotso tsa Ts'ebetso]):

Kopo #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 lihokelo tsa subquery mme e sebelisa ho hlopha. KHETHA, eo ka kakaretso e leng ts'ebetso e batlang e le matla haholo.

Mohlala oa Lipotso tsa Ts'ebetso tsa 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 ponong ea [OperativeQuestions], e lokelang ho fokotsa khetho ea tlhahiso bakeng sa [Email] = @p__linq__0 (ka E TSOANG) ho fihlela ho makholo a lirekoto.

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

Ebile, khokahano e latellanang ea litafole tsohle e etsahala: ka bobeli ho hlahlojoa mangolo a Id ea Lipotso ka Id ho tsoa ho Lipotso tsa Ts'ebetso, le ho sefa ka Imeile.

Ebile, potso e sebetsa le lirekoto tsohle tse mashome a likete tsa OperativeQuestions, empa ho hlokahala feela data ea thahasello ka Imeile.

Mongolo oa tlhahiso ea OperativeQuestions:

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

'Mapa oa Pele oa ho Sheba 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 mantlha 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 mekhoa ea motheo, 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.

Sepheo se seholo ke ho boloka lirekoto ka [Email] = @p__linq__0 ho tsoa ponong ea OperativeQuestions.

Re kenya tšebetso ea tafole [dbo].[OperativeQuestionsUserMail] polokelong ea boitsebiso.

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

Kopo #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

Mona ho khutlisetsoa tafole ea boleng bo nang le 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 lakatsehang e fetoloa ho Query 4:

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

Sheba le ho sebetsa '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");
    }
}
 
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 ts'ebetso e fokotsehile ho tloha 200-800 ms ho 2-20 ms, joalo-joalo, ke hore makhetlo a mashome ka potlako.

Haeba re nka mokhoa o tloaelehileng, 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,
ho fokotseha ho hoholo ha 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 likopo tsa database MS SQL ho pholletsa LINQ ke bothata bo ka rarolloang.

Mosebetsing ona, ho ela hloko le ho tsitsisa ho bohlokoa haholo.

Qalong ea ts'ebetso:

hoa hlokahala ho hlahloba lintlha tseo potso e sebetsang le tsona (litekanyetso, mefuta e khethiloeng ea data)
ho hlahisa lintlha tsena ka nepo
hlahloba ho nepahala ha maemo a ho kopanya pakeng tsa litafole

Phetolelo e latelang ea optimization e senola:

motheo oa potso le sefe e kholo ea potso e ikemiselitse
ho pheta-pheta li-block tsa lipotso tse tšoanang le mateano a litsela a hlahlojoa
ho SSMS kapa GUI e 'ngoe bakeng sa SQL Server e ntlafatsa ka boyona Potso ea SQL (khetho ea polokelo ea data e mahareng, kaho ea potso e hlahisoang ka ho sebelisa polokelo ena (ho ka ba le tse 'maloa))
mothating oa ho qetela, ho nka sephetho e le motheo Potso ea SQL, mohaho o ntse o tsosolosoa LINQ potso

Sephetho se hlahisoang LINQ potso e tlameha ho tšoana ka sebopeho le e bonts'itsoeng hantle 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