🥇Métodos para otimizar consultas LINQ em C#.NET

Introdução

В Este artigo alguns métodos de otimização foram considerados Consultas LINQ.
Aqui apresentamos mais algumas abordagens para otimização de código relacionadas a Consultas LINQ.

Sabe-se que LINQ(Consulta Integrada à Linguagem) é uma linguagem simples e conveniente para consultar uma fonte de dados.

А LINQ para SQL é uma tecnologia para acessar dados em um SGBD. Esta é uma poderosa ferramenta para trabalhar com dados, onde as consultas são construídas através de uma linguagem declarativa, que depois será convertida em Consultas SQL plataforma e enviado ao servidor de banco de dados para execução. No nosso caso, por SGBD queremos dizer MS SQL Server.

No entanto, Consultas LINQ não são convertidos em escritos de forma otimizada Consultas SQL, que um DBA experiente poderia escrever com todas as nuances de otimização consultas SQL:

conexões ideais (Cadastre-se) e filtrando os resultados (ONDE)
muitas nuances no uso de conexões e condições de grupo
muitas variações nas condições de substituição IN em EXISTEи NÃO EM, <> ligado EXISTE
cache intermediário de resultados por meio de tabelas temporárias, CTE, variáveis de tabela
uso de frase (OPÇÃO) com instruções e dicas de tabela COM (...)
usando visualizações indexadas como um dos meios para se livrar de leituras de dados redundantes durante as seleções

Os principais gargalos de desempenho resultantes consultas SQL ao compilar Consultas LINQ são:

consolidação de todo o mecanismo de seleção de dados em uma solicitação
duplicar blocos idênticos de código, o que acaba levando a múltiplas leituras de dados desnecessárias
grupos de condições multicomponentes (lógico “e” e “ou”) - E и OR, combinando-se em condições complexas, leva ao fato de que o otimizador, tendo índices não clusterizados adequados para os campos necessários, finalmente começa a verificar o índice clusterizado (VARREDURA DE ÍNDICE) por grupos de condições
o aninhamento profundo de subconsultas torna a análise muito problemática Instruções SQL e análise do plano de consulta 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 filtros nas tabelas de seleção principais, pois muitas vezes toda a consulta é construída em torno de uma ou duas tabelas principais (aplicativos-pessoas-operações) e com um conjunto padrão de condições (IsClosed, Canceled, Enabled, Status). É importante criar índices apropriados para as amostras identificadas.

Esta solução faz sentido quando a seleção desses campos limita significativamente o conjunto retornado para a consulta.

Por exemplo, temos 500000 inscrições. No entanto, existem apenas 2000 aplicativos ativos. Então, um índice selecionado corretamente nos salvará de VARREDURA DE ÍNDICE em uma tabela grande e permitirá selecionar dados rapidamente por meio de um índice não clusterizado.

Além disso, a falta de índices pode ser identificada por meio de prompts para análise de planos de consulta ou coleta de estatísticas de visualização do sistema MS SQL Server:

Todos os dados de visualização contêm informações sobre índices ausentes, com exceção dos índices espaciais.

No entanto, os índices e o cache são frequentemente métodos para combater as consequências de erros mal escritos. Consultas LINQ и consultas SQL.

Como mostra a dura prática da vida, muitas vezes é importante para uma empresa implementar recursos de negócios dentro de determinados prazos. E, portanto, solicitações pesadas são frequentemente transferidas para segundo plano com cache.

Isto é parcialmente justificado, uma vez que o usuário nem sempre precisa dos dados mais recentes e existe um nível aceitável de capacidade de resposta da interface do usuário.

Essa abordagem permite resolver necessidades de negócios, mas, em última análise, reduz o desempenho do sistema de informação simplesmente atrasando a solução dos problemas.

Vale lembrar também que no processo de busca dos índices necessários para adicionar, sugestões MS SQL a otimização pode estar incorreta, inclusive nas seguintes condições:

se já existirem índices com um conjunto semelhante de campos
se os campos da tabela não puderem ser indexados devido a restrições de indexação (descritas com mais detalhe aqui).

2) Mesclando atributos em um novo atributo

