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

Einführung

In in diesem Artikel wurden einige Optimierungsmethoden behandelt von LINQ-Abfragen.
Hier werden wir einige weitere Ansätze zur Codeoptimierung vorstellen, die mit LINQ-Abfragen.

Es ist bekannt, dass LINQ(Language-Integrated Query) eine einfache und benutzerfreundliche Abfragesprache für Datenquellen ist.

A LINQ to SQL ist eine Technologie für den Datenzugriff in Datenbanken. Es handelt sich um ein leistungsstarkes Werkzeug zur Datenbearbeitung, bei dem über eine deklarative Sprache Abfragen formuliert werden, die dann in SQL-Abfragen auf der Plattform umgewandelt und zur Ausführung an den Datenbankserver gesendet werden. In unserem Fall beziehen wir uns auf MS SQL Server..

Jedoch werden LINQ-Abfragen nicht in optimal geschriebene SQL-Abfragen, die ein erfahrener DBA mit allen Aspekten der Optimierung von SQL-Abfragen erstellen könnte.:

optimale Joins (JOIN) und die Filterung von Ergebnissen (WHERE)
viele Aspekte bei der Verwendung von Joins und Gruppierungsbedingungen
viele Variationen beim Ersetzen von Bedingungen IN findet man EXISTSund NOT IN, auf EXISTS
Zwischencaching von Ergebnissen durch temporäre Tabellen, CTE, tabellarische Variablen
Verwendung der Klausel (OPTION) mit Hinweisen und tabellarischen Hints WITH (…)
die Verwendung von indexierbaren Ansichten als ein Mittel zur Reduzierung überflüssiger Datenlesungen bei Abfragen

Die häufigsten Engpässe in der Performance sind von SQL-Abfragen erstellen könnte. bei der Kompilierung von LINQ-Abfragen sind:

die Konsolidierung des gesamten Datenauswahlmechanismus in einer Anfrage
die Wiederholung identischer Codeblöcke, was letztendlich zu mehrfachen unnötigen Datenlesungen führt
Gruppen von zusammengesetzten Bedingungen (logische „und“ und „oder“) — AND und OR, die zu komplexen Bedingungen verbunden sind, führt dazu, dass der Optimierer, auch wenn er passende nicht clusterisierte Indizes für die erforderlichen Felder hat, letztendlich dennoch mit dem Scannen des Clusterindex beginnt (INDEX SCAN) nach Bedingungsgruppen
die tiefe Verschachtelung von Unterabfragen macht die Analyse von SQL-Anweisungen sowie die Analyse der Abfragepläne seitens der Entwickler und DBA

Optimierungsmethoden

Kommen wir nun direkt zu den Optimierungsmethoden.

1) Zusätzliche Indizierung

Es ist ratsam, Filter auf den Hauptauswahl-Tables zu betrachten, da oft die gesamte Anfrage um ein oder zwei Haupt-Tabellen (Anträge – Personen – Operationen) herum aufgebaut ist und mit einer standardmäßigen Bedingungssammlung (IsClosed, Canceled, Enabled, Status) arbeitet. Es ist wichtig, entsprechende Indizes für die identifizierten Abfragen zu erstellen.

Diese Lösung macht Sinn, wenn die Auswahl nach diesen Feldern die zurückgegebene Menge der Anfragen erheblich einschränkt.

Nehmen wir an, wir haben 500.000 Anträge. Aktive Anträge sind jedoch nur 2.000 Einträge. Dann wird ein gut gewählter Index uns von INDEX SCAN der großen Tabelle befreien und ermöglicht es, Daten schnell über einen nicht gruppierten Index auszuwählen.

Mangel an Indizes kann auch durch Hinweise zur Analyse der Abfragepläne oder durch das Sammeln von Statistiken aus systemischen Ansichten identifiziert werden. MS SQL Server.:

Alle Daten der Ansichten enthalten Informationen über fehlende Indizes, mit Ausnahme von räumlichen Indizes.

Allerdings sind Indizes und das Caching oft Methoden, um die Folgen von schlecht geschriebenen von LINQ-Abfragen und von SQL-Abfragen erstellen könnte..

