ProHoster > Blog > Administration > Methoden zur Optimierung von LINQ-Abfragen in C#.NET

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

Einführung

Im diesem Artikel Einige Optimierungsmethoden wurden betrachtet LINQ-Abfragen.
Hier präsentieren wir einige weitere Ansätze zur Optimierung des Codes, die mit LINQ-Abfragen.

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

A LINQ to SQL ist eine Technologie zum Datenzugriff in DBMS. Es ist ein leistungsstarkes Werkzeug zur Arbeit mit Daten, bei dem Anfragen durch eine deklarative Sprache konstruiert werden, die dann in SQL-Anfragen die Plattform umgewandelt und zur Ausführung an den Datenbankserver gesendet werden. In unserem Fall verstehen wir unter DBMS MS SQL Server.

Allerdings LINQ-Abfragen nicht in optimal geschriebene SQL-Anfragen, die ein erfahrener DBA unter Berücksichtigung aller Optimierungsnuancen schreiben könnte SQL-Abfragen:

  1. optimale Joins (JOIN) und Filterung der Ergebnisse (WHERE)
  2. viele Nuancen bei der Verwendung von Joins und Gruppierungsbedingungen
  3. viele Variationen zur Änderung von Bedingungen IN auf EXISTSund NOT IN, auf EXISTS
  4. Zwischenspeicherung der Ergebnisse über temporäre Tabellen, CTE, Tabellennvariablen
  5. Verwendung des Klausels (OPTION) mit Anweisungen und Tabellenhinweisen WITH (…)
  6. Verwendung von indizierbaren Sichten, um überflüssige Datenlesungen bei Abfragen zu vermeiden

Die Hauptengpässe der erbrachten Leistung SQL-Abfragen bei der Kompilierung LINQ-Abfragen sind:

  1. Konsolidierung des gesamten Datenabrufmechanismus in einer einzigen Abfrage
  2. Duplizierung identischer Codeblöcke, was letztendlich zu mehrfachen überflüssigen Datenlesungen führt
  3. Gruppen aus mehrfachen Bedingungen (logische „und“ und „oder“) — AND und ODER, die sich in komplexen Bedingungen verbinden, führt dazu, dass der Optimierer, obwohl er geeignete nicht-clusterierte Indizes für die erforderlichen Felder hat, letztendlich dennoch mit einem Scan über den clusterierten Index (INDEX SCAN) nach Gruppen von Bedingungen beginnt
  4. Die tiefe Verschachtelung von Unterabfragen macht die Analyse von SQL-Anweisungen und die Analyse des Abfrageplans durch Entwickler und DBA

Optimierungsmethoden

Kommen wir nun direkt zu den Methoden der Optimierung.

1) Zusätzliches Indizieren

Es ist am besten, die Filter auf den Hauptablagetabellen zu betrachten, da häufig die gesamte Abfrage um ein oder zwei Haupttabellen (Anträge, Personen, Operationen) und mit einem standardmäßigen Satz von Bedingungen (IsClosed, Canceled, Enabled, Status) aufgebaut ist. Es ist wichtig, entsprechende Indizes für identifizierte Abfragen zu erstellen.

Diese Lösung macht Sinn, wenn die Auswahl in diesen Feldern die zurückgegebene Ergebnismenge wesentlich einschränkt.

Wir haben beispielsweise 500.000 Anträge. Allerdings sind nur 2.000 davon aktiv. Ein gut ausgewählter Index wird uns von INDEX SCAN einer großen Tabelle befreien und es uns ermöglichen, Daten schnell über einen nicht-klusterisierten Index abzurufen.

Ein Mangel an Indizes kann auch durch Hinweisanalysen von Abfrageplänen oder durch Statistiken aus systemischen Ansichten festgestellt werden, MS SQL Server:

  1. sys.dm_db_missing_index_groups
  2. sys.dm_db_missing_index_group_stats
  3. sys.dm_db_missing_index_details

Alle Datenansichten enthalten Informationen über fehlende Indizes, mit Ausnahme räumlicher Indizes.

Allerdings sind Indizes und Caching oft Methoden, um die Folgen schlecht geschriebener LINQ-Abfragen und SQL-Abfragen.

Wie die harte Realität im Geschäftsleben zeigt, ist die Umsetzung von Geschäftsfeatures oft zu einem bestimmten Zeitpunkt wichtig. Daher werden häufig komplexe Anfragen in den Hintergrund mit Caching verschoben.

Das ist teilweise gerechtfertigt, da Benutzer nicht immer die aktuellsten Daten benötigen und ein akzeptables Antwortniveau der Benutzeroberfläche erreicht wird.

Dieser Ansatz ermöglicht es, Geschäftsanfragen zu erfüllen, senkt aber letztlich die Leistungsfähigkeit des Informationssystems, indem er lediglich die Probleme hinauszögert.

