🥇Méthodes d'optimisation des requêtes LINQ en C#.NET

introduction

В cet article certaines méthodes d'optimisation ont été envisagées Requêtes LINQ.
Nous présentons ici d'autres approches d'optimisation du code liées à Requêtes LINQ.

Il est connu que LINQ(Language-Integrated Query) est un langage simple et pratique pour interroger une source de données.

А LINQ vers SQL est une technologie permettant d'accéder aux données dans un SGBD. Il s'agit d'un outil puissant pour travailler avec des données, où les requêtes sont construites via un langage déclaratif, qui sera ensuite converti en Requêtes SQL plateforme et envoyé au serveur de base de données pour exécution. Dans notre cas, par SGBD nous entendons MS SQL Server.

cependant, Requêtes LINQ ne sont pas convertis en écrits de manière optimale Requêtes SQL, qu'un DBA expérimenté pourrait écrire avec toutes les nuances d'optimisation Requêtes SQL:

connexions optimales (INSCRIPTION) et filtrer les résultats (OÙ)
de nombreuses nuances dans l'utilisation des connexions et des conditions de groupe
de nombreuses variations dans les conditions de remplacement IN sur EXISTEи PAS DANS, <> sur EXISTE
mise en cache intermédiaire des résultats via des tables temporaires, CTE, variables de table
utilisation de la phrase (OPTION) avec instructions et conseils de table WITH (...)
utiliser les vues indexées comme l'un des moyens de se débarrasser des lectures de données redondantes lors des sélections

Les principaux goulots d'étranglement de performance des résultats Requêtes SQL lors de la compilation Requêtes LINQ sont les suivants:

consolidation de l’ensemble du mécanisme de sélection des données en une seule requête
dupliquer des blocs de code identiques, ce qui conduit finalement à plusieurs lectures de données inutiles
groupes de conditions à plusieurs composants (logiques « et » et « ou ») - ET и OR, se combinant en conditions complexes, conduit au fait que l'optimiseur, disposant d'index non clusterisés appropriés pour les champs nécessaires, commence finalement à analyser l'index clusterisé (ANALYSE D'INDEX) par groupes de conditions
l'imbrication profonde des sous-requêtes rend l'analyse très problématique Instructions SQL et analyse du plan de requête de la part des développeurs et DBA

Méthodes d'optimisation

Passons maintenant directement aux méthodes d'optimisation.

1) Indexation supplémentaire

Il est préférable d'envisager des filtres sur les tables de sélection principales, car très souvent toute la requête est construite autour d'une ou deux tables principales (applications-personnes-opérations) et avec un ensemble standard de conditions (IsClosed, Canceled, Enabled, Status). Il est important de créer des indices appropriés pour les échantillons identifiés.

Cette solution est logique lorsque la sélection de ces champs limite considérablement l'ensemble renvoyé à la requête.

Par exemple, nous avons 500000 2000 demandes. Cependant, il n’y a que XNUMX XNUMX applications actives. Ensuite, un index correctement sélectionné nous évitera ANALYSE D'INDEX sur une grande table et vous permettra de sélectionner rapidement des données via un index non clusterisé.

En outre, le manque d'index peut être identifié grâce à des invites pour analyser les plans de requête ou collecter des statistiques sur la vue système. MS SQL Server:

Toutes les données de vue contiennent des informations sur les index manquants, à l'exception des index spatiaux.

Cependant, les index et la mise en cache sont souvent des méthodes permettant de lutter contre les conséquences d'une mauvaise écriture. Requêtes LINQ и Requêtes SQL.

Comme le montre la dure pratique de la vie, il est souvent important pour une entreprise de mettre en œuvre des fonctionnalités commerciales dans certains délais. Et par conséquent, les requêtes lourdes sont souvent transférées en arrière-plan grâce à la mise en cache.

Ceci est en partie justifié, car l'utilisateur n'a pas toujours besoin des données les plus récentes et le niveau de réactivité de l'interface utilisateur est acceptable.

Cette approche permet de répondre aux besoins métiers, mais réduit à terme les performances du système d'information en retardant simplement la résolution des problèmes.

Il convient également de rappeler que lors du processus de recherche des index nécessaires à ajouter, des suggestions MS SQL l'optimisation peut être incorrecte, notamment dans les conditions suivantes :

s'il existe déjà des index avec un ensemble de champs similaire
si les champs de la table ne peuvent pas être indexés en raison de restrictions d'indexation (décrites plus en détail ici).

2) Fusionner les attributs en un nouvel attribut

Parfois, certains champs d'une table, qui servent de base à un groupe de conditions, peuvent être remplacés par l'introduction d'un nouveau champ.