Die harte Lebensrealität zeigt, dass für Unternehmen oft die Umsetzung von Geschäftsfunktionen innerhalb bestimmter Fristen entscheidend ist. Daher werden anspruchsvolle Anfragen häufig in den Hintergrund mit Caching verschoben.

Das ist teilweise gerechtfertigt, da die Benutzer nicht immer die aktuellsten Daten benötigen und ein akzeptables Niveau der Benutzeroberflächenreaktion erzielt wird.

Dieser Ansatz ermöglicht es, die geschäftlichen Anforderungen zu erfüllen, mindert jedoch letztlich die Leistungsfähigkeit des Informationssystems, indem er die Problemlösungen einfach hinauszögert.

Außerdem sollte man berücksichtigen, dass bei der Suche nach den erforderlichen neuen Indizes auch die Vorschläge zur MS SQL Optimierung unter bestimmten Bedingungen unrichtig sein können:

wenn bereits Indizes mit einem ähnlichen Feldsatz existieren
wenn die Felder in der Tabelle aufgrund von Einschränkungen beim Indizieren nicht indiziert werden können (darüber wird ausführlicher hier).

2) Zusammenlegung von Attributen zu einem neuen Attribut

Manchmal können bestimmte Felder aus einer Tabelle, nach denen eine Gruppe von Bedingungen erstellt wird, durch die Einführung eines neuen Feldes ersetzt werden.

Dies ist besonders relevant für Statusfelder, die in der Regel entweder binär oder ganzzahlig sind.

Beispiel:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 wird ersetzt durch Status = 1.

Hier wird das ganzzahlige Attribut Status eingeführt, das durch die Befüllung dieser Status in der Tabelle bereitgestellt wird. Anschließend erfolgt die Indizierung dieses neuen Attributs.

Dies ist eine grundlegende Lösung zur Leistungsverbesserung, da wir auf Daten zugreifen, ohne unnötige Berechnungen durchzuführen.

3) Materialisierung der Ansicht

Leider können in LINQ-Abfragen temporäre Tabellen, CTEs und tabellarische Variablen nicht direkt verwendet werden.

Es gibt jedoch eine weitere Möglichkeit zur Optimierung in diesem Fall - indexierbare Ansichten.

Die Gruppe von Bedingungen (aus dem obigen Beispiel) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (oder eine Reihe anderer ähnlicher Bedingungen) eignet sich gut zur Verwendung in einer indexierbaren Ansicht, wobei ein kleiner Datenausschnitt aus einer großen Menge zwischengespeichert wird.

Es gibt jedoch einige Einschränkungen bei der Materialisierung von Ansichten:

Nutzung von Unterabfragen, Klauseln EXISTS müssen ersetzt werden durch JOIN
Klauseln dürfen nicht verwendet werden, UNION, UNION ALL, EXCEPTION, INTERSECT
Tabellenhinweise und -vorschläge sind nicht zulässig. OPTION
Es gibt keine Möglichkeit, mit Schleifen zu arbeiten.
Es ist nicht möglich, Daten aus verschiedenen Tabellen in einer einzigen Sicht darzustellen.

Es ist wichtig, sich daran zu erinnern, dass der tatsächliche Nutzen der Verwendung einer indizierbaren Ansicht erst durch deren Indizierung erzielt werden kann.

Beim Aufruf der Ansicht könnten jedoch diese Indizes nicht verwendet werden, und für deren explizite Nutzung muss angegeben werden: WITH (NOEXPAND).

Da in LINQ-Abfragen Tabellenhinweise nicht definiert werden können, ist es erforderlich, eine zusätzliche Ansicht – eine 'Wrapper'-Ansicht – folgender Art zu erstellen:

CREATE VIEW NAME_DER_ANSICHT AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Verwendung von Tabellenfunktionen

Oft bilden in LINQ-Abfragen große Blöcke von Unterabfragen oder Blöcke, die komplex strukturierte Ansichten verwenden, eine endgültige Abfrage mit einer sehr komplexen und nicht optimalen Ausführungsstruktur.

