LINQ è entrato in .NET come un nuovo potente linguaggio di manipolazione dei dati. LINQ to SQL, come parte di esso, consente di interagire in modo piuttosto semplice con i database utilizzando, ad esempio, Entity Framework. Tuttavia, frequentemente utilizzandolo, gli sviluppatori dimenticano di controllare quale esatto comando SQL verrà generato dal queryable provider, nel tuo caso — Entity Framework.
Esaminiamo due punti fondamentali attraverso un esempio.
Per questo, nel SQL Server, creiamo un database Test e all'interno di esso, creiamo due tabelle utilizzando la seguente query:
Creazione delle tabelle
USA [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
USA [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 riempiamo la tabella Ref eseguendo il seguente script:
Popolamento della tabella Ref
USA [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
Analogamente, riempiamo la tabella Customer eseguendo il seguente script:
Popolamento della tabella Customer
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=0) SET @ind_ref=4;
ELSE IF (@ind=0) SET @ind_ref=5;
ELSE IF (@ind=0) SET @ind_ref=6;
ELSE IF (@ind=0) SET @ind_ref=7;
ELSE IF (@ind=0) SET @ind_ref=8;
ELSE IF (@ind=0) SET @ind_ref=9;
ELSE IF (@ind=0) SET @ind_ref=10;
ELSE IF (@ind=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind90000;
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
Quindi abbiamo ottenuto due tabelle, una delle quali contiene oltre 1 milione di righe di dati e l'altra oltre 10 milioni di righe di dati.
Ora in Visual Studio è necessario creare un progetto di test Visual C# Console App (.NET Framework):
Successivamente, è necessario aggiungere una libreria per l'interazione con il database utilizzando Entity Framework.
Per aggiungerla, facciamo clic con il tasto destro sul progetto e selezioniamo nel menu contestuale Gestisci pacchetti NuGet:
Poi, nella finestra di gestione dei pacchetti NuGet che appare, digitare "Entity Framework" nella barra di ricerca, selezionare il pacchetto Entity Framework e installarlo:
Successivamente, nel file App.config, dopo la chiusura dell'elemento configSections, è necessario aggiungere il seguente blocco:
Nel connectionString è necessario inserire la stringa di connessione.
Ora creiamo in file separati 3 interfacce:
- 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 creiamo la classe base BaseEntity per le nostre due entità, che conterrà i 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, in file separati creiamo le nostre due entità:
- 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 in un file separato il contesto UserContext:
Implementazione della 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; }
}
}
Abbiamo ottenuto una soluzione pronta per eseguire test di ottimizzazione utilizzando LINQ to SQL attraverso EF per MS SQL Server:
Ora nel file Program.cs inseriamo il seguente codice:
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();
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 avviamo il nostro progetto.
Alla fine dell'esecuzione verrà stampato in console:
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è, nel complesso, il generatore LINQ ha creato un'istruzione SQL piuttosto buona per il database MS SQL Server.
Ora cambiamo la condizione da E a O nell'istruzione LINQ:
LINQ ottenuta
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 di nuovo eseguiamo la nostra applicazione.
L'esecuzione fallirà con un errore legato al superamento del tempo di esecuzione della query di 30 secondi:
Se si guarda quale istruzione è stata generata da LINQ:
, si può constatare che la selezione avviene tramite 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 l'istruzione LINQ come segue:
Istruzione 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 });
Allora otteniamo la seguente query SQL:
la query 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]
Sfortunatamente, nelle query LINQ, la condizione di join può essere solo una, quindi è possibile creare una query equivalente attraverso due richieste per ciascuna condizione, unendole successivamente tramite Union per rimuovere i duplicati tra le righe.
Sì, le query risulteranno generalmente non equivalenti, considerando che possono essere restituiti duplicati completi delle righe. Tuttavia, nella pratica, le righe duplicate complete non sono necessarie e si cerca di evitarle.
Adesso confrontiamo i piani di esecuzione di queste due query:
- per il CROSS JOIN, il tempo medio di esecuzione è di 195 secondi:
- per l'INNER JOIN-UNION, il tempo medio di esecuzione è inferiore ai 24 secondi:
Come si può vedere dai risultati, per due tabelle con milioni di record, la query LINQ ottimizzata funziona di gran lunga più velocemente rispetto a quella non ottimizzata.
Per l'opzione con AND nelle condizioni, la query LINQ ha il seguente aspetto:
LINQ ottenuta
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 una query SQL corretta che verrà eseguita in media in circa 1 secondo:
Inoltre, per le manipolazioni LINQ to Objects invece della query di tipo:
query LINQ (1ª 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 };
si può utilizzare una query di tipo:
query LINQ (2ª 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 Para
class Para
{
public int Key1, Key2;
public string Data;
}
Abbiamo quindi esaminato alcuni aspetti dell'ottimizzazione delle query LINQ per MS SQL Server.
Sfortunatamente, anche gli sviluppatori .NET esperti tendono a dimenticare che è fondamentale capire cosa fanno realmente le istruzioni che utilizzano. Altrimenti, diventano semplicemente configuratori e potrebbero inavvertitamente creare una bomba a orologeria per il futuro, sia in fase di scalabilità della soluzione software sia per piccole modifiche delle condizioni esterne.
È stata inoltre effettuata una breve panoramica .
I sorgenti per il test—il progetto stesso, la creazione delle tabelle nel database TEST e il riempimento di queste tabelle sono disponibili .
In questo repository, nella cartella Plans, si trovano i piani di esecuzione delle query con condizioni OR.
Fonte: habr.com
