🥇Métodos de otimização de consultas LINQ em C#.NET

Introdução

Em este artigo foram considerados alguns métodos de otimização consultas LINQ.
Aqui, apresentaremos também algumas abordagens para a otimização do código relacionadas a consultas LINQ.

É sabido que LINQ(Language-Integrated Query) — é uma linguagem de consulta simples e conveniente para fontes de dados.

A LINQ to SQL é uma tecnologia de acesso a dados em SGBDs. É uma ferramenta poderosa para trabalhar com dados, onde consultas são construídas através de uma linguagem declarativa, que serão então transformadas em consultas SQL plataforma e enviadas ao servidor de banco de dados para execução. Neste contexto, vamos entender SGBD como MS SQL Server.

No entanto, consultas LINQ não são convertidas em consultas SQL, que um DBA experiente poderia escrever, considerando todos os nuances da otimização consultas SQL:

conexões otimizadas (JOIN) e filtragem de resultados (WHERE)
uma série de nuances no uso de junções e condições de grupo
várias variações na substituição de condições IN para EXISTSe NOT IN, para EXISTS
intermediário de armazenamento de resultados através de tabelas temporárias, CTE, variáveis de tabela
uso da cláusula (OPTION) com indicações e dicas de tabela WITH (…)
uso de visualizações indexadas, como uma das formas de eliminar leituras excessivas de dados durante as seleções

Os principais gargalos de desempenho resultantes consultas SQL durante a compilação consultas LINQ são:

consolidação de todo o mecanismo de seleção de dados em uma única consulta
duplicação de blocos de código idênticos, resultando em leituras excessivas de dados
grupos de condições compostas (lógicas 'e' e 'ou') — E e OR, unindo-se em condições complexas, leva o otimizador, tendo índices não-clusterizados apropriados, a, no final, começar a fazer varredura no índice clusterizado (INDEX SCAN) para grupos de condições
a profundidade de aninhamento das subconsultas torna a análise instruções SQL e a análise do plano de consultas por parte dos desenvolvedores e DBA

Métodos de otimização

Agora vamos passar diretamente para os métodos de otimização.

1) Indexação adicional

É melhor considerar os filtros nas tabelas principais de seleção, uma vez que frequentemente toda a consulta é construída em torno de uma ou duas tabelas principais (pedidos, pessoas, operações) e com um conjunto padrão de condições (IsClosed, Canceled, Enabled, Status). É importante criar índices correspondentes para as seleções identificadas.

Essa solução faz sentido quando a seleção por esses campos limita substancialmente o conjunto de resultados da consulta.

Por exemplo, temos 500.000 pedidos. No entanto, há apenas 2.000 pedidos ativos. Um índice bem projetado nos livrará INDEX SCAN de uma tabela grande e permitirá selecionar dados rapidamente através de um índice não clusterizado.

A falta de índices também pode ser identificada através de dicas de análise de planos de consulta ou coleta de estatísticas de visões do sistema MS SQL Server:

Todas as visualizações de dados contêm informações sobre índices ausentes, exceto índices espaciais.

No entanto, índices e cache muitas vezes são métodos de combater as consequências de consultas mal escritas. consultas LINQ e consultas SQL.

A prática dura da vida nos negócios mostra que muitas vezes a implementação de recursos de negócios dentro de certos prazos é importante. Portanto, consultas pesadas muitas vezes são transferidas para o modo em segundo plano com cache.

Isso é parcialmente justificável, pois o usuário nem sempre precisa dos dados mais recentes e há um nível aceitável de resposta da interface do usuário.

Esse approach permite atender às solicitações de negócios, mas acaba diminuindo a funcionalidade do sistema de informação, adiando a resolução de problemas.

Também é importante lembrar que durante a busca por novos índices a serem adicionados, as propostas MS SQL para otimização podem ser incorretas, incluindo nas seguintes condições:

se já existem índices com um conjunto semelhante de campos
se os campos na tabela não podem ser indexados devido a restrições de indexação (detalhes adicionais são descritos aqui).

2) Combinação de atributos em um novo atributo

Às vezes, alguns campos de uma tabela, sobre os quais há um grupo de condições, podem ser substituídos pela introdução de um único novo campo.

Isso é especialmente relevante para campos de estado, que geralmente são do tipo bit ou inteiro.

Exemplo:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 é substituído por Status = 1.

Aqui se insere um atributo inteiro Status, fornecido pela preenchimento desses status na tabela. Em seguida, é feita a indexação desse novo atributo.

Esta é uma solução fundamental para o problema de desempenho, pois acessamos os dados sem cálculos desnecessários.