Cela est particulièrement vrai pour les champs d'état, qui sont généralement de type bit ou entier.

Exemple:

IsClosed = 0 ET Annulé = 0 ET Activé = 0 remplacé par Statut = 1.

C'est ici que l'attribut Statut entier est introduit pour garantir que ces statuts sont renseignés dans le tableau. Ensuite, ce nouvel attribut est indexé.

Il s'agit d'une solution fondamentale au problème de performances, car nous accédons aux données sans calculs inutiles.

3) Matérialisation de la vue

Malheureusement, dans Requêtes LINQ Les tables temporaires, les CTE et les variables de table ne peuvent pas être utilisées directement.

Cependant, il existe une autre façon d'optimiser pour ce cas : les vues indexées.

Groupe de conditions (à partir de l'exemple ci-dessus) IsClosed = 0 ET Annulé = 0 ET Activé = 0 (ou un ensemble d'autres conditions similaires) devient une bonne option pour les utiliser dans une vue indexée, en mettant en cache une petite tranche de données à partir d'un grand ensemble.

Mais il existe un certain nombre de restrictions lors de la matérialisation d'une vue :

utilisation de sous-requêtes, de clauses EXISTE doit être remplacé en utilisant INSCRIPTION
tu ne peux pas utiliser de phrases UNION, UNION TOUS, EXCEPTION, COUPER
Vous ne pouvez pas utiliser d'indicateurs et de clauses de tableau OPTION
pas de possibilité de travailler avec des cycles
Il est impossible d'afficher les données dans une seule vue à partir de différentes tables

Il est important de se rappeler que le véritable avantage de l’utilisation d’une vue indexée ne peut être obtenu qu’en l’indexant réellement.

Mais lors de l'appel d'une vue, ces index ne peuvent pas être utilisés, et pour les utiliser explicitement, vous devez spécifier AVEC(NOEXPAND).

Depuis en Requêtes LINQ Il est impossible de définir des indices de tableau, vous devez donc créer une autre représentation - un « wrapper » de la forme suivante :

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

4) Utilisation des fonctions de table

Souvent dans Requêtes LINQ De gros blocs de sous-requêtes ou des blocs utilisant des vues avec une structure complexe forment une requête finale avec une structure d'exécution très complexe et sous-optimale.

Principaux avantages de l'utilisation des fonctions de table dans Requêtes LINQ:

La possibilité, comme dans le cas des vues, d'être utilisée et spécifiée en tant qu'objet, mais vous pouvez transmettre un ensemble de paramètres d'entrée :
DEPUIS LA FONCTION (@param1, @param2 ...)
En conséquence, un échantillonnage flexible des données peut être obtenu
Dans le cas de l'utilisation d'une fonction de table, il n'y a pas de restrictions aussi fortes que dans le cas des vues indexées décrites ci-dessus :
1. Conseils de table :
  à travers LINQ Vous ne pouvez pas spécifier quels index doivent être utilisés ni déterminer le niveau d'isolation des données lors de l'interrogation.
  Mais la fonction possède ces capacités.
  Avec la fonction, vous pouvez obtenir un plan de requête d'exécution assez constant, où les règles d'utilisation des index et les niveaux d'isolation des données sont définies
2. L'utilisation de la fonction permet, par rapport aux vues indexées, d'obtenir :
  - logique d'échantillonnage de données complexe (même en utilisant des boucles)
  - récupérer des données à partir de nombreuses tables différentes
  - utiliser UNION и EXISTE
Proposition OPTION très utile lorsque nous devons fournir un contrôle de concurrence OPTION(MAXDOPN), l'ordre du plan d'exécution de la requête. Par exemple:
- vous pouvez spécifier une recréation forcée du plan de requête OPTION (RECOMPILE)
- vous pouvez spécifier s'il faut forcer le plan de requête à utiliser l'ordre de jointure spécifié dans la requête OPTION (ORDRE FORCÉ)
Plus de détails sur OPTION décrit ici.
Utilisation de la tranche de données la plus étroite et la plus requise :
Il n'est pas nécessaire de stocker de grands ensembles de données dans des caches (comme c'est le cas avec les vues indexées), à partir desquels vous devez toujours filtrer les données par paramètre.
Par exemple, il existe une table dont le filtre OÙ trois champs sont utilisés (une, b, c).

Classiquement, toutes les requêtes ont une condition constante a = 0 et b = 0.

Cependant, la demande de champ c plus variable.

Laissez la condition a = 0 et b = 0 Cela nous aide vraiment à limiter l'ensemble résultant requis à des milliers d'enregistrements, mais la condition sur с réduit la sélection à une centaine d'enregistrements.

