with problem orm select-n-plus-1

orm - with - ¿Cuál es el problema de la consulta N+1 SELECT?



hibernate problem n 1 (16)

Aquí hay una buena descripción del problema: http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-lazy

Ahora que comprende el problema, normalmente se puede evitar haciendo una búsqueda de unión en su consulta. Básicamente, esto fuerza la recuperación del objeto cargado perezoso para que los datos se recuperen en una consulta en lugar de n + 1 consultas. Espero que esto ayude.

SELECT N + 1 generalmente se declara como un problema en las discusiones de Mapeo Relacional de Objetos (ORM), y entiendo que tiene algo que ver con tener que hacer muchas consultas de base de datos para algo que parece simple en el mundo de objetos.

¿Alguien tiene una explicación más detallada del problema?

El enlace suministrado tiene un ejemplo muy simple del problema n + 1. Si lo aplicas a Hibernate, básicamente se trata de lo mismo. Cuando consulta un objeto, la entidad se carga, pero las asociaciones (a menos que se configure de otro modo) se cargarán de forma perezosa. Por lo tanto, una consulta para los objetos raíz y otra para cargar las asociaciones para cada uno de estos. 100 objetos devueltos significan una consulta inicial y luego 100 consultas adicionales para obtener la asociación para cada uno, n + 1.

http://pramatr.com/2009/02/05/sql-n-1-selects-explained/

El problema de la consulta N + 1 ocurre cuando se olvida de buscar una asociación y luego necesita acceder a ella:

List<PostComment> comments = entityManager.createQuery( "select pc " + "from PostComment pc " + "where pc.review = :review", PostComment.class) .setParameter("review", review) .getResultList(); LOGGER.info("Loaded {} comments", comments.size()); for(PostComment comment : comments) { LOGGER.info("The post title is ''{}''", comment.getPost().getTitle()); }

Que genera las siguientes sentencias de SQL:

SELECT pc.id AS id1_1_, pc.post_id AS post_id3_1_, pc.review AS review2_1_ FROM post_comment pc WHERE pc.review = ''Excellent!'' INFO - Loaded 3 comments SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_ FROM post pc WHERE pc.id = 1 INFO - The post title is ''Post nr. 1'' SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_ FROM post pc WHERE pc.id = 2 INFO - The post title is ''Post nr. 2'' SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_ FROM post pc WHERE pc.id = 3 INFO - The post title is ''Post nr. 3''

Primero, Hibernate ejecuta la consulta JPQL, y se obtiene una lista de entidades PostComment .

Luego, para cada PostComment , la propiedad de post asociada se usa para generar un mensaje de registro que contiene el título de la Post .

Debido a que la asociación de post no está inicializada, Hibernate debe obtener la entidad de Post con una consulta secundaria, y para N entidades de PostComment , se ejecutarán N más consultas (de ahí el problema de consultas de N + 1).

Primero, necesita un registro y supervisión de SQL adecuados para poder detectar este problema.

Segundo, este tipo de problema es mejor ser atrapado por las pruebas de integración. Puede utilizar una aserción automática de JUnit para validar el recuento esperado de sentencias de SQL generadas . El proyecto db-unit ya proporciona esta funcionalidad, y es de código abierto.

Cuando identificó el problema de la consulta de N + 1, debe usar UNIRAR FETCH para que las asociaciones secundarias se recuperen en una consulta, en lugar de N. Si necesita obtener varias asociaciones secundarias, es mejor obtener una colección en la consulta inicial y la segunda con una consulta SQL secundaria.

El problema que otros han expresado con mayor elegancia es que, o bien tiene un producto cartesiano de las columnas OneToMany o está haciendo N + 1 Selects. O posible conjunto de resultados gigantesco o hablador con la base de datos, respectivamente.

Me sorprende que esto no se mencione, pero así es como he solucionado este problema ... Hago una tabla de identificaciones semi-temporales . También hago esto cuando tienes la limitación de la cláusula IN () .

Esto no funciona para todos los casos (probablemente ni siquiera la mayoría), pero funciona particularmente bien si tiene muchos objetos secundarios de manera que el producto cartesiano se salga de control (es decir, en muchas columnas de OneToMany el número de resultados será una multiplicación de las columnas) y es más como un trabajo similar a un lote.

