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

introducción

В este artículo Se consideraron algunos métodos de optimización. consultas LINQ.
Aquí también presentamos algunos enfoques más para la optimización de código relacionados con consultas LINQ.

Se sabe que LINQ(Consulta integrada en el lenguaje) es un lenguaje simple y conveniente para consultar una fuente de datos.

А LINQ a SQL es una tecnología para acceder a datos en un DBMS. Esta es una poderosa herramienta para trabajar con datos, donde las consultas se construyen a través de un lenguaje declarativo, que luego se convertirá en Consultas SQL plataforma y enviado al servidor de base de datos para su ejecución. En nuestro caso, por DBMS nos referimos MS SQL Server.

Sin embargo, consultas LINQ no se convierten en escritos de forma óptima Consultas SQL, que un DBA experimentado podría escribir con todos los matices de optimización Consultas SQL:

conexiones óptimas (SUSCRÍBETE) y filtrar los resultados (¿Dónde?)
Muchos matices en el uso de conexiones y condiciones de grupo.
muchas variaciones en las condiciones de reemplazo IN en EXISTEи NO EN, <> en EXISTE
almacenamiento en caché intermedio de resultados a través de tablas temporales, CTE, variables de tabla
uso de la oración (OPCIÓN) con instrucciones y sugerencias para la tabla HE TRABAJADO (...)
Usar vistas indexadas como uno de los medios para deshacerse de lecturas de datos redundantes durante las selecciones.

Los principales obstáculos al rendimiento del resultado Consultas SQL al compilar consultas LINQ son:

consolidación de todo el mecanismo de selección de datos en una sola solicitud
duplicar bloques idénticos de código, lo que en última instancia conduce a múltiples lecturas de datos innecesarias
grupos de condiciones de múltiples componentes (lógico "y" y "o") - Y и OR, combinado en condiciones complejas, lleva al hecho de que el optimizador, al tener índices no agrupados adecuados para los campos necesarios, finalmente comienza a escanear contra el índice agrupado (ESCANEO DE ÍNDICE) por grupos de condiciones
El anidamiento profundo de subconsultas hace que el análisis sea muy problemático. declaraciones SQL y análisis del plan de consultas por parte de los desarrolladores y DBA

Métodos de optimización

Ahora pasemos directamente a los métodos de optimización.

1) Indexación adicional

Es mejor considerar filtros en las tablas de selección principales, ya que muy a menudo toda la consulta se basa en una o dos tablas principales (aplicaciones-personas-operaciones) y con un conjunto estándar de condiciones (Está cerrada, Cancelada, Habilitada, Estado). Es importante crear índices apropiados para las muestras identificadas.

Esta solución tiene sentido cuando la selección de estos campos limita significativamente el conjunto devuelto a la consulta.

Por ejemplo, tenemos 500000 solicitudes. Sin embargo, sólo hay 2000 solicitudes activas. Entonces un índice correctamente seleccionado nos salvará de ESCANEO DE ÍNDICE en una tabla grande y le permitirá seleccionar datos rápidamente a través de un índice no agrupado.

Además, la falta de índices se puede identificar mediante indicaciones para analizar planes de consulta o recopilar estadísticas de vistas del sistema. MS SQL Server:

Todos los datos de la vista contienen información sobre los índices faltantes, con la excepción de los índices espaciales.

Sin embargo, los índices y el almacenamiento en caché son a menudo métodos para combatir las consecuencias de una mala redacción. consultas LINQ и Consultas SQL.

Como lo demuestra la dura práctica de la vida, a menudo es importante que una empresa implemente funciones comerciales dentro de ciertos plazos. Y, por lo tanto, las solicitudes pesadas a menudo se transfieren a un segundo plano mediante el almacenamiento en caché.

Esto está parcialmente justificado, ya que el usuario no siempre necesita los datos más recientes y la capacidad de respuesta de la interfaz de usuario es aceptable.

Este enfoque permite resolver las necesidades del negocio, pero en última instancia reduce el rendimiento del sistema de información simplemente retrasando las soluciones a los problemas.

También vale la pena recordar que en el proceso de búsqueda de los índices necesarios para agregar, aparecen sugerencias MS SQL La optimización puede ser incorrecta, incluso en las siguientes condiciones:

si ya existen índices con un conjunto similar de campos
si los campos de la tabla no se pueden indexar debido a restricciones de indexación (descritas con más detalle aquí).