3) Materialização de visualização

Infelizmente, em consultas LINQ não se pode usar diretamente tabelas temporárias, CTE e variáveis de tabela.

No entanto, há outra maneira de otimizar para este caso – visualizações indexáveis.

Um grupo de condições (do exemplo acima) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (ou um conjunto de outras condições semelhantes) se torna uma boa opção para usá-las em uma visualização indexável, armazenando uma pequena fatia de dados de um grande conjunto.

Mas há uma série de limitações ao materializar a visualização:

uso de subconsultas, proposições EXISTS devem ser substituídas pelo uso de JOIN
não se pode usar proposições UNION, UNION ALL, EXCEÇÃO, INTERSECT
não se pode usar dicas de tabela e proposições OPTION
não há possibilidade de trabalhar com ciclos
não é possível exibir dados em uma única visualização de tabelas diferentes

É importante lembrar que o benefício real do uso de uma visualização indexável pode ser obtido na verdade apenas mediante sua indexação.

Mas ao chamar a visualização, esses índices podem não ser utilizados, e para o uso explícito deles é necessário especificar WITH (NOEXPAND).

Como em consultas LINQ não se pode definir dicas de tabela, então é necessário criar outra visualização – uma "wrapper" do seguinte tipo:

CREATE VIEW NOME_da_visualização AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Uso de funções de tabela

Frequentemente, em consultas LINQ grandes blocos de subconsultas ou blocos que usam visualizações com estrutura complexa, formam uma consulta final com uma estrutura de execução muito complexa e não otimizada.

As principais vantagens do uso de funções de tabela em consultas LINQ:

A possibilidade, assim como no caso das visualizações, de usar e especificar como objeto, mas pode-se passar um conjunto de parâmetros de entrada:
FROM FUNCTION(@param1, @param2 …)
no final, pode-se alcançar uma seleção de dados flexível
No caso do uso de uma função de tabela, não há restrições tão fortes como no caso das visualizações indexáveis descritas acima:
1. Dicas de tabela:
  via LINQ não é possível especificar quais índices devem ser utilizados e definir o nível de isolamento dos dados em uma consulta.
  Mas na função essas possibilidades existem.
  Com a função é possível obter um plano de consulta de execução bastante consistente, onde são definidas as regras de trabalho com os índices e os níveis de isolamento dos dados.
2. O uso da função permite, em comparação com visualizações indexadas, obter:
  - lógica complexa de seleção de dados (incluindo a utilização de loops)
  - seleção de dados de várias tabelas diferentes
  - uso UNION e EXISTS
A proposta OPTION é muito útil quando precisamos garantir o controle de paralelismo. OPTION(MAXDOP N), ordem do plano de execução da consulta. Por exemplo:
- é possível forçar a recriação do plano da consulta OPTION (RECOMPILE)
- é possível especificar a necessidade de garantir o uso forçado da ordem de junção, conforme indicado na consulta OPTION (FORCE ORDER)
Mais detalhes sobre OPTION está descrito aqui.
Uso do corte de dados mais restrito e necessário:
Não há necessidade de manter grandes conjuntos de dados em caches (como no caso de visualizações indexadas), dos quais ainda precisamos filtrar os dados por parâmetro.
Por exemplo, há uma tabela na qual para o filtro WHERE são utilizados três campos (a, b, c).
Condicionalmente, para todas as consultas há uma condição constante a = 0 e b = 0.
No entanto, a consulta ao campo c é mais variável.
Vamos considerar que a condição a = 0 e b = 0 de fato nos ajuda a restringir o conjunto necessário para milhares de registros, mas a condição sobre com nos limita a seleção a cem registros.
Aqui, a função de tabela pode ser uma opção mais vantajosa.
Além disso, a função de tabela é mais previsível e constante em termos de tempo de execução.

Exemplos

Vamos considerar um exemplo de implementação no banco de dados Questions.

Há uma consulta SELECT, que une várias tabelas e utiliza uma visualização (OperativeQuestions), na qual é verificada a afiliação por email (através de EXISTS) aos 'Consultas Ativas' ([OperativeQuestions]):

Consulta nº 1

(SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL) 
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));

A visualização possui uma estrutura bastante complexa: contém junções de subconsultas e uso de ordenação DISTINCT, que, de maneira geral, é uma operação suficientemente consome recursos.

A seleção da tabela OperativeQuestions contém cerca de dez mil registros.

O principal problema desta consulta é que, para os registros da consulta externa, uma subconsulta interna na visualização [OperativeQuestions] deve, para [Email] = @p__linq__0, restringir os resultados exibidos (através de EXISTS) a centenas de registros.

