orm n+1 - Quel est le problème de requête N + 1 SELECT?





hibernate problem (15)


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

Cela vous obtient un jeu de résultats où les lignes enfant dans table2 provoquent la duplication en retournant les résultats table1 pour chaque ligne enfant dans table2. Les mappeurs O / R doivent différencier les instances table1 en fonction d'un champ clé unique, puis utiliser toutes les colonnes table2 pour remplir les instances enfants.

SELECT table1.*

SELECT table2.* WHERE SomeFkId = #

Le N + 1 est où la première requête remplit l'objet principal et la deuxième requête remplit tous les objets enfants pour chacun des objets principaux uniques renvoyés.

Considérer:

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

class Person
{
    string Name { get; set; }
    int HouseId { get; set; }
}

et des tables avec une structure similaire. Une seule requête pour l'adresse "22 Valley St" peut renvoyer:

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

L'O / RM doit remplir une instance de Home avec ID = 1, Address = "22 Valley St", puis remplir le tableau Habitants avec des instances People pour Dave, John et Mike avec une seule requête.

Une requête N + 1 pour la même adresse utilisée ci-dessus entraînerait:

Id Address
1  22 Valley St

avec une requête séparée comme

SELECT * FROM Person WHERE HouseId = 1

et résultant en un ensemble de données distinct comme

Name    HouseId
Dave    1
John    1
Mike    1

et le résultat final étant le même que ci-dessus avec la requête unique.

Les avantages de sélectionner seul est que vous obtenez toutes les données à l'avance, ce qui peut être ce que vous désirez. Les avantages de N + 1 sont la complexité de la requête est réduite et vous pouvez utiliser le chargement paresseux où les ensembles de résultats enfant ne sont chargés qu'à la première demande.

SELECT N + 1 est généralement indiqué comme un problème dans les discussions ORM (Object-Relational Mapping), et je comprends qu'il a quelque chose à faire avec avoir à faire beaucoup de requêtes de base de données pour quelque chose qui semble simple dans le monde objet.

Quelqu'un a-t-il une explication plus détaillée du problème?




Le lien fourni a un exemple très simple du problème n + 1. Si vous l'appliquez à Hibernate, cela revient à parler de la même chose. Lorsque vous interrogez un objet, l'entité est chargée, mais toutes les associations (sauf configuration contraire) seront chargées paresseusement. D'où une requête pour les objets racine et une autre requête pour charger les associations pour chacun d'eux. 100 objets retournés signifie une requête initiale et ensuite 100 requêtes supplémentaires pour obtenir l'association pour chacun, n + 1.

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




À mon avis, l'article écrit dans http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-lazy est exactement le contraire de la vraie question N + 1 est.

Si vous avez besoin d'une explication correcte, veuillez vous reporter à Hibernate - Chapitre 19: Amélioration des performances - Récupération des stratégies

Sélectionner l'extraction (la valeur par défaut) est extrêmement vulnérable à N + 1 sélectionne les problèmes, nous pouvons donc vouloir activer la récupération des jointures




Le problème, comme d'autres l'ont déclaré plus élégamment, est que vous avez un produit cartésien des colonnes OneToMany ou que vous faites N + 1 Selects. Soit un jeu de résultats gigantesque possible ou bavard avec la base de données, respectivement.

Je suis surpris que ce ne soit pas mentionné mais c'est comme ça que j'ai contourné ce problème ... Je fais une table d'identifiants semi-temporaire . Je fais aussi cela quand vous avez la limitation de la clause IN () .

Cela ne fonctionne pas dans tous les cas (probablement pas même une majorité) mais cela fonctionne particulièrement bien si vous avez beaucoup d'objets enfants tels que le produit cartésien deviendra OneToMany (ie beaucoup de colonnes OneToMany le nombre de résultats sera une multiplication des colonnes) et son plus d'un travail par lots.

D'abord, vous insérez vos ID d'objet parent en tant que lot dans une table d'IDs. Ce batch_id est quelque chose que nous générons dans notre application et maintenons.

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

Maintenant, pour chaque colonne OneToMany , il suffit de faire un SELECT sur la table d'identificateurs INNER JOIN la table enfant avec un WHERE batch_id= (ou vice versa). Vous voulez juste vous assurer que vous commandez par la colonne id car cela facilitera la fusion des colonnes de résultat (sinon vous aurez besoin d'un HashMap / Table pour l'ensemble des résultats qui n'est peut-être pas si mauvais).

