Compartir a través de

Consultas eficientes

Consultar de manera eficiente es un tema amplio que abarca temas tan variados como índices, estrategias de carga de entidades relacionadas y muchos otros. En esta sección se detallan algunos temas comunes para acelerar las consultas y los problemas que suelen encontrar los usuarios.

Uso de índices correctamente

El factor de decisión principal en si una consulta se ejecuta rápidamente o no es si usará correctamente índices cuando corresponda: las bases de datos se usan normalmente para contener grandes cantidades de datos y las consultas que atraviesan tablas completas suelen ser orígenes de problemas graves de rendimiento. Los problemas de indexación no son fáciles de detectar, ya que no es obvio inmediatamente si una consulta determinada usará o no un índice. Por ejemplo:

// Matches on start, so uses an index (on SQL Server)
var posts1 = await context.Posts.Where(p => p.Title.StartsWith("A")).ToListAsync();
// Matches on end, so does not use the index
var posts2 = await context.Posts.Where(p => p.Title.EndsWith("A")).ToListAsync();

Una buena manera de detectar problemas de indexación es identificar primero una consulta lenta y, a continuación, examinar su plan de consulta a través de la herramienta favorita de la base de datos; consulte la página de diagnóstico de rendimiento para obtener más información sobre cómo hacerlo. El plan de consulta muestra si la consulta atraviesa toda la tabla o usa un índice.

Como regla general, no hay ningún conocimiento especial de EF para usar índices ni diagnosticar problemas de rendimiento relacionados con ellos; El conocimiento general de la base de datos relacionado con los índices es tan relevante para las aplicaciones de EF como para las aplicaciones que no usan EF. A continuación se enumeran algunas directrices generales que se deben tener en cuenta al usar índices:

  • Aunque los índices aceleran las consultas, también ralentizan las actualizaciones, ya que deben mantenerse up-to-date. Evite definir índices que no sean necesarios y considere la posibilidad de usar filtros de índice para limitar el índice a un subconjunto de las filas, lo que reduce esta sobrecarga.
  • Los índices compuestos pueden acelerar las consultas que filtran por varias columnas, pero también pueden acelerar las consultas que no filtran por todas las columnas del índice, en función del orden. Por ejemplo, un índice en las columnas A y B acelera el filtrado de consultas por A y B, así como las consultas que filtran solo por A, pero no acelera solo el filtrado de consultas a través de B.
  • Si una consulta filtra por una expresión sobre una columna (por ejemplo price / 2, ), no se puede usar un índice simple. Sin embargo, puede definir una columna persistente almacenada para la expresión y crear un índice sobre ella. Algunas bases de datos también admiten índices de expresión, que se pueden usar directamente para acelerar el filtrado de consultas por cualquier expresión.
  • Las distintas bases de datos permiten configurar índices de varias maneras y, en muchos casos, los proveedores de EF Core los exponen a través de la API fluent. Por ejemplo, el proveedor de SQL Server permite configurar si un índice está agrupado o si establece su factor de relleno. Consulte la documentación de su proveedor para obtener más información.

Proyecta solo las propiedades que necesitas

EF Core facilita la consulta de instancias de entidad y, a continuación, usa esas instancias en el código. Sin embargo, la consulta de instancias de entidad puede extraer con frecuencia más datos de los necesarios de la base de datos. Tenga en cuenta lo siguiente.

await foreach (var blog in context.Blogs.AsAsyncEnumerable())
{
    Console.WriteLine("Blog: " + blog.Url);
}

Aunque este código realmente solo necesita la propiedad de Url de cada blog, se recupera toda la entidad blog y se transfieren columnas innecesarias desde la base de datos.

SELECT [b].[BlogId], [b].[CreationDate], [b].[Name], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]

Esto se puede optimizar mediante Select para indicar a EF qué columnas proyectar:

await foreach (var blogName in context.Blogs.Select(b => b.Url).AsAsyncEnumerable())
{
    Console.WriteLine("Blog: " + blogName);
}

El CÓDIGO SQL resultante solo devuelve las columnas necesarias:

SELECT [b].[Url]
FROM [Blogs] AS [b]