Às vezes, alguns campos de uma tabela que servem de base para um grupo de condições podem ser substituídos pela introdução de um novo campo.

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

Exemplo:

IsClosed = 0 E Cancelado = 0 E Habilitado = 0 substituído por Status = 1.

É aqui que o atributo Status inteiro é introduzido para garantir que esses status sejam preenchidos na tabela. A seguir, este novo atributo é indexado.

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

3) Materialização da visão

Infelizmente, em Consultas LINQ Tabelas temporárias, CTEs e variáveis de tabela não podem ser usadas diretamente.

No entanto, existe outra maneira de otimizar neste caso: visualizações indexadas.

Grupo de condições (do exemplo acima) IsClosed = 0 E Cancelado = 0 E Habilitado = 0 (ou um conjunto de outras condições semelhantes) torna-se uma boa opção para usá-los em uma visualização indexada, armazenando em cache uma pequena fatia de dados de um grande conjunto.

Mas há uma série de restrições ao materializar uma visão:

uso de subconsultas, cláusulas EXISTE deve ser substituído pelo uso Cadastre-se
você não pode usar frases UNIÃO, UNIÃO TUDO, EXCEÇÃO, INTERSEÇÃO
Você não pode usar dicas e cláusulas de tabela OPÇÃO
não há possibilidade de trabalhar com ciclos
É impossível exibir dados em uma visualização de tabelas diferentes

É importante lembrar que o benefício real de usar uma visualização indexada só pode ser alcançado indexando-a de fato.

Mas ao chamar uma visão, esses índices não podem ser usados, e para usá-los explicitamente, você deve especificar COM(NOEXPAND).

Desde em Consultas LINQ É impossível definir dicas de tabela, então você terá que criar outra representação - um “wrapper” no seguinte formato:

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Usando funções de tabela

Muitas vezes em Consultas LINQ Grandes blocos de subconsultas ou blocos que usam visualizações com uma estrutura complexa criam uma consulta final com uma estrutura de execução muito complexa e abaixo do ideal.

Principais benefícios do uso de funções de tabela em Consultas LINQ:

A capacidade, como no caso das visualizações, de serem usadas e especificadas como um objeto, mas você pode passar um conjunto de parâmetros de entrada:
DA FUNÇÃO(@param1, @param2 ...)
Como resultado, a amostragem flexível de dados pode ser alcançada
No caso de usar uma função de tabela, não existem restrições tão fortes como no caso das visualizações indexadas descritas acima:
1. Dicas de mesa:
  através LINQ Você não pode especificar quais índices devem ser usados e determinar o nível de isolamento de dados durante a consulta.
  Mas a função possui esses recursos.
  Com a função, você pode obter um plano de consulta de execução bastante constante, onde são definidas regras para trabalhar com índices e níveis de isolamento de dados
2. O uso da função permite, em comparação com visualizações indexadas, obter:
  - lógica complexa de amostragem de dados (mesmo usando loops)
  - buscando dados de muitas tabelas diferentes
  - usar UNIÃO и EXISTE
Proposta OPÇÃO muito útil quando precisamos fornecer controle de simultaneidade OPÇÃO(MAXDOP N), a ordem do plano de execução da consulta. Por exemplo:
- você pode especificar uma recriação forçada do plano de consulta OPÇÃO (RECOMPILAR)
- você pode especificar se deseja forçar o plano de consulta a usar a ordem de junção especificada na consulta OPÇÃO (ORDEM DE FORÇA)
Mais detalhes sobre OPÇÃO descrito aqui.
Usando a fatia de dados mais estreita e necessária:
Não há necessidade de armazenar grandes conjuntos de dados em caches (como é o caso das visualizações indexadas), dos quais ainda é necessário filtrar os dados por parâmetro.
Por exemplo, existe uma tabela cujo filtro ONDE três campos são usados (a,b,c).

Convencionalmente, todas as solicitações têm uma condição constante a = 0 e b = 0.

No entanto, a solicitação do campo c mais variável.

Deixe a condição a = 0 e b = 0 Realmente nos ajuda limitar o conjunto resultante necessário a milhares de registros, mas a condição em с restringe a seleção a cem registros.