Es sollte außerdem daran erinnert werden, dass bei der Suche nach neuen Indizes Vorschläge zur MS SQL Optimierung unter bestimmten Bedingungen möglicherweise ungültig sein können:

  1. wenn bereits Indizes mit einer ähnlichen Feldkombination existieren
  2. wenn Felder in der Tabelle aufgrund von Indexierungsbeschränkungen nicht indiziert werden können (detaillierter hierzu unter hier).

2) Zusammenführung von Attributen zu einem neuen Attribut

Manchmal können bestimmte Felder aus einer Tabelle, für die es eine Gruppe von Bedingungen gibt, durch die Einführung eines neuen Feldes ersetzt werden.

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

Beispiel:

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

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

Dies ist eine grundlegende Lösung für das Leistungsproblem, da wir Daten ohne unnötige Berechnungen anfordern.

3) Materialisierung der Ansicht

Leider kann man in LINQ-Abfragen keine temporären Tabellen, CTEs und Tabellenvariablen direkt verwenden.

Es gibt jedoch einen weiteren Weg zur Optimierung in diesem Fall – indizierbare Ansichten.

Die Gruppe von Bedingungen (aus dem obigen Beispiel) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (oder eine Reihe anderer ähnlicher Bedingungen) wird zu einer guten Option, um sie in einer indizierbaren Ansicht zu verwenden und wenig Daten von einer großen Menge zu cachen.

Es gibt jedoch eine Reihe von Einschränkungen bei der Materialisierung der Ansicht:

  1. die Verwendung von Unterabfragen, Vorschriften EXISTS müssen durch die Verwendung von JOIN
  2. müssen in Vorschriften nicht verwendet werden. UNION, UNION ALL, EXCEPTION, INTERSECT
  3. Tabellenhinweise und Vorschriften dürfen nicht verwendet werden. OPTION
  4. es gibt keine Möglichkeit, mit Schleifen zu arbeiten.
  5. es ist unmöglich, in einer Ansicht Daten aus verschiedenen Tabellen anzuzeigen.

Es ist wichtig zu beachten, dass der tatsächliche Nutzen der Verwendung einer indizierbaren Ansicht nur bei deren Indizierung erzielt werden kann.

Aber bei der Aufruf dieser Ansicht können diese Indizes möglicherweise nicht verwendet werden, und um sie explizit zu verwenden, muss man angeben, WITH (NOEXPAND).

Da in LINQ-Abfragen Tabellenhinweise nicht definiert werden können, muss man eine weitere Ansicht - eine 'Umhüllung' - des folgenden Typs 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 Ansichten mit komplexer Struktur verwenden, eine finale Abfrage mit einer sehr komplexen und nicht optimalen Ausführungsstruktur.

Die Hauptvorteile der Verwendung von Tabellenfunktionen in LINQ-Abfragen:

  1. Die Möglichkeit, wie bei Ansichten, sie als Objekt zu verwenden und anzugeben, aber man kann eine Reihe von Eingabeparametern übergeben:
    FROM FUNCTION(@param1, @param2 …)
    dadurch kann man eine flexible Datenauswahl erreichen.
  2. Im Fall der Verwendung einer Tabellenfunktion gibt es nicht so starke Einschränkungen wie bei den oben beschriebenen indizierbaren Ansichten:
    1. Tabellenhinweise:
      über LINQ Man kann nicht angeben, welche Indizes verwendet werden sollen und das Isolationsniveau der Daten bei der Abfrage definieren.
      Aber in Funktionen sind diese Möglichkeiten vorhanden.
      Mit einer Funktion kann man einen relativ konstanten Ausführungsplan der Abfrage erreichen, in dem Regeln für die Arbeit mit Indizes und Isolationsniveaus der Daten festgelegt sind.
    2. Die Verwendung der Funktion ermöglicht im Vergleich zu indizierten Sichten Folgendes:
      • komplexe Logik zur Datenauswahl (bis hin zur Verwendung von Schleifen)
      • Datenauswahl aus mehreren verschiedenen Tabellen
      • Nutzung UNION und EXISTS

  3. Angebot OPTION ist sehr nützlich, wenn wir die Verwaltung von Parallelität sicherstellen müssen OPTION(MAXDOP N), in der Reihenfolge des Ausführungsplans der Abfrage. Zum Beispiel:
    • kann man eine erzwungene Neuanlage des Abfrageplans angeben OPTION (RECOMPILE)
    • kann man die Notwendigkeit angeben, dass der Abfrageplan die in der Abfrage angegebene Reihenfolge der Joins zwingend verwenden muss OPTION (FORCE ORDER)

    Detailliertere Informationen über OPTION beschrieben hier.

  4. Die Verwendung der engsten und benötigten Datenschnitte:
    Es ist nicht notwendig, große Datensätze in Caches zu halten (wie es bei indizierten Sichten der Fall ist), aus denen die Daten dann noch nach einem Filterkriterium ausgewählt werden müssen.
    Zum Beispiel gibt es eine Tabelle, bei der für den Filter WHERE drei Felder verwendet werden (a, b, c).

    Bedingt für alle Abfragen gibt es eine konstante Bedingung a = 0 und b = 0.

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

    Nehmen wir an, die Bedingung a = 0 und b = 0 hilft uns tatsächlich, den benötigten Datensatz auf Tausende von Einträgen zu beschränken, aber die Einschränkung durch c verengt die Auswahl auf Hundert Einträge.

    Hier könnte eine tabellarische Funktion vorteilhafter sein.

    Auch ist die tabellarische Funktion vorhersehbarer und konstanter in der Ausführungszeit.

