🥇Metoda pikeun ngaoptimalkeun pamundut LINQ dina C#.NET

perkenalan

В artikel ieu sababaraha métode optimasi dianggap patarosan LINQ.
Di dieu urang ogé nampilkeun sababaraha pendekatan leuwih kana optimasi kode patali patarosan LINQ.

Perlu dipikanyaho yén LINQ(Language-Integrated Query) nyaéta basa anu basajan tur merenah pikeun nanyakeun sumber data.

А LINQ kana SQL nyaéta téknologi pikeun ngakses data dina DBMS. Ieu mangrupikeun alat anu kuat pikeun ngerjakeun data, dimana patarosan diwangun ku basa déklaratif, anu teras bakal dirobih janten queries SQL platform na dikirim ka server database pikeun palaksanaan. Dina kasus urang, ku DBMS kami hartosna MS SQL Server.

Najan kitu, patarosan LINQ henteu dirobah jadi tulisan anu optimal queries SQL, nu hiji DBA ngalaman bisa nulis kalayan sagala nuances optimasi queries SQL:

sambungan optimal (gabung) jeung nyaring hasil (WHERE)
loba nuansa dina ngagunakeun sambungan jeung kaayaan grup
loba variasi dina ngaganti kaayaan IN dina AYEUNAи TEU DI, <> asup AYEUNA
cache panengah hasil via tabel samentara, CTE, variabel méja
ngagunakeun kalimah (pilihan) kalawan parentah jeung petunjuk tabel jeung (...)
ngagunakeun pintonan indéks salaku salah sahiji sarana pikeun nyabut bacaan data kaleuleuwihan salila selections

The bottlenecks kinerja utama hasilna queries SQL nalika nyusun patarosan LINQ nyaéta:

konsolidasi sakabéh mékanisme pamilihan data dina hiji pamundut
duplicating blok idéntik kode, nu pamustunganana ngabalukarkeun sababaraha maca data teu perlu
grup kaayaan multi-komponén (logis "jeung" jeung "atawa") - AND и OR, ngagabungkeun kana kaayaan kompléks, ngabalukarkeun kanyataan yén optimizer, ngabogaan indexes non-clustered cocog pikeun widang perlu, pamustunganana mimiti nyeken ngalawan indéks clustered (INDEX SCAN) ku kelompok kaayaan
nyarang jero subqueries ngajadikeun parsing pisan masalah pernyataan SQL jeung analisis rencana query dina bagian tina pamekar jeung DBA

Métode optimasi

Ayeuna hayu urang ngalih langsung ka metode optimasi.

1) indexing tambahan

Hadé pisan mun éta mertimbangkeun saringan dina tabel Pilihan utama, saprak pisan mindeng sakabéh query diwangun sabudeureun hiji atawa dua tabel utama (aplikasi-jalma-operasi) sarta kalawan susunan standar kaayaan (IsClosed, Dibolaykeun, Aktipkeun, Status). Penting pikeun nyiptakeun indéks anu pas pikeun sampel anu diidentifikasi.

Solusi ieu asup akal nalika milih widang ieu sacara signifikan ngawatesan set anu dipulangkeun kana pamundut.

Contona, urang boga 500000 aplikasi. Tapi, ngan aya 2000 aplikasi aktip. Lajeng indéks dipilih leres bakal nyalametkeun urang tina INDEX SCAN dina tabel badag tur bakal ngidinan Anjeun pikeun gancang milih data ngaliwatan indéks non-clustered.

Ogé, kurangna indéks bisa diidentifikasi ngaliwatan prompts pikeun parsing rencana query atawa ngumpulkeun statistik view sistem. MS SQL Server:

Sadaya data pintonan ngandung émbaran ngeunaan indéks leungit, iwal indéks spasial.

Nanging, indéks sareng cache sering mangrupikeun metode pikeun merangan akibat tina tulisan anu kirang patarosan LINQ и queries SQL.

Salaku prakték kasar tina kahirupan nembongkeun, éta mindeng penting pikeun bisnis pikeun nerapkeun fitur bisnis ku deadlines tangtu. Ku alatan éta, requests beurat mindeng dibikeun ka tukang jeung cache.

Ieu sabagean diyakinkeun, saprak pamaké teu salawasna butuh data panganyarna na aya hiji tingkat ditarima tina responsiveness tina panganteur pamaké.

Pendekatan ieu ngamungkinkeun ngarengsekeun kabutuhan bisnis, tapi pamustunganana ngirangan kinerja sistem inpormasi ku ngan ukur ngalambatkeun solusi pikeun masalah.

Éta ogé sia remembering yén dina prosés néangan indexes perlu pikeun nambahkeun, saran MS SQL optimasi tiasa lepat, kalebet dina kaayaan ieu:

upami tos aya indéks kalayan sakumpulan widang anu sami
lamun widang dina tabél teu bisa indéks alatan larangan indexing (dijelaskeun dina leuwih jéntré di dieu).

2) Ngahijikeun atribut jadi hiji atribut anyar

Kadang-kadang sababaraha widang tina hiji méja, nu ngawula ka salaku dadasar pikeun grup kaayaan, bisa diganti ku ngawanohkeun hiji widang anyar.

