performance - practices - entity framework precompiled query

¿Por qué el operador Contains() degrada el rendimiento de Entity Framework de manera tan dramática?

Question

ACTUALIZACIÓN 3: De acuerdo con este anuncio , esto ha sido abordado por el equipo EF en EF6 alpha 2.

ACTUALIZACIÓN 2: He creado una sugerencia para solucionar este problema. Para votar, ve aquí .

Considere una base de datos SQL con una tabla muy simple.

CREATE TABLE Main (Id INT PRIMARY KEY)

Yo puebla la tabla con 10,000 registros.

WITH Numbers AS
(
  SELECT 1 AS Id
  UNION ALL
  SELECT Id + 1 AS Id FROM Numbers WHERE Id <= 10000
)
INSERT Main (Id)
SELECT Id FROM Numbers
OPTION (MAXRECURSION 0)

Construyo un modelo EF para la tabla y ejecuto la siguiente consulta en LINQPad (estoy usando el modo "C # Statements" para que LINQPad no cree un volcado automáticamente).

var rows = 
  Main
  .ToArray();

El tiempo de ejecución es ~ 0.07 segundos. Ahora agrego el operador Contiene y vuelvo a ejecutar la consulta.

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  Main
  .Where (a => ids.Contains(a.Id))
  .ToArray();

El tiempo de ejecución para este caso es 20.14 segundos (288 veces más lento)!

Al principio, sospeché que el T-SQL emitido para la consulta tardaba más en ejecutarse, así que traté de cortarlo y pegarlo desde el panel SQL de LINQPad en SQL Server Management Studio.

