Consultas eficaces

  • Artículo

La realización eficaz de consultas es un asunto muy amplio, abarca temas tan amplios como los índices y las estrategias de carga de entidades relacionadas, entre otros. En esta sección se explican en detalles algunos temas comunes para agilizar las consultas, así como los problemas que suelen encontrar los usuarios.

Uso correcto de índices

El factor más determinante para que una consulta se ejecute rápidamente o no es si usará correctamente los índices cuando corresponda: las bases de datos se usan normalmente para contener grandes cantidades de datos y las consultas que atraviesan tablas completas suelen provocar problemas graves de rendimiento. Los problemas de indexación no son fáciles de detectar, ya que no es inmediatamente obvio si una consulta determinada va a usar usará un índice, o no. Por ejemplo:

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

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

Como regla general, no hace falta ningún conocimiento especial de EF para usar índices ni diagnosticar problemas de rendimiento relacionados con ellos; el conocimiento general de las bases 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 actualizados. 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, ya que así se reduce la sobrecarga.
  • Los índices compuestos pueden acelerar las consultas que filtran por varias columnas, pero también pueden acelerar las 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 las consultan que solo filtran por B.
  • Si una consulta filtra por una expresión por una columna (por ejemplo, price / 2), no se puede usar un índice simple. Sin embargo, puede definir una columna almacenada para la expresión y crear un índice por ella. Algunas bases de datos también admiten índices de expresiones, 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 fluida. Por ejemplo, el proveedor de SQL Server permite configurar si un índice está agrupado o establecer su factor de relleno. Para más información, consulte la documentación de su proveedor.

Propiedades solo del proyecto que se necesitan

EF Core facilita mucho la consulta de instancias de entidades y, después, usar esas instancias en el código. Sin embargo, la consulta de instancias de entidades pueden extraer frecuentemente más datos de los necesarios de la base de datos. Tenga en cuenta lo siguiente.

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

Aunque este código solo necesita realmente la propiedad Url de cada blog, se captura toda la entidad Blog, y las columnas que no sean necesarias se transfieren 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 se deben proyectar:

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

El SQL resultante solo devuelve las columnas necesarias:

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

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

Tenga en cuenta que esta técnica es muy útil para consultas de sólo lectura, pero las cosas se complican si necesita actualizar los blogs capturados, ya que el seguimiento de cambios de EF sólo funciona con instancias de entidad. Se pueden realizar actualizaciones sin tener que cargar entidades completas. Para ello, hay que adjuntar una instancia de Blog modificada e indicar a EF cuáles son las propiedades que han cambiado, pero esa es una técnica más avanzada que puede que no merezca la pena llevar a cabo.

Limitación del tamaño del conjunto de resultados

De forma predeterminada, las consultas devuelven todas las filas que coinciden con sus filtros:

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

Dado que el número de filas devueltas depende de los datos reales de la base de datos, es imposible saber cuántos datos se van a cargar de la base de datos, cuánta memoria usarán los resultados y cuánta carga adicional se generará al procesar estos resultados (por ejemplo, enviándolos a un explorador de usuarios a través de la red). Significativamente, con frecuencia las bases de datos de prueba contienen pocos datos, por lo que todo funciona bien mientras durante las pruebas, pero los problemas de rendimiento aparecen repentinamente cuando la consulta empieza a ejecutarse en datos reales y se devuelven muchas filas.

En consecuencia, normalmente merece la pena plantearse la posibilidad de limitar el número de resultados:

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

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 forma). 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 pasar a la siguiente página en caso de que sea necesario; para más detalles sobre cómo implementarla de forma eficaz, consulte la siguiente sección.

Paginación eficaz

La paginación significa recuperar resultados en páginas, en lugar de todos a la vez; esta operación se suele realizar en conjuntos de resultados grandes, donde se muestra una interfaz de usuario que permite al usuario ir 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 operadores Skip y Take (OFFSET y LIMIT en SQL); aunque esta implementación es muy intuitiva, también es bastante ineficaz. Para la paginación que permite mover las páginas de una en una (en lugar de saltar a páginas arbitrarias), considere la posibilidad de usar la paginación del conjunto de claves.

Para 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 instrucciones JOIN en consultas únicas.

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, lo que genera un problema conocido como "explosión cartesiana". A medida que se cargan más relaciones 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 más información, consulte la documentación sobre las consultas divididas y únicas.

Nota:

La implementación actual de las 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.

