🥇C#.NET |-də LINQ sorğularının optimallaşdırılması üsulları ProHoster

Giriş

В Bu məqalə bəzi optimallaşdırma üsulları nəzərdən keçirilmişdir LINQ sorğuları.
Burada kodun optimallaşdırılması ilə bağlı daha bir neçə yanaşma təqdim edirik LINQ sorğuları.

Məlumdur ki, LINQ(Language-Integrated Query) məlumat mənbəyini sorğulamaq üçün sadə və rahat dildir.

А LINQ to SQL DBMS-də verilənlərə daxil olmaq üçün texnologiyadır. Bu, sorğuların deklarativ dil vasitəsilə qurulduğu və sonra çevriləcək verilənlərlə işləmək üçün güclü bir vasitədir. SQL sorğuları platforma və icra üçün verilənlər bazası serverinə göndərilir. Bizim vəziyyətimizdə DBMS dedikdə biz nəzərdə tuturuq MS SQL Server.

Lakin, LINQ sorğuları optimal şəkildə yazılmışlara çevrilmir SQL sorğuları, təcrübəli DBA optimallaşdırmanın bütün nüansları ilə yaza bilər SQL sorğuları:

optimal əlaqələr (OL) və nəticələrin filtrlənməsi (HARADA)
əlaqələrin və qrup şərtlərinin istifadəsində bir çox nüanslar
şərtləri əvəz edən bir çox variasiya IN haqqında Mövcuddurи DEYİL, <> açıq Mövcuddur
müvəqqəti cədvəllər, CTE, cədvəl dəyişənləri vasitəsilə nəticələrin aralıq keşləşdirilməsi
cümlənin istifadəsi (OPTION) təlimatlar və cədvəl göstərişləri ilə İLƏ (...)
seçimlər zamanı lazımsız məlumat oxunuşlarından xilas olmaq üçün vasitələrdən biri kimi indeksləşdirilmiş görünüşlərdən istifadə

Nəticədə ortaya çıxan əsas performans darboğazları SQL sorğuları tərtib edərkən LINQ sorğuları aşağıdakılardır:

bütün məlumat seçimi mexanizminin bir sorğuda birləşdirilməsi
eyni kod bloklarının təkrarlanması, nəticədə çoxlu lazımsız məlumatların oxunmasına səbəb olur
çoxkomponentli şərtlər qrupları (məntiqi “və” və “və ya”) - VƏ и OR, mürəkkəb şərtlərdə birləşərək, lazımi sahələr üçün uyğun olmayan qruplaşdırılmamış indekslərə sahib olan optimallaşdırıcının sonda çoxluq indeksinə qarşı skan etməyə başlamasına səbəb olur (İNDEKS TARAMA) şərait qrupları üzrə
alt sorğuların dərin yuvalanması təhlili çox problemli edir SQL ifadələri və tərtibatçılar tərəfindən sorğu planının təhlili və DBA

Optimallaşdırma üsulları

İndi birbaşa optimallaşdırma üsullarına keçək.

1) Əlavə indeksləşdirmə

Əsas seçim cədvəllərində filtrləri nəzərdən keçirmək daha yaxşıdır, çünki çox vaxt bütün sorğu bir və ya iki əsas cədvəl (tətbiqlər-insanlar-əməliyyatlar) ətrafında və standart şərtlər dəsti ilə (Qapalı, Ləğv edilib, Aktiv, Vəziyyət) qurulur. Müəyyən edilmiş nümunələr üçün müvafiq indekslərin yaradılması vacibdir.

Bu sahələr seçildikdə bu həll məna verir ki, sorğuya qaytarılan dəsti əhəmiyyətli dərəcədə məhdudlaşdırır.

Məsələn, 500000 min müraciətimiz var. Ancaq cəmi 2000 aktiv tətbiq var. Sonra düzgün seçilmiş indeks bizi bundan xilas edəcək İNDEKS TARAMA böyük bir cədvəldə və qeyri-klasterli indeks vasitəsilə məlumatları tez seçməyə imkan verəcəkdir.

Həmçinin, indekslərin olmaması sorğu planlarını təhlil etmək və ya sistem görünüşü statistikasını toplamaq üçün göstərişlər vasitəsilə müəyyən edilə bilər. MS SQL Server:

Bütün görünüş məlumatlarında məkan indeksləri istisna olmaqla, çatışmayan indekslər haqqında məlumat var.