SET NOCOUNT ON
SET STATISTICS TIME ON
SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Primary] AS [Extent1]
WHERE [Extent1].[Id] IN (1,2,3,4,5,6,7,8,...

Y el resultado fue

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 88 ms.

Luego, sospeché que LINQPad estaba causando el problema, pero el rendimiento es el mismo ya sea que lo ejecute en LINQPad o en una aplicación de consola.

Entonces, parece que el problema está en alguna parte dentro de Entity Framework.

¿Estoy haciendo algo mal aquí? Esta es una parte crítica del tiempo de mi código, entonces, ¿hay algo que pueda hacer para acelerar el rendimiento?

Estoy usando Entity Framework 4.1 y Sql Server 2008 R2.

ACTUALIZACIÓN 1:

En la discusión a continuación hubo algunas preguntas sobre si el retraso ocurrió mientras EF estaba desarrollando la consulta inicial o mientras estaba analizando los datos que recibió. Para probar esto, ejecuté el siguiente código:

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  (ObjectQuery<MainRow>)
  Main
  .Where (a => ids.Contains(a.Id));
var sql = rows.ToTraceString();

lo que obliga a EF a generar la consulta sin ejecutarla en la base de datos. El resultado fue que este código requería ~ 20 segords para ejecutarse, por lo que parece que casi todo el tiempo se toma al compilar la consulta inicial.

CompiledQuery al rescate entonces? No tan rápido ... CompiledQuery requiere que los parámetros pasados ​​a la consulta sean tipos fundamentales (int, string, float, etc.). No aceptará matrices o IEnumerable, por lo que no puedo usarlo para obtener una lista de Id.

Answer 1

Editando mi respuesta original: existe una posible solución, dependiendo de la complejidad de sus entidades. Si conoce el SQL que EF genera para poblar sus entidades, puede ejecutarlo directamente usando DbContext.Database.SqlQuery . En EF 4, creo que podrías usar ObjectContext.ExecuteStoreQuery , pero no lo intenté.

Por ejemplo, usando el código de mi respuesta original a continuación para generar la instrucción sql usando un StringBuilder , pude hacer lo siguiente

var rows = db.Database.SqlQuery<Main>(sql).ToArray();

y el tiempo total pasó de aproximadamente 26 segundos a 0.5 segundos.

Seré el primero en decir que es feo y espero que se presente una mejor solución.

actualizar

Después de pensar un poco más, me di cuenta de que si usa un join para filtrar sus resultados, EF no tiene que compilar esa larga lista de ids. Esto podría ser complejo según el número de consultas simultáneas, pero creo que podría usar identificadores de usuario o identificadores de sesión para aislarlos.

Para probar esto, creé una tabla Target con el mismo esquema que Main . Luego utilicé un StringBuilder para crear comandos INSERT para llenar la tabla Target en lotes de 1,000 ya que es lo que más SQL Server aceptará en un solo INSERT . La ejecución directa de las sentencias sql fue mucho más rápido que pasar por EF (aproximadamente 0,3 segundos frente a 2,5 segundos), y creo que estaría bien, ya que el esquema de la tabla no debería cambiar.

Finalmente, al seleccionar usar una join obtuvo una consulta mucho más simple y se ejecutó en menos de 0,5 segundos.

ExecuteStoreCommand("DELETE Target");

var ids = Main.Select(a => a.Id).ToArray();
var sb = new StringBuilder();

for (int i = 0; i < 10; i++)
{
    sb.Append("INSERT INTO Target(Id) VALUES (");
    for (int j = 1; j <= 1000; j++)
    {
        if (j > 1)
        {
            sb.Append(",(");
        }
        sb.Append(i * 1000 + j);
        sb.Append(")");
    }
    ExecuteStoreCommand(sb.ToString());
    sb.Clear();
}

var rows = (from m in Main
            join t in Target on m.Id equals t.Id
            select m).ToArray();

rows.Length.Dump();

Y el sql generado por EF para la unión:

SELECT 
[Extent1].[Id] AS [Id]
FROM  [dbo].[Main] AS [Extent1]
INNER JOIN [dbo].[Target] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

(respuesta original)

Esta no es una respuesta, pero quería compartir algo de información adicional y es demasiado tiempo para encajar en un comentario. Pude reproducir tus resultados y agregar algunas cosas más:

El Analizador de SQL muestra que el retraso se produce entre la ejecución de la primera consulta ( Main.Select ) y la segunda consulta Main.Where , por lo que sospeché que el problema estaba en generar y enviar una consulta de ese tamaño (48,980 bytes).

Sin embargo, construir la misma instrucción sql en T-SQL toma dinámicamente menos de 1 segundo, y tomar los ids de su declaración Main.Select , construir la misma instrucción sql y ejecutarla usando un SqlCommand tomó 0,112 segundos, y eso incluye incluir el tiempo para escribir el contenido de la consola.

En este punto, sospecho que EF está haciendo un análisis / procesamiento para cada uno de los 10,000 ids medida que construye la consulta. Ojalá pudiera dar una respuesta definitiva y una solución :(.

Este es el código que probé en SSMS y LINQPad (por favor, no critique demasiado fuerte, estoy apurado tratando de dejar el trabajo):

declare @sql nvarchar(max)

set @sql = ''SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Main] AS [Extent1]
WHERE [Extent1].[Id] IN (''

declare @count int = 0
while @count < 10000
begin
    if @count > 0 set @sql = @sql + '',''
    set @count = @count + 1
    set @sql = @sql + cast(@count as nvarchar)
end
set @sql = @sql + '')''

exec(@sql)

var ids = Mains.Select(a => a.Id).ToArray();

var sb = new StringBuilder();
sb.Append("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN (");
for(int i = 0; i < ids.Length; i++)
{
    if (i > 0) 
        sb.Append(",");     
    sb.Append(ids[i].ToString());
}
sb.Append(")");

using (SqlConnection connection = new SqlConnection("server = localhost;database = Test;integrated security = true"))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = sb.ToString();
    connection.Open();
    using(SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0));
        }
    }
}

Answer 2

El problema es con la generación SQL de Entity Framework. No puede almacenar en caché la consulta si uno de los parámetros es una lista.

Para que EF guarde en caché su consulta, puede convertir su lista en una cadena y hacer un .Contains en la cadena.

Entonces, por ejemplo, este código se ejecutaría mucho más rápido ya que EF podría almacenar en caché la consulta:

var ids = Main.Select(a => a.Id).ToArray();
var idsString = "|" + String.Join("|", ids) + "|";
var rows = Main
.Where (a => idsString.Contains("|" + a.Id + "|"))
.ToArray();

Cuando se genera esta consulta, es probable que se genere con un Me gusta en lugar de una En, por lo que acelerará tu C #, pero podría ralentizar tu SQL. En mi caso, no noté ninguna disminución en el rendimiento en mi ejecución de SQL, y el C # corrió significativamente más rápido.

Answer 3

No estoy familiarizado con Entity Framework, pero ¿es mejor si haces lo siguiente?

En lugar de esto:

var ids = Main.Select(a => a.Id).ToArray();
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

¿Qué tal esto? (suponiendo que el ID sea un int):

var ids = new HashSet<int>(Main.Select(a => a.Id));
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

Answer 4

Pudimos resolver el problema de EF Contiene agregando una tabla intermedia y uniéndonos a esa tabla de la consulta LINQ que necesitaba usar la cláusula Contiene. Pudimos obtener resultados sorprendentes con este enfoque. Tenemos un gran modelo de EF y como "Contiene" no está permitido cuando se compilan las consultas de EF, obtuvimos un rendimiento muy bajo para las consultas que usan la cláusula "Contiene".