E pode parecer que a subconsulta deve calcular os registros para [Email] = @p__linq__0 uma única vez, e depois essas poucas centenas de registros devem ser conectadas por Id com Questions, deixando a consulta rápida.

Na verdade, ocorre a junção sequencial de todas as tabelas: tanto a verificação da correspondência dos Ids das Questions com os Ids da OperativeQuestions quanto a filtragem por Email.

Essencialmente, a consulta está lidando com todas as dezenas de milhar de registros da OperativeQuestions, enquanto apenas os dados relevantes por Email são necessários.

Texto da visualização OperativeQuestions:

Consulta nº 2

 
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM            [dbo].Questions AS Q INNER JOIN
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
                     (SELECT   1 AS HasNoObjects
                      WHERE   NOT EXISTS
                                    (SELECT   1
                                     FROM     [dbo].ObjectUserAccesses AS BOU
                                     WHERE   BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE        CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL 
AND (BO.HasNoObjects = 1 OR
              EXISTS (SELECT   1
                           FROM   [dbo].ObjectUserAccesses AS BOU INNER JOIN
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               WHERE  BOU.ProcessUserAccessId = BPU.Id 
                               AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));

O mapeamento original da visualização no DbContext (EF Core 2)

classe pública QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

Consulta LINQ original

var businessObjectsData = await context
    .OperativeQuestions
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

Neste caso específico, estamos considerando uma solução para esse problema sem mudanças de infraestrutura, sem a introdução de uma tabela separada com resultados prontos (“Consultas Ativas”), para a qual seria necessário um mecanismo para preenchê-la com dados e mantê-la atualizada.

Embora essa seja uma boa solução, existe outra opção de otimização para essa tarefa.

O principal objetivo é armazenar em cache os registros por [Email] = @p__linq__0 da visualização OperativeQuestions.

Introduzimos a função de tabela [dbo].[OperativeQuestionsUserMail] no banco de dados.

Ao enviar o Email como parâmetro de entrada, obtemos uma tabela de valores de volta:

Consulta nº 3


CRIAR FUNÇÃO [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETORNA
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        SELECT Id, @Email
        FROM [OperativeQuestions]  AS [x] WHERE [x].[Email] = @Email;
     
    RETORNAR;
FIM

Aqui, uma tabela de valores é retornada com uma estrutura de dados pré-definida.

Para que as consultas a OperativeQuestionsUserMail sejam otimizadas e tenham planos de consulta adequados, é necessária uma estrutura rigorosa, e não RETORNA TABELA COMO RETORNO…

Neste caso, a Consulta 1 é transformada na Consulta 4:

Consulta nº 4

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
    SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);

Mapeamento da visualização e da função no DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable GetByUserEmail(this DbQuery source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Consulta LINQ final

var businessObjectsData = await context
    .OperativeQuestions
    .GetByUserEmail(Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

O tempo de execução foi reduzido de 200-800 ms para 2-20 ms, ou seja, dezenas de vezes mais rápido.

Se considerarmos de forma mais média, em vez de 350 ms, obtivemos 8 ms.

Alguns benefícios óbvios incluem:

redução geral da carga de leitura,
diminuição significativa da probabilidade de bloqueios
redução do tempo médio de bloqueio para valores aceitáveis

Saída

A otimização e o ajuste fino das consultas ao banco de dados MS SQL via LINQ é uma tarefa que pode ser resolvida.

Nesta tarefa, a atenção e a sequência são muito importantes.

No início do processo:

é necessário verificar os dados com os quais a consulta trabalha (valores, tipos de dados escolhidos)
realizar a indexação correta desses dados
verificar a correção das condições de junção entre as tabelas

Na próxima iteração de otimização, são identificados:

a base da consulta e é determinado o filtro principal da consulta
blocos repetidos semelhantes da consulta e é analisada a interseção das condições
no SSMS ou em outra GUI para SQL Server otimiza-se o próprio SQL-query (destacando um armazenamento de dados intermediário, construindo a consulta resultante com o uso desse armazenamento (pode haver vários))
na etapa final, tomando como base o resultado SQL-query, reestrutura-se a estrutura da consulta LINQ

O resultado obtido LINQ-query deve se tornar estruturalmente idêntico ao identificado como ótimo SQL-query do ponto 3.

Agradecimentos

Um enorme agradecimento aos colegas jobgemws e alex_ozr da empresa Fortis pela ajuda na preparação deste material.

Fonte: habr.com