Si necesita proyectar más de una columna, proyecte a un tipo anónimo en C# con las propiedades que desee.

Tenga en cuenta que esta técnica es muy útil para las consultas de solo lectura, pero las cosas se complican más si necesita actualizar los blogs capturados, ya que el seguimiento de cambios de EF solo funciona con instancias de entidad. Es posible realizar actualizaciones sin cargar entidades completas adjuntando una instancia de Blog modificada e indicando a EF qué propiedades han cambiado, pero que es una técnica más avanzada que puede no merece la pena.

Limitar el tamaño del conjunto de resultados

De forma predeterminada, una consulta devuelve todas las filas que coinciden con sus filtros:

var blogsAll = await context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .ToListAsync();

Dado que el número de filas devueltas depende de los datos reales de la base de datos, es imposible saber cuánto datos se cargarán desde la base de datos, cuánto memoria tomarán los resultados y cuánto carga adicional se generará al procesar estos resultados (por ejemplo, enviandolos a un explorador de usuarios a través de la red). Fundamentalmente, las bases de datos de prueba suelen contener pocos datos, por lo que todo funciona bien mientras se prueban, pero los problemas de rendimiento aparecen repentinamente cuando la consulta comienza a ejecutarse en datos reales y se devuelven muchas filas.

Como resultado, normalmente vale la pena pensar en limitar el número de resultados:

var blogs25 = await context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .Take(25)
    .ToListAsync();

Como mínimo, la interfaz de usuario podría mostrar un mensaje que indica que pueden existir más filas en la base de datos (y permitir recuperarlas de alguna otra manera). Una solución completa implementaría la paginación, donde la interfaz de usuario solo muestra un número determinado de filas a la vez y permite a los usuarios avanzar a la página siguiente según sea necesario; consulte la sección siguiente para más detalles sobre cómo implementarlo eficientemente.

Paginación eficaz

La paginación hace referencia a la recuperación de resultados en páginas, en lugar de en todas a la vez; Esto suele hacerse para grandes conjuntos de resultados, donde se muestra una interfaz de usuario que permite al usuario navegar a la página siguiente o anterior de los resultados. Una manera común de implementar la paginación con bases de datos es usar los Skip operadores y Take (OFFSET y LIMIT en SQL); aunque se trata de una implementación intuitiva, también es bastante ineficaz. Para la paginación que permite mover una página a la vez (en lugar de saltar a páginas arbitrarias), considere la posibilidad de usar la paginación del conjunto de claves en su lugar.

Para obtener más información, consulte la página de documentación sobre la paginación.

En las bases de datos relacionales, todas las entidades relacionadas se cargan mediante la introducción de JOIN en una sola consulta.

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

Si un blog típico tiene varias entradas relacionadas, las filas de estas entradas duplicarán la información del blog. Esta duplicación conduce al problema denominado "explosión cartesiana". A medida que se cargan más relaciones de uno a varios, la cantidad de datos duplicados puede crecer y afectar negativamente al rendimiento de la aplicación.

EF permite evitar este efecto mediante el uso de "consultas divididas", que cargan las entidades relacionadas a través de consultas independientes. Para obtener más información, lea la documentación sobre las consultas divididas y únicas.

Nota:

La implementación actual de consultas divididas ejecuta un recorrido de ida y vuelta para cada consulta. Tenemos previsto mejorar esto en el futuro y ejecutar todas las consultas en un solo recorrido de ida y vuelta.

Se recomienda leer la página dedicada sobre entidades relacionadas antes de continuar con esta sección.

Al tratar con entidades relacionadas, normalmente sabemos con antelación lo que necesitamos cargar: un ejemplo típico sería cargar un determinado conjunto de blogs, junto con todas sus publicaciones. En estos escenarios, siempre es mejor usar la carga diligente, de modo que EF pueda capturar todos los datos necesarios en un recorrido de ida y vuelta. La característica de inclusión filtrada también le permite limitar qué entidades relacionadas desea cargar, al tiempo que mantiene el proceso de carga diligente y, por tanto, se puede hacer en un solo recorrido de ida y vuelta:

