LINQ entrou en .NET como unha poderosa linguaxe de manipulación de datos. LINQ to SQL como parte del permítelle comunicarse de xeito bastante cómodo cun DBMS usando, por exemplo, Entity Framework. Non obstante, usándoo con bastante frecuencia, os desenvolvedores esquécense de mirar que tipo de consulta SQL xerará o provedor consultable, no seu caso Entity Framework.
Vexamos dous puntos principais usando un exemplo.
Para iso, cree unha base de datos de proba en SQL Server e cree nela dúas táboas usando a seguinte consulta:
Creación de táboas
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
Agora imos encher a táboa Ref executando o seguinte script:
Enchendo a táboa de 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
De igual xeito, enchemos a táboa de Clientes usando o seguinte script:
Poboando a táboa Cliente
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
Así, recibimos dúas táboas, unha das cales ten máis de 1 millón de filas de datos e a outra máis de 10 millóns de filas de datos.
Agora, en Visual Studio, debes crear un proxecto de proba da aplicación de consola Visual C# (.NET Framework):
A continuación, cómpre engadir unha biblioteca para que Entity Framework interactúe coa base de datos.
Para engadilo, fai clic co botón dereito sobre o proxecto e selecciona Xestionar paquetes NuGet no menú contextual:
A continuación, na xanela de xestión de paquetes NuGet que aparece, introduza a palabra "Entity Framework" na xanela de busca e seleccione o paquete Entity Framework e instáleo:
A continuación, no ficheiro App.config, despois de pechar o elemento configSections, cómpre engadir o seguinte bloque:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
En connectionString cómpre introducir a cadea de conexión.
Agora imos crear 3 interfaces en ficheiros separados:
- Implementación da interface IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementación da interface IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementación da interface IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
E nun ficheiro separado crearemos unha clase base BaseEntity para as nosas dúas entidades, que incluirá campos comúns:
Implementación da clase 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ón, crearemos as nosas dúas entidades en ficheiros separados:
- Implementación da clase Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implantación da clase 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; } } }
Agora imos crear un contexto UserContext nun ficheiro separado:
Implementación da clase 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; }
}
}
Recibimos unha solución preparada para realizar probas de optimización con LINQ to SQL a través de EF para MS SQL Server:
Agora introduza o seguinte código no ficheiro Program.cs:
Arquivo 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ón, imos lanzar o noso proxecto.
Ao final do traballo, aparecerá o seguinte na consola:
Consulta SQL xerada
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])
É dicir, en xeral, a consulta LINQ xerou bastante ben unha consulta SQL ao DBMS MS SQL Server.
Agora imos cambiar a condición AND a OR na 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 };
E imos lanzar de novo a nosa aplicación.
A execución fallará cun erro debido a que o tempo de execución do comando supera os 30 segundos:
Se mira a consulta que foi xerada por LINQ:
, entón podes asegurarte de que a selección se produce a través do produto cartesiano de dous conxuntos (táboas):
Consulta SQL xerada
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]
Reescribamos a consulta LINQ do seguinte xeito:
Consulta LINQ optimizada
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 });
Despois obtemos a seguinte 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]
Por desgraza, nas consultas LINQ só pode haber unha condición de unión, polo que aquí é posible facer unha consulta equivalente usando dúas consultas para cada condición e combinándoas despois a través de Union para eliminar duplicados entre as filas.
Si, as consultas xeralmente non serán equivalentes, tendo en conta que se poden devolver filas duplicadas completas. Non obstante, na vida real, non son necesarias liñas duplicadas completas e a xente intenta desfacerse delas.
Agora imos comparar os plans de execución destas dúas consultas:
- para CROSS JOIN, o tempo medio de execución é de 195 segundos:
- para INNER JOIN-UNION o tempo medio de execución é inferior a 24 segundos:
Como podes ver nos resultados, para dúas táboas con millóns de rexistros, a consulta LINQ optimizada é moitas veces máis rápida que a non optimizada.
Para a opción con AND nas condicións, unha consulta LINQ do formulario:
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 };
Case sempre xerarase a consulta SQL correcta, que se executará de media en aproximadamente 1 segundo:
Tamén para manipulacións de LINQ to Objects en lugar dunha consulta como:
Consulta LINQ (1ª opción)
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 };
podes usar unha consulta como:
Consulta LINQ (2ª opción)
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 };
en que:
Definición de dúas matrices
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 o tipo Para defínese como segue:
Definición de tipo de para
class Para
{
public int Key1, Key2;
public string Data;
}
Así, examinamos algúns aspectos na optimización de consultas LINQ para MS SQL Server.
Desafortunadamente, incluso os desenvolvedores de .NET experimentados e líderes esquecen que teñen que entender o que fan as instrucións que usan entre bastidores. En caso contrario, convértense en configuradores e poden poñer unha bomba de reloxería no futuro tanto ao escalar a solución de software como con pequenos cambios nas condicións ambientais externas.
Tamén se realizou unha pequena revisión .
Localízanse as fontes para a proba: o propio proxecto, a creación de táboas na base de datos TEST, así como o enchemento destas táboas con datos. .
Tamén neste repositorio, no cartafol Plans, hai plans para executar consultas con condicións OU.
Fonte: www.habr.com