Ici, la fonction table peut être une meilleure option.

De plus, une fonction de table est plus prévisible et cohérente en termes de temps d'exécution.

Exemples

Examinons un exemple d'implémentation utilisant la base de données Questions comme exemple.

Il y a une demande SELECT, qui combine plusieurs tableaux et utilise une seule vue (OperativeQuestions), dans laquelle l'affiliation est vérifiée par email (via EXISTE) aux « Questions opérationnelles » :

Demande n°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])
));

La vue a une structure assez complexe : elle contient des jointures de sous-requêtes et utilise le tri DISTINCT, ce qui est en général une opération assez gourmande en ressources.

Un échantillon de OperativeQuestions contient environ dix mille enregistrements.

Le principal problème de cette requête est que pour les enregistrements de la requête externe, une sous-requête interne est exécutée sur la vue [OperativeQuestions], ce qui devrait pour [Email] = @p__linq__0 nous permettre de limiter la sélection de sortie (via EXISTE) jusqu'à des centaines d'enregistrements.

Et il peut sembler que la sous-requête devrait calculer les enregistrements une fois par [Email] = @p__linq__0, puis ces quelques centaines d'enregistrements devraient être connectés par Id avec Questions, et la requête sera rapide.

En fait, il existe une connexion séquentielle de toutes les tables : vérification de la correspondance des questions Id avec les Id de OperativeQuestions, et filtrage par Email.

En fait, la requête fonctionne avec les dizaines de milliers d'enregistrements OperativeQuestions, mais seules les données qui vous intéressent sont nécessaires par courrier électronique.

Texte de la vue OperativeQuestions :

Demande n°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));

Mappage de vue initial dans 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");
    }
}

Requête LINQ initiale

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

Dans ce cas particulier, nous envisageons une solution à ce problème sans changements d'infrastructure, sans introduire un tableau séparé avec des résultats prêts à l'emploi (« Requêtes actives »), ce qui nécessiterait un mécanisme pour le remplir de données et le maintenir à jour. .

Bien qu’il s’agisse d’une bonne solution, il existe une autre option pour optimiser ce problème.

L'objectif principal est de mettre en cache les entrées par [Email] = @p__linq__0 à partir de la vue OperativeQuestions.

Introduisez la fonction de table [dbo].[OperativeQuestionsUserMail] dans la base de données.

En envoyant Email en paramètre d'entrée, nous récupérons un tableau de valeurs :

Demande n°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

Cela renvoie un tableau de valeurs avec une structure de données prédéfinie.

Pour que les requêtes adressées à OperativeQuestionsUserMail soient optimales et aient des plans de requête optimaux, une structure stricte est requise, et non TABLE DES RETOURS COMME RETOUR...

Dans ce cas, la Requête 1 requise est convertie en Requête 4 :

Demande n°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]);

Mappage des vues et des fonctions dans 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})");
}

Requête LINQ finale

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

L'ordre du temps d'exécution est passé de 200-800 ms, à 2-20 ms, etc., soit des dizaines de fois plus rapide.

Si nous le prenons plus en moyenne, alors au lieu de 350 ms, nous obtenons 8 ms.

Parmi les avantages évidents, nous obtenons également :

réduction générale de la charge de lecture,
réduction significative de la probabilité de blocage
réduire le temps de blocage moyen à des valeurs acceptables

conclusion

Optimisation et mise au point des appels à la base de données MS SQL à travers LINQ est un problème qui peut être résolu.

L'attention et la cohérence sont très importantes dans ce travail.

Au début du processus :

il est nécessaire de vérifier les données avec lesquelles fonctionne la requête (valeurs, types de données sélectionnés)
procéder à une bonne indexation de ces données
vérifier l'exactitude des conditions de jointure entre les tables

La prochaine itération d'optimisation révèle :

base de la demande et définit le filtre principal de la demande
répéter des blocs de requête similaires et analyser l'intersection des conditions
dans SSMS ou autre interface graphique pour SQL Server s'optimise requête SQL (allocation d'un stockage de données intermédiaire, construction de la requête résultante à l'aide de ce stockage (il peut y en avoir plusieurs))
à la dernière étape, en prenant comme base le résultat requête SQL, la structure est en cours de reconstruction Requête LINQ

La résultante Requête LINQ devrait devenir identique dans sa structure à l'optimal identifié requête SQL du point 3.

Remerciements

Un grand merci aux collègues jobgemws и alex_ozr de la société Fortis pour obtenir de l'aide dans la préparation de ce matériel.

Source: habr.com

Méthodes d'optimisation des requêtes LINQ en C#.NET