LINQ a intrat în .NET ca un nou limbaj puternic de manipulare a datelor. LINQ to SQL, ca parte a acestuia, vă permite să comunicați destul de convenabil cu un SGBD folosind, de exemplu, Entity Framework. Cu toate acestea, folosindu-l destul de des, dezvoltatorii uită să se uite la ce fel de interogare SQL va genera furnizorul interogabil, în cazul tău Entity Framework.
Să ne uităm la două puncte principale folosind un exemplu.
Pentru a face acest lucru, creați o bază de date de testare în SQL Server și creați două tabele în ea folosind următoarea interogare:
Crearea de tabele
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
Acum să completăm tabelul Ref rulând următorul script:
Completarea tabelului 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
Să completăm în mod similar tabelul Client folosind următorul script:
Popularea tabelului 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
Astfel, am primit două tabele, dintre care unul are peste 1 milion de rânduri de date, iar celălalt are peste 10 milioane de rânduri de date.
Acum, în Visual Studio, trebuie să creați un proiect de testare Visual C# Console App (.NET Framework):
Apoi, trebuie să adăugați o bibliotecă pentru ca Entity Framework să interacționeze cu baza de date.
Pentru a-l adăuga, faceți clic dreapta pe proiect și selectați Gestionați pachetele NuGet din meniul contextual:
Apoi, în fereastra de gestionare a pachetelor NuGet care apare, introduceți cuvântul „Entity Framework” în fereastra de căutare și selectați pachetul Entity Framework și instalați-l:
Apoi, în fișierul App.config, după închiderea elementului configSections, trebuie să adăugați următorul bloc:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
În connectionString trebuie să introduceți șirul de conexiune.
Acum să creăm 3 interfețe în fișiere separate:
- Implementarea interfeței IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementarea interfeței IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementarea interfeței IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Și într-un fișier separat vom crea o clasă de bază BaseEntity pentru cele două entități ale noastre, care va include câmpuri comune:
Implementarea clasei de bază BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
În continuare, vom crea cele două entități ale noastre în fișiere separate:
- Implementarea clasei Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementarea clasei 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; } } }
Acum să creăm un context UserContext într-un fișier separat:
Implementarea clasei 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; }
}
}
Am primit o soluție gata făcută pentru efectuarea de teste de optimizare cu LINQ to SQL prin EF pentru MS SQL Server:
Acum introduceți următorul cod în fișierul Program.cs:
Fișierul 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();
}
}
}
}
În continuare, să lansăm proiectul nostru.
La sfârșitul lucrării, pe consolă vor fi afișate următoarele:
Interogare SQL generată
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])
Adică, în general, interogarea LINQ a generat destul de bine o interogare SQL către MS SQL Server DBMS.
Acum, să schimbăm condiția AND la OR în interogarea LINQ:
Interogare 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 };
Și hai să lansăm aplicația noastră din nou.
Execuția se va bloca cu o eroare din cauza timpului de execuție a comenzii care depășește 30 de secunde:
Dacă vă uitați la interogarea care a fost generată de LINQ:
, atunci vă puteți asigura că selecția are loc prin produsul cartezian a două mulțimi (tabele):
Interogare SQL generată
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]
Să rescriem interogarea LINQ după cum urmează:
Interogare LINQ optimizată
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 });
Apoi obținem următoarea interogare SQL:
interogare 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]
Din păcate, în interogările LINQ poate exista o singură condiție de alăturare, așa că aici este posibil să se facă o interogare echivalentă folosind două interogări pentru fiecare condiție și apoi combinându-le prin Union pentru a elimina duplicatele dintre rânduri.
Da, interogările vor fi, în general, neechivalente, ținând cont de faptul că rândurile complete duplicat pot fi returnate. Cu toate acestea, în viața reală, nu sunt necesare linii duplicate complete și oamenii încearcă să scape de ele.
Acum să comparăm planurile de execuție ale acestor două interogări:
- pentru CROSS JOIN, timpul mediu de execuție este de 195 de secunde:
- pentru INNER JOIN-UNION timpul mediu de execuție este mai mic de 24 de secunde:
După cum puteți vedea din rezultate, pentru două tabele cu milioane de înregistrări, interogarea LINQ optimizată este de multe ori mai rapidă decât cea neoptimizată.
Pentru opțiunea cu AND în condiții, o interogare LINQ de forma:
Interogare 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 };
Aproape întotdeauna va fi generată interogarea SQL corectă, care va rula în medie în aproximativ 1 secundă:
De asemenea, pentru manipulări LINQ to Objects în loc de o interogare precum:
Interogare LINQ (prima opțiune)
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 };
puteți folosi o interogare ca:
Interogare LINQ (prima opțiune)
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 };
în cazul în care:
Definirea a două matrice
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" } };
, iar tipul Para este definit după cum urmează:
Definiția tipului para
class Para
{
public int Key1, Key2;
public string Data;
}
Astfel, am examinat câteva aspecte în optimizarea interogărilor LINQ către MS SQL Server.
Din păcate, chiar și dezvoltatorii .NET cu experiență și lideri uită că trebuie să înțeleagă ce fac instrucțiunile pe care le folosesc în culise. În caz contrar, aceștia devin configuratori și pot pune o bombă cu ceas în viitor atât la scalarea soluției software, cât și la modificări minore ale condițiilor externe de mediu.
De asemenea, a fost efectuată o scurtă analiză .
Sursele pentru test - proiectul în sine, crearea de tabele în baza de date TEST, precum și completarea acestor tabele cu date sunt localizate .
Tot în acest depozit, în folderul Planuri, există planuri pentru executarea de interogări cu condiții SAU.
Sursa: www.habr.com