🥇Methoden zur Optimierung von LINQ-Abfragen in C#.NET

Einführung

В Dieser Artikel einige Optimierungsmethoden wurden berücksichtigt LINQ-Abfragen.
Hier stellen wir auch einige weitere Ansätze zur Codeoptimierung im Zusammenhang mit vor LINQ-Abfragen.

Es ist bekannt, dass LINQ(Language-Integrated Query) ist eine einfache und praktische Sprache zum Abfragen einer Datenquelle.

А LINQ zu SQL ist eine Technologie für den Zugriff auf Daten in einem DBMS. Hierbei handelt es sich um ein leistungsstarkes Tool für die Arbeit mit Daten, bei dem Abfragen mithilfe einer deklarativen Sprache erstellt und dann in eine deklarative Sprache konvertiert werden SQL-Abfragen Plattform erstellt und zur Ausführung an den Datenbankserver gesendet. In unserem Fall meinen wir mit DBMS MS SQL Server.

Jedoch LINQ-Abfragen werden nicht in optimal geschriebene umgewandelt SQL-Abfragen, das ein erfahrener DBA mit allen Nuancen der Optimierung schreiben könnte SQL-Abfragen:

optimale Verbindungen (JOIN) und Filtern der Ergebnisse (WO)
viele Nuancen bei der Verwendung von Verbindungen und Gruppenbedingungen
viele Variationen bei den Ersetzungsbedingungen IN auf VORHANDENи NICHT IN, <> an VORHANDEN
Zwischenspeicherung der Ergebnisse über temporäre Tabellen, CTE, Tabellenvariablen
Verwendung des Satzes (zur Auswahl) mit Anleitungen und Tabellenhinweisen MIT (...)
Verwenden Sie indizierte Ansichten als eine Möglichkeit, redundante Datenlesungen während der Auswahl zu vermeiden

Die wichtigsten Leistungsengpässe sind die Folge SQL-Abfragen beim Kompilieren LINQ-Abfragen sind:

Konsolidierung des gesamten Datenauswahlmechanismus in einer Anfrage
Das Duplizieren identischer Codeblöcke führt letztendlich zu mehreren unnötigen Datenlesevorgängen
Gruppen von mehrkomponentigen Bedingungen (logisches „und“ und „oder“) – UND и ORDie Kombination zu komplexen Bedingungen führt dazu, dass der Optimierer, der über geeignete nicht gruppierte Indizes für die erforderlichen Felder verfügt, letztendlich beginnt, anhand des gruppierten Index zu scannen (INDEX-SCAN) nach Gruppen von Bedingungen
Eine tiefe Verschachtelung von Unterabfragen macht das Parsen sehr problematisch SQL-Anweisungen und Analyse des Abfrageplans seitens der Entwickler und DBA

Optimierungsmethoden

Kommen wir nun direkt zu den Optimierungsmethoden.

1) Zusätzliche Indizierung

Es ist am besten, Filter für die Hauptauswahltabellen in Betracht zu ziehen, da sehr oft die gesamte Abfrage auf einer oder zwei Haupttabellen (Anwendungen-Personen-Operationen) und mit einem Standardsatz von Bedingungen (IstGeschlossen, Abgebrochen, Aktiviert, Status) aufgebaut ist. Es ist wichtig, geeignete Indizes für die identifizierten Proben zu erstellen.

Diese Lösung ist sinnvoll, wenn die Auswahl dieser Felder den zurückgegebenen Satz auf die Abfrage erheblich einschränkt.

Wir haben zum Beispiel 500000 Bewerbungen. Allerdings gibt es nur 2000 aktive Bewerbungen. Dann wird uns ein richtig ausgewählter Index davor bewahren INDEX-SCAN auf einer großen Tabelle und ermöglicht Ihnen die schnelle Auswahl von Daten über einen nicht gruppierten Index.

Das Fehlen von Indizes kann auch durch Eingabeaufforderungen zum Parsen von Abfrageplänen oder zum Sammeln von Systemansichtsstatistiken festgestellt werden MS SQL Server:

Alle Ansichtsdaten enthalten Informationen zu fehlenden Indizes, mit Ausnahme räumlicher Indizes.

