LINQ è entrato in .NET come un nuovo e potente linguaggio di manipolazione dei dati. LINQ to SQL come parte di esso consente di comunicare in modo abbastanza conveniente con un DBMS utilizzando, ad esempio, Entity Framework. Tuttavia, utilizzandolo abbastanza spesso, gli sviluppatori dimenticano di considerare il tipo di query SQL che il provider interrogabile, nel tuo caso Entity Framework, genererà.
Esaminiamo due punti principali utilizzando un esempio.
Per fare ciò, crea un database di test in SQL Server e crea due tabelle al suo interno utilizzando la seguente query:
Creazione di tabelle
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
Ora popoliamo la tabella Ref eseguendo il seguente script:
Compilazione della tabella 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
Allo stesso modo riempiamo la tabella Clienti utilizzando il seguente script:
Popolamento della tabella Clienti
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
Pertanto, abbiamo ricevuto due tabelle, una delle quali contiene più di 1 milione di righe di dati e l'altra più di 10 milioni di righe di dati.
Ora in Visual Studio è necessario creare un progetto di prova dell'app console Visual C# (.NET Framework):
Successivamente, è necessario aggiungere una libreria affinché Entity Framework possa interagire con il database.
Per aggiungerlo, fare clic con il pulsante destro del mouse sul progetto e selezionare Gestisci pacchetti NuGet dal menu contestuale:
Successivamente, nella finestra di gestione dei pacchetti NuGet che appare, inserisci la parola “Entity Framework” nella finestra di ricerca e seleziona il pacchetto Entity Framework e installalo:
Successivamente, nel file App.config, dopo aver chiuso l'elemento configSections, è necessario aggiungere il seguente blocco:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
In ConnectionString è necessario inserire la stringa di connessione.
Ora creiamo 3 interfacce in file separati:
- Implementazione dell'interfaccia IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementazione dell'interfaccia IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementazione dell'interfaccia IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
E in un file separato creeremo una classe base BaseEntity per le nostre due entità, che includerà campi comuni:
Implementazione della classe 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; }
}
}
Successivamente, creeremo le nostre due entità in file separati:
- Implementazione della classe Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementazione della 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; } } }
Ora creiamo un contesto UserContext in un file separato:
Implementazione della 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; }
}
}
Abbiamo ricevuto una soluzione già pronta per condurre test di ottimizzazione con LINQ to SQL tramite EF per MS SQL Server:
Ora inserisci il seguente codice nel file Program.cs:
File 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<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();
}
}
}
}
Successivamente, lanciamo il nostro progetto.
Al termine del lavoro sulla console verrà visualizzato quanto segue:
Query SQL generata
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])
Cioè, in generale, la query LINQ ha generato abbastanza bene una query SQL sul DBMS MS SQL Server.
Ora cambiamo la condizione AND in OR nella query LINQ:
Domanda 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 };
E lanciamo di nuovo la nostra applicazione.
L'esecuzione si bloccherà con un errore a causa del tempo di esecuzione del comando superiore a 30 secondi:
Se guardi la query generata da LINQ:
, allora puoi fare in modo che la selezione avvenga tramite il prodotto cartesiano di due insiemi (tabelle):
Query SQL generata
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]
Riscriviamo la query LINQ come segue:
Query LINQ ottimizzata
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 });
Quindi otteniamo la seguente query SQL:
Interrogazione 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]
Purtroppo, nelle query LINQ può esserci solo una condizione di join, quindi qui è possibile creare una query equivalente utilizzando due query per ciascuna condizione e poi combinandole tramite Union per rimuovere i duplicati tra le righe.
Sì, le query generalmente non saranno equivalenti, tenendo conto che potrebbero essere restituite righe duplicate complete. Tuttavia, nella vita reale, non sono necessarie linee duplicate complete e le persone cercano di sbarazzarsene.
Ora confrontiamo i piani di esecuzione di queste due query:
- per CROSS JOIN il tempo medio di esecuzione è di 195 secondi:
- per INNER JOIN-UNION il tempo medio di esecuzione è inferiore a 24 secondi:
Come puoi vedere dai risultati, per due tabelle con milioni di record, la query LINQ ottimizzata è molte volte più veloce di quella non ottimizzata.
Per l'opzione con AND nelle condizioni, una query LINQ nel formato:
Domanda 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 };
Verrà quasi sempre generata la query SQL corretta, che verrà eseguita mediamente in circa 1 secondo:
Anche per le manipolazioni LINQ to Objects invece di una query come:
Query LINQ (prima opzione)
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 };
puoi usare una query come:
Query LINQ (prima opzione)
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 };
dove:
Definizione di due array
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" } };
e il tipo Para è definito come segue:
Definizione del tipo di paragrafo
class Para
{
public int Key1, Key2;
public string Data;
}
Pertanto, abbiamo esaminato alcuni aspetti dell'ottimizzazione delle query LINQ su MS SQL Server.
Sfortunatamente, anche gli sviluppatori .NET più esperti e leader dimenticano che è necessario comprendere cosa fanno dietro le quinte le istruzioni che utilizzano. Altrimenti diventano configuratori e possono piazzare una bomba a orologeria per il futuro sia quando si scala la soluzione software che con piccoli cambiamenti nelle condizioni ambientali esterne.
È stata effettuata anche una breve revisione .
Si trovano le fonti del test: il progetto stesso, la creazione di tabelle nel database TEST e il riempimento di queste tabelle con i dati .
Anche in questo repository, nella cartella Plans, sono presenti i piani per l'esecuzione di query con condizioni OR.
Fonte: habr.com