LINQ ingresó a .NET como un nuevo y poderoso lenguaje de manipulación de datos. LINQ to SQL como parte le permite comunicarse de manera bastante conveniente con un DBMS usando, por ejemplo, Entity Framework. Sin embargo, al usarlo con bastante frecuencia, los desarrolladores se olvidan de observar qué tipo de consulta SQL generará el proveedor consultable, en su caso Entity Framework.
Veamos dos puntos principales usando un ejemplo.
Para hacer esto, cree una base de datos de prueba en SQL Server y cree dos tablas en ella usando la siguiente consulta:
Creación de la tabla
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
Ahora completemos la tabla Ref ejecutando el siguiente script:
Llenando la tabla de referencias
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
Completemos de manera similar la tabla Cliente usando el siguiente script:
Llenando la tabla de Clientes
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
Por lo tanto, recibimos dos tablas, una de las cuales tiene más de 1 millón de filas de datos y la otra tiene más de 10 millones de filas de datos.
Ahora en Visual Studio necesita crear un proyecto de prueba de la aplicación de consola Visual C# (.NET Framework):
A continuación, debe agregar una biblioteca para que Entity Framework interactúe con la base de datos.
Para agregarlo, haga clic derecho en el proyecto y seleccione Administrar paquetes NuGet en el menú contextual:
Luego, en la ventana de administración de paquetes NuGet que aparece, ingrese la palabra "Entity Framework" en la ventana de búsqueda, seleccione el paquete Entity Framework e instálelo:
A continuación, en el archivo App.config, después de cerrar el elemento configSections, debe agregar el siguiente bloque:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
En connectString debe ingresar la cadena de conexión.
Ahora creemos 3 interfaces en archivos separados:
- Implementación de la interfaz IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementación de la interfaz IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementación de la interfaz IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Y en un archivo separado crearemos una clase base BaseEntity para nuestras dos entidades, que incluirá campos comunes:
Implementación de la 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 nuestras dos entidades en archivos separados:
- Implementación de la clase Ref.
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementación de la 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; } } }
Ahora creemos un contexto UserContext en un archivo separado:
Implementación de la 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 una solución preparada para realizar pruebas de optimización con LINQ to SQL a través de EF para MS SQL Server:
Ahora ingrese el siguiente código en el archivo Program.cs:
Archivo programa.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, lancemos nuestro proyecto.
Al finalizar el trabajo, se mostrará en la consola lo siguiente:
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])
Es decir, en general, la consulta LINQ generó bastante bien una consulta SQL al DBMS de MS SQL Server.
Ahora cambiemos la condición AND a OR en 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 };
Y ejecutemos nuestra aplicación nuevamente.
La ejecución fallará con un error debido a que el tiempo de ejecución del comando excede los 30 segundos:
Si observa la consulta generada por LINQ:
, entonces puedes asegurarte de que la selección se produzca mediante el producto cartesiano de dos conjuntos (tablas):
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]
Reescribamos la consulta LINQ de la siguiente manera:
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 });
Luego obtenemos la siguiente 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]
Lamentablemente, en las consultas LINQ solo puede haber una condición de unión, por lo que aquí es posible realizar una consulta equivalente usando dos consultas para cada condición y luego combinarlas a través de Union para eliminar duplicados entre las filas.
Sí, las consultas generalmente no serán equivalentes, teniendo en cuenta que es posible que se devuelvan filas duplicadas completas. Sin embargo, en la vida real, no se necesitan líneas duplicadas completas y la gente intenta deshacerse de ellas.
Ahora comparemos los planes de ejecución de estas dos consultas:
- para CROSS JOIN el tiempo promedio de ejecución es 195 segundos:
- para INNER JOIN-UNION el tiempo promedio de ejecución es inferior a 24 segundos:
Como puede ver en los resultados, para dos tablas con millones de registros, la consulta LINQ optimizada es muchas veces más rápida que la no optimizada.
Para la opción con AND en las condiciones, una consulta LINQ del 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 };
Casi siempre se generará la consulta SQL correcta, que se ejecutará en promedio en aproximadamente 1 segundo:
También para manipulaciones de LINQ to Objects en lugar de una consulta como:
Consulta LINQ (primera 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 };
puedes usar una consulta como:
Consulta LINQ (primera 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 };
donde:
Definiendo dos 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" } };
y el tipo Para se define de la siguiente manera:
Definición de tipo de párrafo
class Para
{
public int Key1, Key2;
public string Data;
}
Por lo tanto, examinamos algunos aspectos de la optimización de consultas LINQ a MS SQL Server.
Desafortunadamente, incluso los desarrolladores .NET líderes y experimentados olvidan que necesitan comprender qué hacen las instrucciones que utilizan detrás de escena. De lo contrario, se convierten en configuradores y pueden colocar una bomba de tiempo en el futuro, tanto al escalar la solución de software como con cambios menores en las condiciones ambientales externas.
También se realizó una breve reseña .
Se ubican las fuentes para la prueba: el proyecto en sí, la creación de tablas en la base de datos TEST, así como el llenado de estas tablas con datos. .
También en este repositorio, en la carpeta Planes, hay planes para ejecutar consultas con condiciones OR.
Fuente: habr.com