🥇Metode untuk mengoptimalkan kueri LINQ di C#.NET

pengenalan

В Artikel ini beberapa metode optimasi dipertimbangkan pertanyaan LINQ.
Di sini kami juga menyajikan beberapa pendekatan lain untuk optimasi kode yang terkait dengan pertanyaan LINQ.

Hal ini diketahui bahwa LINQ(Kueri Terintegrasi Bahasa) adalah bahasa yang sederhana dan nyaman untuk menanyakan sumber data.

А LINQ ke SQL adalah teknologi untuk mengakses data dalam DBMS. Ini adalah alat yang ampuh untuk bekerja dengan data, di mana kueri dibuat melalui bahasa deklaratif, yang kemudian akan diubah menjadi Kueri SQL platform dan dikirim ke server database untuk dieksekusi. Dalam kasus kami, yang kami maksud dengan DBMS MSSQL Server.

Namun, pertanyaan LINQ tidak diubah menjadi tulisan yang optimal Kueri SQL, yang dapat ditulis oleh DBA berpengalaman dengan semua nuansa pengoptimalan kueri SQL:

koneksi optimal (BERGABUNG) dan memfilter hasilnya (MANA)
banyak nuansa dalam menggunakan koneksi dan kondisi grup
banyak variasi dalam kondisi penggantian IN pada ADAи TIDAK MASUK, <> aktif ADA
cache hasil perantara melalui tabel sementara, CTE, variabel tabel
penggunaan kalimat (PILIHAN) dengan instruksi dan petunjuk tabel DENGAN (...)
menggunakan tampilan yang diindeks sebagai salah satu cara untuk menghilangkan pembacaan data yang berlebihan selama pemilihan

Hambatan kinerja utama yang dihasilkan kueri SQL saat kompilasi pertanyaan LINQ adalah:

konsolidasi seluruh mekanisme pemilihan data dalam satu permintaan
menduplikasi blok kode yang identik, yang pada akhirnya menyebabkan banyak pembacaan data yang tidak perlu
kelompok kondisi multi-komponen (logis "dan" dan "atau") - DAN и OR, digabungkan ke dalam kondisi yang kompleks, mengarah pada fakta bahwa pengoptimal, yang memiliki indeks non-cluster yang sesuai untuk bidang yang diperlukan, pada akhirnya mulai memindai terhadap indeks cluster (PINDAI INDEKS) menurut kelompok kondisi
subkueri yang bersarang dalam membuat penguraian menjadi sangat bermasalah pernyataan SQL dan analisis rencana kueri oleh pengembang dan DBA

Metode pengoptimalan

Sekarang mari kita beralih langsung ke metode optimasi.

1) Pengindeksan tambahan

Yang terbaik adalah mempertimbangkan filter pada tabel pilihan utama, karena sering kali seluruh kueri dibuat berdasarkan satu atau dua tabel utama (operasi-aplikasi-orang) dan dengan serangkaian kondisi standar (Ditutup, Dibatalkan, Diaktifkan, Status). Penting untuk membuat indeks yang sesuai untuk sampel yang diidentifikasi.

Solusi ini masuk akal ketika memilih bidang ini secara signifikan membatasi kumpulan yang dikembalikan ke kueri.

Misalnya, kami memiliki 500000 aplikasi. Namun yang aktif hanya 2000 aplikasi saja. Maka indeks yang dipilih dengan benar akan menyelamatkan kita dari PINDAI INDEKS pada tabel besar dan memungkinkan Anda memilih data dengan cepat melalui indeks non-cluster.

Selain itu, kurangnya indeks dapat diidentifikasi melalui petunjuk untuk mengurai rencana kueri atau mengumpulkan statistik tampilan sistem MSSQL Server:

Semua data tampilan berisi informasi tentang indeks yang hilang, kecuali indeks spasial.

Namun, indeks dan caching sering kali merupakan metode untuk mengatasi konsekuensi penulisan yang buruk pertanyaan LINQ и kueri SQL.

Seperti yang ditunjukkan oleh praktik kehidupan yang keras, seringkali penting bagi sebuah bisnis untuk mengimplementasikan fitur-fitur bisnis pada tenggat waktu tertentu. Oleh karena itu, permintaan berat sering kali ditransfer ke latar belakang dengan caching.

Hal ini sebagian dibenarkan, karena pengguna tidak selalu membutuhkan data terbaru dan terdapat tingkat respons antarmuka pengguna yang dapat diterima.

Pendekatan ini memungkinkan pemecahan kebutuhan bisnis, namun pada akhirnya mengurangi kinerja sistem informasi hanya dengan menunda solusi terhadap masalah.

Perlu juga diingat bahwa dalam proses mencari indeks yang diperlukan untuk ditambahkan, saran MSSQL pengoptimalan mungkin salah, termasuk dalam kondisi berikut:

jika sudah ada indeks dengan kumpulan bidang serupa
jika bidang dalam tabel tidak dapat diindeks karena pembatasan pengindeksan (dijelaskan lebih detail di sini).

2) Penggabungan atribut menjadi satu atribut baru