Ensuite, vous nettoyez régulièrement la table d'identifiants.

Cela fonctionne aussi particulièrement bien si l'utilisateur sélectionne disons 100 ou des éléments distincts pour une sorte de traitement en bloc. Mettez les 100 identifiants distincts dans la table temporaire.

Maintenant, le nombre de requêtes que vous faites est par le nombre de colonnes OneToMany.




Supposons que vous avez COMPANY et EMPLOYEE. L'ENTREPRISE compte de nombreux EMPLOYÉS (c'est-à-dire que l'EMPLOYÉ a un champ COMPANY_ID).

Dans certaines configurations O / R, lorsque vous avez un objet Company mappé et que vous accédez à ses objets Employee, l'outil O / R effectue une sélection pour chaque employé, alors que si vous étiez en train de faire des opérations SQL, vous pouvez select * from employees where company_id = XX . Donc N (nombre d'employés) plus 1 (compagnie)

C'est ainsi que fonctionnaient les versions initiales d'EJB Entity Beans. Je crois que des choses comme Hibernate ont fait disparaître cela, mais je ne suis pas trop sûr. La plupart des outils incluent généralement des informations sur leur stratégie de cartographie.




Un millionnaire a N voitures. Vous voulez obtenir toutes les (4) roues.

Une (1) requête charge toutes les voitures, mais pour chaque voiture (N), une requête distincte est soumise pour les roues de chargement.

Frais:

Supposons que les index rentrent dans le RAM.

1 + N interrogation analyse et rabotage + recherche d'index ET accès à la plaque 1 + N + (N * 4) pour charger la charge utile.

Supposons que les index ne rentrent pas dans le RAM.

Coûts supplémentaires dans le pire des cas accès à la plaque 1 + N pour l'indice de charge.

Résumé

Le col de la bouteille est accès à la plaque (environ 70 fois par seconde accès aléatoire sur le disque dur) Un joint désireux sélectionnerait également accéder à la plaque 1 + N + (N * 4) fois pour la charge utile. Donc, si les index s'inscrivent dans RAM - pas de problème, c'est assez rapide car seules les opérations de RAM impliqués.




Voici une bonne description du problème - http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-lazy

Maintenant que vous comprenez le problème, vous pouvez généralement l'éviter en effectuant une recherche de jointure dans votre requête. Cela force fondamentalement l'extraction de l'objet chargé paresseux afin que les données soient récupérées dans une requête au lieu de n + 1 requêtes. J'espère que cela t'aides.




Fournisseur avec une relation un-à-plusieurs avec le produit. Un fournisseur a (fournit) de nombreux produits.

***** 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      |
+-----+-----------+--------------------+-------+------------+

Facteurs:

  • Mode paresseux pour le fournisseur défini sur "true" (par défaut)

  • Le mode d'extraction utilisé pour l'interrogation sur le produit est sélectionné

  • Mode de récupération (par défaut): les informations sur le fournisseur sont accessibles

  • La mise en cache ne joue pas de rôle pour la première fois

  • Le fournisseur est consulté

Le mode Fetch est Sélectionner Fetch (par défaut)

// 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=?

Résultat:

  • 1 instruction select pour le produit
  • N sélectionner les déclarations pour le fournisseur

C'est N + 1 problème de sélection!




Consultez le post d'Ayende sur le sujet: Combattre le problème Select N + 1 dans NHibernate

Fondamentalement, lorsque vous utilisez un ORM comme NHibernate ou EntityFramework, si vous avez une relation un-à-plusieurs (maître-détail) et que vous voulez répertorier tous les détails pour chaque enregistrement, vous devez effectuer N + 1 appels de requête au base de données, "N" étant le nombre de fiches principales: 1 requête pour obtenir toutes les fiches et N requêtes, une par fiche, pour obtenir tous les détails par fiche.

Plus d'appels de requête de base de données -> plus de temps de latence -> diminution des performances de l'application / de la base de données.

Cependant, les ORM ont des options pour éviter ce problème, en utilisant principalement des "jointures".