using (var context = new BloggingContext())
{
    var filteredBlogs = await context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.BlogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5))
        .ToListAsync();
}

En otros escenarios, es posible que no sepamos qué entidad relacionada vamos a necesitar antes de obtener su entidad principal. Por ejemplo, al cargar algún blog, es posible que tengamos que consultar algún otro origen de datos (posiblemente un servicio web) para saber si estamos interesados en las entradas de ese blog. En estos casos, la carga explícita o diferida se puede usar para obtener entidades relacionadas por separado y completar la navegación de las entradas del blog. Tenga en cuenta que, dado que estos métodos no se ejecutan de manera inmediata, requieren consultas adicionales a la base de datos, lo cual es fuente de un rendimiento lento. Dependiendo de su escenario específico, puede ser más eficiente cargar siempre todas las Publicaciones, en lugar de realizar las consultas adicionales y obtener de forma selectiva solo las Publicaciones que necesita.

Tenga cuidado con la carga diferida

La carga diferida suele parecer una manera muy útil de escribir lógica de base de datos, ya que EF Core carga automáticamente entidades relacionadas desde la base de datos a medida que el código accede a ellas. Esto evita cargar entidades relacionadas que no son necesarias (como la carga explícita) y aparentemente libera al programador de tener que tratar con entidades relacionadas por completo. Sin embargo, la carga diferida es especialmente propensa a producir recorridos de ida y vuelta adicionales innecesarios que pueden ralentizar la aplicación.

Tenga en cuenta lo siguiente.

foreach (var blog in await context.Blogs.ToListAsync())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Este fragmento de código aparentemente inocente recorre en iteración todos los blogs y sus publicaciones, imprimiéndolas. Al activar el registro de instrucciones de EF Core se muestra lo siguiente:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [b].[BlogId], [b].[Rating], [b].[Url]
      FROM [Blogs] AS [b]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='2'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0

... and so on

¿Qué ocurre aquí? ¿Por qué se envían todas estas consultas sobre los bucles simples anteriores? Con la carga diferida, las entradas de un blog solo se cargan (diferidamente) cuando se accede a su propiedad Posts; como resultado, cada iteración del foreach interno desencadena una consulta de base de datos adicional, en su propio recorrido de ida y vuelta. Como resultado, después de que la consulta inicial cargue todos los blogs, tenemos otra consulta por blog, cargando todas sus entradas; esto a veces se denomina problema N+1 y puede causar problemas de rendimiento muy significativos.

Suponiendo que vamos a necesitar todas las entradas de los blogs, tiene sentido usar la carga diligente aquí en su lugar. Podemos usar el operador Include para realizar la carga, pero dado que solo necesitamos las direcciones URL de los blogs (y solo debemos cargar lo que se necesita). Por lo tanto, usaremos una proyección en su lugar:

await foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).AsAsyncEnumerable())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Esto hará que EF Core capture todos los blogs (junto con sus publicaciones) en una sola consulta. En algunos casos, también puede ser útil evitar efectos de explosión cartesianos mediante consultas divididas.

Advertencia

Dado que la carga diferida facilita el desencadenamiento accidental del problema de N+1, se recomienda evitarla. La carga diligente o explícita hace que sea muy clara en el código fuente cuando se produce un recorrido de ida y vuelta de base de datos.

Almacenamiento en búfer y transmisión

El almacenamiento en búfer hace referencia a la carga de todos los resultados de la consulta en la memoria, mientras que el streaming significa que EF entrega a la aplicación un único resultado cada vez, nunca contiene todo el conjunto de resultados en la memoria. En principio, los requisitos de memoria de una consulta de streaming son fijos; son iguales si la consulta devuelve 1 fila o 1000; una consulta de almacenamiento en búfer, por otro lado, requiere más memoria que se devuelvan más filas. En el caso de las consultas que generan grandes conjuntos de resultados, esto puede ser un factor de rendimiento importante.

Depende de cómo se evalúe si una consulta almacenará en búfer o transmitirá en secuencias.

// ToList and ToArray cause the entire resultset to be buffered:
var blogsList = await context.Posts.Where(p => p.Title.StartsWith("A")).ToListAsync();
var blogsArray = await context.Posts.Where(p => p.Title.StartsWith("A")).ToArrayAsync();

