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

Introduction

Dans cet article Certain optimization methods were considered LINQ queries.
Here, we will also present some additional code optimization approaches related to LINQ queries.

It is known that LINQ(Language-Integrated Query) is a simple and convenient query language for data sources.

Un LINQ to SQL is a data access technology in DBMS. It is a powerful tool for working with data where declarative language is used to construct queries, which are then transformed by des requêtes SQL the platform and sent to the database server for execution. In our case, we will understand DBMS as MS SQL Server.

However, LINQ queries are not transformed into optimally written des requêtes SQL, which an experienced DBA would write with all the nuances of optimization SQL queries:

1. optimal joins (JOIN) and filtering results (OÙ)
2. a multitude of nuances in the use of joins and group conditions
3. a variety of variations in replacing conditions IN sur soient « vrais », maiset NOT IN, to soient « vrais », mais
4. intermediate caching of results through temporary tables, CTEs, table variables
5. using the clause (OPTION) with directives and table hints WITH (…)
6. using indexed views as one of the means to eliminate excessive data reads during selections

The main bottlenecks in the performance of the resulting SQL queries during compilation LINQ queries are:

1. consolidation of the entire data selection mechanism into one query
2. duplication of identical code blocks, which ultimately leads to multiple unnecessary data reads
3. groups of composite conditions (logical “and” and “or”) — AND et OU, when combined into complex conditions, leads to the optimizer, having appropriate non-clustered indexes on necessary fields, ultimately starting to perform a scan on the clustered index (INDEX SCAN) based on the groups of conditions
4. deep nested subqueries make the parsing of SQL statements and the analysis of execution plans from developers very problematic DBA

Optimization methods

Now let's move directly to the optimization methods.

1) Additional indexing

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

Cette solution a du sens lorsque la sélection par ces champs limite considérablement l'ensemble de résultats de la requête.

Par exemple, nous avons 500 000 demandes. Cependant, il y a seulement 2 000 demandes actives. Ainsi, un index bien choisi nous évitera INDEX SCAN de parcourir une grande table et permettra de sélectionner rapidement les données via un index non cluster.

Un manque d'index peut également être détecté par des conseils d'analyse des plans de requêtes ou par la collecte de statistiques des vues système 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

Toutes les données des vues 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 pour lutter contre les conséquences d'un code mal écrit. LINQ queries et SQL queries.

Comme le montre la dure réalité des affaires, la mise en œuvre des fonctionnalités commerciales dans des délais spécifiques est souvent cruciale. C'est pourquoi des requêtes lourdes sont souvent déplacées en arrière-plan avec mise en cache.

C'est en partie justifié, car l'utilisateur n'a pas toujours besoin des données les plus récentes et il y a un niveau de réponse acceptable de l'interface utilisateur.

Cette approche permet de répondre aux demandes commerciales, mais à terme, elle réduit l'efficacité du système d'information, reportant simplement les solutions aux problèmes.

Il convient également de rappeler que, lors de la recherche des nouveaux index à ajouter, les propositions MS SQL d'optimisation peuvent être incorrectes dans certaines conditions :

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

2) La combinaison d'attributs en un nouvel attribut

Parfois, certains champs d'une table, sur lesquels les conditions sont regroupées, peuvent être remplacés par l'introduction d'un nouveau champ.

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

Exemple :

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 est remplacée par Status = 1.

Ici, un attribut entier Status est introduit, assuré par le remplissage de ces statuts dans le tableau. Ensuite, cet nouvel attribut est indexé.

C'est une solution fondamentale au problème de performance, car nous accédons aux données sans calculs inutiles.

3) Matérialisation de la vue

Malheureusement, dans les requêtes LINQ, il n'est pas possible d'utiliser directement des tables temporaires, des CTE et des variables de table.

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

Un groupe de conditions (de l'exemple ci-dessus) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (ou un ensemble d'autres conditions similaires) devient une bonne option pour les utiliser dans une vue indexée, en mettant en cache un petit échantillon de données d'un grand ensemble.

