🥇C#.NET'te LINQ sorgularını optimize etme yöntemleri

Giriş

В Bu makalede bazı optimizasyon yöntemleri dikkate alındı LINQ sorguları.
Burada aşağıdakilerle ilgili kod optimizasyonuna yönelik bazı yaklaşımlar sunuyoruz: LINQ sorguları.

Bu bilinmektedir LINQ(Dil-Entegre Sorgulama), bir veri kaynağını sorgulamak için basit ve kullanışlı bir dildir.

А LINQ'dan SQL'e DBMS'deki verilere erişmeye yönelik bir teknolojidir. Bu, sorguların bildirimsel bir dil aracılığıyla oluşturulduğu ve daha sonra dönüştürüleceği verilerle çalışmak için güçlü bir araçtır. SQL sorguları platforma aktarılır ve yürütülmek üzere veritabanı sunucusuna gönderilir. Bizim durumumuzda DBMS ile kast ettiğimiz MS SQL Server.

Bununla birlikte, LINQ sorguları en iyi şekilde yazılmış olanlara dönüştürülmez SQL sorgularıdeneyimli bir DBA'nın optimizasyonun tüm nüanslarıyla yazabileceği SQL sorguları:

optimum bağlantılar (JOIN) ve sonuçları filtrelemek (NEREDE)
bağlantıları ve grup koşullarını kullanmada birçok nüans
koşulların değiştirilmesinde birçok değişiklik IN üzerinde VARи DEĞİL, <> açık VAR
sonuçların geçici tablolar, CTE, tablo değişkenleri aracılığıyla ara önbelleğe alınması
cümle kullanımı (SEÇENEK) talimatlar ve tablo ipuçlarıyla birlikte İLE (...)
seçimler sırasında gereksiz veri okumalarından kurtulmanın yollarından biri olarak indekslenmiş görünümlerin kullanılması

Ortaya çıkan ana performans darboğazları SQL sorguları derlerken LINQ sorguları şunlardır:

tüm veri seçim mekanizmasının tek bir istekte birleştirilmesi
aynı kod bloklarının kopyalanması, sonuçta birden fazla gereksiz veri okumasına yol açar
çok bileşenli koşul grupları (mantıksal “ve” ve “veya”) - VE и OR, karmaşık koşullar halinde bir araya getirilmesi, gerekli alanlar için uygun kümelenmemiş dizinlere sahip olan optimize edicinin sonuçta kümelenmiş dizine karşı taramaya başlamasına neden olur (DİZİN TARAMA) koşul gruplarına göre
alt sorguların derinlemesine iç içe geçmesi ayrıştırmayı çok sorunlu hale getiriyor SQL ifadeleri ve geliştiriciler adına sorgu planının analizi ve DBA

Optimizasyon yöntemleri

Şimdi doğrudan optimizasyon yöntemlerine geçelim.

1) Ek indeksleme

Sorgunun tamamı genellikle bir veya iki ana tablo (uygulamalar-kişiler-işlemler) etrafında ve standart bir koşullar kümesiyle (Kapalı, İptal Edildi, Etkin, Durum) oluşturulduğundan, ana seçim tablolarındaki filtreleri dikkate almak en iyisidir. Belirlenen örneklere uygun indekslerin oluşturulması önemlidir.

Bu çözüm, bu alanları seçerken sorguya döndürülen kümeyi önemli ölçüde sınırladığında anlamlı olur.

Mesela 500000 başvurumuz var. Ancak sadece 2000 aktif başvuru var. O zaman doğru seçilmiş bir indeks bizi kurtaracaktır. DİZİN TARAMA büyük bir tabloda bulunur ve kümelenmemiş bir dizin aracılığıyla verileri hızlı bir şekilde seçmenize olanak tanır.

Ayrıca, dizinlerin eksikliği, sorgu planlarının ayrıştırılmasına veya sistem görünümü istatistiklerinin toplanmasına yönelik istemler aracılığıyla belirlenebilir. MS SQL Server:

Tüm görünüm verileri, uzamsal dizinler hariç, eksik dizinler hakkında bilgi içerir.

