🥇Métodos para optimizar consultas LINQ en C#.NET

Introdución

В Este artigo consideráronse algúns métodos de optimización Consultas LINQ.
Aquí tamén presentamos algúns enfoques máis relacionados coa optimización de código Consultas LINQ.

Sábese que LINQ(Language-Integrated Query) é unha linguaxe sinxela e conveniente para consultar unha fonte de datos.

А LINQ to SQL é unha tecnoloxía para acceder a datos nun DBMS. Esta é unha poderosa ferramenta para traballar con datos, onde as consultas se constrúen a través dunha linguaxe declarativa, que logo se converterá en Consultas SQL plataforma e enviado ao servidor de base de datos para a súa execución. No noso caso, por DBMS queremos dicir Servidor MS SQL.

Con todo, Consultas LINQ non se converten en escritos de forma óptima Consultas SQL, que un DBA experimentado podería escribir con todos os matices da optimización Consultas SQL:

conexións óptimas (Rexístrese se) e filtrando os resultados (ONDE)
moitos matices no uso de conexións e condicións de grupo
moitas variacións nas condicións de substitución IN en EXISTEи NON EN, <> activado EXISTE
caché intermedio de resultados mediante táboas temporais, CTE, variables de táboa
uso da frase (OPCIÓN) con instrucións e consellos de táboa CON (...)
usando vistas indexadas como un dos medios para desfacerse das lecturas de datos redundantes durante as seleccións

Os principais pescozos de botella de rendemento do resultado Consultas SQL ao compilar Consultas LINQ son:

consolidación de todo o mecanismo de selección de datos nunha única solicitude
duplicando bloques de código idénticos, o que finalmente leva a múltiples lecturas de datos innecesarias
grupos de condicións de varios compoñentes ("e" e "ou" lóxicos) - E и OR, combinando en condicións complexas, leva ao feito de que o optimizador, tendo índices non agrupados axeitados para os campos necesarios, finalmente comeza a escanear contra o índice agrupado (EXPLORACIÓN DE ÍNDICE) por grupos de condicións
o aniñamento profundo de subconsultas fai que a análise sexa moi problemática Instruccións SQL e análise do plan de consulta por parte dos desenvolvedores e DBA

Métodos de optimización

Agora imos pasar directamente aos métodos de optimización.

1) Indización adicional

É mellor considerar filtros nas táboas de selección principais, xa que moitas veces a consulta enteira está construída arredor dunha ou dúas táboas principais (aplicacións-persoas-operacións) e cun conxunto estándar de condicións (IsClosed, Canceled, Enabled, Status). É importante crear índices axeitados para as mostras identificadas.

Esta solución ten sentido cando a selección destes campos limita significativamente o conxunto devolto para a consulta.

Por exemplo, temos 500000 solicitudes. Non obstante, só hai 2000 aplicacións activas. Entón un índice seleccionado correctamente salvaranos EXPLORACIÓN DE ÍNDICE nunha táboa grande e permitirache seleccionar rapidamente datos a través dun índice non agrupado.

Ademais, a falta de índices pódese identificar mediante solicitudes para analizar plans de consulta ou recoller estatísticas de visualización do sistema Servidor MS SQL:

Todos os datos de visualización conteñen información sobre os índices que faltan, a excepción dos índices espaciais.

Non obstante, os índices e a caché adoitan ser métodos para combater as consecuencias dun mal escrito Consultas LINQ и Consultas SQL.

Como mostra a dura práctica da vida, moitas veces é importante que unha empresa implemente funcións comerciais en determinados prazos. E polo tanto, as solicitudes pesadas adoitan transferirse a un segundo plano con caché.

Isto está en parte xustificado, xa que o usuario non sempre necesita os datos máis recentes e existe un nivel aceptable de resposta da interface de usuario.

Este enfoque permite resolver as necesidades empresariais, pero finalmente reduce o rendemento do sistema de información simplemente atrasando as solucións aos problemas.

Tamén convén lembrar que no proceso de busca dos índices necesarios para engadir, suxestións MS SQL a optimización pode ser incorrecta, incluso nas seguintes condicións:

se xa hai índices cun conxunto similar de campos
se os campos da táboa non se poden indexar debido a restricións de indexación (descritas con máis detalle aquí).

2) Combinar atributos nun atributo novo

