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 :

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);
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
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 : Après quelques tests, ça roule plutôt bien en pdo, mais sans les sous-requêtes, je test avec maintenant

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...