Antes de continuar con esta sección es aconsejable leer la página dedicada en entidades relacionadas.

Cuando se usan entidades relacionadas, lo habitual es saber con antelación lo que es preciso cargar: un ejemplo típico sería cargar un determinado conjunto de blogs, junto con todas sus entradas. En estos escenarios, siempre es mejor usar una carga diligente, con el fin de que EF pueda capturar todos los datos necesarios en un recorrido de ida y vuelta. La característica de inclusión filtrada también permite limitar las entidades relacionadas que desea cargar, al tiempo que mantiene el proceso de carga diligente y, por consiguiente, se puede hacer en un solo recorrido de ida y vuelta:

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

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 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 capturar entidades relacionadas por separado y rellenar la navegación Entradas del blog. Tenga en cuenta que, dado que estos métodos no son diligentes, requieren recorridos de ida y vuelta adicionales a la base de datos, que es el origen de la ralentización; en función de su escenario específico, puede ser más eficaz que cargar siempre todas las publicaciones, en lugar de ejecutar los recorridos de ida y vuelta adicionales y obtener de forma selectiva solo las entradas que necesita.

Tenga cuidado con la carga diferida

A menudo, la carga diferida parece una forma 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 cuando el código accede a ellas. Esto evita que se carguen entidades relacionadas que no son necesarias (como la carga explícita) y aparentemente libera al programador de tener que tratar con entidades relacionadas. 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 context.Blogs.ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Este fragmento de código, que aparentemente es inocente, recorre en iteración todos los blogs y sus entradas, y las imprime. 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 para los bucles simples anteriores? Con la carga diferida, las entradas de un blog solo se cargan (de forma diferida) cuando se accede a sus entradas correctamente; en consecuencia, cada iteración del foreach interno desencadena una consulta de base de datos adicional, en su propio recorrido de ida y vuelta. En consecuencia, después de que la consulta inicial cargue todos los blogs, tenemos otra consulta por blog, que carga todas sus entradas; esta acción a veces se denomina problema N+1 y puede causar problemas de rendimiento muy graves.

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

foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).ToList())
{
    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 entradas) en una sola consulta. En algunos casos, también puede resultar útil evitar los efectos de la explosión cartesiana mediante consultas divididas.

Advertencia

Dado que la carga diferida hace que sea muy fácil desencadenar accidentalmente el problema de N+1, se recomienda evitarla. La carga diligente o explícita hace que sea muy claro en el código fuente cuando se produce un recorrido de ida y vuelta de la base de datos.

Almacenamiento en búfer y streaming

El almacenamiento en búfer significa cargar todos los resultados de las consultas en memoria, mientras que el streaming significa que EF entrega a la aplicación un resultado cada vez, nunca contiene todo el conjunto de resultados en la memoria. En principio, los requisitos de memoria de una consulta en streaming son fijos, es decir, son iguales tanto si la consulta devuelve una fila como si devuelve mil. Por su parte, una consulta de almacenamiento en búfer, requiere más memoria cuando 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.

Si una consulta se almacena en búfer o se transmite depende de cómo se evalúe:

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

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

// AsEnumerable 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
    .AsEnumerable()
    .Where(p => SomeDotNetMethod(p)); // Executed at the client on all database results

Si las consultas devuelven pocos resultados, es probable que no tenga que preocuparse de este tema. Sin embargo, si existe la posibilidad de que la consulta devuelva un gran número de filas, quizás merezca la pena usar el streaming, en lugar del almacenamiento en búfer.

Nota:

Evite usar ToList o ToArray si piensa usar otro operador LINQ en el resultado (esto almacenará en el búfer innecesariamente todos los resultados que están en la memoria). En su lugar, use AsEnumerable.

Almacenamiento en búfer interno por parte de EF

En determinadas situaciones, EF almacenará en búfer internamente el conjunto de resultados, independientemente de cómo se evalúe la consulta. Estos son los dos casos en los que esto sucede:

  • Cuando está en vigor una estrategia de ejecución de reintentos. Esto se hace para asegurarse de que se devuelven los mismos resultados si la consulta se reintenta más adelante.
  • Cuando se usa una consulta dividida, se almacenan en el búfer los conjuntos de resultados de todas las consultas, a menos que MARS (conjunto de resultados activo múltiple) esté habilitado en SQL Server. Esto se debe a que normalmente no se pueden tener varios conjuntos de resultados de consulta activos al mismo tiempo.

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

Seguimiento, falta de seguimiento y resolución de identidades

