🥇Kaedah untuk mengoptimumkan pertanyaan LINQ dalam C#.NET

Pengenalan

В artikel ini beberapa kaedah pengoptimuman telah dipertimbangkan pertanyaan LINQ.
Di sini kami juga membentangkan beberapa lagi pendekatan untuk pengoptimuman kod yang berkaitan dengan pertanyaan LINQ.

Telah diketahui bahawa LINQ(Pertanyaan Bersepadu Bahasa) ialah bahasa yang ringkas dan mudah untuk menanyakan sumber data.

А LINQ ke SQL ialah teknologi untuk mengakses data dalam DBMS. Ini ialah alat yang berkuasa untuk bekerja dengan data, di mana pertanyaan dibina melalui bahasa deklaratif, yang kemudiannya akan ditukar menjadi pertanyaan SQL platform dan dihantar ke pelayan pangkalan data untuk dilaksanakan. Dalam kes kami, dengan DBMS yang kami maksudkan Pelayan MS SQL.

Walau bagaimanapun, pertanyaan LINQ tidak ditukar menjadi tulisan yang optimum pertanyaan SQL, yang boleh ditulis oleh DBA berpengalaman dengan semua nuansa pengoptimuman pertanyaan SQL:

sambungan optimum (JOIN) dan menapis keputusan (DIMANA)
banyak nuansa dalam menggunakan sambungan dan keadaan kumpulan
banyak variasi dalam menggantikan keadaan IN pada ADAи BUKAN DALAM, <> pada ADA
caching perantaraan hasil melalui jadual sementara, CTE, pembolehubah jadual
penggunaan ayat (OPTION) dengan arahan dan pembayang jadual DENGAN (...)
menggunakan paparan diindeks sebagai salah satu cara untuk menyingkirkan bacaan data yang berlebihan semasa pemilihan

Kesesakan prestasi utama yang terhasil pertanyaan SQL semasa menyusun pertanyaan LINQ adalah seperti berikut:

penyatuan keseluruhan mekanisme pemilihan data dalam satu permintaan
menduplikasi blok kod yang sama, yang akhirnya membawa kepada beberapa bacaan data yang tidak diperlukan
kumpulan keadaan berbilang komponen (logik “dan” dan “atau”) - DAN и OR, bergabung ke dalam keadaan yang kompleks, membawa kepada fakta bahawa pengoptimum, yang mempunyai indeks tidak berkelompok yang sesuai untuk medan yang diperlukan, akhirnya mula mengimbas terhadap indeks berkelompok (IMBALAN INDEKS) mengikut kumpulan syarat
sarang subkueri yang mendalam menjadikan penghuraian sangat bermasalah pernyataan SQL dan analisis pelan pertanyaan di pihak pembangun dan DBA

Kaedah pengoptimuman

Sekarang mari kita beralih terus ke kaedah pengoptimuman.

1) Pengindeksan tambahan

Adalah lebih baik untuk mempertimbangkan penapis pada jadual pemilihan utama, kerana selalunya keseluruhan pertanyaan dibina di sekitar satu atau dua jadual utama (aplikasi-orang-operasi) dan dengan set syarat standard (Ditutup, Dibatalkan, Didayakan, Status). Adalah penting untuk mencipta indeks yang sesuai untuk sampel yang dikenal pasti.

Penyelesaian ini masuk akal apabila memilih medan ini dengan ketara mengehadkan set yang dikembalikan kepada pertanyaan.

Sebagai contoh, kami mempunyai 500000 permohonan. Walau bagaimanapun, terdapat hanya 2000 aplikasi aktif. Kemudian indeks yang dipilih dengan betul akan menyelamatkan kita daripada IMBALAN INDEKS pada jadual besar dan akan membolehkan anda memilih data dengan cepat melalui indeks bukan berkelompok.

Selain itu, kekurangan indeks boleh dikenal pasti melalui gesaan untuk menghuraikan rancangan pertanyaan atau mengumpul statistik paparan sistem Pelayan MS SQL:

Semua data paparan mengandungi maklumat tentang indeks yang hilang, kecuali indeks spatial.

Walau bagaimanapun, indeks dan caching selalunya merupakan kaedah untuk memerangi akibat daripada penulisan yang tidak baik pertanyaan LINQ и pertanyaan SQL.

Seperti yang ditunjukkan oleh amalan hidup yang keras, selalunya penting bagi perniagaan untuk melaksanakan ciri perniagaan mengikut tarikh akhir tertentu. Oleh itu, permintaan berat sering dipindahkan ke latar belakang dengan caching.

Ini sebahagiannya wajar, kerana pengguna tidak selalu memerlukan data terkini dan terdapat tahap tindak balas antara muka pengguna yang boleh diterima.

Pendekatan ini membolehkan menyelesaikan keperluan perniagaan, tetapi akhirnya mengurangkan prestasi sistem maklumat dengan hanya menangguhkan penyelesaian kepada masalah.

Ia juga bernilai mengingati bahawa dalam proses mencari indeks yang diperlukan untuk menambah, cadangan MS SQL pengoptimuman mungkin tidak betul, termasuk di bawah syarat berikut:

jika sudah terdapat indeks dengan set medan yang serupa
jika medan dalam jadual tidak boleh diindeks kerana sekatan pengindeksan (diterangkan dengan lebih terperinci di sini).

2) Menggabungkan atribut menjadi satu atribut baharu

Kadangkala beberapa medan daripada satu jadual, yang berfungsi sebagai asas untuk sekumpulan syarat, boleh digantikan dengan memperkenalkan satu medan baharu.

Ini adalah benar terutamanya untuk medan status, yang biasanya sama ada dalam jenis bit atau integer.

Contoh:

IsClosed = 0 DAN Dibatalkan = 0 DAN Didayakan = 0 digantikan oleh Status = 1.

Di sinilah atribut Status integer diperkenalkan untuk memastikan status ini diisi dalam jadual. Seterusnya, atribut baharu ini diindeks.

Ini adalah penyelesaian asas kepada masalah prestasi, kerana Kami mengakses data tanpa pengiraan yang tidak perlu.

3) Pewujudan pandangan

Malangnya, dalam pertanyaan LINQ Jadual sementara, CTE dan pembolehubah jadual tidak boleh digunakan secara langsung.

Walau bagaimanapun, terdapat cara lain untuk mengoptimumkan untuk kes ini - paparan diindeks.

Kumpulan keadaan (daripada contoh di atas) IsClosed = 0 DAN Dibatalkan = 0 DAN Didayakan = 0 (atau set syarat lain yang serupa) menjadi pilihan yang baik untuk menggunakannya dalam paparan diindeks, menyimpan sekeping kecil data daripada set besar.

Tetapi terdapat beberapa sekatan apabila merealisasikan pandangan:

penggunaan subkueri, klausa ADA hendaklah diganti dengan menggunakan JOIN
anda tidak boleh menggunakan ayat UNION, UNION SEMUA, PENGECUALIAN, MENARIK
Anda tidak boleh menggunakan pembayang jadual dan klausa OPTION
tiada kemungkinan untuk bekerja dengan kitaran
Adalah mustahil untuk memaparkan data dalam satu paparan dari jadual yang berbeza

Adalah penting untuk diingat bahawa faedah sebenar menggunakan paparan diindeks hanya boleh dicapai dengan benar-benar mengindeksnya.

Tetapi apabila memanggil paparan, indeks ini mungkin tidak digunakan dan untuk menggunakannya secara eksplisit, anda mesti menentukan DENGAN(NOEXPAND).

Sejak di pertanyaan LINQ Tidak mustahil untuk menentukan petunjuk jadual, jadi anda perlu membuat perwakilan lain - "pembungkus" dalam bentuk berikut:

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

4) Menggunakan fungsi jadual

Selalunya dalam pertanyaan LINQ Blok besar subkueri atau blok menggunakan paparan dengan struktur kompleks membentuk pertanyaan akhir dengan struktur pelaksanaan yang sangat kompleks dan suboptimum.

