🥇Métodos de optimización de consultas LINQ en C#.NET

Introducción

En este artículo se consideraron algunos métodos de optimización consultas LINQ.
A continuación, presentaremos algunos enfoques para la optimización del código relacionados con consultas LINQ.

Es conocido que LINQ(Language-Integrated Query) es un lenguaje de consulta simple y conveniente para fuentes de datos.

A LINQ to SQL es una tecnología para acceder a datos en bases de datos. Es una poderosa herramienta para trabajar con datos, donde a través de un lenguaje declarativo se construyen consultas que luego se transformarán en . A ellos les resulta mucho más fácil operar en términos de OOP. Escribimos instrucciones sobre cómo trabajar con los objetos de la base de datos, se formó la consulta SQL y se ejecutó. La nueva versión de la base de datos está lista, ha funcionado — todo bien, todo funciona. la plataforma y se enviarán al servidor de bases de datos para su ejecución. En nuestro caso, entendemos por base de datos MS SQL Server.

Sin embargo, las consultas LINQ no se transforman en consultas SQL óptimamente escritas . A ellos les resulta mucho más fácil operar en términos de OOP. Escribimos instrucciones sobre cómo trabajar con los objetos de la base de datos, se formó la consulta SQL y se ejecutó. La nueva versión de la base de datos está lista, ha funcionado — todo bien, todo funciona., que podría escribir un DBA experimentado con todos los matices de optimización consultas SQL:

uniones óptimas (JOIN) y filtrado de resultados (WHERE)
numerosos matices en el uso de uniones y condiciones grupales
muchas variaciones en la sustitución de condiciones IN en EXISTSy NOT IN, en EXISTS
caché intermedia de resultados a través de tablas temporales, CTE, variables de tabla
uso de la cláusula (OPTION) con indicaciones y sugerencias de tablas WITH (…)
uso de vistas indexadas, como uno de los medios para eliminar lecturas excesivas de datos durante las selecciones

Los principales cuellos de botella de rendimiento que se obtienen consultas SQL al compilar consultas LINQ son:

la consolidación de todo el mecanismo de selección de datos en una única consulta
la duplicación de bloques de código idénticos, lo que al final lleva a lecturas excesivas de datos
grupo de condiciones compuestas (lógicas «y» y «o») — Y y O, combinándose en condiciones complejas, provoca que el optimizador, teniendo índices no agrupados adecuados en los campos necesarios, al final empiece a hacer un escaneo por el índice agrupado (INDEX SCAN) por grupos de condiciones
la profunda anidación de subconsultas hace que el análisis de las instrucciones SQL y el análisis del plan de consultas desde el lado 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 los filtros en las tablas principales de selección, ya que a menudo toda la consulta se construye en torno a una o dos tablas principales (solicitudes-personas-operaciones) y con un conjunto estándar de condiciones (IsClosed, Canceled, Enabled, Status). Es importante crear índices correspondientes para las selecciones identificadas.

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

Por ejemplo, tenemos 500,000 solicitudes. Sin embargo, sólo hay 2,000 solicitudes activas. Entonces, un índice bien elegido nos librará de INDEX SCAN una gran tabla y permitirá seleccionar rápidamente los datos a través de un índice no clúster.

También se puede identificar la falta de índices a través de sugerencias en el análisis de planes de consulta o de la recolección de estadísticas desde vistas del sistema. MS SQL Server:

Toda la información de las vistas contiene datos sobre índices faltantes, excepto los índices espaciales.

Sin embargo, los índices y la caché son a menudo métodos para combatir las consecuencias de consultas mal diseñadas. consultas LINQ y consultas SQL.

Como muestra la dura práctica de la vida empresarial, a menudo es importante implementar funciones comerciales en plazos específicos. Por lo tanto, las consultas pesadas a menudo se trasladan a un segundo plano con caché.

