LINQ va entrar a .NET com un nou llenguatge de manipulació de dades potent. LINQ to SQL, com a part, us permet comunicar-vos amb el SGBD de manera molt còmoda utilitzant, per exemple, l'Entity Framework. Tanmateix, utilitzant-lo amb força freqüència, els desenvolupadors s'obliden de mirar quin tipus de consulta SQL generarà el proveïdor consultable, en el vostre cas, l'Entity Framework.
Vegem dos punts principals amb un exemple.
Per fer-ho, a SQL Server crearem una base de dades Test, i en ella crearem dues taules mitjançant la següent consulta:
Creació de taules
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
Ara omplim la taula Ref executant l'script següent:
Omplir la taula 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
Omplim la taula de clients de la mateixa manera utilitzant l'script següent:
Omplint la taula de clients
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
Així, hem obtingut dues taules, una de les quals té més d'1 milió de files de dades i l'altra té més de 10 milions de files de dades.
Ara, a Visual Studio, heu de crear un projecte de prova Visual C# Console App (.NET Framework):
A continuació, heu d'afegir una biblioteca perquè l'Entity Framework interactuï amb la base de dades.
Per afegir-lo, feu clic amb el botó dret al projecte i seleccioneu Gestiona paquets NuGet al menú contextual:
A continuació, a la finestra de gestió de paquets NuGet que apareix, al quadre de cerca, introduïu la paraula "Entity Framework" i seleccioneu el paquet Entity Framework i instal·leu-lo:
A continuació, al fitxer App.config, després de tancar l'element configSections, afegiu el bloc següent:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
A connectionString heu d'introduir la cadena de connexió.
Ara creem 3 interfícies en fitxers separats:
- Implementació de la interfície IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementació de la interfície IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementació de la interfície IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
I en un fitxer separat, crearem una classe base BaseEntity per a les nostres dues entitats, que inclourà camps comuns:
Implementació de la 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; }
}
}
A continuació, en fitxers separats, crearem les nostres dues entitats:
- Implementació de la classe Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementació de la classe 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; } } }
Ara creem un context UserContext en un fitxer separat:
Implementació de la 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; }
}
}
Tenim una solució preparada per realitzar proves d'optimització amb LINQ to SQL mitjançant EF per a MS SQL Server:
Ara, al fitxer Program.cs, introduïu el codi següent:
Fitxer 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();
}
}
}
}
A continuació, executem el nostre projecte.
Al final del treball, es mostrarà el següent a la consola:
Consulta SQL generada
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])
És a dir, en general, la consulta LINQ va generar una consulta SQL al DBMS MS SQL Server força bé.
Ara canviem la condició AND a OR a la consulta LINQ:
Consulta 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 };
Tornem a executar la nostra aplicació.
L'execució es bloquejarà amb un error relacionat amb el temps d'execució de l'ordre que superi els 30 segons:
Si observeu quina consulta va generar LINQ:
, llavors podeu assegurar-vos que la selecció es produeix mitjançant el producte cartesià de dos conjunts (taules):
Consulta SQL generada
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]
Reescriurem la consulta LINQ així:
Consulta LINQ optimitzada
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 });
Aleshores obtenim la següent consulta SQL:
Consulta 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]
Per desgràcia, a les consultes LINQ només hi pot haver una condició d'unió, per tant, és possible fer una consulta equivalent mitjançant dues consultes per a cada condició, seguida de la seva unió mitjançant Union per eliminar duplicats entre files.
Sí, les consultes generalment no seran equivalents, ja que es poden retornar files duplicades completes. Tanmateix, a la vida real, les línies duplicades completes no són necessàries i estan intentant desfer-se'n.
Ara comparem els plans d'execució d'aquestes dues consultes:
- per a CROSS JOIN, el temps mitjà d'execució és de 195 segons:
- per a INNER JOIN-UNION el temps mitjà d'execució és inferior a 24 segons:
Com es pot veure als resultats, per a dues taules amb milions de registres, la consulta LINQ optimitzada és moltes vegades més ràpida que la no optimitzada.
Per a la variant amb AND en les condicions d'una consulta LINQ de la forma:
Consulta 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 };
gairebé sempre es generarà una consulta SQL correcta, que s'executarà de mitjana durant aproximadament 1 segon:
També per a les manipulacions de LINQ to Objects en lloc de consultar la vista:
Consulta LINQ (1a opció)
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 };
Podeu utilitzar una consulta com:
Consulta LINQ (2a opció)
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 };
on:
Definició de dues matrius
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" } };
, i el tipus Para es defineix de la següent manera:
Definició de tipus para
class Para
{
public int Key1, Key2;
public string Data;
}
Així, hem considerat alguns aspectes en l'optimització de consultes LINQ a MS SQL Server.
Malauradament, fins i tot els desenvolupadors de .NET amb experiència i líders obliden que cal entendre què fan les instruccions que utilitzen darrere de les escenes. En cas contrari, es converteixen en configuradors i poden posar una bomba de rellotgeria en el futur, tant en escalar una solució de programari com amb canvis menors en les condicions ambientals externes.
També hi va haver una petita ressenya .
Fonts de la prova: es troba el projecte en si, creant taules a la base de dades TEST, així com omplint aquestes taules amb dades. .
També en aquest repositori a la carpeta Plans hi ha plans per executar consultes amb condicions OR.
Font: www.habr.com