🥇C#.NET | da LINQ so'rovlarini optimallashtirish usullari ProHoster

kirish

В Ushbu maqola ba'zi optimallashtirish usullari ko'rib chiqildi LINQ so'rovlari.
Bu erda biz kodni optimallashtirish bo'yicha yana bir qancha yondashuvlarni taqdim etamiz LINQ so'rovlari.

Ma'lumki, bu narsa LINQ(Language-Integrated Query) - ma'lumotlar manbasini so'rash uchun oddiy va qulay til.

А LINQ to SQL DBMSdagi ma'lumotlarga kirish texnologiyasidir. Bu ma'lumotlar bilan ishlash uchun kuchli vosita bo'lib, bu erda so'rovlar deklarativ til orqali tuziladi, keyin esa tilga aylantiriladi. SQL so'rovlari platformasi va bajarilishi uchun ma'lumotlar bazasi serveriga yuboriladi. Bizning holatda, DBMS deganda biz tushunamiz MS SQL server.

Biroq, LINQ so'rovlari optimal yozilganlarga aylantirilmaydi SQL so'rovlari, tajribali DBA optimallashtirishning barcha nuanslari bilan yozishi mumkin edi SQL so'rovlari:

optimal ulanishlar (JOIN) va natijalarni filtrlash (WHERE)
ulanishlar va guruh sharoitlaridan foydalanishda ko'plab nuanslar
sharoitlarni almashtirishda ko'plab o'zgarishlar IN haqida Mavjudи YO'Q, <> yoqilgan Mavjud
vaqtinchalik jadvallar, CTE, jadval o'zgaruvchilari orqali natijalarni oraliq keshlash
jumladan foydalanish (OPTION) ko'rsatmalar va jadval ko'rsatmalari bilan BILAN (...)
tanlovlar paytida ortiqcha ma'lumotlarni o'qishdan xalos bo'lish vositalaridan biri sifatida indekslangan ko'rinishlardan foydalanish

Natijada yuzaga keladigan asosiy ishlash to'siqlari SQL so'rovlari kompilyatsiya qilishda LINQ so'rovlari quyidagilar:

ma'lumotlarni tanlashning butun mexanizmini bitta so'rovda birlashtirish
bir xil kod bloklarini takrorlash, natijada bir nechta keraksiz ma'lumotlarni o'qishga olib keladi
ko'p komponentli shartlar guruhlari (mantiqiy "va" va "yoki") - VA и OR, murakkab sharoitlarga birlashishi, kerakli maydonlar uchun mos bo'lmagan klasterli indekslarga ega bo'lgan optimallashtiruvchi oxir-oqibat klasterlangan indeksga qarshi skanerlashni boshlashiga olib keladi (INDEX SAN) shartlar guruhlari bo'yicha
pastki so'rovlarni chuqur joylashtirish tahlilni juda muammoli qiladi SQL bayonotlari va ishlab chiquvchilar tomonidan so'rovlar rejasini tahlil qilish va DBA

Optimallashtirish usullari

Endi to'g'ridan-to'g'ri optimallashtirish usullariga o'tamiz.

1) Qo'shimcha indekslash

Asosiy tanlov jadvallarida filtrlarni ko'rib chiqish yaxshidir, chunki ko'pincha butun so'rov bir yoki ikkita asosiy jadval (dasturlar-odamlar-operatsiyalar) atrofida va standart shartlar to'plami (IsClosed, Canceled, Enabled, Status) bilan tuziladi. Aniqlangan namunalar uchun tegishli indekslarni yaratish muhimdir.

Ushbu maydonlarni tanlashda ushbu yechim mantiqiy bo'lib, so'rovga qaytarilgan to'plamni sezilarli darajada cheklaydi.

Masalan, bizda 500000 2000 ta ariza bor. Biroq, faqat XNUMX ta faol ilovalar mavjud. Keyin to'g'ri tanlangan indeks bizni qutqaradi INDEX SAN katta jadvalda va klasterli bo'lmagan indeks orqali ma'lumotlarni tezda tanlash imkonini beradi.

Shuningdek, indekslarning etishmasligi so'rovlar rejalarini tahlil qilish yoki tizim ko'rinishi statistikasini yig'ish bo'yicha takliflar orqali aniqlanishi mumkin. MS SQL server:

Barcha ko'rish ma'lumotlari fazoviy indekslar bundan mustasno, etishmayotgan indekslar haqidagi ma'lumotlarni o'z ichiga oladi.