Ás veces, algúns campos dunha táboa, que serven de base para un grupo de condicións, pódense substituír introducindo un novo campo.

Isto é especialmente certo para os campos de estado, que normalmente son de tipo bit ou enteiro.

Exemplo:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 substitúese por Estado = 1.

Aquí é onde se introduce o atributo Estado enteiro para garantir que estes estados se enchen na táboa. A continuación, indézase este novo atributo.

Esta é unha solución fundamental ao problema de rendemento, porque accedemos aos datos sen cálculos innecesarios.

3) Materialización da vista

Lamentablemente en Consultas LINQ As táboas temporais, os CTE e as variables da táboa non se poden usar directamente.

Non obstante, hai outra forma de optimizar este caso: as vistas indexadas.

Grupo de condicións (do exemplo anterior) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (ou un conxunto doutras condicións similares) convértese nunha boa opción para usalas nunha vista indexada, almacenando na caché unha pequena porción de datos dun conxunto grande.

Pero hai unha serie de restricións ao materializar unha vista:

uso de subconsultas, cláusulas EXISTE debe ser substituído polo uso Rexístrese se
non podes usar frases UNIÓN, UNIÓN TODOS, EXCEPCIÓN, INTERSECTA
Non pode usar suxestións e cláusulas da táboa OPCIÓN
sen posibilidade de traballar con ciclos
É imposible mostrar datos nunha vista de táboas diferentes

É importante lembrar que o beneficio real de usar unha vista indexada só se pode conseguir indexándoa.

Pero ao chamar a unha vista, é posible que non se utilicen estes índices e, para usalos de forma explícita, debes especificar CON (SEN EXPANDIR).

Dende en Consultas LINQ É imposible definir suxestións de táboa, polo que tes que crear outra representación: un "envoltorio" da seguinte forma:

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

4) Usando funcións da táboa

Moitas veces en Consultas LINQ Grandes bloques de subconsultas ou bloques que utilizan vistas cunha estrutura complexa forman unha consulta final cunha estrutura de execución moi complexa e subóptima.

Principais vantaxes do uso de funcións de táboa en Consultas LINQ:

A capacidade, como no caso das vistas, de usar e especificar como obxecto, pero pode pasar un conxunto de parámetros de entrada:
FROM FUNCTION(@param1, @param2...)
Como resultado, pódese conseguir unha mostraxe flexible de datos
No caso de usar unha función de táboa, non hai restricións tan fortes como no caso das vistas indexadas descritas anteriormente:
1. Consellos da táboa:
  través LINQ Non pode especificar que índices se deben usar e determinar o nivel de illamento de datos ao realizar unha consulta.
  Pero a función ten estas capacidades.
  Coa función, pode conseguir un plan de consulta de execución bastante constante, onde se definen regras para traballar con índices e niveis de illamento de datos.
2. Usar a función permite, en comparación coas vistas indexadas, obter:
  - lóxica de mostraxe de datos complexa (incluso usando bucles)
  - obtendo datos de moitas táboas diferentes
  - o uso de UNIÓN и EXISTE
Oferta OPCIÓN moi útil cando necesitamos proporcionar control de concorrencia OPCIÓN (MAXDOP N), a orde do plan de execución da consulta. Por exemplo:
- pode especificar unha recreación forzada do plan de consulta OPCIÓN (RECOMPILACIÓN)
- pode especificar se debe forzar o plan de consulta a utilizar a orde de unión especificada na consulta OPCIÓN (ORDE FORZADA)
Máis detalles sobre OPCIÓN descrito aquí.
Usando o segmento de datos máis estreito e necesario:
Non é necesario almacenar grandes conxuntos de datos en cachés (como é o caso das vistas indexadas), desde o que aínda debe filtrar os datos por parámetro.
Por exemplo, hai unha táboa cuxo filtro ONDE utilízanse tres campos (a, b, c).

Convencionalmente, todas as solicitudes teñen unha condición constante a = 0 e b = 0.

Con todo, a solicitude para o campo c máis variable.

Deixa a condición a = 0 e b = 0 Realmente axúdanos a limitar o conxunto resultante necesario a miles de rexistros, pero a condición está activada с reduce a selección a cen rexistros.

Aquí a función de táboa pode ser unha mellor opción.