Bununla birlikte, dizinler ve önbelleğe alma genellikle kötü yazılmış hataların sonuçlarıyla mücadele etmenin yöntemleridir. LINQ sorguları и SQL sorguları.

Hayatın zorlu uygulamalarının gösterdiği gibi, bir işletmenin iş özelliklerini belirli son tarihlere kadar uygulamaya koyması genellikle önemlidir. Bu nedenle ağır istekler genellikle önbellekleme ile arka plana aktarılır.

Kullanıcı her zaman en son verilere ihtiyaç duymadığından ve kullanıcı arayüzünün kabul edilebilir düzeyde yanıt verme yeteneği olduğundan, bu kısmen haklıdır.

Bu yaklaşım iş ihtiyaçlarının çözülmesine olanak tanır, ancak sonuçta sorunların çözümünü geciktirerek bilgi sisteminin performansını azaltır.

Eklenecek gerekli indeksleri arama sürecinde önerilerin olduğunu da hatırlamakta fayda var. MS SQL Aşağıdaki koşullar da dahil olmak üzere optimizasyon hatalı olabilir:

benzer alan kümesine sahip dizinler zaten varsa
tablodaki alanlar indeksleme kısıtlamaları nedeniyle indekslenemiyorsa (daha ayrıntılı olarak açıklanmıştır) burada).

2) Niteliklerin yeni bir nitelik halinde birleştirilmesi

Bazen bir tablodaki bir grup koşula temel teşkil eden bazı alanlar, yeni bir alan eklenerek değiştirilebilir.

Bu özellikle bit veya tamsayı türünden olan durum alanları için geçerlidir.

Örnek:

Kapalı = 0 VE İptal Edildi = 0 VE Etkin = 0 ile değiştirilir Durum = 1.

Bu durumların tabloda doldurulmasını sağlamak için tamsayı Durum özelliğinin tanıtıldığı yer burasıdır. Daha sonra bu yeni özellik indekslenir.

Bu, performans sorununa temel bir çözümdür çünkü verilere gereksiz hesaplamalar yapmadan erişiriz.

3) Görünümün gerçekleştirilmesi

Ne yazık ki, içinde LINQ sorguları Geçici tablolar, CTE'ler ve tablo değişkenleri doğrudan kullanılamaz.

Ancak bu durumu optimize etmenin başka bir yolu daha var: indekslenmiş görünümler.

Koşul grubu (yukarıdaki örnekten) Kapalı = 0 VE İptal Edildi = 0 VE Etkin = 0 (veya bir dizi başka benzer koşul), büyük bir kümeden küçük bir veri dilimini önbelleğe alarak bunları dizine alınmış bir görünümde kullanmak için iyi bir seçenek haline gelir.

Ancak bir görünümü gerçekleştirirken bir takım kısıtlamalar vardır:

alt sorguların, cümleciklerin kullanımı VAR kullanılarak değiştirilmelidir. JOIN
cümle kullanamazsın BİRLİĞİ, BİRLİĞİ TÜMÜ, İSTİSNA, KESİŞİM
Tablo ipuçlarını ve cümleciklerini kullanamazsınız SEÇENEK
döngülerle çalışma imkanı yok
Farklı tablolardan verileri tek bir görünümde görüntülemek imkansızdır

Dizine alınmış bir görünüm kullanmanın gerçek faydasının yalnızca onu gerçekten dizine ekleyerek elde edilebileceğini unutmamak önemlidir.

Ancak bir görünümü çağırırken bu dizinler kullanılamayabilir ve bunları açıkça kullanmak için şunu belirtmeniz gerekir: İLE(NOEXPAND).

Beri LINQ sorguları Tablo ipuçlarını tanımlamak imkansızdır, bu nedenle başka bir gösterim - aşağıdaki biçimde bir "sarmalayıcı" - oluşturmanız gerekir:

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

4) Tablo işlevlerini kullanma

Genellikle LINQ sorguları Büyük alt sorgu blokları veya karmaşık yapıya sahip görünümler kullanan bloklar, çok karmaşık ve optimumun altında yürütme yapısına sahip son bir sorgu oluşturur.

