🥇Metòd pou optimize demann LINQ nan C#.NET

Entwodiksyon

В atik sa a kèk metòd optimize yo te konsidere Rekèt LINQ.
Isit la nou tou prezante kèk plis apwòch nan optimize kòd ki gen rapò ak Rekèt LINQ.

Li konnen sa LINQ(Language-Integrated Query) se yon lang ki senp epi pratik pou demann yon sous done.

А LINQ pou SQL se yon teknoloji pou jwenn aksè nan done nan yon DBMS. Sa a se yon zouti pwisan pou travay ak done, kote demann yo konstwi atravè yon langaj deklaratif, ki pral Lè sa a, konvèti nan Rekèt SQL platfòm epi voye nan sèvè baz done a pou ekzekisyon. Nan ka nou an, pa DBMS nou vle di MS SQL sèvè.

Sepandan, Rekèt LINQ yo pa konvèti nan sa ki pi byen ekri Rekèt SQL, ki yon DBA ki gen eksperyans ta ka ekri ak tout nuans yo nan optimize Rekèt SQL:

koneksyon optimal (JOIN) ak filtraj rezilta yo (KI KOTE)
anpil nuans nan lè l sèvi avèk koneksyon ak kondisyon gwoup
anpil varyasyon nan ranplase kondisyon yo IN sou EGZISTEи PA NAN, <> sou EGZISTE
kachèt entèmedyè rezilta atravè tab tanporè, CTE, varyab tab
itilizasyon fraz (OPSYON) ak enstriksyon ak sijesyon tab aK (...)
lè l sèvi avèk endèks opinyon kòm youn nan mwayen yo debarase m de lekti done redondants pandan seleksyon yo

Pwensipal blokaj pèfòmans nan rezilta a Rekèt SQL lè konpile Rekèt LINQ yo se:

konsolidasyon tout mekanis seleksyon done nan yon sèl demann
diplike blòk ki idantik nan kòd, ki finalman mennen nan plizyè done ki pa nesesè li
gwoup kondisyon milti-konpozan (lojik "ak" ak "oswa") - AK и OR, konbine nan kondisyon konplèks, mennen nan lefèt ke optimiseur a, ki gen endis apwopriye ki pa grap pou jaden ki nesesè yo, finalman kòmanse eskane kont endèks la grap (ENDEX SCAN) pa gwoup kondisyon
gwo twou san fon nidifikasyon nan subqueries fè analiz trè pwoblèm Deklarasyon SQL ak analiz de plan rechèch la sou pati nan devlopè ak DBA

Metòd optimize

Koulye a, kite a deplase dirèkteman nan metòd optimize.

1) Indexing adisyonèl

Li pi bon pou konsidere filtè sou tab seleksyon prensipal yo, paske trè souvan tout rechèch la bati ozalantou youn oubyen de tab prensipal (aplikasyon-moun-operasyon) ak yon seri kondisyon estanda (Fèmen, Anile, Pèmèt, Estati). Li enpòtan pou kreye endis apwopriye pou echantiyon yo idantifye yo.

Solisyon sa a fè sans lè w ap chwazi jaden sa yo siyifikativman limite seri a retounen nan rechèch la.

Pa egzanp, nou gen 500000 aplikasyon. Sepandan, gen sèlman 2000 aplikasyon aktif. Lè sa a, yon endèks kòrèkteman chwazi pral sove nou soti nan ENDEX SCAN sou yon tab gwo epi yo pral pèmèt ou byen vit chwazi done atravè yon endèks ki pa grap.

Epitou, yo ka idantifye mank de endèks atravè envit pou analize plan rechèch oswa kolekte estatistik wè sistèm. MS SQL sèvè:

Tout done gade yo genyen enfòmasyon sou endèks ki manke yo, eksepte endèks espasyal yo.

Sepandan, endèks ak kachèt yo souvan metòd pou konbat konsekans mal ekri Rekèt LINQ и Rekèt SQL.

Kòm pratik ki piman bouk nan lavi montre, li souvan enpòtan pou yon biznis aplike karakteristik biznis nan sèten dat limit. Se poutèt sa, demann lou yo souvan transfere nan background nan ak kachèt.

Sa a se an pati jistifye, depi itilizatè a pa toujou bezwen dènye done yo epi gen yon nivo akseptab nan reyaksyon nan koòdone itilizatè a.

Apwòch sa a pèmèt rezoud bezwen biznis, men finalman diminye pèfòmans nan sistèm enfòmasyon an pa senpleman retade solisyon a pwoblèm.

Li se tou vo sonje ke nan pwosesis pou chèche endis ki nesesè yo ajoute, sijesyon MS SQL optimize ka kòrèk, ki gen ladan nan kondisyon sa yo:

si gen deja endèks ak yon seri jaden menm jan an
si jaden yo nan tablo a pa ka endis akòz restriksyon endis (ki dekri an plis detay isit la).

2) Fusion atribi nan yon nouvo atribi

Pafwa kèk jaden ki soti nan yon tab, ki sèvi kòm yon baz pou yon gwoup kondisyon, ka ranplase pa entwodwi yon nouvo jaden.

