LINQ a introduit .NET comme nouveau langage puissant de manipulation de données. LINQ to SQL dans le cadre de celui-ci vous permet de communiquer assez facilement avec un SGBD en utilisant, par exemple, Entity Framework. Cependant, en l'utilisant assez souvent, les développeurs oublient de regarder quel type de requête SQL le fournisseur interrogeable, dans votre cas Entity Framework, générera.
Examinons deux points principaux à l'aide d'un exemple.
Pour ce faire, créez une base de données de test dans SQL Server et créez-y deux tables à l'aide de la requête suivante :
Création de tableaux
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
[ID] [int] NOT NULL,
[ID2] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ref] ADD CONSTRAINT [DF_Ref_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Ref_ID] [int] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[Ref_ID2] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_Ref_ID] DEFAULT ((0)) FOR [Ref_ID]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
Remplissons maintenant la table Ref en exécutant le script suivant :
Remplir la table Ref
USE [TEST]
GO
DECLARE @ind INT=1;
WHILE(@ind<1200000)
BEGIN
INSERT INTO [dbo].[Ref]
([ID]
,[ID2]
,[Name])
SELECT
@ind
,@ind
,CAST(@ind AS NVARCHAR(255));
SET @ind=@ind+1;
END
GO
Remplissons de la même manière la table Customer à l'aide du script suivant :
Remplir la table Client
USE [TEST]
GO
DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;
WHILE(@ind<=12000000)
BEGIN
IF(@ind%3=0) SET @ind_ref=1;
ELSE IF (@ind%5=0) SET @ind_ref=2;
ELSE IF (@ind%7=0) SET @ind_ref=3;
ELSE IF (@ind%11=0) SET @ind_ref=4;
ELSE IF (@ind%13=0) SET @ind_ref=5;
ELSE IF (@ind%17=0) SET @ind_ref=6;
ELSE IF (@ind%19=0) SET @ind_ref=7;
ELSE IF (@ind%23=0) SET @ind_ref=8;
ELSE IF (@ind%29=0) SET @ind_ref=9;
ELSE IF (@ind%31=0) SET @ind_ref=10;
ELSE IF (@ind%37=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind%1190000;
INSERT INTO [dbo].[Customer]
([ID]
,[Name]
,[Ref_ID]
,[Ref_ID2])
SELECT
@ind,
CAST(@ind AS NVARCHAR(255)),
@ind_ref,
@ind_ref;
SET @ind=@ind+1;
END
GO
Ainsi, nous avons reçu deux tableaux, dont l'un contient plus d'un million de lignes de données et l'autre plus de 1 millions de lignes de données.
Maintenant, dans Visual Studio, vous devez créer un projet de test d'application console Visual C# (.NET Framework) :
Ensuite, vous devez ajouter une bibliothèque pour qu'Entity Framework puisse interagir avec la base de données.
Pour l'ajouter, cliquez avec le bouton droit sur le projet et sélectionnez Gérer les packages NuGet dans le menu contextuel :
Ensuite, dans la fenêtre de gestion des packages NuGet qui apparaît, saisissez le mot « Entity Framework » dans la fenêtre de recherche, sélectionnez le package Entity Framework et installez-le :
Ensuite, dans le fichier App.config, après avoir fermé l'élément configSections, vous devez ajouter le bloc suivant :
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Dans connectionString, vous devez saisir la chaîne de connexion.
Créons maintenant 3 interfaces dans des fichiers séparés :
- Implémentation de l'interface IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implémentation de l'interface IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implémentation de l'interface IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Et dans un fichier séparé, nous créerons une classe de base BaseEntity pour nos deux entités, qui comprendra des champs communs :
Implémentation de la classe de base BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Ensuite, nous allons créer nos deux entités dans des fichiers séparés :
- Implémentation de la classe Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implémentation de la classe Client
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Customer")] public class Customer: BaseEntity { public int Ref_ID { get; set; } public int Ref_ID2 { get; set; } } }
Créons maintenant un contexte UserContext dans un fichier séparé :
Implémentation de la classe UserContex
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer<UserContext>(null);
}
public DbSet<Customer> Customer { get; set; }
public DbSet<Ref> Ref { get; set; }
}
}
Nous avons reçu une solution prête à l'emploi pour effectuer des tests d'optimisation avec LINQ to SQL via EF pour MS SQL Server :
Entrez maintenant le code suivant dans le fichier Program.cs :
Fichier programme.cs
using System;
using System.Collections.Generic;
using System.Linq;
namespace TestLINQ
{
class Program
{
static void Main(string[] args)
{
using (UserContext db = new UserContext())
{
var dblog = new List<string>();
db.Database.Log = dblog.Add;
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
var result = query.Take(1000).ToList();
Console.WriteLine(dblog[1]);
Console.ReadKey();
}
}
}
}
Ensuite, lançons notre projet.
A la fin des travaux, les éléments suivants seront affichés sur la console :
Requête SQL générée
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])
Autrement dit, en général, la requête LINQ a assez bien généré une requête SQL vers le SGBD MS SQL Server.
Modifions maintenant la condition AND en OR dans la requête LINQ :
Requête LINQ
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
|| (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
Et relançons notre application.
L'exécution plantera avec une erreur en raison du temps d'exécution de la commande dépassant 30 secondes :
Si vous regardez la requête générée par LINQ :
, alors vous pouvez vous assurer que la sélection s'effectue via le produit cartésien de deux ensembles (tables) :
Requête SQL générée
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
CROSS JOIN [dbo].[Ref] AS [Extent2]
WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]
Réécrivons la requête LINQ comme suit :
Requête LINQ optimisée
var query = (from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID equals e2.ID
select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID2 equals e2.ID2
select new { Data1 = e1.Name, Data2 = e2.Name });
Nous obtenons alors la requête SQL suivante :
requête SQL
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3]
FROM ( SELECT DISTINCT TOP (1000)
[UnionAll1].[C1] AS [C1],
[UnionAll1].[Name] AS [C2],
[UnionAll1].[Name1] AS [C3]
FROM (SELECT
1 AS [C1],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
UNION ALL
SELECT
1 AS [C1],
[Extent3].[Name] AS [Name],
[Extent4].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent3]
INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
) AS [Limit1]
Hélas, dans les requêtes LINQ, il ne peut y avoir qu'une seule condition de jointure, il est donc ici possible de créer une requête équivalente en utilisant deux requêtes pour chaque condition, puis de les combiner via Union pour supprimer les doublons entre les lignes.
Oui, les requêtes ne seront généralement pas équivalentes, étant donné que des lignes complètes en double peuvent être renvoyées. Cependant, dans la vraie vie, des lignes en double complètes ne sont pas nécessaires et les gens essaient de s'en débarrasser.
Comparons maintenant les plans d'exécution de ces deux requêtes :
- pour CROSS JOIN le temps d'exécution moyen est de 195 secondes :
- pour INNER JOIN-UNION le temps d'exécution moyen est inférieur à 24 secondes :
Comme vous pouvez le voir sur les résultats, pour deux tables contenant des millions d'enregistrements, la requête LINQ optimisée est plusieurs fois plus rapide que la requête non optimisée.
Pour l'option avec AND dans les conditions, une requête LINQ de la forme :
Requête LINQ
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
La requête SQL correcte sera presque toujours générée, qui s'exécutera en moyenne en 1 seconde environ :
Également pour les manipulations LINQ to Objects au lieu d'une requête comme :
Requête LINQ (1ère option)
var query = from e1 in seq1
from e2 in seq2
where (e1.Key1==e2.Key1)
&& (e1.Key2==e2.Key2)
select new { Data1 = e1.Data, Data2 = e2.Data };
vous pouvez utiliser une requête comme :
Requête LINQ (2ère option)
var query = from e1 in seq1
join e2 in seq2
on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
select new { Data1 = e1.Data, Data2 = e2.Data };
où:
Définir deux tableaux
Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };
, et le type Para est défini comme suit :
Définition du type de para
class Para
{
public int Key1, Key2;
public string Data;
}
Ainsi, nous avons examiné certains aspects de l'optimisation des requêtes LINQ vers MS SQL Server.
Malheureusement, même les développeurs .NET expérimentés et de premier plan oublient qu'ils doivent comprendre ce que font les instructions qu'ils utilisent en coulisses. Sinon, ils deviennent des configurateurs et peuvent poser une bombe à retardement à l'avenir, à la fois lors de la mise à l'échelle de la solution logicielle et en cas de changements mineurs des conditions environnementales externes.
Un bref examen a également été effectué .
Les sources du test - le projet lui-même, la création de tables dans la base de données TEST, ainsi que le remplissage de ces tables avec des données sont localisés .
Également dans ce référentiel, dans le dossier Plans, il existe des plans pour exécuter des requêtes avec des conditions OR.
Source: habr.com