🥇Cara kanggo ngoptimalake pitakon LINQ ing C#.NET

Pambuka

В artikel iki sawetara cara optimasi dianggep pitakon LINQ.
Ing kene kita uga nampilake sawetara pendekatan liyane kanggo optimasi kode sing ana gandhengane pitakon LINQ.

Dikenal yen Linq(Language-Integrated Query) minangka basa sing prasaja lan trep kanggo takon sumber data.

А LINQ menyang SQL minangka teknologi kanggo ngakses data ing DBMS. Iki minangka alat sing kuat kanggo nggarap data, ing ngendi pitakon digawe liwat basa deklaratif, sing banjur bakal diowahi dadi pitakon SQL platform lan dikirim menyang server database kanggo eksekusi. Ing kasus kita, kanthi DBMS tegese Server MS SQL.

Nanging, pitakon LINQ ora diowahi dadi tulisan kanthi optimal pitakon SQL, sing DBA sing berpengalaman bisa nulis kanthi kabeh nuansa optimasi pitakon SQL:

koneksi optimal (NGGABUNGA) lan nyaring asil (ngendi)
akeh nuansa ing nggunakake sambungan lan kahanan klompok
akeh variasi ing ngganti kahanan IN ing Anaи ORA IN, <> ing Ana
caching penengah asil liwat tabel sauntara, CTE, variabel Tabel
nggunakake ukara (OPTION) kanthi instruksi lan pitunjuk meja Kanthi (...)
nggunakake tampilan sing diindeks minangka salah sawijining cara kanggo nyingkirake maca data sing berlebihan sajrone pilihan

Bottlenecks kinerja utama asil pitakon SQL nalika nyusun pitakon LINQ yaiku:

konsolidasi kabeh mekanisme pilihan data ing siji panyuwunan
duplikat pamblokiran identik kode, kang wekasanipun ndadékaké kanggo macem-macem data rasah diwaca
klompok kondisi multi-komponen (logis "lan" lan "utawa") - lan и OR, nggabungke menyang kahanan Komplek, ndadékaké kanggo kasunyatan sing optimizer, gadhah indeks non-clustered cocok kanggo kothak perlu, wekasanipun wiwit mindai marang indeks clustered (INDEKS SCAN) miturut kelompok kahanan
nesting jero subqueries ndadekake parsing banget masalah statement SQL lan analisis rencana query ing bagean pangembang lan DBA

Cara ngoptimalake

Saiki ayo pindhah langsung menyang cara optimasi.

1) indeksasi tambahan

Iku paling apik kanggo nimbang saringan ing tabel pilihan utama, amarga asring banget kabeh pitakonan dibangun watara siji utawa loro tabel utama (aplikasi-wong-operasi) lan karo pesawat standar kahanan (IsClosed, Batal, Aktif, Status). Penting kanggo nggawe indeks sing cocog kanggo conto sing diidentifikasi.

Solusi iki bisa dimangerteni nalika milih kolom kasebut kanthi signifikan mbatesi set bali menyang pitakon.

Contone, kita duwe 500000 aplikasi. Nanging, mung ana 2000 aplikasi aktif. Banjur indeks sing dipilih kanthi bener bakal nylametake kita INDEKS SCAN ing meja gedhe lan bakal ngidini sampeyan milih data kanthi cepet liwat indeks non-clustered.

Uga, kekurangan indeks bisa diidentifikasi liwat pituduh kanggo parsing rencana pitakon utawa ngumpulake statistik tampilan sistem Server MS SQL:

Kabeh data tampilan ngemot informasi babagan indeks sing ilang, kajaba indeks spasial.

Nanging, indeks lan caching asring cara kanggo nglawan akibat saka tulisan sing ora apik pitakon LINQ и pitakon SQL.

Minangka laku atos gesang nuduhake, iku asring penting kanggo bisnis kanggo ngleksanakake fitur bisnis dening tenggat wektu tartamtu. Mulane, panjalukan sing abot asring ditransfer menyang latar mburi kanthi caching.

Iki sebagian dibenerake, amarga pangguna ora mbutuhake data paling anyar lan ana tingkat responsif antarmuka pangguna sing bisa ditampa.

Pendekatan iki ngidini ngrampungake kabutuhan bisnis, nanging pungkasane nyuda kinerja sistem informasi kanthi mung nundha solusi kanggo masalah.

Sampeyan uga kudu eling yen ing proses nggoleki indeks sing perlu ditambahake, saran MS SQL optimasi bisa uga salah, kalebu ing kahanan ing ngisor iki:

yen wis ana indeks kanthi set lapangan sing padha
yen kolom ing tabel ora bisa diindeks amarga watesan indeksasi (diterangake kanthi luwih rinci kene).

2) Nggabungake atribut dadi siji atribut anyar

Kadhangkala sawetara lapangan saka siji meja, sing dadi basis kanggo klompok kondisi, bisa diganti kanthi ngenalake siji lapangan anyar.

