¿Por qué es .Contains slow? La forma más eficiente de obtener múltiples entidades por clave principal?

¿Cuál es la forma más eficiente de seleccionar entidades múltiples por clave principal?

public IEnumerable GetImagesById(IEnumerable ids) { //return ids.Select(id => Images.Find(id)); //is this cool? return Images.Where( im => ids.Contains(im.Id)); //is this better, worse or the same? //is there a (better) third way? } 

Me doy cuenta de que podría hacer algunas pruebas de rendimiento para comparar, pero me pregunto si de hecho hay una mejor manera que ambas, y estoy buscando alguna aclaración sobre cuál es la diferencia entre estas dos consultas, si las hay, una vez que han sido ‘traducido’.

ACTUALIZACIÓN: Con la adición de InExpression en EF6, el rendimiento del procesamiento de Enumerable.Contains mejoró drásticamente. El análisis en esta respuesta es excelente, pero en gran medida obsoleto desde 2013.

Usar Contains en Entity Framework es realmente muy lento. Es cierto que se traduce en una cláusula IN en SQL y que la consulta SQL se ejecuta rápidamente. Pero el problema y el cuello de botella de rendimiento está en la traducción de su consulta LINQ a SQL. El árbol de expresiones que se creará se expande en una larga cadena de concatenaciones de OR porque no hay expresiones nativas que representen un IN . Cuando se crea el SQL, esta expresión de muchas OR se reconoce y se vuelve a colapsar en la cláusula SQL IN .

Esto no significa que usar Contains sea ​​peor que emitir una consulta por elemento en su colección de ids (su primera opción). Probablemente sea aún mejor, al menos para colecciones no demasiado grandes. Pero para colecciones grandes es realmente malo. Recuerdo que hace un tiempo probé una consulta de Contains con alrededor de 12.000 elementos que funcionó pero duró alrededor de un minuto, aunque la consulta en SQL se ejecutó en menos de un segundo.

Podría valer la pena probar el rendimiento de una combinación de múltiples recorridos de ida y vuelta a la base de datos con un número menor de elementos en una expresión Contains para cada ida y vuelta.

Este enfoque y también las limitaciones de usar Contains con Entity Framework se muestran y explican aquí:

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

Es posible que un comando SQL sin procesar tenga el mejor rendimiento en esta situación, lo que significa que debe llamar a dbContext.Database.SqlQuery(sqlString) o dbContext.Images.SqlQuery(sqlString) donde sqlString es el SQL que se muestra en la respuesta de @ Rune.

Editar

Aquí hay algunas medidas:

He hecho esto en una tabla con 550000 registros y 11 columnas (los ID comienzan desde 1 sin espacios) y elegí 20000 ids al azar:

 using (var context = new MyDbContext()) { Random rand = new Random(); var ids = new List(); for (int i = 0; i < 20000; i++) ids.Add(rand.Next(550000)); Stopwatch watch = new Stopwatch(); watch.Start(); // here are the code snippets from below watch.Stop(); var msec = watch.ElapsedMilliseconds; } 

Prueba 1

 var result = context.Set() .Where(e => ids.Contains(e.ID)) .ToList(); 

Resultado -> mseg = 85.5 segundos

Prueba 2

 var result = context.Set().AsNoTracking() .Where(e => ids.Contains(e.ID)) .ToList(); 

Resultado -> mseg = 84.5 segundos

Este pequeño efecto de AsNoTracking es muy inusual. Indica que el cuello de botella no es materialización del objeto (y no SQL como se muestra a continuación).

Para ambas pruebas, se puede ver en SQL Profiler que la consulta SQL llega a la base de datos muy tarde. (No medí exactamente, pero fue más de 70 segundos.) Obviamente, la traducción de esta consulta LINQ a SQL es muy costosa.

Prueba 3

 var values = new StringBuilder(); values.AppendFormat("{0}", ids[0]); for (int i = 1; i < ids.Count; i++) values.AppendFormat(", {0}", ids[i]); var sql = string.Format( "SELECT * FROM [MyDb].[dbo].[MyEntities] WHERE [ID] IN ({0})", values); var result = context.Set().SqlQuery(sql).ToList(); 

Resultado -> mseg = 5.1 segundos

Prueba 4

 // same as Test 3 but this time including AsNoTracking var result = context.Set().SqlQuery(sql).AsNoTracking().ToList(); 

Resultado -> mseg = 3.8 seg

Esta vez, el efecto de deshabilitar el seguimiento es más notorio.

Prueba 5

 // same as Test 3 but this time using Database.SqlQuery var result = context.Database.SqlQuery(sql).ToList(); 

Resultado -> mseg = 3.7 seg

Mi comprensión es que context.Database.SqlQuery(sql) es lo mismo que context.Set().SqlQuery(sql).AsNoTracking() , por lo que no se esperan diferencias entre Test 4 y Test 5.

(La longitud de los conjuntos de resultados no siempre fue la misma debido a los posibles duplicados después de la selección de identificación aleatoria, pero siempre estuvo entre 19600 y 19640 elementos).

Editar 2

Prueba 6

Incluso 20000 recorridos de ida y vuelta a la base de datos son más rápidos que el uso de Contains :

 var result = new List(); foreach (var id in ids) result.Add(context.Set().SingleOrDefault(e => e.ID == id)); 

Resultado -> mseg = 73.6 segundos

Tenga en cuenta que he usado SingleOrDefault lugar de Find . Usar el mismo código con Find es muy lento (cancelé la prueba después de varios minutos) porque Find calls DetectChanges internamente. Al deshabilitar la detección automática de cambios ( context.Configuration.AutoDetectChangesEnabled = false ) se SingleOrDefault aproximadamente el mismo rendimiento que SingleOrDefault . El uso de AsNoTracking reduce el tiempo en uno o dos segundos.

Las pruebas se realizaron con el cliente de la base de datos (aplicación de la consola) y el servidor de la base de datos en la misma máquina. El último resultado puede empeorar significativamente con una base de datos "remota" debido a la gran cantidad de viajes de ida y vuelta.

La segunda opción es definitivamente mejor que la primera. La primera opción dará como resultado ids.Length consultas a la base de datos, mientras que la segunda opción puede utilizar un operador 'IN' en la consulta SQL. Básicamente, convertirá su consulta LINQ en algo así como el siguiente SQL:

 SELECT * FROM ImagesTable WHERE id IN (value1,value2,...) 

donde value1, value2, etc. son los valores de tu variable de ids. Tenga en cuenta, sin embargo, que creo que puede haber un límite superior en la cantidad de valores que se pueden serializar en una consulta de esta manera. Veré si puedo encontrar algo de documentación …

Estoy usando Entity Framework 6.1 y descubrí usando su código que es mejor usar:

 return db.PERSON.Find(id); 

más bien que:

 return db.PERSONA.FirstOrDefault(x => x.ID == id); 

El rendimiento de Find () frente a FirstOrDefault son algunas reflexiones sobre esto.

Weel, recientemente tuve un problema similar y la mejor manera que encontré fue insertar la lista de contiene en una tabla temporal y luego hacer una unión.

 private List GetFoos(IEnumerable ids) { var sb = new StringBuilder(); sb.Append("DECLARE @Temp TABLE (Id bitint PRIMARY KEY)\n"); foreach (var id in ids) { sb.Append("INSERT INTO @Temp VALUES ('"); sb.Append(id); sb.Append("')\n"); } sb.Append("SELECT f.* FROM [dbo].[Foo] f inner join @Temp t on f.Id = t.Id"); return this.context.Database.SqlQuery(sb.ToString()).ToList(); } 

No es una forma bonita, pero para listas grandes es muy eficiente.

La transformación de la lista en una matriz con toArray () aumenta el rendimiento. Puedes hacerlo de esta manera:

 ids.Select(id => Images.Find(id)); return Images.toArray().Where( im => ids.Contains(im.Id));