Tablo İşlevlerini Kullanmanın Temel Faydaları LINQ sorguları:

Görünümlerde olduğu gibi bir nesne olarak kullanılma ve belirtilme yeteneği, ancak bir dizi giriş parametresini iletebilirsiniz:
FONKSİYONDAN(@param1, @param2 ...)
Sonuç olarak esnek veri örneklemesi sağlanabilir
Bir tablo fonksiyonunun kullanılması durumunda, yukarıda açıklanan indekslenmiş görünümlerde olduğu gibi güçlü kısıtlamalar yoktur:
1. Tablo ipuçları:
  sayesinde LINQ Sorgulama sırasında hangi indekslerin kullanılması gerektiğini belirleyemez ve veri izolasyon düzeyini belirleyemezsiniz.
  Ancak fonksiyon bu yeteneklere sahiptir.
  Bu fonksiyonla, indekslerle çalışma kurallarının ve veri izolasyon seviyelerinin tanımlandığı oldukça sabit bir yürütme sorgu planı elde edebilirsiniz.
2. Fonksiyonun kullanılması, indekslenmiş görünümlerle karşılaştırıldığında aşağıdakilerin elde edilmesine olanak sağlar:
  - karmaşık veri örnekleme mantığı (döngüler kullanıldığında bile)
  - birçok farklı tablodan veri almak
  - kullanmak BİRLİĞİ и VAR
Öneri SEÇENEK eşzamanlılık kontrolü sağlamamız gerektiğinde çok kullanışlıdır SEÇENEK(MAXDOP N), sorgu yürütme planının sırası. Örneğin:
- sorgu planının zorunlu olarak yeniden oluşturulmasını belirtebilirsiniz SEÇENEK (YENİDEN DERLEME)
- sorgu planının sorguda belirtilen birleştirme sırasını kullanmaya zorlanıp zorlanmayacağını belirtebilirsiniz SEÇENEK (SİPARİŞİ ZORLA)
Hakkında daha fazla ayrıntı SEÇENEK tarif burada.
En dar ve en gerekli veri dilimini kullanma:
Büyük veri kümelerini önbelleklerde saklamanıza gerek yoktur (dizine alınmış görünümlerde olduğu gibi), buradan da verileri parametrelere göre filtrelemeniz gerekir.
Örneğin, filtresi olan bir tablo var. NEREDE üç alan kullanılıyor (a, b, c).

Geleneksel olarak tüm isteklerin sabit bir koşulu vardır a = 0 ve b = 0.

Ancak saha talebi c daha değişken.

Koşul olsun a = 0 ve b = 0 Gerekli sonuç kümesini binlerce kayıtla sınırlandırmamıza gerçekten yardımcı oluyor, ancak с seçimi yüz kayda kadar daraltır.

Burada tablo işlevi daha iyi bir seçenek olabilir.

Ayrıca, bir tablo işlevi yürütme süresi açısından daha öngörülebilir ve tutarlıdır.

Örnekler

Soru veritabanını örnek olarak kullanan örnek bir uygulamaya bakalım.

Bir istek var SEÇİNBirkaç tabloyu birleştiren ve üyeliğin e-postayla kontrol edildiği tek bir görünüm (OperativeQuestions) kullanan ( VAR) "Operatif Sorular"a:

1 No'lu Talep

(@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üm oldukça karmaşık bir yapıya sahiptir: alt sorguların birleşimini içerir ve sıralamayı kullanır DISTINCT, genel olarak oldukça kaynak yoğun bir işlemdir.

OperativeQuestions'tan alınan bir örnek yaklaşık on bin kayıttır.

Bu sorgudaki ana sorun, dış sorgudaki kayıtlar için, [OperativeQuestions] görünümünde dahili bir alt sorgunun yürütülmesidir; bu, [E-posta] = @p__linq__0 için çıktı seçimini sınırlamamıza izin vermelidir (üzerinden) VAR) yüzlerce kayda kadar.