Mais il y a certaines limitations lors de la matérialisation de la vue :

1. l'utilisation de sous-requêtes, les clauses soient « vrais », mais doivent être remplacées par l'utilisation de JOIN
2. il n'est pas possible d'utiliser des clauses UNION, UNION ALL, EXCEPTION, INTERSECT
3. il n'est pas possible d'utiliser des indexes de table et des clauses OPTION
4. il n'est pas possible de travailler avec des boucles
5. il n'est pas possible de retourner des 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 de la vue, ces index peuvent ne pas être utilisés, et pour les utiliser explicitement, il est nécessaire d'indiquer WITH (NOEXPAND).

Étant donné que dans les requêtes LINQ, il n'est pas possible de définir des indexes de table, il faut donc créer une autre vue — «votre wrapper» comme suit :

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

4) Utilisation de fonctions de table

Souvent dans les requêtes LINQ, de grands 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 non optimisée.

Les principaux avantages de l'utilisation des fonctions de table dans les requêtes LINQ,:

1. La possibilité, comme dans le cas des vues, d'utiliser et de spécifier comme objet, mais il est possible de passer un ensemble de paramètres d'entrée :
   FROM FUNCTION(@param1, @param2 …)
   en fin de compte, on peut obtenir une sélection flexible des données
2. 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. Indexes de table :
      via LINQ Il n'est pas possible de spécifier quels index doivent être utilisés et de définir le niveau d'isolation des données lors de la requête.
      Mais dans la fonction, ces capacités existent.
      Avec la fonction, il est possible d'obtenir un plan d'exécution de requête assez constant, où sont définies les règles de travail avec les index et les niveaux d'isolation des données.
   2. L'utilisation de la fonction permet, par rapport aux vues indexées, d'obtenir :
      • une logique complexe de sélection de données (jusqu'à l'utilisation de boucles)
      • sélection de données à partir de plusieurs tables différentes.
      • l'utilisation UNION et soient « vrais », mais

3. La proposition OPTION est très utile quand nous devons assurer la gestion du parallélisme. OPTION(MAXDOP N), concernant le plan d'exécution de la requête. Par exemple :
   • il est possible de spécifier la recréation forcée du plan de requête. OPTION (RECOMPILE)
   • il est possible de spécifier la nécessité d'assurer l'utilisation forcée par le plan de requête de l'ordre de jointure indiqué dans la requête. OPTION (FORCE ORDER)

   Plus de détails sur OPTION est décrit ici.

4. L'utilisation du sous-ensemble de données le plus étroit et requis :
   Il n'est pas nécessaire de garder de grands ensembles de données en cache (comme dans le cas des vues indexées), à partir desquelles il est encore nécessaire de filtrer les données par paramètre.
   Par exemple, il existe une table qui utilise trois champs pour le filtre. OÙ (a, b, c) Conditionnellement, pour toutes les requêtes, il y a une condition constante..

   a = 0 et b = 0. Cependant, la requête sur le champ.

   est plus variable. c Supposons que la condition

   nous aide vraiment à limiter l'ensemble requis à des milliers d'enregistrements, mais la condition selon Cependant, la requête sur le champ nous réduit l'échantillon à une centaine d'enregistrements. avec Ici, une fonction de table peut s'avérer être une option plus avantageuse.

   De plus, la fonction de table est plus prévisible et constante en termes de temps d'exécution.

   Considérons un exemple de mise en œuvre basé sur la base de données Questions.

Exemples

Il existe une requête

, qui connecte plusieurs tables et utilise une vue (OperativeQuestions), dans laquelle on vérifie par email l'appartenance (via SELECT) aux « Requêtes Actives » ([OperativeQuestions]) : soient « vrais », maisRequête n° 1.

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 comporte des jointures de sous-requêtes et utilise le tri DISTINCT, ce qui est généralement une opération assez gourmande en ressources.