Die Hauptvorteile der Verwendung von Tabellenfunktionen in LINQ-Abfragen:

Es besteht die Möglichkeit, wie bei den Ansichten auch, diese als Objekt zu verwenden, jedoch können auch Eingabeparameter übergeben werden:
FROM FUNCTION(@param1, @param2 …)
somit kann eine flexible Datenauswahl erzielt werden.
Bei der Verwendung von Tabellfunktionen gibt es nicht so starke Einschränkungen wie bei den oben beschriebenen indizierten Ansichten.
1. Tabell-Hinweise:
  über LINQ es kann nicht festgelegt werden, welche Indizes verwendet werden müssen und welches Grad der Isolierung der Daten bei der Abfrage anzuwenden ist.
  Aber in Funktionen sind diese Möglichkeiten vorhanden.
  Mit der Funktion kann ein relativ stabiler Ausführungsplan für die Abfrage erreicht werden, wo die Regeln für die Arbeit mit Indizes und die Datenisolationsebenen definiert sind.
2. Die Verwendung der Funktion ermöglicht im Vergleich zu indizierten Ansichten:
  - komplexe Logik zur Datenabfrage (bis hin zur Verwendung von Schleifen)
  - Abfragen von Daten aus verschiedenen Tabellen
  - Verwendung UNION und EXISTS
Anweisung OPTION ist sehr nützlich, wenn wir die Verwaltung von Parallelität sicherstellen müssen. OPTION(MAXDOP N), des Ausführungsplans der Abfrage. Zum Beispiel:
- es kann eine erzwungene Neuschaffung des Abfrageplans angegeben werden. OPTION (RECOMPILE)
- es kann die Notwendigkeit angegeben werden, dass der Abfrageplan die angegebene Reihenfolge der Joins in der Abfrage berücksichtigt. OPTION (FORCE ORDER)
Detaillierter über OPTION beschrieben hier.
Verwendung des engsten und benötigten Datenausschnitts:
Es ist nicht erforderlich, große Datensätze im Cache zu halten (wie bei indexierbaren Ansichten), aus denen die Daten dann noch nach Parametern gefiltert werden müssen.
Beispielsweise gibt es eine Tabelle, bei der für die Filterung WHERE drei Felder verwendet werden (a, b, c).
Bedingungsgemäß gibt es für alle Anfragen eine konstante Bedingung a = 0 und b = 0.
Allerdings ist die Anfrage an das Feld c variabler.
Angenommen, die Bedingung a = 0 und b = 0 hilft uns wirklich, den benötigten Ergebnissatz auf tausend Datensätze zu begrenzen, jedoch schränkt die Bedingung mit die Auswahl auf hundert Datensätze ein.
Hier könnte eine tabellarische Funktion die bessere Wahl sein.
Außerdem ist eine tabellarische Funktion zeitlich vorhersehbarer und konsistenter.

Beispiele

Betrachten wir ein Beispiel für die Implementierung anhand der Datenbank Fragen.

Es gibt eine Anfrage SELECT, die mehrere Tabellen verbindet und eine Ansicht (OperativeQuestions) verwendet, in der die Zugehörigkeit anhand der E-Mail geprüft wird (über EXISTS) zu den "Aktiven Anfragen" ([OperativeQuestions]):

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 umfasst Verknüpfungen von Unterabfragen und die Verwendung von Sortierung. DISTINCT, die im Allgemeinen eine recht ressourcenintensive Operation darstellt.

Die Abfrage aus OperativeQuestions umfasst etwa zehntausend Datensätze.

Das Hauptproblem dieser Abfrage besteht darin, dass für die Datensätze aus der äußeren Abfrage eine innere Unterabfrage auf der Ansicht [OperativeQuestions] ausgeführt wird, die für [Email] = @p__linq__0 unser Ausgabeergebnis einschränken sollte (über EXISTS) auf Hunderte von Datensätzen.

Es könnte den Anschein haben, dass die Abfrage einmal die Datensätze für [Email] = @p__linq__0 berechnen sollte und dann diese paar Hundert Datensätze nach Id mit Fragen verknüpft werden, und die Abfrage somit schnell wäre.