Le problème de requête N + 1 se produit lorsque vous oubliez d'extraire une association et que vous devez y accéder:

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());
}

Qui génère les instructions SQL suivantes:

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'

Premièrement, Hibernate exécute la requête JPQL et une liste d'entités PostComment est récupérée.

Ensuite, pour chaque PostComment , la propriété de post associée est utilisée pour générer un message de journal contenant le titre de Post .

Comme l'association de post n'est pas initialisée, Hibernate doit extraire l'entité Post avec une requête secondaire, et pour N entités PostComment , N requêtes supplémentaires vont être exécutées (d'où le problème de requête N + 1).

Tout d'abord, vous avez besoin d' une journalisation et d'une surveillance SQL appropriées pour pouvoir détecter ce problème.

Deuxièmement, ce type de problème est préférable d'être attrapé par des tests d'intégration. Vous pouvez utiliser un assertion JUnit automatique pour valider le nombre attendu d'instructions SQL générées . Le projet db-unit fournit déjà cette fonctionnalité, et il est open source.

Lorsque vous avez identifié le problème de requête N + 1, vous devez utiliser un JOIN FETCH pour que les associations enfants soient récupérées dans une requête au lieu de N. Si vous avez besoin d'extraire plusieurs associations enfants, il est préférable de récupérer une collection dans la requête initiale et la seconde avec une requête SQL secondaire.




Nous nous sommes éloignés de l'ORM de Django à cause de ce problème. Fondamentalement, si vous essayez et faites

for p in person:
    print p.car.colour

L'ORM retournera volontiers toutes les personnes (généralement en tant qu'objets d'un objet Person), mais il devra ensuite interroger la table de voiture pour chaque personne.

Une approche simple et très efficace est ce que j'appelle « fanfolding », ce qui évite l'idée absurde que les résultats de la requête d'une base de données relationnelle doivent correspondre aux tables d'origine à partir desquelles la requête est composée.

Étape 1: sélection large

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

Cela retournera quelque chose comme

  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

Étape 2: Objectify

Suce les résultats dans un créateur d'objet générique avec un argument à diviser après le troisième élément. Cela signifie que l'objet "jones" ne sera pas créé plus d'une fois.

Étape 3: Rendu

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

Voir cette page web pour une implémentation de fanfolding pour python.




Il est beaucoup plus rapide d'émettre une requête qui renvoie 100 résultats que d'émettre 100 requêtes qui retournent chacune 1 résultat.




Le problème de sélection N + 1 est une douleur, et il est logique de détecter de tels cas dans les tests unitaires. J'ai développé une petite bibliothèque pour vérifier le nombre de requêtes exécutées par une méthode de test donnée ou juste un bloc arbitraire de code - JDBC Sniffer

Ajoutez simplement une règle JUnit spéciale à votre classe de test et placez l'annotation avec le nombre attendu de requêtes sur vos méthodes de test:

@Rule
public final QueryCounter queryCounter = new QueryCounter();

@Expectation(atMost = 3)
@Test
public void testInvokingDatabase() {
    // your JDBC or JPA code
}



Prenons l'exemple de Matt Solnit, imaginez que vous définissez une association entre Car and Wheels comme LAZY et que vous avez besoin de champs Wheels. Cela signifie qu'après la première sélection, hibernate va faire "Select * from Wheels" où car_id =: id "POUR CHAQUE voiture.

Cela rend le premier choix et plus de 1 sélectionner par chaque voiture N, c'est pourquoi il est appelé problème n + 1.

Pour éviter cela, faites en sorte que l'association soit aussi rapide, afin qu'hibernate charge les données avec une jointure.

Mais attention, si plusieurs fois vous n'accédez pas aux Roues associées, il est préférable de le garder paresseux ou de changer le type de recherche avec les Critères.




Personnellement, je n'ai pas eu une grande expérience avec la technologie ORM à ce jour. Je travaille actuellement pour une entreprise qui utilise nHibernate et je ne peux vraiment pas le faire. Donnez-moi un proc stocké et DAL n'importe quel jour! Plus de code sûr ... mais aussi plus de contrôle et de code qui est plus facile à déboguer - d'après mon expérience en utilisant une ancienne version de nHibernate, il faut l'ajouter.





orm select-n-plus-1