Esto es parcialmente justificable, ya que el usuario no siempre necesita los datos más recientes y se mantiene un nivel aceptable de respuesta de la interfaz de usuario.

Este enfoque permite cumplir con las solicitudes del negocio, pero en última instancia reduce la operabilidad del sistema de información, simplemente posponiendo la resolución de problemas.

También es importante recordar que, en el proceso de búsqueda de los índices nuevos necesarios para agregar, las propuestas MS SQL de optimización pueden ser incorrectas, incluso en las siguientes condiciones:

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

2) Combinación de atributos en un nuevo atributo

A veces, algunos campos de una tabla, sobre los cuales se forman grupos de condiciones, pueden ser reemplazados introduciendo un nuevo campo.

Esto es especialmente relevante para los campos de estado, que generalmente son de tipo bit o enteros.

Ejemplo:

IsClosed = 0 Y Canceled = 0 Y Enabled = 0 se reemplaza por Status = 1.

Aquí se introduce un atributo entero Status, garantizado mediante el llenado de estos estados en la tabla. A continuación, se procede a la indexación de este nuevo atributo.

Esta es una solución fundamental al problema del rendimiento, ya que accedemos a los datos sin cálculos adicionales.

3) Materialización de la vista

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

Sin embargo, hay otra forma de optimización para este caso: las vistas indexadas.

Un conjunto de condiciones (del ejemplo anterior) IsClosed = 0 Y Canceled = 0 Y Enabled = 0 (o un conjunto de otras condiciones similares) se convierte en una buena opción para utilizarlas en una vista indexada, almacenando en caché un pequeño conjunto de datos de un gran conjunto.

Pero hay una serie de limitaciones al materializar una vista:

el uso de subconsultas, las cláusulas EXISTS deben ser sustituidas por el uso de JOIN
no se pueden usar cláusulas RÁPIDO, UNION ALL, EXCEPCIÓN, INTERSECCIÓN
no se pueden usar pistas de tabla y cláusulas OPTION
no hay posibilidad de trabajar con ciclos
no es posible extraer datos de una sola vista desde diferentes tablas

Es importante recordar que el verdadero beneficio de utilizar una vista indexada solo puede obtenerse realmente al indexarla.

Pero al invocar la vista, estos índices pueden no usarse, y para utilizarlos explícitamente es necesario especificar WITH (NOEXPAND).

Dado que no se pueden definir pistas de tabla en las consultas LINQ , hay que crear otra vista: un 'wrapper' de la siguiente manera:

CREATE VIEW NOMBRE_VISTA AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Uso de funciones de tabla

A menudo, en las consultas LINQ grandes bloques de subconsultas o bloques que utilizan vistas con estructuras complejas, formulan una consulta final con una estructura de ejecución muy compleja y no óptima.

Las principales ventajas de utilizar funciones de tabla en las consultas LINQ:

La posibilidad, al igual que en el caso de las vistas, de usar y especificar como objeto, pero se pueden pasar un conjunto de parámetros de entrada:
FROM FUNCTION(@param1, @param2 …)
en definitiva, se puede lograr una recuperación de datos flexible
En el caso de utilizar funciones de tabla no existen restricciones tan severas como en el caso de las vistas indexadas mencionadas anteriormente:
1. Pistas de tabla:
  a través de LINQ no se pueden especificar qué índices deben usarse y definir el nivel de aislamiento de los datos al realizar la consulta.
  Pero en la función estas posibilidades existen.
  Con la función se puede lograr un plan de consulta de ejecución bastante constante, donde se definen las reglas de trabajo con los índices y los niveles de aislamiento de los datos.
2. El uso de la función permite, en comparación con las vistas indexadas, obtener:
  - lógica compleja para seleccionar datos (incluso utilizando ciclos)
  - selección de datos de múltiples tablas diferentes
  - uso de RÁPIDO y EXISTS