Primero inserte sus identificadores de objeto padre como lote en una tabla de identificadores. Este batch_id es algo que generamos en nuestra aplicación y mantenemos.

INSERT INTO temp_ids (product_id, batch_id) (SELECT p.product_id, ? FROM product p ORDER BY p.product_id LIMIT ? OFFSET ?);

Ahora, para cada columna OneToMany , simplemente haga un SELECT en la tabla de identificadores. UNE INNER JOIN a la tabla secundaria con un WHERE batch_id= (o viceversa). Solo desea asegurarse de ordenar por la columna de identificación, ya que facilitará la fusión de las columnas de resultados (de lo contrario, necesitará un HashMap / Table para todo el conjunto de resultados, que puede no ser tan malo).

Luego simplemente limpie periódicamente la tabla de ID.

Esto también funciona particularmente bien si el usuario selecciona, por ejemplo, 100 o más elementos distintos para algún tipo de procesamiento en masa. Ponga los 100 identificadores distintos en la tabla temporal.

Ahora el número de consultas que está haciendo es por el número de columnas OneToMany.

Es mucho más rápido emitir 1 consulta que devuelve 100 resultados que emitir 100 consultas y cada una devuelve 1 resultado.

No puedo comentar directamente sobre otras respuestas, porque no tengo suficiente reputación. Pero vale la pena señalar que el problema esencialmente solo surge porque, históricamente, muchos Dbms han sido bastante pobres cuando se trata de manejar uniones (MySQL es un ejemplo particularmente notable). Entonces n + 1 ha sido, a menudo, notablemente más rápido que una unión. Y luego hay formas de mejorar n + 1 pero aún sin necesidad de una unión, que es a lo que se refiere el problema original.

Sin embargo, MySQL ahora es mucho mejor de lo que solía ser cuando se trata de uniones. Cuando aprendí MySQL por primera vez, usé muchas combinaciones. Luego descubrí lo lentas que son y cambié a n + 1 en el código. Pero, recientemente, he estado volviendo a las uniones, porque MySQL ahora es mucho mejor para manejarlas que cuando empecé a usarlas.

En estos días, una combinación simple en un conjunto de tablas correctamente indexadas rara vez es un problema, en términos de rendimiento. Y si da un impacto en el rendimiento, entonces el uso de sugerencias de índice a menudo los resuelve.

Esto es discutido aquí por uno de los equipos de desarrollo de MySQL:

http://jorgenloland.blogspot.co.uk/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html

Así que el resumen es: si ha estado evitando uniones en el pasado debido al rendimiento abismal de MySQL con ellos, intente nuevamente con las últimas versiones. Probablemente te sorprenderá gratamente.

Nos alejamos del ORM en Django debido a este problema. Básicamente, si lo intentas y lo haces.

for p in person: print p.car.colour

El ORM felizmente devolverá a todas las personas (normalmente como instancias de un objeto de Persona), pero luego deberá consultar la tabla de autos para cada Persona.

Un enfoque simple y muy efectivo para esto es algo que yo llamo " despliegue en abanico ", que evita la idea absurda de que los resultados de la consulta de una base de datos relacional deben volver a las tablas originales a partir de las cuales se compone la consulta.

Paso 1: Amplia selección

select * from people_car_colour; # this is a view or sql function

Esto devolverá algo como

p.id | p.name | p.telno | car.id | car.type | car.colour -----+--------+---------+--------+----------+----------- 2 | jones | 2145 | 77 | ford | red 2 | jones | 2145 | 1012 | toyota | blue 16 | ashby | 124 | 99 | bmw | yellow

Paso 2: Objectificar

Aspire los resultados en un creador de objetos genéricos con un argumento para dividir después del tercer elemento. Esto significa que el objeto "jones" no se creará más de una vez.

Paso 3: Render

for p in people: print p.car.colour # no more car queries

Vea esta página web para una implementación de fanfolding para python.

Proveedor con una relación de uno a varios con el producto. Un proveedor tiene (suministros) muchos productos.