Aqui a função de tabela pode ser uma opção melhor.

Além disso, uma função de tabela é mais previsível e consistente no tempo de execução.

Примеры

Vejamos um exemplo de implementação usando o banco de dados Questions como exemplo.

Há um pedido SELECIONE, que combina diversas tabelas e utiliza uma visualização (OperativeQuestions), na qual a afiliação é verificada por e-mail (via EXISTE) para “Questões Operativas”:

Pedido nº 1

(@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 [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 tem uma estrutura bastante complexa: possui junções de subconsultas e usa classificação DISTINCT, que em geral é uma operação que consome bastante recursos.

Uma amostra do OperativeQuestions tem cerca de dez mil registros.

O principal problema com esta consulta é que para os registros da consulta externa, uma subconsulta interna é executada na visualização [OperativeQuestions], o que deve para [Email] = @p__linq__0 nos permitir limitar a seleção de saída (via EXISTE) até centenas de registros.

E pode parecer que a subconsulta deve calcular os registros uma vez por [Email] = @p__linq__0, e então essas centenas de registros devem ser conectados por Id com Perguntas, e a consulta será rápida.

Na verdade, existe uma conexão sequencial de todas as tabelas: verificação da correspondência do Id Questions com o Id do OperativeQuestions e filtragem por Email.

Na verdade, a solicitação funciona com todas as dezenas de milhares de registros do OperativeQuestions, mas apenas os dados de interesse são necessários via e-mail.

Texto da visualização das Perguntas Operativas:

Pedido 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));

Mapeamento de visualização inicial em DbContext (EF Core 2)

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

Consulta LINQ inicial

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 particular, estamos a considerar uma solução para este problema sem alterações infraestruturais, sem introdução de uma tabela separada com resultados prontos (“Consultas Ativas”), o que exigiria um mecanismo para preenchê-lo com dados e mantê-lo atualizado. .

Embora esta seja uma boa solução, existe outra opção para otimizar este problema.

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

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

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

Pedido nº 3


CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@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;
     
    RETURN;
END

Isso retorna uma tabela de valores com uma estrutura de dados predefinida.

Para que as consultas ao OperativeQuestionsUserMail sejam ideais e tenham planos de consulta ideais, é necessária uma estrutura rígida, e não TABELA DE DEVOLUÇÕES COMO RETORNO...

Neste caso, a Consulta 1 necessária é convertida na Consulta 4:

Pedido 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]);

Mapeando visualizações e funções em DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> 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();

A ordem do tempo de execução caiu de 200-800 ms para 2-20 ms, etc., ou seja, dezenas de vezes mais rápido.

Se considerarmos mais medianamente, em vez de 350 ms obteremos 8 ms.

Das vantagens óbvias também obtemos:

redução geral na carga de leitura,
redução significativa na probabilidade de bloqueio
reduzindo o tempo médio de bloqueio para valores aceitáveis

Jogar aviator online grátis: hack aviator funciona

Otimização e ajuste fino de chamadas de banco de dados MS SQL através LINQ é um problema que pode ser resolvido.

Atenção e consistência são muito importantes neste trabalho.

No início do processo:

é necessário verificar os dados com os quais a solicitação funciona (valores, tipos de dados selecionados)
realizar a indexação adequada desses dados
verifique a exatidão das condições de união entre tabelas

A próxima iteração de otimização revela:

com base na solicitação e define o filtro principal da solicitação
repetindo blocos de consulta semelhantes e analisando a interseção de condições
em SSMS ou outra GUI para SQL Server se otimiza Consulta SQL (alocando um armazenamento intermediário de dados, construindo a consulta resultante usando esse armazenamento (pode haver vários))
na última etapa, tomando como base o resultado Consulta SQL, a estrutura está sendo reconstruída Consulta LINQ

O resultado Consulta LINQ deve tornar-se idêntico em estrutura ao ideal identificado Consulta SQL do ponto 3.

Agradecimentos

Muito obrigado aos colegas jobgemws и alex_ozr da empresa Fortis pela ajuda na preparação deste material.

Fonte: habr.com

Métodos para otimizar consultas LINQ em C#.NET