Propuesta OPTION es muy útil cuando necesitamos garantizar el control del paralelismo. OPTION(MAXDOP N), orden del plan de ejecución de la consulta. Por ejemplo:
- se puede especificar la recreación forzada del plan de consulta. OPTION (RECOMPILE)
- se puede especificar la necesidad de garantizar el uso forzado del orden de unión que se indica en la consulta. OPTION (FORCE ORDER)
Más detalladamente sobre OPTION descrito aquí.
El uso del subconjunto de datos más estrecho y requerido:
No es necesario mantener grandes conjuntos de datos en las cachés (como en el caso de las vistas indexadas), de los cuales aún hay que filtrar los datos por parámetro.
Por ejemplo, hay una tabla que tiene un filtro WHERE que utiliza tres campos (a, b, c).
Condicionalmente para todas las consultas hay una condición constante a = 0 and b = 0.
Sin embargo, la consulta al campo c es más variable.
Supongamos que la condición a = 0 and b = 0 realmente nos ayuda a limitar el conjunto requerido a miles de registros, pero la condición de con nos reduce la selección a cientos de registros.
Aquí, la función tabular puede ser una opción más ventajosa.
Además, la función tabular es más predecible y constante en el tiempo de ejecución.

Ejemplos

Consideremos un ejemplo de implementación en la base de datos Questions.

Hay una consulta SELECCIONAR, que une varias tablas y utiliza una vista (OperativeQuestions), en la que se verifica la pertenencia por email (a través de EXISTS) a 'Consultas Activas' ([OperativeQuestions]):

Consulta Nº 1

(@p__linq__0 nvarchar(4000))SELECCIONAR
1 COMO [C1],
[Extent1].[Id] COMO [Id],
[Join2].[Object_Id] COMO [Object_Id],
[Join2].[ObjectType_Id] COMO [ObjectType_Id],
[Join2].[Name] COMO [Name],
[Join2].[ExternalId] COMO [ExternalId]
DE [dbo].[Questions] COMO [Extent1]
UNIR INTERNO (SELECCIONAR [Extent2].[Object_Id] COMO [Object_Id],
[Extent2].[Question_Id] COMO [Question_Id], [Extent3].[ExternalId] COMO [ExternalId],
[Extent3].[ObjectType_Id] COMO [ObjectType_Id], [Extent4].[Name] COMO [Name]
DE [dbo].[ObjectQuestions] COMO [Extent2]
UNIR INTERNO [dbo].[Objects] COMO [Extent3] EN [Extent2].[Object_Id] = [Extent3].[Id]
UNIR EXTERNO IZQUIERDO [dbo].[ObjectTypes] COMO [Extent4] 
EN [Extent3].[ObjectType_Id] = [Extent4].[Id] ) COMO [Join2] 
EN [Extent1].[Id] = [Join2].[Question_Id]
DONDE ([Extent1].[AnswerId] ES NULO) Y (0 = [Extent1].[Exp]) Y (EXISTE (SELECCIONAR
1 COMO [C1]
DE [dbo].[OperativeQuestions] COMO [Extent5]
DONDE (([Extent5].[Email] = @p__linq__0) O (([Extent5].[Email] ES NULO) 
Y (@p__linq__0 ES NULO))) Y ([Extent5].[Id] = [Extent1].[Id])
));

La vista tiene una estructura bastante compleja: incluye uniones de subconsultas y el uso de ordenación. DISTINCT, que en términos generales es una operación bastante intensiva en recursos.

La selección de OperativeQuestions es del orden de diez mil registros.

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

Y podría parecer que la subconsulta solo debería calcular los registros para [Email] = @p__linq__0 una vez, y luego estos pocos cientos de registros deberían unirse por Id con Questions, y la consulta sería rápida.

Sin embargo, lo que realmente sucede es una unión secuencial de todas las tablas: tanto la verificación de correspondencia de Id entre Questions e Id de OperativeQuestions, como el filtrado por Email.