Biroq, indekslar va keshlash ko'pincha yomon yozilgan oqibatlarga qarshi kurash usullaridir LINQ so'rovlari и SQL so'rovlari.

Hayotning og'ir amaliyoti shuni ko'rsatadiki, biznes uchun biznes xususiyatlarini ma'lum muddatlarda amalga oshirish ko'pincha muhimdir. Va shuning uchun og'ir so'rovlar ko'pincha keshlash bilan fonga o'tkaziladi.

Bu qisman oqlanadi, chunki foydalanuvchi har doim ham so'nggi ma'lumotlarga muhtoj emas va foydalanuvchi interfeysining maqbul darajasi mavjud.

Ushbu yondashuv biznes ehtiyojlarini hal qilishga imkon beradi, lekin oxir-oqibat muammolarni hal qilishni kechiktirish orqali axborot tizimining ishlashini pasaytiradi.

Shuni ham yodda tutish kerakki, kerakli indekslarni qidirish jarayonida takliflar qo'shiladi MS SQL optimallashtirish noto'g'ri bo'lishi mumkin, shu jumladan quyidagi sharoitlarda:

agar shunga o'xshash maydonlar to'plamiga ega indekslar mavjud bo'lsa
agar jadvaldagi maydonlarni indekslash cheklovlari tufayli indekslash mumkin bo'lmasa (batafsilroq tavsiflangan) shu yerda).

2) Atributlarni yangi atributga birlashtirish

Ba'zida shartlar guruhi uchun asos bo'lib xizmat qiladigan bitta jadvalning ba'zi maydonlari bitta yangi maydonni kiritish bilan almashtirilishi mumkin.

Bu, ayniqsa, odatda bit yoki butun son turi bo'lgan holat maydonlari uchun to'g'ri keladi.

Misol:

IsClosed = 0 VA Bekor qilingan = 0 VA Yoqilgan = 0 bilan almashtiriladi Holati = 1.

Jadvalda ushbu statuslar to'ldirilishini ta'minlash uchun tamsayı Status atributi aynan shu erda kiritiladi. Keyinchalik, bu yangi atribut indekslanadi.

Bu ishlash muammosining asosiy yechimidir, chunki biz ma'lumotlarga keraksiz hisob-kitoblarsiz kirishimiz mumkin.

3) Ko'rinishni moddiylashtirish

Afsuski, ichida LINQ so'rovlari Vaqtinchalik jadvallar, CTE va jadval o'zgaruvchilari to'g'ridan-to'g'ri ishlatilmaydi.

Biroq, bu holat uchun optimallashtirishning yana bir usuli bor - indekslangan ko'rinishlar.

Shartlar guruhi (yuqoridagi misoldan) IsClosed = 0 VA Bekor qilingan = 0 VA Yoqilgan = 0 (yoki boshqa shunga o'xshash shartlar to'plami) ularni katta to'plamdan kichik bo'lak ma'lumotlarni keshlash, indekslangan ko'rinishda ishlatishning yaxshi variantiga aylanadi.

Ammo ko'rinishni amalga oshirishda bir qator cheklovlar mavjud:

pastki so‘rovlardan, bandlardan foydalanish Mavjud yordamida almashtirilishi kerak JOIN
jumlalarni ishlata olmaysiz Union, HAMMA BIRLIK, Istisno, QO'SHISH
Siz jadval ko'rsatmalari va bandlaridan foydalana olmaysiz OPTION
tsikllar bilan ishlash imkoniyati yo'q
Turli jadvallardagi ma'lumotlarni bir ko'rinishda ko'rsatish mumkin emas

Shuni esda tutish kerakki, indekslangan ko'rinishdan foydalanishning haqiqiy foydasiga faqat uni indekslash orqali erishish mumkin.

Ammo ko'rinishni chaqirganda, bu indekslardan foydalanmaslik mumkin va ulardan aniq foydalanish uchun siz ko'rsatishingiz kerak BILAN(NO EXPAND).

Yildan beri LINQ so'rovlari Jadval ko'rsatmalarini aniqlab bo'lmaydi, shuning uchun siz boshqa tasvirni yaratishingiz kerak - quyidagi shakldagi "o'rash":

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

4) Jadval funksiyalaridan foydalanish

Ko'pincha ichkarida LINQ so'rovlari Murakkab tuzilishga ega ko'rinishlardan foydalangan holda kichik so'rovlar yoki bloklarning katta bloklari juda murakkab va optimal bo'lmagan ijro tuzilmasi bilan yakuniy so'rovni tashkil qiladi.