Bununla belə, indekslər və keşləmə tez-tez pis yazılmış nəticələrlə mübarizə üsullarıdır LINQ sorğuları и SQL sorğuları.

Həyatın sərt təcrübəsinin göstərdiyi kimi, bir iş üçün müəyyən müddət ərzində biznes xüsusiyyətlərini həyata keçirmək çox vaxt vacibdir. Buna görə də, ağır sorğular tez-tez keşləmə ilə arxa plana keçirilir.

Bu, qismən haqlıdır, çünki istifadəçi həmişə ən son məlumatlara ehtiyac duymur və istifadəçi interfeysinin məqbul cavab səviyyəsi var.

Bu yanaşma biznes ehtiyaclarını həll etməyə imkan verir, lakin son nəticədə problemlərin həllini sadəcə gecikdirməklə informasiya sisteminin işini azaldır.

Əlavə etmək üçün lazımi indekslərin axtarışı prosesində təkliflərin olduğunu da xatırlamağa dəyər MS SQL optimallaşdırma, o cümlədən aşağıdakı şərtlərdə səhv ola bilər:

oxşar sahələr dəstinə malik indekslər artıq varsa
cədvəldəki sahələr indeksləşdirmə məhdudiyyətlərinə görə indeksləşdirilə bilmirsə (daha ətraflı təsvir edilmişdir) burada).

2) Atributların bir yeni atributda birləşdirilməsi

Bəzən bir cədvəldən bir qrup şərtlər üçün əsas kimi xidmət edən bəzi sahələr bir yeni sahənin təqdim edilməsi ilə əvəz edilə bilər.

Bu, adətən bit və ya tam tipli status sahələri üçün xüsusilə doğrudur.

Misal:

Qapalı = 0 VƏ Ləğv = 0 VƏ Aktiv = 0 ilə əvəz olunur Vəziyyət = 1.

Bu statusların cədvəldə doldurulmasını təmin etmək üçün tam Status atributunun təqdim edildiyi yer budur. Sonra bu yeni atribut indeksləşdirilir.

Bu, performans probleminin əsas həllidir, çünki Biz lazımsız hesablamalar olmadan məlumatlara daxil oluruq.

3) Görünüşün maddiləşdirilməsi

Təəssüf ki, daxil LINQ sorğuları Müvəqqəti cədvəllər, CTE-lər və cədvəl dəyişənləri birbaşa istifadə edilə bilməz.

Bununla belə, bu iş üçün optimallaşdırmağın başqa bir yolu var - indeksləşdirilmiş görünüşlər.

Şərt qrupu (yuxarıdakı nümunədən) Qapalı = 0 VƏ Ləğv = 0 VƏ Aktiv = 0 (və ya digər oxşar şərtlər toplusu) böyük dəstdən kiçik bir dilim məlumatı keşləməklə onları indeksləşdirilmiş görünüşdə istifadə etmək üçün yaxşı seçim olur.

Ancaq bir görünüşü həyata keçirərkən bir sıra məhdudiyyətlər var:

alt sorğuların, bəndlərin istifadəsi Mövcuddur istifadə etməklə əvəz edilməlidir OL
cümlələrdən istifadə edə bilməzsən UNION, BÜTÜN Birlik, TƏRCÜMƏ, QARŞI
Cədvəl göstərişlərindən və bəndlərdən istifadə edə bilməzsiniz OPTION
dövrələrlə işləmək imkanı yoxdur
Müxtəlif cədvəllərdən verilənləri bir görünüşdə göstərmək mümkün deyil

Yadda saxlamaq lazımdır ki, indeksləşdirilmiş görünüşdən istifadənin real faydası yalnız onu faktiki indeksləşdirməklə əldə edilə bilər.

Ancaq bir görünüş çağırarkən, bu indekslər istifadə edilə bilməz və onlardan açıq şəkildə istifadə etmək üçün siz müəyyən etməlisiniz İLƏ(YOK GENİŞLEME).

İldən LINQ sorğuları Cədvəl göstərişlərini müəyyən etmək qeyri-mümkündür, ona görə də başqa bir təqdimat yaratmalısınız - aşağıdakı formanın "sarğı":

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

4) Cədvəl funksiyalarından istifadə

Tez-tez daxil LINQ sorğuları Böyük alt sorğu blokları və ya mürəkkəb strukturlu görünüşlərdən istifadə edən bloklar çox mürəkkəb və optimal olmayan icra strukturu ilə yekun sorğu təşkil edir.