Faedah Utama Menggunakan Fungsi Jadual dalam pertanyaan LINQ:

Keupayaan, seperti dalam kes pandangan, untuk digunakan dan ditentukan sebagai objek, tetapi anda boleh lulus satu set parameter input:
DARI FUNGSI(@param1, @param2 ...)
Hasilnya, pensampelan data yang fleksibel boleh dicapai
Dalam kes menggunakan fungsi jadual, tiada sekatan yang kuat seperti dalam kes paparan diindeks yang diterangkan di atas:
1. Petua jadual:
  melalui LINQ Anda tidak boleh menentukan indeks yang harus digunakan dan menentukan tahap pengasingan data semasa membuat pertanyaan.
  Tetapi fungsi mempunyai keupayaan ini.
  Dengan fungsi, anda boleh mencapai pelan pertanyaan pelaksanaan yang agak berterusan, di mana peraturan untuk bekerja dengan indeks dan tahap pengasingan data ditentukan
2. Menggunakan fungsi membolehkan, berbanding dengan pandangan diindeks, untuk mendapatkan:
  - logik pensampelan data yang kompleks (walaupun menggunakan gelung)
  - mengambil data daripada banyak jadual yang berbeza
  - penggunaan UNION и ADA
Tawaran OPTION sangat berguna apabila kita perlu menyediakan kawalan konkurensi PILIHAN (MAXDOP N), susunan rancangan pelaksanaan pertanyaan. Sebagai contoh:
- anda boleh menentukan penciptaan semula pelan pertanyaan secara paksa PILIHAN (COMPILE)
- anda boleh menentukan sama ada untuk memaksa pelan pertanyaan untuk menggunakan susunan gabungan yang dinyatakan dalam pertanyaan PILIHAN (PAKSA PESANAN)
Butiran lanjut tentang OPTION diterangkan di sini.
Menggunakan kepingan data yang paling sempit dan paling diperlukan:
Tidak perlu menyimpan set data yang besar dalam cache (seperti yang berlaku dengan paparan diindeks), yang mana anda masih perlu menapis data mengikut parameter.
Sebagai contoh, terdapat jadual yang penapisnya DIMANA tiga medan digunakan (a, b, c).

Secara konvensional, semua permintaan mempunyai syarat yang tetap a = 0 dan b = 0.

Walau bagaimanapun, permintaan untuk bidang c lebih berubah-ubah.

Biar syarat a = 0 dan b = 0 Ia benar-benar membantu kami untuk mengehadkan set terhasil yang diperlukan kepada beribu-ribu rekod, tetapi syaratnya dihidupkan с menyempitkan pemilihan kepada seratus rekod.

Di sini fungsi jadual mungkin pilihan yang lebih baik.

Selain itu, fungsi jadual lebih boleh diramal dan konsisten dalam masa pelaksanaan.

contoh

Mari kita lihat contoh pelaksanaan menggunakan pangkalan data Soalan sebagai contoh.

Ada permintaan SELECT, yang menggabungkan beberapa jadual dan menggunakan satu paparan (OperativeQuestions), di mana gabungan disemak melalui e-mel (melalui ADA) kepada “Soalan Operasi”:

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

Pandangan mempunyai struktur yang agak kompleks: ia mengandungi gabungan subkueri dan menggunakan pengisihan DISTINCT, yang secara amnya merupakan operasi yang agak intensif sumber.

Satu sampel daripada OperativeQuestions ialah kira-kira sepuluh ribu rekod.

Masalah utama dengan pertanyaan ini ialah untuk rekod daripada pertanyaan luar, subquery dalaman dilaksanakan pada paparan [OperativeQuestions], yang sepatutnya untuk [E-mel] = @p__linq__0 membenarkan kami mengehadkan pemilihan output (melalui ADA) sehingga ratusan rekod.

Dan nampaknya subkueri harus mengira rekod sekali dengan [E-mel] = @p__linq__0, dan kemudian beberapa ratus rekod ini harus disambungkan dengan Id dengan Soalan, dan pertanyaan akan menjadi pantas.