Sa a se laverite espesyalman pou jaden estati, ki anjeneral swa ti jan oswa nonb antye relatif nan kalite.

Egzanp:

IsClosed = 0 AK Anile = 0 AK Aktivite = 0 se ranplase pa Estati = 1.

Sa a se kote atribi Estati nonb antye relatif la prezante pou asire ke estati sa yo peple nan tablo a. Apre sa, nouvo atribi sa a endis.

Sa a se yon solisyon fondamantal nan pwoblèm nan pèfòmans, paske Nou jwenn aksè nan done san yo pa kalkil nesesè.

3) materyalizasyon vi a

Malerezman, nan Rekèt LINQ Tablo tanporè, CTE, ak varyab tab yo pa ka itilize dirèkteman.

Sepandan, gen yon lòt fason yo optimize pou ka sa a - opinyon endis.

Gwoup kondisyon (nan egzanp ki anwo a) IsClosed = 0 AK Anile = 0 AK Aktivite = 0 (oswa yon seri lòt kondisyon ki sanble) vin tounen yon bon opsyon pou sèvi ak yo nan yon View endis, kachèt yon ti tranch done ki sòti nan yon seri gwo.

Men, gen yon kantite restriksyon lè konkretize yon vi:

itilizasyon subqueries, paragraf EGZISTE ta dwe ranplase pa itilize JOIN
ou pa ka sèvi ak fraz UNION, INYON TOUT, Eksepsyon, ENSÈKS
Ou pa kapab sèvi ak sijesyon tab ak paraz yo OPSYON
pa gen posiblite pou travay ak sik
Li enposib pou montre done nan yon sèl gade nan tab diferan

Li enpòtan sonje ke benefis reyèl la nan lè l sèvi avèk yon View endis ka sèlman reyalize pa aktyèlman endèks li.

Men, lè w ap rele yon vi, endis sa yo pa ka itilize, epi pou itilize yo klèman, ou dwe presize AK(NOEXPAND).

Depi nan Rekèt LINQ Li enposib pou defini sijesyon tab, kidonk ou dwe kreye yon lòt reprezantasyon - yon "anbalaj" nan fòm sa a:

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

4) Sèvi ak fonksyon tab

Souvan nan Rekèt LINQ Gwo blòk nan subqueries oswa blòk lè l sèvi avèk opinyon ak yon estrikti konplèks fòme yon rechèch final ak yon estrikti ekzekisyon trè konplèks ak pa pi bon.

Avantaj kle nan Sèvi ak Fonksyon Tablo nan Rekèt LINQ:

Kapasite a, tankou nan ka a nan opinyon, yo dwe itilize ak espesifye kòm yon objè, men ou ka pase yon seri paramèt opinyon:
FROM FUNCTION(@param1, @param2...)
Kòm yon rezilta, echantiyon done fleksib ka reyalize
Nan ka itilize yon fonksyon tab, pa gen okenn restriksyon fò tankou nan ka a nan endis opinyon ki dekri pi wo a:
1. Sijesyon tab la:
  nan LINQ Ou pa ka presize ki endèks yo ta dwe itilize epi detèmine nivo izolasyon done yo lè w ap fè demann.
  Men, fonksyon an gen kapasite sa yo.
  Avèk fonksyon an, ou ka reyalize yon plan demann ekzekisyon san patipri konstan, kote règ pou travay ak endèks ak nivo izolasyon done yo defini.
2. Sèvi ak fonksyon an pèmèt, an konparezon ak opinyon endis, jwenn:
  - lojik echantiyon done konplèks (menm lè l sèvi avèk bouk)
  - chache done ki sòti nan plizyè tab diferan
  - itilize nan UNION и EGZISTE
Ofri OPSYON trè itil lè nou bezwen bay kontwòl konkou OPTION (MAXDOP N), lòd plan ekzekisyon demann lan. Pa egzanp:
- ou ka presize yon fòse re-kreyasyon nan plan rechèch la OPTION (RECOMPILE)
- ou ka presize si wi ou non pou fòse plan rechèch la pou sèvi ak lòd rantre ki espesifye nan rechèch la OPTION (FÒSE LÒD)
Plis detay sou OPSYON dekri isit la.
Sèvi ak tranch done ki pi etwat ak pi obligatwa:
Pa gen okenn nesesite pou estoke seri done gwo nan kachèt (tankou se ka a ak opinyon endis), ki soti nan ki ou toujou bezwen filtre done yo pa paramèt.
Pou egzanp, gen yon tab ki gen filtre KI KOTE twa jaden yo itilize (a, b, c).

Konvansyonèl, tout demann gen yon kondisyon konstan a = 0 ak b = 0.

Sepandan, demann lan pou jaden an c plis varyab.

Kite kondisyon an a = 0 ak b = 0 Li vrèman ede nou limite seri ki nesesè yo ki kapab lakòz a dè milye de dosye, men kondisyon an sou с limite seleksyon an desann nan yon santèn dosye.

Isit la fonksyon tab la ka yon pi bon opsyon.