In Wirklichkeit erfolgt jedoch eine sequenzielle Verknüpfung aller Tabellen: Sowohl die Überprüfung der Übereinstimmung der Id der Fragen mit der Id aus den OperativeQuestions als auch die Filterung nach Email.

Im Grunde genommen arbeitet die Abfrage mit allen Zehntausenden von Datensätzen in den OperativeQuestions, obwohl nur die relevanten Daten nach Email benötigt werden.

Darstellungstext der OperativeQuestions:

Abfrage Nr. 2

 
ERSTELLEN SIE DIEANSICHT [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));

Ursprüngliche Mapping-Ansicht im 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");
    }
}

Ursprüngliche 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 wird die Lösung dieses Problems ohne infrastrukturelle Änderungen und ohne die Einführung einer separaten Tabelle mit den fertigen Ergebnissen („Aktive Anfragen“) in Betracht gezogen, für die ein Mechanismus zur Datenbefüllung und -aktualisierung erforderlich wäre.

Obwohl das eine gute Lösung ist, gibt es auch eine weitere Möglichkeit zur Optimierung dieser Aufgabe.

Das Hauptziel ist, die Einträge für [Email] = @p__linq__0 aus der Ansicht OperativeQuestions zwischenzuspeichern.

Wir führen die Tabellenfunktion [dbo].[OperativeQuestionsUserMail] in die Datenbank ein.

Indem wir Email als Eingabeparameter übergeben, erhalten wir eine Ergebnistabelle zurück:

Abfrage 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

Hier wird eine Wertetabelle mit vordefinierter Datenstruktur zurückgegeben.

Um die Abfragen an OperativeQuestionsUserMail zu optimieren und optimale Abfragepläne zu erhalten, ist eine strenge Struktur erforderlich, und nicht RÜCKGABE DER TABELLE ALS RÜCKGABE…

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

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

Mapping von View und Funktion im DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable GetByUserEmail(this DbQuery source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Die endgültige 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 wurde von 200-800 ms auf 2-20 ms reduziert, also um ein Vielfaches schneller.

Im Durchschnitt erhielten wir statt 350 ms nur 8 ms.

Unter den offensichtlichen Vorteilen erhalten wir auch:

eine allgemeine Reduzierung der Leselast,
eine signifikante Verringerung der Wahrscheinlichkeit von Sperren
eine Reduzierung der durchschnittlichen Sperrzeit auf akzeptable Werte

Fazit

Die Optimierung und Feinabstimmung von Datenbankabfragen MS SQL über LINQ ist eine Aufgabe, die lösbar ist.

In dieser Arbeit sind Aufmerksamkeit und Nachvollziehbarkeit von großer Bedeutung.

Zu Beginn des Prozesses:

müssen die Daten, mit denen die Abfrage arbeitet (Werte, gewählte Datentypen), überprüft werden
eine korrekte Indizierung dieser Daten durchführen
die Richtigkeit der Verbindungsbedingungen zwischen den Tabellen überprüfen

In der nächsten Optimierungsrunde werden festgestellt:

die Grundlage der Abfrage und der Hauptfilter der Abfrage werden definiert
wiederholte ähnliche Abfrageblöcke und die Schnittmenge der Bedingungen wird analysiert
in SSMS oder einem anderen GUI für SQL Server wird die Abfrage selbst optimiert SQL-Abfrage (Hervorhebung des Zwischenspeichers, Erstellung der resultierenden Abfrage unter Verwendung dieses Speichers (kann mehrere sein))
In der letzten Phase wird, basierend auf dem Ergebnis SQL-Abfrage, die Struktur des LINQ-Abfrage neu aufgebaut

Das resultierende LINQ-Abfrage sollte in der Struktur identisch mit der festgestellten optimalen SQL-Abfrage aus Punkt 3 sein.

Danksagungen

Ein großes Dankeschön an die Kollegen jobgemws und alex_ozr vom Unternehmen Fortis für die Unterstützung bei der Vorbereitung dieses Materials.

Quelle: habr.com