***** Table: Supplier ***** +-----+-------------------+ | ID | NAME | +-----+-------------------+ | 1 | Supplier Name 1 | | 2 | Supplier Name 2 | | 3 | Supplier Name 3 | | 4 | Supplier Name 4 | +-----+-------------------+ ***** Table: Product ***** +-----+-----------+--------------------+-------+------------+ | ID | NAME | DESCRIPTION | PRICE | SUPPLIERID | +-----+-----------+--------------------+-------+------------+ |1 | Product 1 | Name for Product 1 | 2.0 | 1 | |2 | Product 2 | Name for Product 2 | 22.0 | 1 | |3 | Product 3 | Name for Product 3 | 30.0 | 2 | |4 | Product 4 | Name for Product 4 | 7.0 | 3 | +-----+-----------+--------------------+-------+------------+

Factores:

  • Modo perezoso para el Proveedor establecido en "verdadero" (predeterminado)

  • El modo de captura utilizado para consultar en Producto es Seleccionar

  • Modo de captura (predeterminado): se accede a la información del proveedor

  • El almacenamiento en caché no juega un papel por primera vez el

  • Se accede al proveedor

El modo de captura es Seleccionar captura (predeterminado)

// It takes Select fetch mode as a default Query query = session.createQuery( "from Product p"); List list = query.list(); // Supplier is being accessed displayProductsListWithSupplierName(results); select ... various field names ... from PRODUCT select ... various field names ... from SUPPLIER where SUPPLIER.id=? select ... various field names ... from SUPPLIER where SUPPLIER.id=? select ... various field names ... from SUPPLIER where SUPPLIER.id=?

Resultado:

  • 1 declaración selecta para Producto
  • N seleccionar declaraciones para Proveedor

¡Esto es N + 1 problema de selección!

Supongamos que tienes EMPRESA y EMPLEADO. LA COMPAÑÍA tiene muchos EMPLEADOS (es decir, el EMPLEADO tiene un campo COMPANY_ID).

