Bonjour à tous et à toutes !
Je suis en train de migrer mon app zend framework 1.9 en symfony 3.
J'ai des requêtes assez 'lourdes' qui contiennent des sous-requêtes (calcul d'un total par lignes), qui sont très rapides via zf (0.2s) mais qui prennent un temps fou via sf3 (3.9s)
J'ai testé via le query builder, via create query et même en direct via pdo mais les résultats sont toujours pas top :/
Vous avez des idées ou astuces ? C'est vraiment dès qu'il y a une (ou +) sous requête(s)
Mes codes :
(Je simplifie un peu)
Via createQuery :
Via pdo :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 $sql = $em->createQuery(' SELECT q, (SELECT COUNT(f.id) FROM AdminBundle:File f WHERE f.type=\'D\' AND f.linked_id=q.id) AS countFileQuotation, (SELECT COUNT(f2.id) FROM AdminBundle:File f2 WHERE f2.type=\'M\' AND f2.linked_id=q.id) AS countMandateQuotation, (SELECT COUNT(f3.id) FROM AdminBundle:File f3 WHERE f3.type=\'DES\' AND f3.linked_id=p.id) AS countSoilStudyQuotation, (SELECT COUNT(f4.id) FROM AdminBundle:File f4 WHERE f4.type=\'DEASS\' AND f4.linked_id=p.id) AS countSanitationStudyQuotation FROM AdminBundle:Quotation AS q INNER JOIN AdminBundle:Model AS m WITH m.id=q.model INNER JOIN AdminBundle:Project AS p WITH p.id=q.project INNER JOIN AdminBundle:Customer AS c WITH c.id=p.customer INNER JOIN AdminBundle:HousePlace AS hp WITH hp.id=q.housePlace INNER JOIN AdminBundle:House AS h WITH h.id=hp.house INNER JOIN AdminBundle:User AS u WITH u.id=c.user INNER JOIN AdminBundle:Place AS pl WITH pl.id=hp.place WHERE q.state > 3 AND p.isFinished = 1 AND q.addendum = 0 AND c.prospect = 0 AND p.projectStep = :projectStep ') ->setParameter(':projectStep', $projectStep);
Edit : Après quelques tests, ça roule plutôt bien en pdo, mais sans les sous-requêtes, je test avec maintenant
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 $sql = 'SELECT c.address, c.postalCode, c.city, c.email1, c.email2, q.id AS quotationId, c.id AS customerId, c.name1 AS customerName1, c.surname1 AS customerSurname1, c.name2 AS customerName2, c.surname2 AS customerSurname2, m.name AS modelName, h.name AS houseName, pl.name AS housePlace, p.id AS projectId, p.soilStudy, p.sanitationStudy, p.damageWork, c.phone1, (SELECT COUNT(f.id) FROM file f WHERE f.type=\'D\' AND f.linked_id=q.id) AS countFileQuotation, (SELECT COUNT(f2.id) FROM file f2 WHERE f2.type=\'M\' AND f2.linked_id=q.id) AS countMandateQuotation, (SELECT COUNT(f3.id) FROM file f3 WHERE f3.type=\'DES\' AND f3.linked_id=p.id) AS countSoilStudyQuotation, (SELECT COUNT(f4.id) FROM file f4 WHERE f4.type=\'DEASS\' AND f4.linked_id=p.id) AS countSanitationStudyQuotation FROM quotation AS q INNER JOIN model AS m ON m.id=q.model_id INNER JOIN project AS p ON p.id=q.project_id INNER JOIN customer AS c ON c.id=p.customer_id INNER JOIN housePlace AS hp ON hp.id=q.housePlace_id INNER JOIN house AS h ON h.id=hp.house_id INNER JOIN user AS u ON u.id=c.user_id INNER JOIN place AS pl ON pl.id=hp.place_id WHERE q.state > 3 AND p.isFinished = 1 AND q.addendum = 0 AND c.prospect = 0 AND p.projectStep = :projectStep;' $stmt = $em->getConnection()->prepare($sql); $stmt->bindValue(':projectStep', $projectStep);
Edit 2: C'est bien les sous-requêtes qui plombe la durée, du coup quand je loop mes lignes je fais une requête count par ligne (sic), c'est quand même bien plus rapide même si pas très propre...
Partager