Ieu hususna leres pikeun widang status, nu biasana boh bit atawa integer dina tipe.

contona:

IsClosed = 0 AND Dibolaykeun = 0 AND Diaktipkeun = 0 diganti ku Status = 1.

Ieu dimana atribut Status integer diwanohkeun pikeun mastikeun yén statuses ieu Asezare populata dina tabél. Salajengna, atribut anyar ieu indéks.

Ieu mangrupikeun solusi dasar pikeun masalah kinerja, sabab Urang ngaksés data tanpa itungan anu teu perlu.

3) Materialisasi pintonan

Hanjakal di patarosan LINQ Tabel samentara, CTEs, sarta variabel tabel teu bisa dipaké langsung.

Sanajan kitu, aya cara séjén pikeun ngaoptimalkeun pikeun hal ieu - indexed views.

Grup kaayaan (tina conto di luhur) IsClosed = 0 AND Dibolaykeun = 0 AND Diaktipkeun = 0 (atanapi sakumpulan kaayaan anu sanés anu sami) janten pilihan anu saé pikeun dianggo dina tampilan anu indéks, cache nyiksikan data leutik tina set ageung.

Tapi aya sababaraha larangan nalika ngawujudkeun pandangan:

pamakéan subqueries, klausa AYEUNA kudu diganti ku ngagunakeun gabung
anjeun teu bisa ngagunakeun kalimah ngahijikeun, UNION KABEH, PENGECUALIAN, PANUTUP
Anjeun teu tiasa nganggo petunjuk tabel sareng klausa pilihan
euweuh kamungkinan pikeun digawe sareng siklus
Teu mungkin pikeun mintonkeun data dina hiji pintonan ti tabel béda

Penting pikeun émut yén kauntungan nyata ngagunakeun pandangan anu diindéks ngan ukur tiasa dimeunangkeun ku ngindeks éta.

Tapi lamun nelepon view a, indéks ieu bisa jadi teu dipaké, sarta ngagunakeun éta eksplisit, anjeun kudu nangtukeun JEUNG (NOEXPAND).

Kusabab di patarosan LINQ Teu mungkin pikeun ngartikeun petunjuk méja, janten anjeun kedah nyiptakeun perwakilan anu sanés - "wrapper" tina bentuk ieu:

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

4) Ngagunakeun fungsi tabel

Sering di patarosan LINQ Blok badag tina subqueries atawa blok ngagunakeun pintonan kalawan struktur kompléks ngabentuk query ahir kalawan struktur palaksanaan pisan kompléks jeung suboptimal.

Mangpaat Utama Ngagunakeun Fungsi Méja di patarosan LINQ:

Kamampuhan, sapertos dina kasus pintonan, dianggo sareng disaluyukeun salaku obyék, tapi anjeun tiasa ngalangkungan sakumpulan parameter input:
TI FUNGSI (@param1, @param2 ...)
Hasilna, sampling data anu fleksibel tiasa dihontal
Dina hal ngagunakeun fungsi méja, teu aya larangan anu kuat sapertos dina kasus tampilan indéks anu dijelaskeun di luhur:
1. Pitunjuk tabel:
  через LINQ Anjeun teu bisa nangtukeun indéks mana nu kudu dipake jeung nangtukeun tingkat isolasi data nalika querying.
  Tapi fungsina ngagaduhan kamampuan ieu.
  Kalayan fungsina, anjeun tiasa ngahontal rencana query palaksanaan anu cukup konstan, dimana aturan pikeun damel sareng indéks sareng tingkat isolasi data ditetepkeun.
2. Ngagunakeun fungsi ngamungkinkeun, dibandingkeun jeung pintonan indéks, pikeun ménta:
  - logika sampling data kompléks (sanajan ngagunakeun loop)
  - fetching data tina loba tabel béda
  - pamakean ngahijikeun и AYEUNA
Kurban pilihan pohara kapaké lamun urang kudu nyadiakeun kontrol concurrency PILIHAN (MAXDOP N), urutan rencana palaksanaan query. Salaku conto:
- Anjeun tiasa nangtukeun hiji kapaksa ulang nyieun rencana query PILIHAN (RECOMPILE)
- Anjeun tiasa nangtukeun naha rék maksa rencana query ngagunakeun urutan gabung dieusian dina query PILIHAN (PAKSA ORDER)
Leuwih jéntré ngeunaan pilihan digambarkeun di dieu.
Nganggo potongan data anu paling heureut sareng paling diperyogikeun:
Teu perlu nyimpen set data badag dina caches (sakumaha dina kasus kalawan pintonan indéks), ti mana anjeun masih kudu nyaring data ku parameter.
Contona, aya hiji méja nu filter WHERE tilu widang dipaké (a, b, c).

Conventionally, sadaya requests boga kaayaan konstan a = 0 jeung b = 0.

Sanajan kitu, paménta pikeun sawah c leuwih variabel.

Hayu kaayaan a = 0 jeung b = 0 Bener mantuan kami pikeun ngawatesan set hasilna diperlukeun pikeun rébuan rékaman, tapi kaayaan on с ngahususkeun pilihan ka saratus rékaman.