Iki utamané bener kanggo kolom status, kang biasane salah siji bit utawa integer ing jinis.

Conto:

IsClosed = 0 LAN Dibatalkan = 0 AND Enabled = 0 diganti dening Status = 1.

Iki ngendi atribut Status integer dienalake kanggo mesthekake yen status kasebut diisi ing tabel. Sabanjure, atribut anyar iki diindeks.

Iki minangka solusi dhasar kanggo masalah kinerja, amarga Kita ngakses data tanpa petungan sing ora perlu.

3) Materialisasi tampilan

Sayange ing pitakon LINQ Tabel sementara, CTE, lan variabel tabel ora bisa digunakake langsung.

Nanging, ana cara liya kanggo ngoptimalake kasus iki - tampilan sing diindeks.

Klompok kahanan (saka conto ing ndhuwur) IsClosed = 0 LAN Dibatalkan = 0 AND Enabled = 0 (utawa sakumpulan kahanan liyane sing padha) dadi pilihan sing apik kanggo nggunakake ing tampilan sing diindeks, nyimpen irisan data cilik saka sakumpulan gedhe.

Nanging ana sawetara watesan nalika nggawe tampilan:

nggunakake subqueries, klausa Ana kudu diganti nganggo NGGABUNGA
sampeyan ora bisa nggunakake ukara UNION, UNION KABEH, PENGECUALIAN, INTERSEKSI
Sampeyan ora bisa nggunakake pitunjuk lan klausa tabel OPTION
ora ana kamungkinan kanggo bisa karo siklus
Ora bisa nampilake data ing siji tampilan saka tabel sing beda

Penting kanggo elinga yen entuk manfaat nyata nggunakake tampilan sing diindeks mung bisa digayuh kanthi bener ngindeks.

Nanging nalika nelpon tampilan, indeks kasebut bisa uga ora digunakake, lan kanggo nggunakake kanthi eksplisit, sampeyan kudu nemtokake KARO (NOEXPAND).

Wiwit ing pitakon LINQ Ora mungkin kanggo nemtokake pitunjuk tabel, mula sampeyan kudu nggawe perwakilan liyane - "pembungkus" saka formulir ing ngisor iki:

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

4) Nggunakake fungsi tabel

Asring ing pitakon LINQ Blok subkueri utawa blok gedhe sing nggunakake tampilan kanthi struktur kompleks mbentuk pitakon pungkasan kanthi struktur eksekusi sing kompleks lan suboptimal.

Keuntungan Utama Nggunakake Fungsi Tabel ing pitakon LINQ:

Kemampuan, kaya ing kasus tampilan, digunakake lan ditemtokake minangka obyek, nanging sampeyan bisa ngliwati set paramèter input:
FROM FUNGSI (@param1, @param2 ...)
Akibaté, sampling data sing fleksibel bisa digayuh
Ing kasus nggunakake fungsi tabel, ora ana watesan sing kuat kaya ing kasus tampilan sing diindeks sing diterangake ing ndhuwur:
1. Petunjuk tabel:
  liwat Linq Sampeyan ora bisa nemtokake indeks sing kudu digunakake lan nemtokake tingkat isolasi data nalika takon.
  Nanging fungsi kasebut nduweni kemampuan kasebut.
  Kanthi fungsi kasebut, sampeyan bisa entuk rencana query eksekusi sing cukup konstan, ing ngendi aturan kanggo nggarap indeks lan tingkat isolasi data ditetepake.
2. Nggunakake fungsi kasebut ngidini, dibandhingake karo tampilan sing diindeks, entuk:
  - logika sampling data kompleks (sanajan nggunakake loops)
  - njupuk data saka macem-macem tabel
  - nggunakake UNION и Ana
Nawakake OPTION banget migunani nalika kita kudu nyedhiyani kontrol concurrency PILIHAN (MAXDOP N), urutan rencana eksekusi pitakon. Tuladhane:
- sampeyan bisa nemtokake nggawe maneh paksa rencana query PILIHAN (RECOMPILE)
- sampeyan bisa nemtokake manawa arep meksa rencana pitakon nggunakake urutan gabung sing ditemtokake ing pitakon PILIHAN (FORCE ORDER)
Rincian liyane babagan OPTION diterangake kene.
Nggunakake irisan data sing paling sempit lan paling dibutuhake:
Ora perlu nyimpen set data gedhe ing cache (kaya kasus karo tampilan sing diindeks), saka ngendi sampeyan isih kudu nyaring data miturut parameter.
Contone, ana meja sing saringan ngendi telung lapangan digunakake (a,b,c).

Conventionally, kabeh panjalukan duwe kondisi pancet a = 0 lan b = 0.

Nanging, panjalukan kanggo lapangan c liyane variabel.

Ayo kahanan a = 0 lan b = 0 Iku pancene mbantu kita kanggo matesi set asil dibutuhake kanggo ewu cathetan, nanging kondisi ing с narrows pilihan mudhun kanggo satus cathetan.