Allerdings sind Indizes und Caching oft Methoden, um die Folgen von mangelhaftem Schreiben zu bekämpfen LINQ-Abfragen и SQL-Abfragen.

Wie die harte Praxis des Lebens zeigt, ist es für ein Unternehmen oft wichtig, Geschäftsfunktionen innerhalb bestimmter Fristen umzusetzen. Und deshalb werden schwere Anfragen durch Caching oft in den Hintergrund verlagert.

Dies ist teilweise gerechtfertigt, da der Benutzer nicht immer die neuesten Daten benötigt und die Reaktionsfähigkeit der Benutzeroberfläche akzeptabel ist.

Dieser Ansatz ermöglicht die Lösung von Geschäftsanforderungen, verringert jedoch letztendlich die Leistung des Informationssystems, indem die Lösung von Problemen einfach verzögert wird.

Denken Sie auch daran, dass bei der Suche nach den erforderlichen Indizes Vorschläge gemacht werden müssen MS SQL Die Optimierung kann fehlerhaft sein, auch unter den folgenden Bedingungen:

wenn es bereits Indizes mit einem ähnlichen Feldsatz gibt
wenn die Felder in der Tabelle aufgrund von Indizierungsbeschränkungen nicht indiziert werden können (näher beschrieben). hier).

2) Attribute zu einem neuen Attribut zusammenführen

Manchmal können einige Felder aus einer Tabelle, die als Grundlage für eine Gruppe von Bedingungen dienen, durch die Einführung eines neuen Felds ersetzt werden.

Dies gilt insbesondere für Statusfelder, die normalerweise entweder vom Typ Bit oder Ganzzahl sind.

Beispiel:

IsClosed = 0 UND Abgebrochen = 0 UND Aktiviert = 0 ersetzt durch Stand = 1.

Hier wird das ganzzahlige Statusattribut eingeführt, um sicherzustellen, dass diese Status in die Tabelle eingetragen werden. Als nächstes wird dieses neue Attribut indiziert.

Dies ist eine grundlegende Lösung des Leistungsproblems, da wir ohne unnötige Berechnungen auf Daten zugreifen.

3) Materialisierung der Ansicht

Leider in LINQ-Abfragen Temporäre Tabellen, CTEs und Tabellenvariablen können nicht direkt verwendet werden.

Es gibt jedoch eine andere Möglichkeit zur Optimierung für diesen Fall – indizierte Ansichten.

Bedingungsgruppe (aus obigem Beispiel) IsClosed = 0 UND Abgebrochen = 0 UND Aktiviert = 0 (oder eine Reihe anderer ähnlicher Bedingungen) wird zu einer guten Option, um sie in einer indizierten Ansicht zu verwenden und einen kleinen Datenausschnitt aus einer großen Menge zwischenzuspeichern.

Beim Materialisieren einer Ansicht gibt es jedoch eine Reihe von Einschränkungen:

Verwendung von Unterabfragen, Klauseln VORHANDEN sollte durch using ersetzt werden JOIN
Du kannst keine Sätze verwenden UNION, UNION ALL, AUSNAHME, INTERSECT
Sie können keine Tabellenhinweise und -klauseln verwenden zur Auswahl
keine Möglichkeit, mit Zyklen zu arbeiten
Es ist nicht möglich, Daten aus verschiedenen Tabellen in einer Ansicht anzuzeigen

Es ist wichtig zu bedenken, dass der wahre Nutzen der Verwendung einer indizierten Ansicht nur durch die tatsächliche Indizierung erzielt werden kann.

Beim Aufrufen einer Ansicht dürfen diese Indizes jedoch nicht verwendet werden. Um sie explizit zu verwenden, müssen Sie sie angeben WITH(NOEXPAND).

Seit in LINQ-Abfragen Es ist nicht möglich, Tabellenhinweise zu definieren, daher müssen Sie eine andere Darstellung erstellen – einen „Wrapper“ der folgenden Form:

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

4) Verwendung von Tabellenfunktionen

Oft in LINQ-Abfragen Große Blöcke von Unterabfragen oder Blöcke, die Ansichten mit komplexer Struktur verwenden, bilden eine endgültige Abfrage mit einer sehr komplexen und suboptimalen Ausführungsstruktur.

Hauptvorteile der Verwendung von Tabellenfunktionen in LINQ-Abfragen:

Die Möglichkeit, wie bei Ansichten, als Objekt verwendet und angegeben zu werden, Sie können jedoch eine Reihe von Eingabeparametern übergeben:
VON FUNKTION(@param1, @param2 ...)
Dadurch kann eine flexible Datenerfassung erreicht werden
Bei der Verwendung einer Tabellenfunktion gibt es keine so starken Einschränkungen wie bei den oben beschriebenen indizierten Ansichten:
1. Tabellenhinweise:
  durch LINQ Sie können bei der Abfrage nicht angeben, welche Indizes verwendet werden sollen, und die Datenisolationsstufe bestimmen.
  Aber die Funktion verfügt über diese Fähigkeiten.
  Mit der Funktion können Sie einen ziemlich konstanten Ausführungsplan für Abfragen erreichen, in dem Regeln für die Arbeit mit Indizes und Datenisolationsstufen definiert werden
2. Mit der Funktion können Sie im Vergleich zu indizierten Ansichten Folgendes erhalten:
  - komplexe Datenerfassungslogik (sogar unter Verwendung von Schleifen)
  - Abrufen von Daten aus vielen verschiedenen Tabellen
  - verwenden UNION и VORHANDEN
Vorschlag zur Auswahl Sehr nützlich, wenn wir eine Parallelitätskontrolle bereitstellen müssen OPTION(MAXDOP N), die Reihenfolge des Abfrageausführungsplans. Zum Beispiel:
- Sie können eine erzwungene Neuerstellung des Abfrageplans festlegen OPTION (Neukompilieren)
- Sie können angeben, ob der Abfrageplan gezwungen werden soll, die in der Abfrage angegebene Verknüpfungsreihenfolge zu verwenden OPTION (ANORDNUNG ZWISCHEN)
Weitere Details zu zur Auswahl beschrieben hier.
Verwendung des schmalsten und am meisten benötigten Datenausschnitts:
Es ist nicht erforderlich, große Datensätze in Caches zu speichern (wie es bei indizierten Ansichten der Fall ist), aus denen Sie die Daten weiterhin nach Parametern filtern müssen.
Beispielsweise gibt es eine Tabelle, deren Filter WO Es werden drei Felder verwendet (a, b, c).

Herkömmlicherweise haben alle Anfragen eine konstante Bedingung a = 0 und b = 0.

Allerdings ist die Anfrage für das Feld c variabler.

Lassen Sie den Zustand a = 0 und b = 0 Es hilft uns wirklich, die erforderliche Ergebnismenge auf Tausende von Datensätzen zu beschränken, aber die Bedingung ist erfüllt с grenzt die Auswahl auf hundert Datensätze ein.

Hier ist möglicherweise die Tabellenfunktion eine bessere Option.

Außerdem ist eine Tabellenfunktion vorhersehbarer und konsistenter in der Ausführungszeit.

Примеры

Schauen wir uns eine Beispielimplementierung am Beispiel der Questions-Datenbank an.

Es liegt eine Anfrage vor SELECT, das mehrere Tabellen kombiniert und eine Ansicht (OperativeQuestions) verwendet, in der die Zugehörigkeit per E-Mail überprüft wird (via VORHANDEN) zu „Operative Fragen“:

Anfrage Nr. 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])
));

Die Ansicht hat eine ziemlich komplexe Struktur: Sie verfügt über Unterabfrage-Joins und verwendet Sortierung DISTINCT, was im Allgemeinen ein ziemlich ressourcenintensiver Vorgang ist.

Eine Stichprobe von OperativeQuestions umfasst etwa zehntausend Datensätze.

Das Hauptproblem bei dieser Abfrage besteht darin, dass für die Datensätze aus der äußeren Abfrage eine interne Unterabfrage in der Ansicht [OperativeQuestions] ausgeführt wird, die es uns für [Email] = @p__linq__0 ermöglichen sollte, die Ausgabeauswahl einzuschränken (über VORHANDEN) bis zu Hunderten von Datensätzen.

Und es könnte den Anschein haben, dass die Unterabfrage die Datensätze einmal durch [Email] = @p__linq__0 berechnen sollte und dann diese paar hundert Datensätze durch Id mit Fragen verbunden werden sollten, und die Abfrage wird schnell sein.

