LINQ a Ă©tĂ© intĂ©grĂ© Ă .NET comme un nouveau langage puissant de manipulation des donnĂ©es. LINQ to SQL, en tant que partie de celui-ci, permet une interaction relativement facile avec les bases de donnĂ©es en utilisant, par exemple, Entity Framework. Cependant, en l'utilisant frĂ©quemment, les dĂ©veloppeurs oublient souvent de vĂ©rifier quelle requĂȘte SQL le fournisseur queryable gĂ©nĂ©rera, dans votre cas â Entity Framework.
Examinons deux points principaux Ă l'aide d'un exemple.
Pour cela, dans SQL Server, crĂ©ons une base de donnĂ©es Test, et dans celle-ci, crĂ©ons deux tables avec la requĂȘte suivante :
Création des tables
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
Nous allons maintenant remplir la table Ref en exécutant le script suivant :
Remplissage de 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
De la mĂȘme maniĂšre, remplissons la table Customer avec le script suivant :
Remplissage de la table Customer
UTILISER [TEST]
GO
DĂCLARER @ind INT=1;
DĂCLARER @ind_ref INT=1;
ALORS(@ind<=12000000)
DĂBUT
SI(@ind%3=0) SET @ind_ref=1;
SINON SI (@ind%5=0) SET @ind_ref=2;
SINON SI (@ind%7=0) SET @ind_ref=3;
SINON SI (@ind=0) SET @ind_ref=4;
SINON SI (@ind=0) SET @ind_ref=5;
SINON SI (@ind=0) SET @ind_ref=6;
SINON SI (@ind=0) SET @ind_ref=7;
SINON SI (@ind=0) SET @ind_ref=8;
SINON SI (@ind=0) SET @ind_ref=9;
SINON SI (@ind=0) SET @ind_ref=10;
SINON SI (@ind=0) SET @ind_ref=11;
SINON SET @ind_ref=@ind90000;
INSĂRER DANS [dbo].[Client]
([ID]
,[Nom]
,[Ref_ID]
,[Ref_ID2])
SĂLECTIONNER
@ind,
CAST(@ind AS NVARCHAR(255)),
@ind_ref,
@ind_ref;
RĂGLER @ind=@ind+1;
FIN
GO
Ainsi, nous avons obtenu deux tables, dont l'une contient plus d'un million de lignes de données, et l'autre plus de 10 millions de lignes de données.
Maintenant, dans Visual Studio, il est nécessaire de créer un projet de test Visual C# Console App (.NET Framework) :
Ensuite, il est nécessaire d'ajouter une bibliothÚque pour Entity Framework afin d'interagir avec la base de données.
Pour l'ajouter, faisons un clic droit sur le projet et sélectionnons "Gérer les packages NuGet" dans le menu contextuel :
Ensuite, dans la fenĂȘtre de gestion des packages NuGet qui apparaĂźt, saisissons le mot « Entity Framework » dans la barre de recherche, choisissons le paquet Entity Framework et installons-le :
Puis, dans le fichier App.config, aprÚs la fermeture de l'élément configSections, nous devons ajouter le bloc suivant :
Dans le connectionString, il faut Ă©crire la chaĂźne de connexion.
Maintenant créons 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 la classe de base BaseEntity pour nos deux entités, qui contiendra 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, dans des fichiers séparés, créons nos deux entité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 Customer
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; } } }
Maintenant, créons un fichier séparé pour le contexte UserContext :
Implémentation de la classe UserContext
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer(null);
}
public DbSet Customer { get; set; }
public DbSet Ref { get; set; }
}
}
Nous avons obtenu une solution prĂȘte Ă ĂȘtre testĂ©e pour l'optimisation avec LINQ to SQL via EF pour MS SQL Server :
Maintenant, dans le fichier Program.cs, Ă©crivons le code suivant :
Fichier Program.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();
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, nous allons lancer notre projet.
à la fin de l'exécution, la console affichera :
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])
C'est-Ă -dire, dans l'ensemble, la requĂȘte LINQ a gĂ©nĂ©rĂ© une requĂȘte SQL pour la base de donnĂ©es MS SQL Server qui est plutĂŽt bonne.
Maintenant, changeons la condition ET en OU 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 échouera avec une erreur liée au dépassement du temps d'exécution de la commande de 30 secondes :
Si nous regardons quelle requĂȘte a Ă©tĂ© gĂ©nĂ©rĂ©e par LINQ,
, nous pouvons constater que la sélection se fait par 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 obtiendrons 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]
Malheureusement, dans les requĂȘtes LINQ, il ne peut y avoir qu'une seule condition de jointure, c'est pourquoi il est possible de faire une requĂȘte Ă©quivalente Ă travers deux requĂȘtes pour chaque condition, suivie d'une union pour Ă©liminer les doublons parmi les lignes.
Oui, les requĂȘtes seront en gĂ©nĂ©ral non Ă©quivalentes, Ă©tant donnĂ© que des doublons complets de lignes peuvent ĂȘtre retournĂ©s. Cependant, dans la rĂ©alitĂ©, les doublons complets ne sont pas nĂ©cessaires et on essaie de s'en dĂ©barrasser.
Comparons maintenant les plans d'exĂ©cution de ces deux requĂȘtes :
- pour le CROSS JOIN, le temps d'exécution moyen est de 195 secondes :
- pour l'INNER JOIN-UNION, le temps d'exécution moyen est inférieur à 24 secondes :
Comme on peut le voir dans les rĂ©sultats, pour deux tables contenant des millions d'enregistrements, la requĂȘte LINQ optimisĂ©e fonctionne de maniĂšre beaucoup plus rapide que la version non optimisĂ©e.
Pour le cas avec des conditions 'ET' dans une requĂȘte LINQ de type :
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 };
presque toujours une requĂȘte SQL correcte sera gĂ©nĂ©rĂ©e, qui s'exĂ©cutera en moyenne en environ 1 seconde :
Ăgalement, pour les manipulations LINQ to Objects au lieu d'une requĂȘte de type :
RequĂȘte LINQ (premiĂšre variante)
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 };
on peut utiliser une requĂȘte de type :
RequĂȘte LINQ (deuxiĂšme variante)
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Ă©finition de 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 Para
class Para
{
public int Key1, Key2;
public string Data;
}
Ainsi, nous avons abordĂ© certains aspects de l'optimisation des requĂȘtes LINQ sur MS SQL Server.
Malheureusement, mĂȘme les dĂ©veloppeurs .NET expĂ©rimentĂ©s et de premier plan oublient qu'il est nĂ©cessaire de comprendre ce que les instructions qu'ils utilisent font en coulisses. Sinon, ils deviennent des configureurs et peuvent poser une bombe Ă retardement pour l'avenir, tant lors de la scalabilitĂ© de la solution logicielle que lors de changements mineurs des conditions environnementales.
Une petite revue a également été réalisée et .
Les sources pour le 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 se trouvent .
Dans ce dĂ©pĂŽt, le dossier Plans contient des plans d'exĂ©cution de requĂȘtes avec des conditions OU.
Source : habr.com
