LINQ hà intrutu in .NET cum'è una nova lingua putente di manipulazione di dati. LINQ to SQL cum'è parte di questu permette di cumunicà cù u DBMS abbastanza convenientemente utilizendu, per esempiu, l'Entity Framework. In ogni casu, aduprendu abbastanza spessu, i sviluppatori si scurdanu di guardà chì tipu di query SQL chì u fornitore di interrogazione generarà, in u vostru casu, l'Entity Framework.
Fighjemu dui punti principali cù un esempiu.
Per fà questu, in SQL Server avemu da creà una basa di dati Test, è in questu avemu da creà dui tavule cù a seguente dumanda:
Creazione di tavule
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
Avà populate a tabella Ref eseguendu u script seguente:
Ripienu a tavula 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
Pupulamu a tavola di u Cliente in u listessu modu usendu u script seguente:
Pupulare a tavola 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
Cusì, avemu dui tavule, unu di i quali hà più di 1 milione di file di dati, è l'altru hà più di 10 milioni di file di dati.
Avà in Visual Studio, avete bisognu di creà un prughjettu di prova Visual C# Console App (.NET Framework):
In seguitu, avete bisognu di aghjunghje una biblioteca per l'Entity Framework per interagisce cù a basa di dati.
Per aghjunghje, cliccate right-click nant'à u prugettu è selezziunate Manage NuGet Packages da u menù di cuntestu:
Dopu, in a finestra di gestione di u pacchettu NuGet chì appare, in a casella di ricerca, inserite a parolla "Entity Framework" è selezziunate u pacchettu Entity Framework è installate:
Dopu, in u schedariu App.config, dopu avè chjusu l'elementu configSections, aghjunghje u seguente bloccu:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
In connectionString avete bisognu di inserisce a stringa di cunnessione.
Avà creemu 3 interfacce in schedarii separati:
- Implementazione di l'interfaccia IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementazione di l'interfaccia IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementazione di l'interfaccia IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
È in un schedariu separatu, creeremu una classa di basa BaseEntity per e nostre duie entità, chì includeranu campi cumuni:
Implementazione di a classa 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; }
}
}
Dopu, in schedarii separati, creeremu e nostre duie entità:
- Implementazione di a classe Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementazione di a classa Cliente
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; } } }
Avà criemu un cuntestu UserContext in un schedariu separatu:
Implementazione di a 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; }
}
}
Avemu una soluzione pronta per fà teste di ottimisazione cù LINQ to SQL via EF per MS SQL Server:
Avà in u schedariu Program.cs, entre u codice seguente:
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();
}
}
}
}
Dopu, andemu à eseguisce u nostru prughjettu.
À a fine di u travagliu, i seguenti seranu visualizati nantu à a cunsola:
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])
Hè, in generale, a query LINQ hà generatu una dumanda SQL à u MS SQL Server DBMS abbastanza bè.
Avà cambiemu a cundizione AND in OR in a query LINQ:
dumanda 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 };
Eseguimu di novu a nostra applicazione.
L'esekzione crash cun un errore ligatu à u tempu di esecuzione di u cumandimu più di 30 seconde:
Se guardate quale dumanda hè stata generata da LINQ:
, allura pudete assicurà chì a selezzione si faci per mezu di u produttu cartesianu di dui setti (tavule):
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 a query LINQ cusì:
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 });
Allora avemu a seguente dumanda SQL:
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]
Alas, in e dumande LINQ ci pò esse una sola cundizione di cunghjunzione, dunque hè pussibule di fà una dumanda equivalente attraversu duie dumande per ogni cundizione, seguita da a so unione attraversu Union per sguassà i duplicati trà e fila.
Iè, e dumande seranu generalmente micca equivalenti, postu chì e file duplicate cumplete ponu esse restituite. In ogni casu, in a vita reale, e linee duplicate piene ùn sò micca necessarii è stanu circannu di caccià.
Avà paragunemu i piani di esecuzione di sti dui dumande:
- per CROSS JOIN, u tempu mediu di esecuzione hè di 195 sec:
- per INNER JOIN-UNION u tempu mediu di esecuzione hè menu di 24 sec:
Comu pò esse vistu da i risultati, per duie tavule cù milioni di registri, a query LINQ ottimisata hè parechje volte più veloce di quella micca ottimizzata.
Per a variante cù AND in e cundizioni di una dumanda LINQ di a forma:
dumanda 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 };
quasi sempre una dumanda SQL curretta serà generata, chì correrà in media per circa 1 secondu:
Ancu per e manipulazioni di LINQ to Objects invece di dumandà a vista:
Query LINQ (1a 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 };
Pudete aduprà una dumanda cum'è:
Query LINQ (2a 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 };
induve:
Definizione di dui matrici
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" } };
, è u tipu Para hè definitu cusì:
Definizione di tipu para
class Para
{
public int Key1, Key2;
public string Data;
}
Cusì, avemu cunsideratu certi aspetti in l'ottimisazione di e dumande LINQ à MS SQL Server.
Sfurtunatamente, ancu i sviluppatori .NET sperimentati è di punta si scurdanu chì hè necessariu di capisce ciò chì l'istruzzioni chì utilizanu facenu daretu à i sceni. Altrimenti, diventanu cunfiguratori è ponu mette una bomba di tempu in u futuru, sia quandu scala una suluzione software, sia cù cambiamenti minori in e cundizioni ambientali esterni.
Ci era ancu una piccula rivista .
Fonti per a prova - u prughjettu stessu, creendu tavule in a basa di dati TEST, è ancu riempie sti tavule cù dati hè situatu .
Ancu in questu repository in u cartulare di i Piani sò piani per eseguisce dumande cù e cundizioni OR.
Source: www.habr.com