Una visión general:

• Cree una tabla en SQL Server; por ejemplo, HelperForContainsOfIntType con HelperID de Guid data-type y ReferenceID de int tipo de datos de columnas. Cree tablas diferentes con ReferenceID de diferentes tipos de datos según sea necesario.

• Cree una Entidad / EntitySet para HelperForContainsOfIntType y otras tablas similares en el modelo EF. Cree diferentes Entity / EntitySet para diferentes tipos de datos según sea necesario.

• Cree un método auxiliar en el código .NET que toma la entrada de un IEnumerable<int> y devuelve una Guid . Este método genera un nuevo Guid e inserta los valores de IEnumerable<int> en HelperForContainsOfIntType junto con el Guid generado. A continuación, el método devuelve este Guid recién generado a la persona que llama. Para insertar rápidamente en la tabla HelperForContainsOfIntType , cree un procedimiento almacenado que toma la entrada de una lista de valores y realiza la inserción. Consulte los Parámetros con valores de tabla en SQL Server 2008 (ADO.NET) . Cree diferentes ayudantes para diferentes tipos de datos o cree un método de ayuda genérico para manejar diferentes tipos de datos.

• Cree una consulta compilada de EF que sea similar a algo como a continuación:

  static Func<MyEntities, Guid, IEnumerable<Customer>> _selectCustomers =
      CompiledQuery.Compile(
          (MyEntities db, Guid containsHelperID) =>
              from cust in db.Customers
              join x in db.HelperForContainsOfIntType on cust.CustomerID equals x.ReferenceID where x.HelperID == containsHelperID
              select cust 
      );

• Llame al método auxiliar con los valores que se utilizarán en la cláusula Contains y obtenga el Guid para usar en la consulta. Por ejemplo:

  var containsHelperID = dbHelper.InsertIntoHelperForContainsOfIntType(new int[] { 1, 2, 3 });
  var result = _selectCustomers(_dbContext, containsHelperID).ToList();

Answer 5

Se corrigió en Entity Framework 6 Alpha 2: http://entityframework.codeplex.com/SourceControl/changeset/a7b70f69e551

http://blogs.msdn.com/b/adonet/archive/2012/12/10/ef6-alpha-2-available-on-nuget.aspx

Answer 6

Si encuentra un problema de rendimiento que le bloquea, no intente pasar siglos tratando de resolverlo, porque lo más probable es que no tenga éxito y tendrá que comunicarlo directamente con MS (si tiene soporte premium) y se necesita siglos.

Utilice la solución alternativa y la solución alternativa en caso de problema de rendimiento y EF significa SQL directo. No hay nada malo al respecto. Idea global de que usar EF = no usar SQL más es una mentira. Tiene SQL Server 2008 R2 entonces:

• Cree un procedimiento almacenado que acepte el parámetro valuado de la tabla para pasar sus identificadores
• Deje que su procedimiento almacenado devuelva múltiples conjuntos de resultados para emular Include lógica de manera óptima
• Si necesita construir una consulta compleja, use SQL dinámico dentro del procedimiento almacenado
• Usa SqlDataReader para obtener resultados y construir tus entidades
• Adjúntelos al contexto y trabaje con ellos como si se cargaran desde EF

Si el rendimiento es crítico para usted, no encontrará una mejor solución. EF no puede mapear y ejecutar este procedimiento porque la versión actual no admite parámetros de tabla o conjuntos de resultados múltiples.

Answer 7

¿Una alternativa almacenable en caché a Contiene?

Esto me mordió, así que agregué mis dos peniques al enlace Sugerencias de funciones de Entity Framework.

El problema es definitivamente cuando se genera el SQL. Tengo un cliente sobre los datos de quién fue la generación de la consulta de 4 segundos, pero la ejecución fue de 0.1 segundos.

Noté que al usar LINQ y OR dinámicos, la generación sql tardaba tanto, pero generaba algo que podía almacenarse en caché . Entonces, al ejecutarlo de nuevo, fue hasta 0.2 segundos.

Tenga en cuenta que todavía se generó un SQL en.

Algo más a tener en cuenta si puedes soportar el golpe inicial, tu recuento de arreglo no cambia mucho y ejecutar la consulta mucho. (Probado en LINQ Pad)

Answer 8

ACTUALIZACIÓN: EF6 incluye mejoras de rendimiento dramáticas para Enumerable.Contains.