Di dieu fungsi tabel bisa jadi pilihan hadé.

Ogé, fungsi tabel langkung tiasa diprediksi sareng konsisten dina waktos palaksanaan.

conto

Hayu urang tingali conto palaksanaan ngagunakeun database Patarosan salaku conto.

Aya pamundut MILIH, nu ngagabungkeun sababaraha tabel sarta ngagunakeun hiji pintonan (OperativeQuestions), dimana afiliasi dipariksa ku email (via AYEUNA) kana "Patarosan Operasi":

Paménta 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])
));

Pintonan ngagaduhan struktur anu rada kompleks: éta ngandung gabungan subqueries sareng ngagunakeun asihan DISTINCT, nu sacara umum mangrupakeun operasi cukup sumberdaya-intensif.

Sampel tina OperativeQuestions sakitar sapuluh rébu rékaman.

Masalah utama query ieu nya éta pikeun rékaman tina query luar, hiji subquery internal dieksekusi dina [OperativeQuestions] view, nu kedah pikeun [Email] = @p__linq__0 urang ngawatesan pilihan kaluaran (via AYEUNA) nepi ka ratusan rékaman.

Sareng sigana yén subquery kedah ngitung rékaman sakali ku [Email] = @p__linq__0, teras sababaraha ratus rékaman ieu kedah dihubungkeun ku Id sareng Patarosan, sareng pamundutna bakal gancang.

Kanyataanna, aya sambungan sequential sadaya tabel: mariksa korespondensi Id Patarosan jeung Id ti OperativeQuestions, sarta nyaring ku Email.

Nyatana, pamundut tiasa dianggo sareng sadaya puluhan rébu rékaman OperativeQuestions, tapi ngan ukur data anu dipikaresep ku Email.

OperativeQuestions ningali téks:

Paménta 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));

Pemetaan pintonan awal dina 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");
    }
}

Paménta LINQ awal

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

Dina hal husus ieu, urang tempo solusi pikeun masalah ieu tanpa parobahan infrastruktur, tanpa ngenalkeun tabel misah kalawan hasil siap-dijieun ("Queries Aktif"), nu ngabutuhkeun mékanisme pikeun ngeusian ku data sarta tetep up to date. .

Sanajan ieu téh leyuran alus, aya pilihan séjén pikeun ngaoptimalkeun masalah ieu.

Tujuan utama nyaéta pikeun nyimpen éntri ku [Email] = @p__linq__0 tina tampilan OperativeQuestions.

Ngawanohkeun fungsi tabel [dbo].[OperativeQuestionsUserMail] kana database.

Ku ngirim Email salaku parameter input, urang meunang deui tabel nilai:

Paménta 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

Ieu mulihkeun tabel nilai sareng struktur data anu tos siap.

Supados patarosan ka OperativeQuestionsUserMail janten optimal sareng gaduh rencana pamundut anu optimal, struktur anu ketat diperyogikeun, sareng henteu. Ngabalikeun tabel AS balik...

Dina hal ieu, Query 1 anu diperyogikeun dirobih janten Query 4:

Paménta 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]);

Mapping pintonan sareng fungsi dina 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})");
}

Paménta LINQ pamungkas

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

Urutan waktos palaksanaan parantos turun tina 200-800 ms, janten 2-20 ms, jsb., nyaéta puluhan kali langkung gancang.

Lamun urang nyandak eta leuwih averagely, lajeng tinimbang 350 mdet urang meunang 8 mdet.

Tina kaunggulan anu jelas urang ogé nampi:

pangurangan umum dina beban bacaan,
réduksi signifikan dina likelihood blocking
ngurangan rata waktos blocking kana nilai ditarima

kacindekan

Optimasi sareng fine-tuning telepon database MS SQL через LINQ mangrupa masalah anu bisa direngsekeun.

Attentiveness jeung konsistensi pohara penting dina karya ieu.

Dina awal prosés:

perlu mariksa data anu dipénta jalanna (nilai, jinis data anu dipilih)
ngalaksanakeun indexing ditangtoskeun tina data ieu
pariksa correctness kaayaan gabung antara tabel

Iteration optimasi salajengna nembongkeun:

dasar pamundut jeung nangtukeun filter pamundut utama
ngulang blok query sarupa jeung nganalisis simpang kaayaan
di SSMS atanapi GUI séjén pikeun SQL Server optimizes sorangan pamundut SQL (alokasi panyimpen data perantara, ngawangun query anu dihasilkeun ngagunakeun panyimpenan ieu (bisa aya sababaraha))
dina tahap panungtungan, nyokot salaku dadasar hasilna pamundut SQL, struktur keur diwangun deui patarosan LINQ

Hasilna patarosan LINQ kudu jadi idéntik dina struktur ka optimal dicirikeun pamundut SQL ti titik 3.

Ngahaturkeun

Hatur nuhun pisan ka kolega jobgemws и alex_ozr ti pausahaan Fortis pikeun pitulung dina nyiapkeun bahan ieu.

sumber: www.habr.com

Métode pikeun ngaoptimalkeun pamundut LINQ dina C #.NET