Beispiele

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

Es gibt eine Abfrage SELECT, die mehrere Tabellen verbindet und eine Ansicht (OperativeQuestions) verwendet, bei der anhand der E-Mail die Zugehörigkeit überprüft wird (durch EXISTS) zu "Aktiven Anfragen"([OperativeQuestions]):

Abfrage 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 Darstellung hat eine ziemlich komplexe Struktur: Sie beinhaltet Verknüpfungen von Unterabfragen und die Verwendung von Sortierung DISTINCT, was im Allgemeinen eine ressourcenintensive Operation ist.

Die Auswahl aus OperativeQuestions umfasst etwa zehntausend Datensätze.

Das Hauptproblem dieser Abfrage besteht darin, dass für Datensätze aus der externen Abfrage eine interne Unterabfrage auf der Ansicht [OperativeQuestions] ausgeführt wird, die für [Email] = @p__linq__0 unsere zurückgegebene Auswahl einschränken muss (über EXISTS) auf Hunderte von Datensätzen.

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

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

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

Der Text der Ansicht OperativeQuestions:

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

Die ursprüngliche Zuordnung der Ansicht im DbContext (EF Core 2)

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

Die 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 untersucht, ohne die Einführung einer separaten Tabelle mit fertigen Ergebnissen („Aktive Anfragen“), für die ein Mechanismus zur Befüllung mit Daten und zur Aufrechterhaltung des aktuellen Stands erforderlich wäre.

Obwohl dies eine gute Lösung ist, gibt es auch eine andere Variante zur Optimierung dieser Aufgabe.

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

Wir führen die tabellarische Funktion [dbo].[OperativeQuestionsUserMail] in der Datenbank ein.

Indem wir Email als Eingabeparameter übergeben, erhalten wir eine Tabelle mit Werten 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 Tabelle mit Werten mit einer vorher festgelegten Datenstruktur zurückgegeben.

Damit die Abfragen an OperativeQuestionsUserMail optimal sind und optimale Abfragepläne haben, ist eine strenge Struktur erforderlich und nicht RETURNS TABLE AS RETURN…

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 der Ansicht und der Funktion in 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})");
}

Der 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 hat sich von 200-800 ms auf 2-20 ms verringert, usw., das heißt, um ein Vielfaches schneller.

Wenn man es aggregiert betrachtet, hat sich die Zeit von 350 ms auf 8 ms reduziert.

Zu den offensichtlichen Vorteilen gehören ebenfalls:

  1. allgemeine Reduzierung der Leselast,
  2. deutliche Verringerung der Wahrscheinlichkeit von Sperren
  3. Verringerung der durchschnittlichen Sperrzeit auf akzeptable Werte

Ausgabe

Optimierung und Feinabstimmung von Datenbankzugriffen MS SQL über LINQ ist eine Aufgabe, die gelöst werden kann.

In dieser Arbeit sind Genauigkeit und Konsequenz von großer Bedeutung.

Zu Beginn des Prozesses:

  1. müssen die Daten geprüft werden, mit denen die Anfrage arbeitet (Werte, ausgewählte Datentypen)
  2. eine korrekte Indizierung dieser Daten durchgeführt werden
  3. die Richtigkeit der Verknüpfungsbedingungen zwischen den Tabellen überprüft werden

In der nächsten Optimierungsiteration werden festgestellt:

  1. die Grundlage der Anfrage und der Hauptfilter der Anfrage werden definiert
  2. wiederholende ähnliche Anfrageblöcke und die Überschneidung der Bedingungen wird analysiert
  3. in SSMS oder einer anderen GUI für SQL Server wird die Anfrage selbst optimiert SQL-Abfrage (Bereitstellung eines zwischenzeitlichen Datenspeichers, Erstellung der endgültigen Anfrage unter Verwendung dieses Speichers (es kann mehrere geben))
  4. In der letzten Phase, basierend auf dem endgültigen SQL-Abfrage, wird die Struktur der LINQ-Abfrage neu aufgebaut

Das resultierende LINQ-Abfrage sollte strukturell identisch mit der ermittelten optimalen SQL-Abfrage aus Punkt 3 sein.

Dank

Ein großes Dankeschön an die Kollegen jobgemws und alex_ozr von der Firma Fortis für die Hilfe bei der Vorbereitung dieses Materials.

Quelle: habr.com

60GB SSD 8Gb DDR4