Ve alt sorgunun kayıtları [Email] = @p__linq__0 ile bir kez hesaplaması gerektiği ve ardından bu birkaç yüz kaydın Id ile Sorular ile bağlanması gerektiği ve sorgunun hızlı olacağı gibi görünebilir.

Aslında, tüm tabloların sıralı bir bağlantısı vardır: Kimlik Sorularının OperativeQuestion'daki Id ile yazışmalarının kontrol edilmesi ve E-posta ile filtreleme.

Aslında istek, onbinlerce OperativeQuestions kaydının tamamıyla çalışır, ancak E-posta yoluyla yalnızca ilgilenilen verilere ihtiyaç vardır.

Operatif Sorular görünüm metni:

2 No'lu Talep

 
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'te ilk görünüm eşlemesi (EF Core 2)

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

İlk LINQ sorgusu

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 özel durumda, altyapı değişiklikleri olmadan, hazır sonuçları içeren ayrı bir tablo ("Aktif Sorgular") sunmadan, bu tabloyu verilerle doldurmak ve güncel tutmak için bir mekanizma gerektirmeden bu soruna bir çözüm düşünüyoruz. .

Bu iyi bir çözüm olsa da, bu sorunu optimize etmek için başka bir seçenek daha var.

Ana amaç, OperativeQuestions görünümündeki girişleri [Email] = @p__linq__0 ile önbelleğe almaktır.

Veritabanına [dbo].[OperativeQuestionsUserMail] tablo fonksiyonunu ekleyin.

E-postayı giriş parametresi olarak göndererek bir değerler tablosu elde ederiz:

3 No'lu Talep


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, önceden tanımlanmış bir veri yapısına sahip bir değerler tablosu döndürür.

OperativeQuestionsUserMail'e yapılan sorguların optimal olması ve optimal sorgu planlarına sahip olması için sıkı bir yapı gereklidir. DÖNÜŞ OLARAK DÖNÜŞ TABLOSU...

Bu durumda gerekli Sorgu 1, Sorgu 4'e dönüştürülür:

4 No'lu Talep

(@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'te görünümleri ve işlevleri eşleme (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 sorgusu

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

Yürütme sırası 200-800 ms'den 2-20 ms'ye vs. düştü, yani onlarca kat daha hızlı.

Daha ortalama alırsak 350 ms yerine 8 ms elde ederiz.

Ayrıca elde ettiğimiz bariz avantajlardan:

okuma yükünde genel azalma,
engelleme olasılığında önemli azalma
ortalama engelleme süresinin kabul edilebilir değerlere düşürülmesi

Aviator apk

Veritabanı çağrılarının optimizasyonu ve ince ayarı MS SQL sayesinde LINQ çözülebilecek bir sorundur.

Bu çalışmada dikkat ve tutarlılık çok önemlidir.

Sürecin başında:

isteğin çalıştığı verileri (değerler, seçilen veri türleri) kontrol etmek gerekir
Bu verilerin uygun şekilde indekslenmesini sağlayın
tablolar arasındaki birleştirme koşullarının doğruluğunu kontrol edin

Bir sonraki optimizasyon yinelemesi şunları ortaya koyuyor:

isteği temel alır ve ana istek filtresini tanımlar
benzer sorgu bloklarını tekrarlamak ve koşulların kesişimini analiz etmek
SSMS'de veya başka bir GUI'de SQL Server kendini optimize eder SQL sorgusu (bir ara veri deposu tahsis etmek, bu depolamayı kullanarak ortaya çıkan sorguyu oluşturmak (birkaç tane olabilir))
son aşamada elde edilen sonuçları esas alarak SQL sorgusuyapı yeniden inşa ediliyor LINQ sorgusu

Sonuç LINQ sorgusu Yapı olarak tanımlanan optimalle aynı hale gelmelidir SQL sorgusu 3. noktadan itibaren.

Teşekkür

Meslektaşlarımıza çok teşekkür ederiz işgemws и alex_ozr şirketten Fortis Bu materyalin hazırlanmasında yardım için.

Kaynak: habr.com

C#.NET'te LINQ sorgularını iyileştirme yöntemleri