Jadval funksiyalaridan foydalanishning asosiy afzalliklari LINQ so'rovlari:

Ko'rinishdagi kabi ob'ekt sifatida foydalanish va ko'rsatish qobiliyati, lekin siz kiritish parametrlari to'plamini o'tkazishingiz mumkin:
FROM FUNCTION (@param1, @param2 ...)
Natijada, moslashuvchan ma'lumotlarni tanlashga erishish mumkin
Jadval funktsiyasidan foydalanishda, yuqorida tavsiflangan indekslangan ko'rinishlardagi kabi kuchli cheklovlar yo'q:
1. Jadval bo'yicha maslahatlar:
  ichidan LINQ Qaysi indekslardan foydalanish kerakligini aniqlay olmaysiz va so'rov paytida ma'lumotlarning izolyatsiyasi darajasini aniqlay olmaysiz.
  Lekin funksiya bu imkoniyatlarga ega.
  Funktsiya yordamida siz juda doimiy so'rovlar rejasiga erishishingiz mumkin, bu erda indekslar bilan ishlash qoidalari va ma'lumotlarni izolyatsiyalash darajalari aniqlanadi.
2. Funktsiyadan foydalanish indekslangan ko'rinishlar bilan solishtirganda quyidagilarni olish imkonini beradi:
  - murakkab ma'lumotlarni namuna olish mantig'i (hatto looplar yordamida ham)
  - turli xil jadvallardan ma'lumotlarni olish
  - foydalanish Union и Mavjud
Taklif qiling OPTION bir vaqtning o'zida nazoratni ta'minlash kerak bo'lganda juda foydali OPTION (MAXDOP N), so'rovni bajarish rejasining tartibi. Masalan:
- so'rov rejasini majburiy qayta yaratishni belgilashingiz mumkin VARIANT (QAYTA TURMUSH)
- so'rov rejasini so'rovda ko'rsatilgan qo'shilish tartibidan foydalanishga majburlashni belgilashingiz mumkin VARIANT (MAJBUR TARTIBI)
Haqida batafsil ma'lumot OPTION tasvirlangan shu yerda.
Eng tor va eng kerakli ma'lumotlar bo'lagidan foydalanish:
Katta ma'lumotlar to'plamlarini keshlarda saqlashning hojati yo'q (indekslangan ko'rinishlarda bo'lgani kabi), siz hali ham ma'lumotlarni parametr bo'yicha filtrlashingiz kerak.
Misol uchun, filtri bo'lgan jadval mavjud WHERE uchta maydon ishlatiladi (a, b, c).

An'anaviy ravishda barcha so'rovlar doimiy shartga ega a = 0 va b = 0.

Biroq, maydon uchun so'rov c ko'proq o'zgaruvchan.

Shartga ruxsat bering a = 0 va b = 0 Bu haqiqatan ham kerakli natija to'plamini minglab yozuvlar bilan cheklashimizga yordam beradi, ammo shart с tanlovni yuzta rekordgacha qisqartiradi.

Bu erda jadval funktsiyasi yaxshiroq variant bo'lishi mumkin.

Bundan tashqari, jadval funksiyasi ko'proq bashorat qilinadigan va bajarilish vaqtida izchil.

misollar

Misol sifatida Questions ma'lumotlar bazasidan foydalanib, misolni amalga oshirishni ko'rib chiqaylik.

Murojaat bor SELECT, bir nechta jadvallarni birlashtiradi va bitta ko'rinishdan foydalanadi (OperativeQuestions), bunda mansublik elektron pochta orqali tekshiriladi (orqali). Mavjud) "Tezkor savollar" ga:

So'rov № 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])
));

Ko'rinish ancha murakkab tuzilishga ega: u quyi so'rovlarga qo'shiladi va saralashdan foydalanadi DISTINCT, bu umuman olganda juda ko'p resurs talab qiladigan operatsiya.

OperativeQuestions-dan namuna o'n mingga yaqin yozuvlarni o'z ichiga oladi.

Ushbu so'rovning asosiy muammosi shundaki, tashqi so'rovdagi yozuvlar uchun ichki quyi so'rov [OperativeQuestions] ko'rinishida bajariladi, bu [Email] = @p__linq__0 uchun chiqish tanlovini cheklash imkonini beradi (orqali orqali). Mavjud) yuzlab yozuvlar.

Va shunday tuyulishi mumkinki, quyi so'rov yozuvlarni bir marta [Email] = @p__linq__0 orqali hisoblashi kerak va keyin bu bir necha yuz yozuvlar Id bilan Savollar bilan bog'lanishi kerak va so'rov tez bo'ladi.