De hecho, la consulta trabaja con decenas de miles de registros de OperativeQuestions, cuando solo se necesitan los datos relevantes por Email.

Texto de la vista OperativeQuestions:

Consulta n.º 2

 
CREAR VISTA [dbo].[OperativeQuestions]
COMO
SELECCIONAR DISTINCT Q.Id, USR.email COMO Email
DE            [dbo].Questions COMO Q UNIR INTERNO
                         [dbo].ProcessUserAccesses COMO BPU EN BPU.ProcessId = CQ.Process_Id 
APLICAR EXTERNO
                     (SELECCIONAR   1 COMO HasNoObjects
                      DONDE   NO EXISTE
                                    (SELECCIONAR   1
                                     DE     [dbo].ObjectUserAccesses COMO BOU
                                     DONDE   BOU.ProcessUserAccessId = BPU.[Id] Y BOU.[To] ES NULO)
) COMO BO UNIR INTERNO
                         [dbo].Users COMO USR EN USR.Id = BPU.UserId
DONDE        CQ.[Exp] = 0 Y CQ.AnswerId ES NULO Y BPU.[To] ES NULO 
Y (BO.HasNoObjects = 1 O
              EXISTE (SELECCIONAR   1
                           DE   [dbo].ObjectUserAccesses COMO BOU UNIR INTERNO
                                      [dbo].ObjectQuestions COMO QBO 
                                                  EN QBO.[Object_Id] =BOU.ObjectId
                               DONDE  BOU.ProcessUserAccessId = BPU.Id 
                               Y BOU.[To] ES NULO Y QBO.Question_Id = CQ.Id));

Mapeo original de la vista en DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().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();

En este caso concreto, se considera la solución a este problema sin cambios en la infraestructura, sin introducir una tabla separada con resultados predefinidos («Consultas activas»), para la cual sería necesario contar con un mecanismo que la llene de datos y la mantenga actualizada.

Aunque esta es una buena solución, hay otra opción para optimizar esta tarea.

El objetivo principal es almacenar en caché los registros por [Email] = @p__linq__0 de la vista OperativeQuestions.

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

Al enviar como parámetro de entrada Email, se obtiene de vuelta una tabla de valores:

Consulta № 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

Aquí se 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, es necesaria una estructura estricta, y no RETURNS TABLE AS RETURN…

En este caso, la Consulta 1 buscada se transforma en la Consulta 4:

Consulta № 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 la vista y función en 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();

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

En términos más promedio, en lugar de 350 ms obtuvimos 8 ms.

Entre los beneficios evidentes también tenemos:

una reducción general de la carga de lectura,
una disminución significativa de la probabilidad de bloqueos
una reducción del tiempo promedio de bloqueo a valores aceptables

Salida

La optimización y el ajuste fino de las consultas a la base de datos MS SQL a través de LINQ es una tarea que se puede resolver.

En este trabajo, la atención y la secuencialidad son muy importantes.

Al comienzo del proceso:

es necesario verificar los datos con los que trabaja la consulta (valores, tipos de datos seleccionados)
realizar una correcta indexación de estos datos
verificar la corrección de las condiciones de unión entre las tablas

En la siguiente iteración de optimización se identifican:

la base de la consulta y se determina el filtro principal de la consulta
bloques similares repetidos de la consulta y se analiza la intersección de las condiciones
en SSMS u otra GUI para SQL Server optimiza el mismo Consulta SQL (donde se destaca el almacenamiento intermedio de datos, construyendo la consulta resultante usando este almacenamiento (puede haber varios))
en la última etapa, tomando como base la resultante Consulta SQL, se reestructura la estructura de la consulta LINQ

Como resultado, la consulta resultante Consulta LINQ debe volverse estructuralmente idéntica a la óptima identificada consulta SQL del punto 3.

Agradecimientos

Muchas gracias a los colegas jobgemws y alex_ozr de la empresa Fortis por la ayuda en la preparación de este material.

Fuente: habr.com