// Foreach streams, processing one row at a time:
await foreach (var blog in context.Posts.Where(p => p.Title.StartsWith("A")).AsAsyncEnumerable())
{
    // ...
}

// AsAsyncEnumerable also streams, allowing you to execute LINQ operators on the client-side:
var doubleFilteredBlogs = context.Posts
    .Where(p => p.Title.StartsWith("A")) // Translated to SQL and executed in the database
    .AsAsyncEnumerable()
    .Where(p => SomeDotNetMethod(p)); // Executed at the client on all database results

Si las consultas devuelven solo unos pocos resultados, es probable que no tenga que preocuparse por esto. Sin embargo, si su consulta puede devolver un gran número de filas, es recomendable considerar la transmisión en flujo en lugar del uso de búfer.

Nota:

Evite usar ToList o ToArray si piensa usar otro operador LINQ en el resultado: esto no necesitará almacenar en búfer todos los resultados en la memoria. En su lugar, use AsEnumerable.

Memoria intermedia interna de EF

En determinadas situaciones, EF en sí mismo almacenará en búfer el conjunto de resultados internamente, sin importar cómo evalúe su consulta. Los dos casos en los que esto sucede son:

  • Cuando se implementa una estrategia de ejecución de reintento. Esto se hace para asegurarse de que se devuelven los mismos resultados si la consulta se reintenta más adelante.
  • Cuando se usa la consulta dividida , los conjuntos de resultados de todas pero la última consulta se almacenan en búfer, a menos que MARS (conjuntos de resultados activos múltiples) esté habilitado en SQL Server. Esto se debe a que normalmente es imposible tener varios conjuntos de resultados de consulta activos al mismo tiempo.

Tenga en cuenta que este almacenamiento en búfer interno ocurre además de cualquier almacenamiento en búfer que cause a través de operadores LINQ. Por ejemplo, si usa ToList en una consulta y se implementa una estrategia de ejecución de reintento, el conjunto de resultados se carga en la memoria dos veces: una vez internamente por EF y una vez por ToList.

Seguimiento, sin seguimiento y resolución de identidades

Se recomienda leer la página dedicada sobre seguimiento y no seguimiento antes de continuar con esta sección.

EF realiza un seguimiento de las instancias de entidad de forma predeterminada, de modo que los cambios en ellas se detectan y se guardan cuando se llama a SaveChanges. Otro efecto de las consultas de seguimiento es que EF detecta si ya se ha cargado una instancia para los datos y devolverá automáticamente esa instancia de seguimiento en lugar de devolver una nueva; esto se denomina resolución de identidad. Desde una perspectiva de rendimiento, el seguimiento de cambios significa lo siguiente:

  • EF mantiene internamente un diccionario de instancias rastreadas. Cuando se cargan nuevos datos, EF comprueba el diccionario para ver si ya se está rastreando una instancia para la clave de esa entidad (resolución de identidad). El mantenimiento del diccionario y las búsquedas tardan algún tiempo al cargar los resultados de la consulta.
  • Antes de entregar una instancia cargada a la aplicación, EF toma una instantánea de dicha instancia y la mantiene internamente. Cuando se llama a SaveChanges, la instancia de la aplicación se compara con la instantánea para detectar los cambios que deben persistir. La instantánea ocupa más memoria y el propio proceso de creación de instantáneas tarda tiempo; a veces es posible especificar un comportamiento de instantánea diferente, posiblemente más eficaz a través de comparadores de valores, o usar servidores proxy de seguimiento de cambios para omitir el proceso de creación de instantáneas por completo (aunque esto incluye su propio conjunto de desventajas).

En escenarios de solo lectura en los que los cambios no se guardan en la base de datos, se pueden evitar las sobrecargas anteriores mediante consultas sin seguimiento. Sin embargo, dado que las consultas sin seguimiento no realizan la resolución de identidades, se materializará una fila de base de datos a la que hacen referencia varias filas cargadas como instancias diferentes.