Ademais, unha función de táboa é máis previsible e consistente no tempo de execución.

Exemplos

Vexamos un exemplo de implementación usando a base de datos de Preguntas como exemplo.

Hai unha solicitude SELECT, que combina varias táboas e utiliza unha vista (OperativeQuestions), na que se verifica a afiliación por correo electrónico (a través de EXISTE) a "Consultas activas" ([OperativeQuestions]):

Solicitude no 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 vista ten unha estrutura bastante complexa: ten unións de subconsulta e usa a ordenación Distinto, que en xeral é unha operación bastante intensiva en recursos.

Unha mostra de OperativeQuestions é duns dez mil rexistros.

O principal problema con esta consulta é que para os rexistros da consulta externa, execútase unha subconsulta interna na vista [OperativeQuestions], que debería para [Email] = @p__linq__0 permitirnos limitar a selección de saída (a través de EXISTE) ata centos de rexistros.

E pode parecer que a subconsulta debería calcular os rexistros unha vez por [Email] = @p__linq__0, e despois estes dous centos de rexistros deberían conectarse mediante Id with Questions, e a consulta será rápida.

De feito, hai unha conexión secuencial de todas as táboas: comprobando a correspondencia das preguntas de identificación coa identificación de Preguntas operativas e filtrando por correo electrónico.

De feito, a solicitude funciona con todas as decenas de miles de rexistros de OperativeQuestions, pero só se necesitan os datos de interese por correo electrónico.

OperativeQuestions ver texto:

Solicitude no 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));

Mapeo de vista inicial en 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 concreto, estamos considerando unha solución a este problema sen cambios de infraestrutura, sen introducir unha táboa separada con resultados xa preparados (“Consultas activas”), que requiriría un mecanismo para enchelo de datos e mantelos actualizados. .

Aínda que esta é unha boa solución, hai outra opción para optimizar este problema.

O propósito principal é almacenar en caché as entradas de [Email] = @p__linq__0 desde a vista OperativeQuestions.

Introduza a función de táboa [dbo].[OperativeQuestionsUserMail] na base de datos.

Ao enviar correo electrónico como parámetro de entrada, obtemos unha táboa de valores:

Solicitude no 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

Isto devolve unha táboa de valores cunha estrutura de datos predefinida.

Para que as consultas a OperativeQuestionsUserMail sexan óptimas e teñan plans de consulta óptimos, é necesaria unha estrutura estrita e non TÁBOA DE DEVOLUCIÓNS COMO DEVOLUCIÓN...

Neste caso, a consulta 1 necesaria convértese na consulta 4:

Solicitude no 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]);

Mapeo de vistas e funcións en 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 final LINQ

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 orde do tempo de execución baixou de 200-800 ms a 2-20 ms, etc., é dicir, decenas de veces máis rápido.

Se o tomamos de forma máis media, entón en lugar de 350 ms obtemos 8 ms.

Das vantaxes obvias tamén obtemos:

redución xeral da carga de lectura,
redución significativa da probabilidade de bloqueo
reducindo o tempo medio de bloqueo a valores aceptables

Saída

Optimización e axuste de chamadas de bases de datos MS SQL través LINQ é un problema que se pode solucionar.

A atención e a coherencia son moi importantes neste traballo.

Ao comezo do proceso:

é necesario comprobar os datos cos que funciona a solicitude (valores, tipos de datos seleccionados)
realizar a indexación adecuada destes datos
comprobar a corrección das condicións de unión entre táboas

A seguinte iteración de optimización revela:

base da solicitude e define o filtro de solicitude principal
repetindo bloques de consulta semellantes e analizando a intersección de condicións
en SSMS ou noutra GUI para SQL Server optimízase a si mesmo Consulta SQL (asignando un almacenamento de datos intermedio, creando a consulta resultante usando este almacenamento (pode haber varios))
na última etapa, tomando como base o resultante Consulta SQL, a estrutura estase a reconstruír Consulta LINQ

O resultante Consulta LINQ debe ser idéntica en estrutura á óptima identificada Consulta SQL do punto 3.

Agradecementos

Moitas grazas aos compañeiros traballos и alex_ozr da empresa Fortis para obter axuda na preparación deste material.

Fonte: www.habr.com

Métodos para optimizar consultas LINQ en C#.NET