Terkadang beberapa bidang dari satu tabel, yang berfungsi sebagai dasar untuk sekelompok kondisi, dapat diganti dengan memperkenalkan satu bidang baru.

Hal ini terutama berlaku untuk bidang status, yang biasanya bertipe bit atau integer.

Contoh:

IsClosed = 0 DAN Dibatalkan = 0 DAN Diaktifkan = 0 digantikan oleh Keadaan = 1.

Di sinilah atribut Status bilangan bulat diperkenalkan untuk memastikan bahwa status ini diisi dalam tabel. Selanjutnya, atribut baru ini diindeks.

Ini adalah solusi mendasar untuk masalah kinerja, karena Kami mengakses data tanpa perhitungan yang tidak perlu.

3) Perwujudan pandangan

Sayangnya, di pertanyaan LINQ Tabel sementara, CTE, dan variabel tabel tidak dapat digunakan secara langsung.

Namun, ada cara lain untuk mengoptimalkan kasus ini - tampilan yang diindeks.

Grup kondisi (dari contoh di atas) IsClosed = 0 DAN Dibatalkan = 0 DAN Diaktifkan = 0 (atau sekumpulan kondisi serupa lainnya) menjadi pilihan yang baik untuk menggunakannya dalam tampilan yang diindeks, menyimpan sebagian kecil data dari kumpulan besar.

Namun ada sejumlah batasan saat mewujudkan suatu pandangan:

penggunaan subkueri, klausa ADA harus diganti dengan menggunakan BERGABUNG
Anda tidak dapat menggunakan kalimat PERSATUAN, UNI SEMUA, PENGECUALIAN, MEMOTONG
Anda tidak dapat menggunakan petunjuk tabel dan klausa PILIHAN
tidak ada kemungkinan untuk bekerja dengan siklus
Tidak mungkin menampilkan data dalam satu tampilan dari tabel berbeda

Penting untuk diingat bahwa manfaat nyata menggunakan tampilan yang diindeks hanya dapat dicapai dengan benar-benar mengindeksnya.

Namun saat memanggil tampilan, indeks ini tidak boleh digunakan, dan untuk menggunakannya secara eksplisit, Anda harus menentukannya DENGAN(TANPA PERLUAS).

Sejak di pertanyaan LINQ Tidak mungkin untuk menentukan petunjuk tabel, jadi Anda harus membuat representasi lain - "pembungkus" dengan bentuk berikut:

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

4) Menggunakan fungsi tabel

Sering di pertanyaan LINQ Blok subkueri yang besar atau blok yang menggunakan tampilan dengan struktur kompleks membentuk kueri akhir dengan struktur eksekusi yang sangat kompleks dan suboptimal.

Manfaat Utama Menggunakan Fungsi Tabel di pertanyaan LINQ:

Kemampuan, seperti halnya tampilan, untuk digunakan dan ditentukan sebagai objek, namun Anda dapat meneruskan serangkaian parameter masukan:
DARI FUNGSI(@param1, @param2 ...)
Hasilnya, pengambilan sampel data yang fleksibel dapat dicapai
Dalam hal menggunakan fungsi tabel, tidak ada batasan kuat seperti dalam kasus tampilan terindeks yang dijelaskan di atas:
1. Petunjuk tabel:
  melalui LINQ Anda tidak dapat menentukan indeks mana yang harus digunakan dan menentukan tingkat isolasi data saat melakukan kueri.
  Namun fungsinya memiliki kemampuan tersebut.
  Dengan fungsi ini, Anda dapat mencapai rencana kueri eksekusi yang cukup konstan, di mana aturan untuk bekerja dengan indeks dan tingkat isolasi data ditentukan
2. Penggunaan fungsi ini memungkinkan, dibandingkan dengan tampilan yang diindeks, untuk memperoleh:
  - logika pengambilan sampel data yang kompleks (bahkan menggunakan loop)
  - mengambil data dari banyak tabel berbeda
  - menggunakan PERSATUAN и ADA
Tawarkan PILIHAN sangat berguna ketika kita perlu menyediakan kontrol konkurensi OPSI (MAXDOP N), urutan rencana eksekusi kueri. Misalnya:
- Anda dapat menentukan pembuatan ulang paksa rencana kueri OPSI (KOMPIL ULANG)
- Anda dapat menentukan apakah akan memaksa rencana kueri untuk menggunakan urutan gabungan yang ditentukan dalam kueri OPSI (PESAN PAKSA)
Lebih detail tentang PILIHAN dijelaskan di sini.
Menggunakan potongan data yang paling sempit dan paling dibutuhkan:
Tidak perlu menyimpan kumpulan data besar dalam cache (seperti halnya tampilan yang diindeks), sehingga Anda masih perlu memfilter data berdasarkan parameter.
Misalnya ada tabel yang filternya MANA tiga bidang digunakan (a,b,c).

Secara konvensional, semua permintaan memiliki kondisi konstan a = 0 dan b = 0.

Namun, permintaan untuk lapangan c lebih bervariasi.

Biarkan kondisinya a = 0 dan b = 0 Ini sangat membantu kami untuk membatasi kumpulan hasil yang diperlukan menjadi ribuan catatan, tetapi syaratnya aktif с mempersempit pilihan menjadi seratus catatan.