Ing kene fungsi tabel bisa dadi pilihan sing luwih apik.

Uga, fungsi tabel luwih bisa diprediksi lan konsisten ing wektu eksekusi.

conto

Ayo goleki conto implementasine nggunakake database Pitakonan minangka conto.

Ana panjaluk Pilih, sing nggabungake sawetara tabel lan nggunakake siji tampilan (OperativeQuestions), sing afiliasi dicenthang liwat email (liwat Ana) menyang "Pitakonan Operasi":

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

Tampilan kasebut nduweni struktur sing rada rumit: nduweni subquery gabung lan nggunakake sorting DISTINCT, sing umume minangka operasi sing cukup intensif sumber daya.

Sampel saka OperativeQuestions kira-kira sepuluh ewu cathetan.

Masalah utama karo pitakonan iki yaiku kanggo rekaman saka pitakonan njaba, subquery internal dieksekusi ing tampilan [OperativeQuestions], sing kudu kanggo [Email] = @p__linq__0 ngidini kita mbatesi pilihan output (liwat Ana) nganti atusan cathetan.

Lan misale jek sing subquery kudu ngetung cathetan sapisan dening [Email] = @p__linq__0, banjur iki sawetara atus cathetan kudu disambungake dening Id karo Pitakonan, lan pitakonan bakal cepet.

Nyatane, ana sambungan urutan kabeh tabel: mriksa korespondensi Pitakonan Id karo Id saka OperativeQuestions, lan nyaring kanthi Email.

Nyatane, panyuwunan kasebut bisa digunakake karo kabeh puluhan ewu cathetan OperativeQuestions, nanging mung data kapentingan sing dibutuhake liwat Email.

Pitakonan Operatif ndeleng teks:

Panjaluk 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 tampilan awal ing 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");
    }
}

Pitakonan LINQ wiwitan

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

Ing kasus tartamtu iki, kita nimbang solusi kanggo masalah iki tanpa owah-owahan infrastruktur, tanpa ngenalake tabel kapisah kanthi asil sing wis siap ("Queries Aktif"), sing mbutuhake mekanisme kanggo ngisi data lan tetep anyar. .

Sanajan iki minangka solusi sing apik, ana pilihan liyane kanggo ngoptimalake masalah iki.

Tujuan utama yaiku kanggo nyimpen entri kanthi [Email] = @p__linq__0 saka tampilan OperativeQuestions.

Tepangaken fungsi tabel [dbo].[OperativeQuestionsUserMail] menyang database.

Kanthi ngirim Email minangka parameter input, kita bali tabel nilai:

Panjaluk 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

Iki ngasilake tabel nilai kanthi struktur data sing wis ditemtokake.

Supaya pitakon menyang OperativeQuestionsUserMail dadi optimal lan duwe rencana pitakon sing optimal, struktur sing ketat dibutuhake, lan ora RETURNS TABLE AS RETURN...

Ing kasus iki, Query 1 sing dibutuhake diowahi dadi Query 4:

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

Pemetaan tampilan lan fungsi ing 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})");
}

Pitakonan LINQ pungkasan

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 wektu eksekusi wis mudhun saka 200-800 ms, dadi 2-20 ms, lan liya-liyane, yaiku kaping puluhan luwih cepet.

Yen kita njupuk luwih rata-rata, tinimbang 350 ms kita entuk 8 ms.

Saka kaluwihan sing jelas kita uga entuk:

nyuda beban maca umum,
nyuda sing signifikan ing kemungkinan pamblokiran
nyuda wektu pamblokiran rata-rata kanggo nilai sing bisa ditampa

kesimpulan

Optimization lan fine-tuning saka telpon database MS SQL liwat Linq minangka masalah sing bisa ditanggulangi.

Attentiveness lan konsistensi penting banget ing karya iki.

Ing wiwitan proses:

perlu kanggo mriksa data sing bisa digunakake panyuwunan (nilai, jinis data sing dipilih)
nindakake indeksasi data iki kanthi bener
mriksa bener saka kondisi gabung antarane tabel

Pengulangan optimasi sabanjure nuduhake:

basis panjalukan lan nemtokake panyaring request utama
mbaleni pamblokiran pitakon sing padha lan nganalisa persimpangan kahanan
ing SSMS utawa GUI liyane kanggo SQL Server ngoptimalake dhewe pitakon SQL (nyedhiyakake panyimpenan data penengah, mbangun pitakon asil nggunakake panyimpenan iki (bisa uga ana sawetara))
ing tataran pungkasan, njupuk minangka basis asil pitakon SQL, struktur lagi dibangun maneh pitakon LINQ

Hasile pitakon LINQ kudu dadi struktur sing padha menyang optimal sing diidentifikasi pitakon SQL saka titik 3.

Matur suwun

Many thanks kanggo kolega jobgemws и alex_ozr saka perusahaan Fortis kanggo pitulungan kanggo nyiapake materi iki.

Source: www.habr.com

Metode kanggo ngoptimalake pitakon LINQ ing C#.NET