La sélection dans OperativeQuestions compte environ dix mille enregistrements.

Le principal problème de cette requête est que pour les enregistrements de la sous-requête externe, une sous-requête interne sur la vue [OperativeQuestions] doit nous limiter la sortie (par soient « vrais », mais) à quelques centaines d'enregistrements.

Il pourrait sembler que la sous-requête doive calculer une fois les enregistrements pour [Email] = @p__linq__0, puis que ces quelques centaines d'enregistrements doivent être jointes par Id avec Questions, et que la requête serait rapide.

En réalité, il se produit une jointure séquentielle de toutes les tables : et la vérification de la correspondance des Id Questions avec les Id de OperativeQuestions, ainsi que le filtrage par Email.

En fait, la requête travaille avec des dizaines de milliers d'enregistrements d'OperativeQuestions, alors que seules les données d'intérêt par Email sont nécessaires.

Le texte de la vue OperativeQuestions :

Requête 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));

Le mapping d'origine de la vue dans DbContext (EF Core 2)

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

Requête LINQ d'origine

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 examinons la solution de ce problème sans modifications infrastucturelles, sans introduire une table distincte avec des résultats préparés («Questions Actives»), pour laquelle un mécanisme de mise à jour de ses données et de maintien de son actualité serait nécessaire.

Bien que cela soit une bonne solution, il existe une autre option pour optimiser cette tâche.

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

Nous introduisons une fonction de table [dbo].[OperativeQuestionsUserMail] dans la base de données.

En envoyant comme paramètre d'entrée Email, nous obtenons un tableau de valeurs :

Requête 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

Ici, une table de valeurs est renvoyée avec une structure de données prédéfinie.

Pour que les requêtes vers OperativeQuestionsUserMail soient optimales et disposent de plans d'exécution adéquats, une structure stricte est nécessaire, et non RETURNS TABLE AS RETURN…

Dans ce cas, la Requête 1 recherchée est transformée en Requête 4 :

Requête 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]);

Mapping de la vue et de la fonction dans DbContext (EF Core 2)

classe publique QuestionsDbContext : DbContext
{
    \/\/...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    \/\/...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
classe statique 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();

Le temps d'exécution a été réduit de 200-800 ms à 2-20 ms, c'est-à-dire des dizaines de fois plus rapide.

En moyenne, au lieu de 350 ms, nous avons obtenu 8 ms.

Parmi les avantages évidents, nous avons également :

1. une réduction générale de la charge en lecture,
2. une diminution significative de la probabilité de blocages
3. une réduction du temps moyen de blocage à des valeurs acceptables

Sortie

L'optimisation et le réglage des appels à la base de données MS SQL via LINQ sont une tâche résoluble.

Dans ce travail, l'attention et la rigueur sont essentielles.

Au début du processus :

1. il est nécessaire de vérifier les données avec lesquelles la requête travaille (valeurs, types de données sélectionnés)
2. de faire un bon indexage de ces données
3. de vérifier la validité des conditions de jointure entre les tables

Lors de la prochaine itération d'optimisation, nous identifions :

1. la base de la requête et déterminons le filtre principal de la requête
2. des blocs similaires et répétitifs de la requête et analysons l'intersection des conditions
3. dans SSMS ou un autre GUI pour SQL Server optimiser cela même requête SQL (extraction de stockage intermédiaire, construction de la requête de résultat en utilisant ce stockage (peut être plusieurs))
4. à la dernière étape, en prenant la base de la requête de résultat requête SQL, la structure de la requête LINQ

En fin de compte, le résultat obtenu requête LINQ doit avoir une structure identique à celle de la requête SQL optimale révélée du point 3. Un immense merci aux collègues

Remerciements

jobgemws alex_ozr et de l'entreprise Fortis pour leur aide dans la préparation de ce matériel. Introduction Dans cet article, nous avons discuté de certaines méthodes d'optimisation des requêtes LINQ. Nous allons également en présenter d'autres.

Source : habr.com