Antes de continuar con esta sección se recomienda leer la página dedicada al seguimiento y no seguimiento.

EF realiza un seguimiento de las instancias de entidad de forma predeterminada, de modo que los cambios que se realizan en ellas se detectan y se conservan 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, en lugar de una nueva; esto se denomina resolución de identidad. Desde una perspectiva de rendimiento, esto es lo que significa el seguimiento de cambios:

  • EF mantiene internamente un diccionario de instancias con seguimiento. Cuando se cargan nuevos datos, EF comprueba el diccionario para ver si ya se realiza un seguimiento de alguna instancia de la clave de esa entidad (resolución de identidad). Tanto el mantenimiento del diccionario como las búsquedas tardan un tiempo al cargar los resultados de la consulta.
  • Antes de entregar una instancia cargada a la aplicación, EF crea una instantánea de la misma 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 se van a conservar. La instantánea ocupa más memoria y el propio proceso de creación de la instantánea tarda tiempo; a veces se puede especificar un comportamiento de instantánea diferente, posiblemente más eficaz, a través de comparadores de valores, o bien usar servidores proxy de seguimiento de cambios para omitir el proceso de creación de instantáneas (aunque esto también tiene sus 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, como las consultas sin seguimiento no realizan la resolución de identidad, se materializará una fila de base de datos a la que hacen referencia varias filas cargadas como instancias diferentes.

Para ilustrarlo, supongamos que vamos a cargar muchas entradas de la base de datos, así como el blog al que hace referencia cada una estas entradas. Si cien entradas hacen referencia al mismo blog, una consulta de seguimiento lo detecta a través de la resolución de identidad y todas las instancias de las entradas harán referencia a la misma instancia del blog desduplicada. En cambio, las consultas sin seguimiento duplican el mismo blog cien veces y el código de la aplicación se debe escribir en consecuencia.

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

Método NumBlogs NumPostsPerBlog Media Error StdDev Valor medio Proporción RatioSD Gen 0 Gen 1 Gen 2 Asignado
AsTracking 10 20 1414.7 us 27,20 us 45,44 us 1405,5 us 1.00 0,00 60,5469 13,6719 - 380,11 KB
AsNoTracking 10 20 993,3 us 24,04 us 65,40 us 966,2 us 0.71 0,05 37,1094 6,8359 - 232,89 KB

Por último, es posible realizar actualizaciones sin la sobrecarga del seguimiento de cambios, para lo que se usa una consulta sin seguimiento y, posteriormente, se adjunta la instancia devuelta al contexto, especificando los cambios que 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 de pruebas comparativas.

Uso de consultas SQL

En algunos casos, existe un SQL más optimizado para su 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 formas de hacerlo.

  • Use consultas SQL directamente en la consulta, por ejemplo a través de FromSqlRaw. EF incluso permite redactar sobre SQL con consultas LINQ normales, lo que permite expresar solo una parte de la consulta en SQL. Se trata de una buena técnica cuando el SQL se debe usar en una sola consulta del código base.
  • Defina una función definida por el usuario (UDF) y, después, llámela desde las consultas. Tenga en cuenta que EF permite que las UDF devuelvan conjuntos de resultados completos, que 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 en las consultas una vista de base de datos y una consulta a partir de ella. Tenga en cuenta que, a diferencia de las funciones, las vistas no pueden aceptar parámetros.

Nota:

Por lo general, SQL sin formato se debe usar como último recurso, tras asegurarse de que EF no puede generar el SQL que se desea y cuando el rendimiento es lo suficientemente importante como para que la consulta dada lo justifique. El uso de SQL conlleva considerables desventajas de mantenimiento.

Programación asincrónica

Normalmente, para que una aplicación sea escalable, es importante usar siempre API asincrónicas, en lugar de una sincrónicas (por ejemplo SaveChangesAsync, en lugar de SaveChanges). Las API sincrónicas bloquean el subproceso durante la entrada y salida de la base de datos, lo que aumenta la necesidad de subprocesos y el número de modificadores de contexto de subprocesos que deben producirse.

Para 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

Desafortunadamente, la implementación asincrónica de Microsoft.Data.SqlClient tiene algunos problemas conocidos (p. ej, #593, #601 y otros). Si observa problemas inesperados de rendimiento, pruebe a usar la ejecución del comando de sincronización en su lugar, especialmente cuando trabaje con valores binarios o de texto grandes.

Recursos adicionales