Epitou, yon fonksyon tab se pi previzib ak konsistan nan tan ekzekisyon.

egzanp

Ann gade nan yon egzanp aplikasyon lè l sèvi avèk baz done Kesyon an kòm yon egzanp.

Gen yon demann Chwazi, ki konbine plizyè tab epi sèvi ak yon sèl vi (OperativeQuestions), kote yo tcheke afilyasyon pa imèl (via EGZISTE) nan "Kesyon Operatif":

Demann nimewo 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])
));

View a gen yon estrikti olye konplèks: li gen subquery rantre epi sèvi ak klasman distenk, ki an jeneral se yon operasyon jistis resous-entansif.

Yon echantiyon ki soti nan OperativeQuestions se apeprè dis mil dosye.

Pwoblèm prensipal ak rechèch sa a se ke pou dosye yo soti nan rechèch la deyò, yo egzekite yon subquery entèn sou gade nan [OperativeQuestions], ki ta dwe pou [Imèl] = @p__linq__0 pèmèt nou limite seleksyon pwodiksyon an (via EGZISTE) jiska dè santèn de dosye.

Epi li ka sanble ke subquery a ta dwe kalkile dosye yo yon fwa pa [Imèl] = @p__linq__0, ak Lè sa a, koup la san dosye sa yo ta dwe konekte pa Id ak Kesyon, ak rechèch la pral rapid.

An reyalite, gen yon koneksyon sekans nan tout tab yo: tcheke korespondans nan Kesyon Id ak Id soti nan OperativeQuestions, ak filtraj pa Imèl.

An reyalite, demann lan ap travay ak tout dizèn de milye dosye OperativeQuestions, men se sèlman done ki enterese yo bezwen atravè Imèl.

OperativeQuestions gade tèks:

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

Map View inisyal nan 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");
    }
}

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

Nan ka sa a an patikilye, nou ap konsidere yon solisyon a pwoblèm sa a san chanjman enfrastrikti, san yo pa entwodwi yon tablo separe ak rezilta pare-fè ("Rekèt aktif"), ki ta mande yon mekanis pou ranpli li ak done ak kenbe li ajou. .

Malgre ke sa a se yon bon solisyon, gen yon lòt opsyon pou optimize pwoblèm sa a.

Objektif prensipal la se antre nan kachèt pa [Imèl] = @p__linq__0 nan View OperativeQuestions.

Entwodwi fonksyon tab [dbo].[OperativeQuestionsUserMail] nan baz done a.

Lè nou voye Imèl kòm yon paramèt opinyon, nou jwenn tounen yon tablo valè:

Demann nimewo 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

Sa a retounen yon tablo valè ak yon estrikti done predefini.

Nan lòd pou demann nan OperativeQuestionsUserMail yo dwe pi bon epi yo gen plan rechèch pi bon, yo mande yon estrikti strik, epi yo pa RETOU TABLE KÒM RETOUR...

Nan ka sa a, Rekèt 1 ki nesesè yo konvèti nan Rekèt 4:

Demann nimewo 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]);

Katyografi opinyon ak fonksyon nan 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})");
}

Final rechèch 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();

Lòd tan ekzekisyon an te tonbe soti nan 200-800 ms, nan 2-20 ms, elatriye, sa vle di dè dizèn de fwa pi vit.

Si nou pran li plis mwayèn, Lè sa a, olye pou yo 350 ms nou te resevwa 8 ms.

Soti nan avantaj evidan nou jwenn tou:

rediksyon jeneral nan chaj lekti,
rediksyon enpòtan nan chans pou bloke
diminye tan an mwayèn bloke nan valè akseptab

Sòti

Optimizasyon ak ajisteman nan baz done apèl yo MS SQL nan LINQ se yon pwoblèm ki ka rezoud.

Atansyon ak konsistans yo trè enpòtan nan travay sa a.

Nan kòmansman pwosesis la:

li nesesè tcheke done yo ak ki demann lan ap travay (valè, kalite done chwazi)
fè bon jan endèks done sa yo
tcheke kòrèkteman nan rantre nan kondisyon ant tab yo

Pwochen iterasyon optimize a revele:

baz demann lan epi li defini filtè demann prensipal la
repete blòk rechèch ki sanble ak analize entèseksyon kondisyon yo
nan SSMS oswa lòt entèfas pou SQL sèvè optimize tèt li Rekèt SQL (Alocasyon yon depo done entèmedyè, bati rechèch la ki kapab lakòz lè l sèvi avèk depo sa a (kapab genyen plizyè))
nan dènye etap la, pran kòm yon baz rezilta a Rekèt SQL, estrikti a ap rebati Rekèt LINQ

Rezilta a Rekèt LINQ ta dwe vin idantik nan estrikti ak pi bon an idantifye Rekèt SQL soti nan pwen 3.

Remèsiman

Mèsi anpil ak kòlèg yo jobgemws и alex_ozr soti nan konpayi an Fortis pou asistans nan prepare materyèl sa a.

Sous: www.habr.com

Metòd pou optimize demann LINQ nan C#.NET