Cədvəl funksiyalarından istifadənin əsas üstünlükləri LINQ sorğuları:

Görünüşlərdə olduğu kimi, obyekt kimi istifadə etmək və təyin etmək qabiliyyəti, lakin bir sıra giriş parametrlərini ötürə bilərsiniz:
FROM FUNCTION(@param1, @param2 ...)
Nəticədə çevik məlumat seçilməsi əldə edilə bilər
Cədvəl funksiyasından istifadə edildiyi təqdirdə, yuxarıda təsvir edilmiş indeksləşdirilmiş görünüşlərdə olduğu kimi güclü məhdudiyyətlər yoxdur:
1. Cədvəl göstərişləri:
  vasitəsilə LINQ Sorğu zamanı hansı indekslərin istifadə olunacağını və məlumatların izolyasiya səviyyəsini təyin edə bilməzsiniz.
  Lakin funksiya bu imkanlara malikdir.
  Funksiya ilə indekslərlə işləmə qaydaları və məlumatların izolyasiya səviyyələrinin müəyyən edildiyi kifayət qədər sabit icra sorğu planına nail ola bilərsiniz.
2. Funksiyadan istifadə indeksləşdirilmiş görünüşlərlə müqayisədə aşağıdakıları əldə etməyə imkan verir:
  - mürəkkəb məlumat seçmə məntiqi (hətta döngələrdən istifadə etməklə)
  - bir çox müxtəlif cədvəllərdən məlumatların alınması
  - istifadə UNION и Mövcuddur
Təklif et OPTION paralellik nəzarətini təmin etməmiz lazım olduqda çox faydalıdır OPSİYON(MAXDOP N), sorğunun icra planının sırası. Misal üçün:
- sorğu planının məcburi yenidən yaradılmasını təyin edə bilərsiniz OPSİYON (YENİDƏN TƏRƏB EDİN)
- sorğu planını sorğuda göstərilən birləşmə sırasından istifadə etməyə məcbur edib-etməməyi təyin edə bilərsiniz OPSİYON (MƏCBUR SİPARİŞ)
Haqqında ətraflı məlumat OPTION təsvir edilmişdir burada.
Ən dar və ən çox tələb olunan məlumat dilimindən istifadə:
Böyük məlumat dəstlərini keşlərdə saxlamağa ehtiyac yoxdur (indekslənmiş görünüşlərdə olduğu kimi), onlardan hələ də məlumatları parametrə görə filtrləməlisiniz.
Məsələn, filtri olan bir cədvəl var HARADA üç sahə istifadə olunur (a, b, c).

Şərti olaraq, bütün sorğuların daimi şərti var a = 0 və b = 0.

Ancaq sahə tələbi c daha çox dəyişkəndir.

Şərt olsun a = 0 və b = 0 Bu, həqiqətən bizə tələb olunan nəticə dəstini minlərlə qeydlə məhdudlaşdırmağa kömək edir, lakin şərt açıqdır с seçimi yüz qeydə qədər daraldır.

Burada cədvəl funksiyası daha yaxşı seçim ola bilər.

Həmçinin, cədvəl funksiyası icra müddətində daha proqnozlaşdırıla bilən və ardıcıldır.

Nümunələr

Nümunə olaraq Questions verilənlər bazasından istifadə edərək həyata keçirilən nümunəyə baxaq.

Bir xahiş var SELECT, bir neçə cədvəli birləşdirən və bir görünüşdən istifadə edən (OperativeQuestions), mənsubiyyətin e-poçt vasitəsilə yoxlanıldığı (vasitəsilə) Mövcuddur) "Əməliyyat sualları"na:

Sorğu №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])
));

Görünüşün kifayət qədər mürəkkəb strukturu var: onun alt sorğu birləşmələri var və çeşidləmədən istifadə edir fərqli, bu ümumiyyətlə kifayət qədər resurs tələb edən əməliyyatdır.

OperativeQuestions-dan bir nümunə təxminən on min qeyddir.

Bu sorğu ilə bağlı əsas problem ondan ibarətdir ki, xarici sorğudan olan qeydlər üçün [OperativeQuestions] görünüşündə daxili alt sorğu yerinə yetirilir ki, bu da [Email] = @p__linq__0 üçün çıxış seçimini məhdudlaşdırmağa imkan verməlidir (vasitəsilə Mövcuddur) yüzlərlə qeydə qədər.

