O LINQ entrou no .NET como uma nova e poderosa linguagem de manipulação de dados. LINQ to SQL como parte dele permite que você se comunique de maneira bastante conveniente com um SGBD usando, por exemplo, Entity Framework. No entanto, ao usá-lo com frequência, os desenvolvedores esquecem de observar que tipo de consulta SQL o provedor consultável, no seu caso, o Entity Framework, irá gerar.
Vejamos dois pontos principais usando um exemplo.
Para fazer isso, crie um banco de dados de teste no SQL Server e crie duas tabelas nele usando a seguinte consulta:
Criando tabelas
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 vamos preencher a tabela Ref executando o seguinte script:
Preenchendo a tabela 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
Vamos preencher de forma semelhante a tabela Cliente usando o seguinte script:
Preenchendo a tabela 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
Assim, recebemos duas tabelas, uma das quais possui mais de 1 milhão de linhas de dados e a outra possui mais de 10 milhões de linhas de dados.
Agora, no Visual Studio, você precisa criar um projeto de teste do aplicativo de console do Visual C# (.NET Framework):
Em seguida, você precisa adicionar uma biblioteca para o Entity Framework interagir com o banco de dados.
Para adicioná-lo, clique com o botão direito no projeto e selecione Gerenciar pacotes NuGet no menu de contexto:
Em seguida, na janela de gerenciamento de pacotes NuGet que aparece, digite a palavra “Entity Framework” na janela de pesquisa e selecione o pacote Entity Framework e instale-o:
A seguir, no arquivo App.config, após fechar o elemento configSections, você precisa adicionar o seguinte bloco:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Em connectionString você precisa inserir a string de conexão.
Agora vamos criar 3 interfaces em arquivos separados:
- Implementando a interface IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementação da interface IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementação da interface IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
E em um arquivo separado criaremos uma classe base BaseEntity para nossas duas entidades, que incluirá campos comuns:
Implementação da 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 seguir, criaremos nossas duas entidades em arquivos separados:
- Implementação da classe Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementação da classe 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 vamos criar um contexto UserContext em um arquivo separado:
Implementação da 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; }
}
}
Recebemos uma solução pronta para realização de testes de otimização com LINQ to SQL via EF para MS SQL Server:
Agora insira o seguinte código no arquivo Program.cs:
Arquivo 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 seguir, vamos lançar nosso projeto.
Ao final do trabalho, será exibido no console:
Consulta SQL gerada
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])
Ou seja, em geral, a consulta LINQ gerou muito bem uma consulta SQL para o SGBD MS SQL Server.
Agora vamos alterar a condição AND para 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 vamos lançar nosso aplicativo novamente.
A execução irá travar com um erro devido ao tempo de execução do comando exceder 30 segundos:
Se você observar a consulta gerada pelo LINQ:
, então você pode ter certeza de que a seleção ocorre através do produto cartesiano de dois conjuntos (tabelas):
Consulta SQL gerada
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]
Vamos reescrever a consulta LINQ da seguinte maneira:
Consulta LINQ otimizada
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 });
Então 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]
Infelizmente, em consultas LINQ só pode haver uma condição de junção, então aqui é possível fazer uma consulta equivalente usando duas consultas para cada condição e depois combiná-las através de Union para remover duplicatas entre as linhas.
Sim, as consultas geralmente serão não equivalentes, levando em consideração que podem ser retornadas linhas duplicadas completas. No entanto, na vida real, linhas duplicadas completas não são necessárias e as pessoas tentam se livrar delas.
Agora vamos comparar os planos de execução dessas duas consultas:
- para CROSS JOIN o tempo médio de execução é de 195 segundos:
- para INNER JOIN-UNION o tempo médio de execução é inferior a 24 segundos:
Como você pode ver nos resultados, para duas tabelas com milhões de registros, a consulta LINQ otimizada é muitas vezes mais rápida que a não otimizada.
Para a opção com AND nas condições, uma consulta LINQ no formato:
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 };
Quase sempre será gerada a consulta SQL correta, que será executada em média em cerca de 1 segundo:
Também para manipulações de LINQ to Objects em vez de uma consulta como:
Consulta LINQ (1ª opção)
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 };
você pode usar uma consulta como:
Consulta LINQ (2ª opção)
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 };
em que:
Definindo duas matrizes
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 é definido da seguinte forma:
Definição de tipo de parágrafo
class Para
{
public int Key1, Key2;
public string Data;
}
Assim, examinamos alguns aspectos da otimização de consultas LINQ para MS SQL Server.
Infelizmente, mesmo os desenvolvedores .NET experientes e líderes esquecem que precisam entender o que as instruções que usam fazem nos bastidores. Caso contrário, eles se tornam configuradores e podem plantar uma bomba-relógio no futuro, tanto ao dimensionar a solução de software quanto com pequenas alterações nas condições ambientais externas.
Também foi realizada uma breve revisão .
As fontes para o teste - o próprio projeto, a criação de tabelas no banco de dados TEST, bem como o preenchimento dessas tabelas com dados estão localizadas .
Também neste repositório, na pasta Planos, existem planos para execução de consultas com condições OR.
Fonte: habr.com