Di sini fungsi tabel mungkin merupakan pilihan yang lebih baik.

Selain itu, fungsi tabel lebih dapat diprediksi dan konsisten dalam waktu eksekusi.

contoh

Mari kita lihat contoh implementasi menggunakan database Pertanyaan sebagai contoh.

Ada permintaan MEMILIH, yang menggabungkan beberapa tabel dan menggunakan satu tampilan (OperativeQuestions), di mana afiliasi diperiksa melalui email (melalui ADA) ke “Pertanyaan Operasional”:

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

Tampilannya memiliki struktur yang agak rumit: memiliki gabungan subkueri dan menggunakan penyortiran BERBEDA, yang secara umum merupakan operasi yang cukup intensif sumber daya.

Sampel dari OperativeQuestions berjumlah sekitar sepuluh ribu catatan.

Masalah utama dengan kueri ini adalah bahwa untuk catatan dari kueri luar, subkueri internal dijalankan pada tampilan [OperativeQuestions], yang seharusnya untuk [Email] = @p__linq__0 memungkinkan kita membatasi pilihan keluaran (melalui ADA) hingga ratusan catatan.

Dan sepertinya subkueri harus menghitung catatan satu kali dengan [Email] = @p__linq__0, dan kemudian beberapa ratus catatan ini harus dihubungkan dengan Id dengan Pertanyaan, dan kuerinya akan cepat.

Faktanya, ada koneksi berurutan dari semua tabel: memeriksa korespondensi Id Questions dengan Id dari OperativeQuestions, dan memfilter berdasarkan Email.

Faktanya, permintaan tersebut berfungsi dengan puluhan ribu catatan OperativeQuestions, tetapi hanya data yang diperlukan melalui Email.

Teks tampilan 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 tampilan awal di 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");
    }
}

Kueri 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 kasus khusus ini, kami sedang mempertimbangkan solusi untuk masalah ini tanpa perubahan infrastruktur, tanpa memperkenalkan tabel terpisah dengan hasil siap pakai (“Kueri Aktif”), yang memerlukan mekanisme untuk mengisinya dengan data dan menjaganya tetap mutakhir. .

Meskipun ini merupakan solusi yang baik, ada opsi lain untuk mengoptimalkan masalah ini.

Tujuan utamanya adalah untuk menyimpan entri dalam cache oleh [Email] = @p__linq__0 dari tampilan OperativeQuestions.

Perkenalkan fungsi tabel [dbo].[OperativeQuestionsUserMail] ke dalam database.

Dengan mengirimkan Email sebagai parameter masukan, kami mendapatkan kembali tabel 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 tabel nilai dengan struktur data yang telah ditentukan sebelumnya.

Agar kueri ke OperativeQuestionsUserMail menjadi optimal dan memiliki rencana kueri yang optimal, diperlukan struktur yang ketat, dan bukan TABEL PENGEMBALIAN SEBAGAI PENGEMBALIAN...

Dalam hal ini, Kueri 1 yang diperlukan diubah menjadi Kueri 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 tampilan dan fungsi di 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})");
}

Kueri 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 waktu eksekusi telah turun dari 200-800 mdtk, menjadi 2-20 mdtk, dan seterusnya, yaitu puluhan kali lebih cepat.

Jika kita mengambilnya lebih rata-rata, maka alih-alih 350 ms kita mendapat 8 ms.

Dari keuntungan nyata kami juga mendapatkan:

pengurangan umum dalam beban membaca,
pengurangan signifikan dalam kemungkinan pemblokiran
mengurangi waktu pemblokiran rata-rata ke nilai yang dapat diterima

Keluaran

Optimalisasi dan penyesuaian panggilan database MSSQL melalui LINQ adalah masalah yang bisa diselesaikan.

Kehati-hatian dan konsistensi sangat penting dalam pekerjaan ini.

Di awal proses:

perlu untuk memeriksa data yang digunakan permintaan tersebut (nilai, tipe data yang dipilih)
melakukan pengindeksan yang tepat atas data ini
periksa kebenaran kondisi penggabungan antar tabel

Iterasi pengoptimalan berikutnya mengungkapkan:

dasar permintaan dan mendefinisikan filter permintaan utama
mengulangi blok kueri serupa dan menganalisis perpotongan kondisi
di SSMS atau GUI lainnya untuk SQL Server mengoptimalkan dirinya sendiri kueri SQL (mengalokasikan penyimpanan data perantara, membuat kueri yang dihasilkan menggunakan penyimpanan ini (mungkin ada beberapa))
pada tahap terakhir, berdasarkan hasilnya kueri SQL, strukturnya sedang dibangun kembali permintaan LINQ

Hasilnya permintaan LINQ strukturnya harus identik dengan optimal yang teridentifikasi kueri SQL dari poin 3.

Ucapan Terima Kasih

Terima kasih banyak kepada rekan-rekan pekerjaangemws и alex_ozr dari perusahaan Fortis untuk bantuan dalam mempersiapkan materi ini.

Sumber: www.habr.com

Metode untuk mengoptimalkan kueri LINQ di C#.NET