Və belə görünə bilər ki, alt sorğu [Email] = @p__linq__0 ilə qeydləri bir dəfə hesablamalıdır və sonra bu bir neçə yüz qeyd Suallarla Id ilə birləşdirilməlidir və sorğu sürətli olacaq.

Əslində, bütün cədvəllərin ardıcıl əlaqəsi var: Id Suallarının OperativeQuestions-dan Id ilə uyğunluğunu yoxlamaq və E-poçtla filtrasiya.

Əslində, sorğu bütün on minlərlə OperativeQuestions qeydləri ilə işləyir, lakin E-poçt vasitəsilə yalnız maraq məlumatları tələb olunur.

Operativ Suallar mətnə baxın:

Sorğu №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-də ilkin görünüş xəritəsi (EF Core 2)

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

İlkin LINQ sorğusu

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

Bu konkret halda, biz infrastruktur dəyişiklikləri olmadan, hazır nəticələri olan ayrıca cədvəl (“Aktiv sorğular”) təqdim etmədən bu problemin həllini nəzərdən keçiririk ki, bu da onu məlumatlarla doldurmaq və aktual saxlamaq mexanizmini tələb edir. .

Bu yaxşı bir həll olsa da, bu problemi optimallaşdırmaq üçün başqa bir seçim var.

Əsas məqsəd [Email] = @p__linq__0 tərəfindən OperativeQuestions görünüşündən qeydləri keş etməkdir.

[dbo].[OperativeQuestionsUserMail] cədvəl funksiyasını verilənlər bazasına daxil edin.

Giriş parametri kimi E-poçt göndərməklə, biz dəyərlər cədvəlini geri alırıq:

Sorğu №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, əvvəlcədən təyin edilmiş məlumat strukturu ilə dəyərlər cədvəlini qaytarır.

OperativeQuestionsUserMail-ə sorğuların optimal olması və optimal sorğu planlarına malik olması üçün ciddi struktur tələb olunur. CƏDVƏL QAYTARIŞ KİMİ QAYTARIR...

Bu halda, tələb olunan Sorğu 1 Sorğu 4-ə çevrilir:

Sorğu №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-də xəritəçəkmə görünüşləri və funksiyaları (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})");
}

Son LINQ sorğusu

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

İcra müddətinin sırası 200-800 ms-dən 2-20 ms-ə və s., yəni onlarla dəfə daha sürətli.

Daha orta hesabla götürsək, 350 ms əvəzinə 8 ms aldıq.

Aşkar üstünlüklərdən biz də əldə edirik:

oxu yükünün ümumi azalması,
bloklanma ehtimalının əhəmiyyətli dərəcədə azalması
orta bloklama vaxtının məqbul dəyərlərə endirilməsi

Buraxılış

Verilənlər bazası zənglərinin optimallaşdırılması və dəqiq tənzimlənməsi MS SQL vasitəsilə LINQ həll edilə bilən problemdir.

Bu işdə diqqətlilik və ardıcıllıq çox vacibdir.

Prosesin əvvəlində:

sorğunun işlədiyi məlumatları yoxlamaq lazımdır (dəyərlər, seçilmiş məlumat növləri)
bu məlumatların düzgün indeksləşdirilməsini həyata keçirin
cədvəllər arasında birləşmə şərtlərinin düzgünlüyünü yoxlayın

Növbəti optimallaşdırma iterasiyası aşkar edir:

sorğunun əsasını təşkil edir və əsas sorğu filtrini müəyyənləşdirir
oxşar sorğu bloklarının təkrarlanması və şərtlərin kəsişməsinin təhlili
SSMS və ya digər GUI-də SQL Server özünü optimallaşdırır SQL sorğusu (aralıq məlumat yaddaşının ayrılması, bu yaddaşdan istifadə edərək əldə edilən sorğunun qurulması (bir neçə ola bilər))
son mərhələdə nəticəni əsas götürərək SQL sorğusu, struktur yenidən qurulur LINQ sorğusu

Nəticədə LINQ sorğusu müəyyən edilmiş optimalla strukturca eyni olmalıdır SQL sorğusu 3-cü bənddən.

Təşəkkürlər

Həmkarlara çox sağ olun jobgemws и alex_ozr şirkətdən Fortis bu materialın hazırlanmasında kömək üçün.

Mənbə: www.habr.com

C#.NET-də LINQ sorğularının optimallaşdırılması üsulları