Tiene razón en que pasa la mayor parte del tiempo procesando la traducción de la consulta. El modelo de proveedor de EF actualmente no incluye una expresión que represente una cláusula IN, por lo tanto, los proveedores de ADO.NET no pueden admitir IN de forma nativa. En cambio, la implementación de Enumerable.Contains lo traduce en un árbol de expresiones OR, es decir, para algo que en C # se ve así:

new []{1, 2, 3, 4}.Contains(i)

... generaremos un árbol DbExpression que podría representarse así:

((1 = @i) OR (2 = @i)) OR ((3 = @i) OR (4 = @i))

(Los árboles de expresión tienen que estar equilibrados porque si tuviéramos todas las RUP en un único lomo largo, habría más posibilidades de que la expresión visitante golpeara un desbordamiento de la pila (sí, de hecho, lo golpeamos en nuestras pruebas))

Posteriormente enviamos un árbol como este al proveedor ADO.NET, que puede tener la capacidad de reconocer este patrón y reducirlo a la cláusula IN durante la generación de SQL.

Cuando agregamos soporte para Enumerable.Contains en EF4, pensamos que era deseable hacerlo sin tener que introducir soporte para las expresiones IN en el modelo del proveedor, y honestamente, 10,000 es mucho más que la cantidad de elementos que anticipamos que los clientes pasarían a Enumerable.Contains. Dicho esto, entiendo que esto es una molestia y que la manipulación de árboles de expresiones hace que las cosas sean demasiado caras en su escenario particular.

Discutí esto con uno de nuestros desarrolladores y creemos que en el futuro podríamos cambiar la implementación agregando soporte de primera clase para IN. Me aseguraré de que esto se agregue a nuestro trabajo atrasado, pero no puedo prometer cuándo lo hará dado que hay muchas otras mejoras que nos gustaría hacer.

Para las soluciones alternativas ya sugeridas en el hilo, agregaría lo siguiente:

Considere la posibilidad de crear un método que equilibre la cantidad de viajes de ida y vuelta de la base de datos con la cantidad de elementos que pasa a Contiene. Por ejemplo, en mis propias pruebas observé que al computar y ejecutar contra una instancia local de SQL Server, la consulta con 100 elementos toma 1/60 de segundo. Si puede escribir su consulta de tal manera que la ejecución de 100 consultas con 100 conjuntos diferentes de identificadores le daría un resultado equivalente a la consulta con 10.000 elementos, entonces puede obtener los resultados en aproximadamente 1,67 segundos en lugar de 18 segundos.

Diferentes tamaños de fragmentos deberían funcionar mejor según la consulta y la latencia de la conexión de la base de datos. Para ciertas consultas, es decir, si la secuencia aprobada tiene duplicados o si se usa Enumerable.Contains en una condición anidada, puede obtener elementos duplicados en los resultados.

Aquí hay un fragmento de código (lo siento si el código utilizado para dividir la entrada en fragmentos parece un poco complejo. Hay formas más simples de lograr lo mismo, pero estaba tratando de encontrar un patrón que preserve la transmisión de la secuencia y No pude encontrar nada parecido en LINQ, así que probablemente exagere esa parte :)):

Uso:

var list = context.GetMainItems(ids).ToList();

Método para el contexto o repositorio:

public partial class ContainsTestEntities
{
    public IEnumerable<Main> GetMainItems(IEnumerable<int> ids, int chunkSize = 100)
    {
        foreach (var chunk in ids.Chunk(chunkSize))
        {
            var q = this.MainItems.Where(a => chunk.Contains(a.Id));
            foreach (var item in q)
            {
                yield return item;
            }
        }
    }
}

Métodos de extensión para cortar secuencias enumerables:

public static class EnumerableSlicing
{

    private class Status
    {
        public bool EndOfSequence;
    }

    private static IEnumerable<T> TakeOnEnumerator<T>(IEnumerator<T> enumerator, int count, 
        Status status)
    {
        while (--count > 0 && (enumerator.MoveNext() || !(status.EndOfSequence = true)))
        {
            yield return enumerator.Current;
        }
    }

    public static IEnumerable<IEnumerable<T>> Chunk<T>(this IEnumerable<T> items, int chunkSize)
    {
        if (chunkSize < 1)
        {
            throw new ArgumentException("Chunks should not be smaller than 1 element");
        }
        var status = new Status { EndOfSequence = false };
        using (var enumerator = items.GetEnumerator())
        {
            while (!status.EndOfSequence)
            {
                yield return TakeOnEnumerator(enumerator, chunkSize, status);
            }
        }
    }
}

¡Espero que esto ayude!