Malah, terdapat sambungan berurutan bagi semua jadual: menyemak surat-menyurat Soalan Id dengan Id daripada OperativeQuestions dan menapis melalui E-mel.

Malah, permintaan itu berfungsi dengan semua puluhan ribu rekod OperativeQuestions, tetapi hanya data minat diperlukan melalui E-mel.

Teks paparan OperativeQuestions:

Permintaan 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 paparan awal dalam 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");
    }
}

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

Dalam kes khusus ini, kami sedang mempertimbangkan penyelesaian kepada masalah ini tanpa perubahan infrastruktur, tanpa memperkenalkan jadual berasingan dengan hasil siap sedia (“Pertanyaan Aktif”), yang memerlukan mekanisme untuk mengisinya dengan data dan memastikannya dikemas kini. .

Walaupun ini adalah penyelesaian yang baik, terdapat satu lagi pilihan untuk mengoptimumkan masalah ini.

Tujuan utama adalah untuk menyimpan entri oleh [E-mel] = @p__linq__0 daripada paparan OperativeQuestions.

Perkenalkan fungsi jadual [dbo].[OperativeQuestionsUserMail] ke dalam pangkalan data.

Dengan menghantar E-mel sebagai parameter input, kami mendapat kembali jadual nilai:

Permintaan 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

Ini mengembalikan jadual nilai dengan struktur data yang telah ditetapkan.

Agar pertanyaan kepada OperativeQuestionsUserMail menjadi optimum dan mempunyai rancangan pertanyaan yang optimum, struktur yang ketat diperlukan dan bukan MENGEMBALIKAN MEJA SEBAGAI PEMULANGAN...

Dalam kes ini, Pertanyaan 1 yang diperlukan ditukar kepada Pertanyaan 4:

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

Memetakan pandangan dan fungsi dalam 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})");
}

Pertanyaan LINQ terakhir

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 masa pelaksanaan telah menurun daripada 200-800 ms, kepada 2-20 ms, dsb., iaitu berpuluh-puluh kali lebih cepat.

Jika kita mengambilnya dengan lebih purata, maka bukannya 350 ms kita mendapat 8 ms.

Daripada kelebihan yang jelas kami juga mendapat:

pengurangan umum dalam beban bacaan,
pengurangan ketara dalam kemungkinan menyekat
mengurangkan purata masa menyekat kepada nilai yang boleh diterima

Output

Pengoptimuman dan penalaan halus panggilan pangkalan data MS SQL melalui LINQ adalah masalah yang boleh diselesaikan.

Perhatian dan konsistensi adalah sangat penting dalam kerja ini.

Pada permulaan proses:

adalah perlu untuk menyemak data yang mana permintaan berfungsi (nilai, jenis data yang dipilih)
menjalankan pengindeksan yang betul bagi data ini
semak ketepatan syarat cantuman antara jadual

Lelaran pengoptimuman seterusnya mendedahkan:

asas permintaan dan mentakrifkan penapis permintaan utama
mengulangi blok pertanyaan serupa dan menganalisis persilangan keadaan
dalam SSMS atau GUI lain untuk SQL Server mengoptimumkan dirinya Pertanyaan SQL (memperuntukkan storan data perantaraan, membina pertanyaan yang terhasil menggunakan storan ini (mungkin terdapat beberapa))
pada peringkat terakhir, mengambil sebagai asas yang terhasil Pertanyaan SQL, struktur sedang dibina semula pertanyaan LINQ

Yang terhasil pertanyaan LINQ harus menjadi sama dalam struktur kepada optimum yang dikenal pasti Pertanyaan SQL dari titik 3.

Ucapan terima kasih

Jutaan terima kasih kepada rakan sekerja jobgemws и alex_ozr daripada syarikat Fortis untuk bantuan dalam penyediaan bahan ini.

Sumber: www.habr.com

Kaedah untuk mengoptimumkan pertanyaan LINQ dalam C#.NET