Darhaqiqat, barcha jadvallarning ketma-ket ulanishi mavjud: OperativeQuestions-dan Id bilan identifikator savollarining mosligini tekshirish va elektron pochta orqali filtrlash.

Aslida, so'rov barcha o'n minglab OperativeQuestions yozuvlari bilan ishlaydi, ammo elektron pochta orqali faqat qiziqish ma'lumotlari kerak bo'ladi.

OperativeQuestions matnini ko'rish:

So'rov № 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));

DbContext-da dastlabki ko'rinishni xaritalash (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

Dastlabki LINQ so'rovi

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

Bunday holda, biz ushbu muammoni infratuzilmaviy o'zgarishlarsiz, tayyor natijalarga ega bo'lgan alohida jadvalni ("Faol so'rovlar") kiritmasdan hal qilishni ko'rib chiqmoqdamiz, bu esa uni ma'lumotlar bilan to'ldirish va ularni yangilab turish mexanizmini talab qiladi. .

Bu yaxshi yechim bo'lsa-da, bu muammoni optimallashtirishning yana bir varianti mavjud.

Asosiy maqsad - OperativeQuestions ko'rinishidan [Email] = @p__linq__0 tomonidan kiritilgan yozuvlarni keshlash.

Ma'lumotlar bazasiga [dbo].[OperativeQuestionsUserMail] jadval funksiyasini kiriting.

Elektron pochtani kirish parametri sifatida yuborish orqali biz qiymatlar jadvalini qaytarib olamiz:

So'rov № 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

Bu oldindan belgilangan ma'lumotlar tuzilmasi bilan qiymatlar jadvalini qaytaradi.

OperativeQuestionsUserMail-ga so'rovlar optimal bo'lishi va optimal so'rov rejalariga ega bo'lishi uchun qat'iy tuzilma talab qilinadi. JADVOLNI QAYTIB QAYTIB QAYTADI...

Bunday holda, talab qilinadigan 1-so'rov 4-so'rovga aylantiriladi:

So'rov № 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]);

DbContext'dagi ko'rinishlar va funktsiyalarni xaritalash (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})");
}

Yakuniy LINQ so'rovi

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

Bajarish vaqtining tartibi 200-800 ms dan 2-20 ms gacha va hokazo, ya'ni o'nlab marta tezroq kamaydi.

Agar biz uni o'rtacha hisoblasak, 350 ms o'rniga biz 8 milodiyni oldik.

Aniq afzalliklardan biz ham olamiz:

o'qish yukining umumiy kamayishi,
blokirovka qilish ehtimoli sezilarli darajada kamayadi
o'rtacha blokirovka vaqtini maqbul qiymatlarga qisqartirish

xulosa

Ma'lumotlar bazasi qo'ng'iroqlarini optimallashtirish va nozik sozlash MS SQL ichidan LINQ hal qilinishi mumkin bo‘lgan muammodir.

Bu ishda e'tibor va izchillik juda muhimdir.

Jarayon boshida:

so'rov ishlaydigan ma'lumotlarni tekshirish kerak (qiymatlar, tanlangan ma'lumotlar turlari)
ushbu ma'lumotlarni to'g'ri indekslashni amalga oshiring
jadvallar orasidagi ulanish shartlarining to'g'riligini tekshiring

Keyingi optimallashtirish iteratsiyasi quyidagilarni ochib beradi:

so'rovga asoslanadi va asosiy so'rov filtrini belgilaydi
shunga o'xshash so'rov bloklarini takrorlash va shartlarning kesishishini tahlil qilish
uchun SSMS yoki boshqa GUI-da SQL Server o'zini optimallashtiradi SQL so'rovi (oraliq ma'lumotlarni saqlash joyini ajratish, ushbu xotiradan foydalanib olingan so'rovni yaratish (bir nechta bo'lishi mumkin))
oxirgi bosqichda, natijani asos qilib oladi SQL so'rovi, struktura qayta qurilmoqda LINQ so'rovi

Olingan LINQ so'rovi aniqlangan optimalga tuzilishi jihatidan bir xil bo'lishi kerak SQL so'rovi 3-banddan.

Rahmatlar

Hamkasblarga katta rahmat jobgemws и alex_ozr kompaniyadan Fortis ushbu materialni tayyorlashda yordam uchun.

Manba: www.habr.com

C#.NET da LINQ so'rovlarini optimallashtirish usullari