2) Fusionar atributos en un nuevo atributo

A veces, algunos campos de una tabla, que sirven como base para un grupo de condiciones, se pueden reemplazar introduciendo un campo nuevo.

Esto es especialmente cierto para los campos de estado, que normalmente son de tipo bit o entero.

Ejemplo:

EstáCerrado = 0 Y Cancelado = 0 Y Habilitado = 0 reemplazado por Estado = 1.

Aquí es donde se introduce el atributo Estado entero para garantizar que estos estados se completen en la tabla. A continuación, se indexa este nuevo atributo.

Esta es una solución fundamental al problema de rendimiento, porque accedemos a los datos sin cálculos innecesarios.

3) Materialización de la vista

Desafortunadamente, en consultas LINQ Las tablas temporales, CTE y variables de tabla no se pueden utilizar directamente.

Sin embargo, hay otra forma de optimizar para este caso: las vistas indexadas.

Grupo de condiciones (del ejemplo anterior) EstáCerrado = 0 Y Cancelado = 0 Y Habilitado = 0 (o un conjunto de otras condiciones similares) se convierte en una buena opción para usarlas en una vista indexada, almacenando en caché una pequeña porción de datos de un conjunto grande.

Pero existen una serie de restricciones a la hora de materializar una vista:

uso de subconsultas, cláusulas EXISTE debe ser reemplazado usando SUSCRÍBETE
no puedes usar oraciones UNIÓN, UNION TODO, EXCEPCIÓN, INTERSECARSE
No se pueden utilizar sugerencias y cláusulas de tabla. OPCIÓN
No hay posibilidad de trabajar con ciclos.
Es imposible mostrar datos en una vista desde diferentes tablas.

Es importante recordar que el beneficio real de utilizar una vista indexada sólo se puede lograr indexándola.

Pero al llamar a una vista, estos índices no se pueden usar y, para usarlos explícitamente, debe especificar CON (NO EXPANDIR).

Ya que en consultas LINQ Es imposible definir sugerencias de tabla, por lo que debe crear otra representación: un "envoltorio" de la siguiente forma:

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

4) Usar funciones de tabla

A menudo en consultas LINQ Grandes bloques de subconsultas o bloques que utilizan vistas con una estructura compleja forman una consulta final con una estructura de ejecución muy compleja y subóptima.

Beneficios clave del uso de funciones de tabla en consultas LINQ:

La capacidad, como en el caso de las vistas, de usarse y especificarse como un objeto, pero se puede pasar un conjunto de parámetros de entrada:
DE FUNCIÓN(@param1, @param2...)
Como resultado, se puede lograr un muestreo de datos flexible.
En el caso de utilizar una función de tabla, no existen restricciones tan estrictas como en el caso de las vistas indexadas descritas anteriormente:
1. Consejos para la tabla:
  a través de LINQ No puede especificar qué índices se deben utilizar ni determinar el nivel de aislamiento de datos al realizar consultas.
  Pero la función tiene estas capacidades.
  Con la función, puede lograr un plan de consulta de ejecución bastante constante, donde se definen reglas para trabajar con índices y niveles de aislamiento de datos.
2. El uso de la función permite, en comparación con las vistas indexadas, obtener:
  - lógica de muestreo de datos compleja (incluso usando bucles)
  - obteniendo datos de muchas tablas diferentes
  - utilizar UNIÓN и EXISTE
Propuesta OPCIÓN muy útil cuando necesitamos proporcionar control de concurrencia OPCIÓN(MAXDOP N), el orden del plan de ejecución de la consulta. Por ejemplo:
- puede especificar una recreación forzada del plan de consulta OPCIÓN (RECOMPILAR)
- puede especificar si se debe forzar que el plan de consulta utilice el orden de unión especificado en la consulta OPCIÓN (FORZAR ORDEN)
Más detalles sobre OPCIÓN descrito aquí.
Usando la porción de datos más estrecha y requerida:
No es necesario almacenar grandes conjuntos de datos en cachés (como es el caso de las vistas indexadas), de las cuales aún es necesario filtrar los datos por parámetro.
Por ejemplo, hay una tabla cuyo filtro ¿Dónde? Se utilizan tres campos. (a B C).

Convencionalmente, todas las solicitudes tienen una condición constante. a = 0 y b = 0.

Sin embargo, la solicitud del campo c más variables.

