ProHoster > Blog > Administração > Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

O LINQ entrou no .NET como uma nova linguagem poderosa para manipulação de dados. O LINQ to SQL, como parte dele, permite uma comunicação bastante conveniente com bancos de dados usando, por exemplo, o Entity Framework. No entanto, ao utilizá-lo com frequência, os desenvolvedores costumam esquecer de observar qual consulta SQL será gerada pelo provedor queryable, no seu caso — Entity Framework.

Vamos discutir dois pontos principais através de um exemplo.
Para isso, no SQL Server, criaremos um banco de dados Teste e, dentro dele, criaremos duas tabelas com o seguinte comando:

Criação de tabelas

USE [TESTE]
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 [TESTE]
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:

Preenchimento da tabela Ref

USE [TESTE]
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

Analogamente, iremos preencher a tabela Customer com o seguinte script:

Preenchimento da tabela Customer

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=0) SET @ind_ref=4;
	ELSE IF (@ind=0) SET @ind_ref=5;
	ELSE IF (@ind=0) SET @ind_ref=6;
	ELSE IF (@ind=0) SET @ind_ref=7;
	ELSE IF (@ind=0) SET @ind_ref=8;
	ELSE IF (@ind=0) SET @ind_ref=9;
	ELSE IF (@ind=0) SET @ind_ref=10;
	ELSE IF (@ind=0) SET @ind_ref=11;
	ELSE SET @ind_ref=@ind90000;
	
	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, conseguimos duas tabelas, uma com mais de 1 milhão de linhas de dados e a outra com mais de 10 milhões de linhas de dados.

Agora, no Visual Studio, é necessário criar um projeto de teste Visual C# Console App (.NET Framework):

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

Em seguida, é necessário adicionar a biblioteca para interação com o banco de dados para o Entity Framework.
Para adicionar, clique com o botão direito no projeto e selecione no menu de contexto Gerenciar Pacotes NuGet:

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

Em seguida, na janela de gerenciamento de pacotes NuGet que aparece, digite a palavra "Entity Framework" na caixa de pesquisa, selecione o pacote Entity Framework e instale-o:

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

Depois, no arquivo App.config, após o fechamento do elemento configSections, adicione o seguinte bloco:

No connectionString, você precisa inserir a string de conexão.

Agora, vamos criar 3 interfaces em arquivos separados:

  1. Implementação da interface IBaseEntityID
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. Implementação da interface IBaseEntityName
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. Implementação da interface IBaseNameInsertUTCDate
    namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

E em um arquivo separado, criaremos a classe base BaseEntity para nossas duas entidades, que conterá os 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; }
    }
}

Em seguida, criaremos nossas duas entidades em arquivos separados:

  1. Implementação da classe Ref
    using System.ComponentModel.DataAnnotations.Schema;

namespace TestLINQ
{
    [Table("Ref")]
    public class Ref : BaseEntity
    {
        public int ID2 { get; set; }
    }
}

  2. Implementação da classe Customer
    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, criaremos o contexto UserContext em um arquivo separado:

Implementação da classe UserContext

using System.Data.Entity;

namespace TestLINQ
{
    public class UserContext : DbContext
    {
        public UserContext()
            : base("DbConnection")
        {
            Database.SetInitializer(null);
        }

        public DbSet Customer { get; set; }
        public DbSet Ref { get; set; }
    }
}

Obtivemos uma solução pronta para executar testes de otimização com LINQ to SQL através do EF para MS SQL Server:

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

Agora, insira o seguinte código no arquivo Program.cs:

Arquivo Program.cs

usando System;
usando System.Collections.Generic;
usando System.Linq;

namespace TestLINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            usando (UserContext db = new UserContext())
            {
                var dblog = new List();
                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 iniciar nosso projeto.

No final da execução, 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, de forma geral, o LINQ gerou uma consulta SQL bastante aceitável para o SGBD MS SQL Server.

Agora, vamos alterar a condição de E para OU na consulta LINQ:

LINQ-query

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 novamente, vamos executar nosso aplicativo.

A execução falhará com um erro relacionado ao tempo limite da execução do comando em 30 segundos:

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

Se observarmos qual consulta foi gerada pelo LINQ:

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server
, podemos confirmar que a seleção está ocorrendo 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 forma:

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, obteremos a seguinte consulta SQL:

SQL-query

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, a condição de junção pode ser apenas uma, por isso é possível realizar uma consulta equivalente através de duas consultas para cada condição, com posterior união delas por meio do Union para eliminar duplicatas entre as linhas.
Sim, as consultas em geral resultarão em não equivalentes, considerando que podem ser retornadas duplicatas completas de linhas. No entanto, na vida real, duplicatas completas de linhas não são necessárias e procura-se eliminá-las.

Agora vamos comparar os planos de execução dessas duas consultas:

  1. para CROSS JOIN, o tempo médio de execução é de 195 segundos:
    Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server
  2. para INNER JOIN-UNION, o tempo médio de execução é de menos de 24 segundos:
    Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server

Como pode ser visto nos resultados, para duas tabelas com milhões de registros, a consulta LINQ otimizada funciona várias vezes mais rápido do que a não otimizada.

Para a versão com E nas condições da consulta LINQ do tipo:

LINQ-query

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 uma consulta SQL correta, que será executada em média em cerca de 1 segundo:

Alguns aspectos da otimização de consultas LINQ em C#.NET para MS SQL Server
Além disso, para manipulações LINQ to Objects, em vez da consulta do tipo:

Consulta LINQ (1ª versã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 };

pode-se usar uma consulta do tipo:

Consulta LINQ (2ª versã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 };

onde:

Definição de dois arrays

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 do tipo Para

class Para
{
        public int Key1, Key2;
        public string Data;
}

Assim, consideramos alguns aspectos na otimização de consultas LINQ para MS SQL Server.

Infelizmente, até mesmo desenvolvedores .NET experientes e principais esquecem que é necessário entender o que as instruções que usam fazem nos bastidores. Caso contrário, tornam-se configuradores e podem colocar uma bomba de atraso no futuro, tanto ao escalar a solução de software quanto com pequenas mudanças nas condições externas do ambiente.

Também foi realizada uma breve revisão e aqui.

Os arquivos fonte para o teste — o próprio projeto, a criação de tabelas no banco de dados TEST e também o preenchimento dessas tabelas está localizado aqui.
Além disso, neste repositório, na pasta Plans, estão os planos de execução de consultas com condições OU.

Fonte: habr.com

Compre hospedagem confiável para sites com proteção contra DDoS, servidores VPS VDS 🔥 Compre hospedagem confiável para sites com proteção contra DDoS, servidores VPS VDS | ProHoster