Para ilustrarlo, supongamos que estamos cargando un gran número de entradas de la base de datos, así como el blog al que hace referencia cada entrada. Si 100 entradas hacen referencia al mismo blog, una consulta de seguimiento detecta esto a través de la resolución de identidad y todas las instancias de Post harán referencia a la misma instancia de Blog desduplicada. Una consulta sin seguimiento, en cambio, duplica el mismo blog 100 veces y el código de aplicación se debe escribir en consecuencia.

Estos son los resultados de una prueba comparativa que compara el seguimiento frente al comportamiento sin seguimiento de una consulta que carga 10 blogs con 20 entradas cada una. El código fuente está disponible aquí, no dude en usarlo como base para sus propias medidas.

Método NumBlogs NúmeroDePublicacionesPorBlog Promedio Error StdDev Mediana Proporción RatioSD Gen 0 Gen 1 Gen 2 Asignado
AsTracking 10 20 1.414,7 27.20 nosotros 45.44 nosotros 1.405,5 us 1.00 0.00 60,5469 13.6719 - 380.11 KB
AsNoTracking 10 20 993.3 nosotros 24.04 nosotros 65.40 nosotros 966.2 µs 0.71 0,05 37.1094 6.8359 - 232,89 KB

Por último, es posible realizar actualizaciones sin la sobrecarga del seguimiento de cambios, mediante el uso de una consulta sin seguimiento y, a continuación, adjuntar la instancia devuelta al contexto, especificando qué cambios se van a realizar. Esto transfiere la carga del seguimiento de cambios de EF al usuario y solo debe intentarse si se ha demostrado que la sobrecarga de seguimiento de cambios es inaceptable a través de la generación de perfiles o pruebas comparativas.

Uso de consultas SQL

En algunos casos, existe SQL más optimizado para la consulta, que EF no genera. Esto puede ocurrir cuando la construcción SQL es una extensión específica de la base de datos que no se admite o simplemente porque EF aún no se traduce en ella. En estos casos, escribir SQL a mano puede proporcionar un aumento considerable del rendimiento y EF admite varias maneras de hacerlo.

  • Use consultas SQL directamente en tu consulta, por ejemplo, a través de FromSqlRaw. EF incluso le permite componer sobre SQL con consultas LINQ normales, permitiéndole expresar solo una parte de la consulta en SQL. Se trata de una buena técnica cuando solo es necesario usar SQL en una sola consulta del código base.
  • Defina una función definida por el usuario (UDF) y luego llámela desde sus consultas. Tenga en cuenta que EF permite que las UDF devuelvan conjuntos de resultados completos ( se conocen como funciones con valores de tabla (TVF) y también permiten asignar un elemento DbSet a una función, lo que hace que parezca simplemente otra tabla.
  • Defina una vista de base de datos y consúltela en sus consultas. Tenga en cuenta que, a diferencia de las funciones, las vistas no pueden aceptar parámetros.

Nota:

SQL sin formato se debe usar generalmente como último recurso, después de asegurarse de que EF no puede generar el SQL que necesita y cuando el rendimiento es lo suficientemente importante como para justificarlo en la consulta dada. El uso de SQL sin procesar aporta considerables desventajas de mantenimiento.

Programación asincrónica

Como regla general, para que la aplicación sea escalable, es importante usar siempre API asincrónicas en lugar de una sincrónica (por ejemplo SaveChangesAsync , en lugar de SaveChanges). Las API sincrónicas bloquean el subproceso durante la E/S de la base de datos, lo que aumenta la necesidad de subprocesos y el número de cambios de contexto de subproceso que deben ocurrir.

Para obtener más información, consulte la página sobre la programación asincrónica.

Advertencia

Evite mezclar código sincrónico y asincrónico en la misma aplicación: es muy fácil desencadenar accidentalmente problemas sutiles de colapso del grupo de subprocesos.

Advertencia

La implementación asincrónica de Microsoft.Data.SqlClient desafortunadamente tiene algunos problemas conocidos (por ejemplo, #593, #601y otros). Si ve problemas de rendimiento inesperados, intente usar la ejecución de comandos de sincronización en su lugar, especialmente cuando se trate de valores binarios o texto grandes.

Recursos adicionales