En algunas configuraciones de O / R, cuando tiene un objeto de la Compañía asignado y va a acceder a sus objetos de Empleado, la herramienta O / R hará una selección para cada empleado, aunque si estuviera haciendo las cosas directamente en SQL, podría select * from employees where company_id = XX . Así N (# de empleados) más 1 (empresa)

Así es como funcionaron las versiones iniciales de EJB Entity Beans. Creo que cosas como Hibernate han eliminado esto, pero no estoy muy seguro. La mayoría de las herramientas suelen incluir información sobre su estrategia de mapeo.

Tome el ejemplo de Matt Solnit, imagine que define una asociación entre Coche y Ruedas como LAZY y necesita algunos campos de Ruedas. Esto significa que después de la primera selección, hibernación hará "Seleccionar * de Ruedas donde car_id =: id" PARA CADA Automóvil.

Esto hace que la primera selección y más selección 1 por cada coche N, por eso se llama problema n + 1.

Para evitar esto, haga que la asociación se muestre como ansiosa, de modo que la hibernación cargue los datos con una unión.

Pero atención, si muchas veces no accedes a Wheels asociadas, es mejor mantenerlo en modo LAZY o cambiar el tipo de búsqueda con Criteria.

Un millonario tiene N autos. Quieres conseguir todas las (4) ruedas.

Una (1) consulta carga todos los autos, pero para cada (N) auto se envía una consulta separada para las ruedas de carga.

Costos:

Supongamos que los índices encajan en el ram.

Análisis y planificación de consultas 1 + N + búsqueda de índices Y acceso a la placa 1 + N + (N * 4) para cargar la carga útil.

Supongamos que los índices no encajan en el ram.

Costos adicionales en el peor de los casos 1 + N placa de acceso para índice de carga.

Resumen

El cuello de la botella es el acceso a la placa (aproximadamente 70 veces por segundo, acceso aleatorio en el disco duro) Una selección de combinación ansiosa también tendría acceso a la placa 1 + N + (N * 4) veces para la carga útil. Entonces, si los índices encajan en el ram, no hay problema, es lo suficientemente rápido porque solo involucraron las operaciones del ram.

Un problema de selección de N + 1 es una molestia, y tiene sentido detectar estos casos en pruebas unitarias. He desarrollado una pequeña biblioteca para verificar el número de consultas ejecutadas por un método de prueba determinado o simplemente por un bloque de código arbitrario - JDBC Sniffer

Simplemente agregue una regla especial de JUnit a su clase de prueba y coloque la anotación con el número esperado de consultas en sus métodos de prueba:

@Rule public final QueryCounter queryCounter = new QueryCounter(); @Expectation(atMost = 3) @Test public void testInvokingDatabase() { // your JDBC or JPA code }

Verifique la publicación de Ayende sobre el tema: Combatir el problema Select N + 1 en NHibernate

Básicamente, cuando usa un ORM como NHibernate o EntityFramework, si tiene una relación de uno a muchos (maestro-detalle) y desea enumerar todos los detalles de cada registro maestro, tiene que hacer N + 1 llamadas de consulta al base de datos, siendo "N" el número de registros maestros: 1 consulta para obtener todos los registros maestros, y N consultas, una por registro maestro, para obtener todos los detalles por registro maestro.

Más llamadas de consulta a la base de datos -> más tiempo de latencia -> menor rendimiento de la aplicación / base de datos.

Sin embargo, los ORM tienen opciones para evitar este problema, principalmente utilizando "uniones".

Digamos que tiene una colección de objetos Car (filas de la base de datos), y cada Car tiene una colección de objetos Wheel (también filas). En otras palabras, Car -> Wheel es una relación de 1 a muchos.

Ahora, digamos que necesita recorrer todos los autos y, para cada uno, imprimir una lista de las ruedas. La implementación ingenua de O / R haría lo siguiente:

SELECT * FROM Cars;

Y luego para cada Car :

SELECT * FROM Wheel WHERE CarId = ?

En otras palabras, tiene una selección para los Autos, y luego N selecciona más, donde N es el número total de autos.

Alternativamente, uno podría obtener todas las ruedas y realizar las búsquedas en la memoria:

SELECT * FROM Wheel

Esto reduce la cantidad de viajes de ida y vuelta a la base de datos de N + 1 a 2. La mayoría de las herramientas ORM le brindan varias maneras de evitar que se seleccione N + 1.

Referencia: Persistencia de Java con Hibernate , capítulo 13.

SELECT table1.* , table2.* INNER JOIN table2 ON table2.SomeFkId = table1.SomeId

Eso le da un conjunto de resultados donde las filas secundarias en la tabla 2 causan duplicación al devolver los resultados de la tabla1 para cada fila secundaria en la tabla2. Los asignadores de O / R deben diferenciar las instancias de table1 en función de un campo de clave único, luego usar todas las columnas de table2 para completar instancias secundarias.

SELECT table1.* SELECT table2.* WHERE SomeFkId = #

La N + 1 es donde la primera consulta rellena el objeto primario y la segunda consulta rellena todos los objetos secundarios para cada uno de los objetos primarios únicos devueltos.

Considerar:

class House { int Id { get; set; } string Address { get; set; } Person[] Inhabitants { get; set; } } class Person { string Name { get; set; } int HouseId { get; set; } }

Y mesas con una estructura similar. Una sola consulta para la dirección "22 Valley St" puede devolver:

Id Address Name HouseId 1 22 Valley St Dave 1 1 22 Valley St John 1 1 22 Valley St Mike 1

La O / RM debe llenar una instancia de Inicio con ID = 1, Dirección = "22 Valley St" y luego llenar la matriz de Habitantes con instancias de Personas para Dave, John y Mike con solo una consulta.

Una consulta N + 1 para la misma dirección utilizada anteriormente resultaría en:

Id Address 1 22 Valley St

con una consulta separada como

SELECT * FROM Person WHERE HouseId = 1

y resultando en un conjunto de datos separado como

Name HouseId Dave 1 John 1 Mike 1

y el resultado final es el mismo que el anterior con la única consulta.

Las ventajas de la selección única es que obtiene todos los datos por adelantado, lo que puede ser lo que finalmente desea. Las ventajas de N + 1 es que la complejidad de las consultas se reduce y puede usar la carga diferida en la que los conjuntos de resultados secundarios solo se cargan cuando se solicita por primera vez.