[2.7] Optimiser la performance pour la pagination
Bonjour à tous et à toutes.
Je viens sur le forum demander un peu d'aide car j'ai un gros problème d'optimisation sur un systeme de pagination.
Pour le contexte (ça va etre un peu long, accrochez vous !) : Je travaille sur une application qui doit gérer beaucoup de produits. Pour leur faciliter la tache, les administrateurs souhaitent avoir à disposition un tableau de données qui affiche quasiment toutes les informations des produits sur un grand nombre de produits (Jusqu'a 1000 par page).
Dans ma base de donnée, j'ai pour l'instant environ 100 000 produits, chacun possédants plusieurs images, catégories, propriétaires, etcs ... Au final, sans les GROUP BY ça fait quelque chose comme 200 000 lignes.
Donc pour paginer ce tableau, j'utilise le bundle 'knp_paginator' qui de manière générale est assez efficace, sauf dans mon cas précis.
Mon problème c'est que lorsque je tente d'effectuer un ORDER BY sur ma requête de récupération, avec le DISTINCT que rajoute le knp_paginator, ma requête s'emballe et met prés de 8mn à s'exécuter. Pour pallier à se problème j'ai activer l'option 'wrap-queries' du knp_paginator, ça a pour effet d'encapsuler la requête et le temps de chargement passe à environ 2x6sc (car le knp paginator effectue deux sous requêtes). Malheureusement, c'est encore trop long pour mes utilisateur et j'aimerais encore l'optimiser un peu. Qui-plus-est la sous-requête consomme énormément de mémoire.
Voilà la requête en question :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT DISTINCT id1, title7
FROM
(
SELECT SUM(c0_.stock) AS sclr0, c1_.id AS id1, c1_.SKU AS SKU2, c1_.EAN AS EAN3, c1_.ISBN AS ISBN4, c1_.brand AS brand5, c1_.weight AS weight6, c1_.title AS title7, c1_.created AS created8, c1_.quality_level AS quality_level9, c2_.id AS id10, c2_.webPath AS webPath11, c3_.id AS id12, c3_.code AS code13, c4_.id AS id14, c5_.id AS id15, c5_.name AS name16, c6_.id AS id17, c6_.name AS name18
FROM catalog_products c1_
LEFT JOIN catalog_products_pictures c2_ ON c1_.id = c2_.product_id
LEFT JOIN catalog_status c3_ ON c1_.status_id = c3_.id
LEFT JOIN catalog_stores_products_owner c4_ ON c1_.id = c4_.product_id
LEFT JOIN catalog_store c5_ ON c4_.store_id = c5_.id
LEFT JOIN catalog_products_categories c7_ ON c1_.id = c7_.products_id
LEFT JOIN catalog_categories c6_ ON c6_.id = c7_.categories_id
LEFT JOIN catalog_stores_products_owner c0_ ON c1_.id = c0_.product_id AND (c0_.isSupplied = 1)
GROUP BY c1_.id, c6_.id, c4_.id ORDER BY c1_.title ASC, c4_.price asc
)
dctrn_result ORDER BY title7 ASC LIMIT 100 OFFSET 0 |
Je suis bien conscient que la requête est un peu gourmande pour rien et récupère des informations dont elle ne se sert pas, mais malheureusement le knp_paginator (a ma connaissance) ne prend en parametre qu'une seule requête, celle finale avec tout les champs et c'est lui qui générer la requête ci-dessus. je ne peux donc rien enlever au risque de le perdre dans ma requête finale.
Du coup j'ai plusieurs question :
- Existe t'il un moyen de faire cohabiter le distinct et le group by sans passer par wrap query ?
- Puis-je désactiver le distinct et faire mon propre group by ? Il existe bien un option mais ça ne supprime que le distinct de la seconde requête
- Comment pourrais-je améliorer la sous-requête pour qu'elle soit plus rapide et consomme moins de mémoire sans avoir à perdre des informations sur la seconde requête
Voilà, tout ça est un peu compliqué et j'espère avoir été clair.
Merci d'avance :)
PS : Je sais que c'est quasiment un problème de SQL et non de Symfony, mais les contrainte des bundle font que je poste mon sujet ici. Sans celles-ci je pense que j'aurais pu arriver à une réponse assez rapidement en pur SQL.