Deja que la condición a = 0 y b = 0 Realmente nos ayuda a limitar el conjunto resultante requerido a miles de registros, pero la condición en с reduce la selección a cien registros.

Aquí la función de tabla puede ser una mejor opción.

Además, una función de tabla es más predecible y consistente en el tiempo de ejecución.

Примеры

Veamos una implementación de ejemplo usando la base de datos de Preguntas como ejemplo.

hay una solicitud SELECCIONAR, que combina varias tablas y utiliza una vista (OperativeQuestions), en la que la afiliación se verifica por correo electrónico (a través de EXISTE) a “Preguntas operativas”:

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

La vista tiene una estructura bastante compleja: tiene uniones de subconsultas y utiliza clasificación. DISTINCT, que en general es una operación que requiere bastantes recursos.

Una muestra de OperativeQuestions tiene unos diez mil registros.

El principal problema con esta consulta es que para los registros de la consulta externa, se ejecuta una subconsulta interna en la vista [OperativeQuestions], lo que debería limitar la selección de salida para [Email] = @p__linq__0 (a través de EXISTE) hasta cientos de registros.

Y puede parecer que la subconsulta debería calcular los registros una vez mediante [Email] = @p__linq__0, y luego estos cientos de registros deberían conectarse mediante Id con Preguntas, y la consulta será rápida.

De hecho, existe una conexión secuencial de todas las tablas: verificar la correspondencia de las preguntas de identificación con la identificación de OperativeQuestions y filtrar por correo electrónico.

De hecho, la solicitud funciona con las decenas de miles de registros de OperativeQuestions, pero solo se necesitan los datos de interés por correo electrónico.

Texto de vista de Preguntas Operativas:

Solicitud 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();

En este caso particular, estamos considerando una solución a este problema sin cambios de infraestructura, sin introducir una tabla separada con resultados ya preparados ("Consultas Activas"), lo que requeriría un mecanismo para llenarla con datos y mantenerla actualizada. .

Aunque esta es una buena solución, existe otra opción para optimizar este problema.

El objetivo principal es almacenar en caché las entradas mediante [Email] = @p__linq__0 desde la vista OperativeQuestions.

Introduzca la función de tabla [dbo].[OperativeQuestionsUserMail] en la base de datos.

Al enviar un correo electrónico como parámetro de entrada, obtenemos una tabla de valores:

Solicitud 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

Esto devuelve una tabla de valores con una estructura de datos predefinida.

Para que las consultas a OperativeQuestionsUserMail sean óptimas y tengan planes de consulta óptimos, se requiere una estructura estricta, y no TABLA DE DEVOLUCIONES COMO DEVOLUCIONES...

En este caso, la Consulta 1 requerida se convierte en la Consulta 4:

Solicitud 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 y funciones 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 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();

El orden de tiempo de ejecución ha bajado de 200-800 ms a 2-20 ms, etc., es decir, decenas de veces más rápido.

Si lo tomamos de manera más promedio, en lugar de 350 ms obtenemos 8 ms.

De las ventajas obvias también obtenemos:

reducción general de la carga lectora,
reducción significativa en la probabilidad de bloqueo
Reducir el tiempo medio de bloqueo a valores aceptables.

conclusión

Optimización y ajuste de llamadas a bases de datos. MS SQL a través de LINQ es un problema que se puede solucionar.

La atención y la coherencia son muy importantes en este trabajo.

Al inicio del proceso:

es necesario verificar los datos con los que trabaja la solicitud (valores, tipos de datos seleccionados)
llevar a cabo una indexación adecuada de estos datos
comprobar la exactitud de las condiciones de unión entre tablas

La siguiente iteración de optimización revela:

base de la solicitud y define el filtro de solicitud principal
repetir bloques de consulta similares y analizar la intersección de condiciones
en SSMS u otra GUI para SQL Server se optimiza consulta SQL (asignar un almacenamiento de datos intermedio, crear la consulta resultante utilizando este almacenamiento (puede haber varios))
en la última etapa, tomando como base el resultado consulta SQL, la estructura está siendo reconstruida consulta LINQ

La resultante consulta LINQ debe volverse idéntico en estructura al óptimo identificado consulta SQL desde el punto 3.

Agradecimientos

Muchas gracias a los colegas. gemas de trabajo и alex_ozr De la compañia Fortis para obtener ayuda en la preparación de este material.

Fuente: habr.com

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