Tatsächlich gibt es eine sequentielle Verbindung aller Tabellen: Überprüfung der Übereinstimmung von Id-Fragen mit Id von OperativeQuestions und Filterung nach E-Mail.

Tatsächlich funktioniert die Anfrage mit allen Zehntausenden von OperativeQuestions-Datensätzen, es werden jedoch nur die gewünschten Daten per E-Mail benötigt.

OperativeQuestions-Ansichtstext:

Anfrage Nr. 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));

Erste Ansichtszuordnung in 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");
    }
}

Erste LINQ-Abfrage

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

In diesem speziellen Fall erwägen wir eine Lösung dieses Problems ohne infrastrukturelle Änderungen, ohne die Einführung einer separaten Tabelle mit vorgefertigten Ergebnissen („Active Queries“), die einen Mechanismus erfordern würde, um sie mit Daten zu füllen und auf dem neuesten Stand zu halten .

Obwohl dies eine gute Lösung ist, gibt es eine weitere Möglichkeit, dieses Problem zu optimieren.

Der Hauptzweck besteht darin, Einträge von [Email] = @p__linq__0 aus der OperativeQuestions-Ansicht zwischenzuspeichern.

Führen Sie die Tabellenfunktion [dbo].[OperativeQuestionsUserMail] in die Datenbank ein.

Indem wir E-Mail als Eingabeparameter senden, erhalten wir eine Wertetabelle zurück:

Anfrage Nr. 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

Dies gibt eine Wertetabelle mit einer vordefinierten Datenstruktur zurück.

Damit Abfragen an OperativeQuestionsUserMail optimal sind und über optimale Abfragepläne verfügen, ist eine strenge Struktur erforderlich und nicht RÜCKGABETABELLE ALS RÜCKGABE...

In diesem Fall wird die erforderliche Abfrage 1 in Abfrage 4 umgewandelt:

Anfrage Nr. 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]);

Zuordnen von Ansichten und Funktionen in 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})");
}

Letzte LINQ-Abfrage

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

Die Ausführungszeit ist von 200–800 ms auf 2–20 ms usw. gesunken, also um ein Vielfaches schneller.

Wenn wir es durchschnittlicher angehen, dann sind es statt 350 ms 8 ms.

Aus den offensichtlichen Vorteilen ergeben sich außerdem:

allgemeine Reduzierung der Lesebelastung,
deutliche Reduzierung der Blockierwahrscheinlichkeit
Reduzierung der durchschnittlichen Blockierungszeit auf akzeptable Werte

Abschluss

Optimierung und Feinabstimmung von Datenbankaufrufen MS SQL durch LINQ ist ein Problem, das gelöst werden kann.

Aufmerksamkeit und Konsequenz sind bei dieser Arbeit sehr wichtig.

Zu Beginn des Prozesses:

Es ist notwendig, die Daten zu überprüfen, mit denen die Anfrage arbeitet (Werte, ausgewählte Datentypen).
Führen Sie eine ordnungsgemäße Indizierung dieser Daten durch
Überprüfen Sie die Richtigkeit der Verknüpfungsbedingungen zwischen Tabellen

Die nächste Optimierungsiteration zeigt:

Grundlage der Anfrage und definiert den Hauptanfragefilter
Wiederholen ähnlicher Abfrageblöcke und Analysieren der Schnittmenge von Bedingungen
in SSMS oder einer anderen GUI für SQL Server optimiert sich selbst SQL-Abfrage (Zuweisen eines Zwischendatenspeichers, Erstellen der resultierenden Abfrage unter Verwendung dieses Speichers (es können mehrere sein))
im letzten Schritt unter Berücksichtigung des Ergebnisses SQL-Abfrage, die Struktur wird umgebaut LINQ-Abfrage

Das Ergebnis LINQ-Abfrage sollte in seiner Struktur mit dem identifizierten Optimum identisch sein SQL-Abfrage ab Punkt 3.

Danksagung

Vielen Dank an die Kollegen jobgemws и alex_ozr von der Firma Fortis für Ihre Unterstützung bei der Vorbereitung dieses Materials.

Source: habr.com

Methoden zur Optimierung von LINQ-Abfragen in C#.NET