Bonjour à tous,
un client subit un ralentissement sur une version 5.0.1 dans le réassort en stock théorique.
Il a évidemment un grand nombre de commandes client et fournisseurs et de nombreuses lignes de commandes (plus de 10 000 dans chaque table).
La requête tracée dans mysql mets plus d’une minutes à l’exécution (vu dans mysql.slow_log)…
Savez vous si ce process a été optimisé dans les dernières versions ? Voyez vous une solution pour l’aider ?
Merci de vos idées…
Pour info voici la requête détaillée :
SELECT p.rowid, p.ref, p.label, p.description, p.price, p.price_ttc, p.price_base_type,p.fk_product_type, p.tms as datem, p.duration, p.tobuy ,p.desiredstock,p.seuil_stock_alerte ,p.desiredstock as desiredstock, p.seuil_stock_alerte as alertstock, SUM(IF(s.reel IS NULL,0,s.reel)) as stock_physique
FROM llx_product as p
LEFT JOIN llx_product_stock as s ON p.rowid = s.fk_product
WHERE p.entity IN (1) AND p.tobuy = 1
GROUP BY p.rowid, p.ref, p.label, p.description, p.price, p.price_ttc, p.price_base_type,p.fk_product_type, p.tms, p.duration, p.tobuy, p.desiredstock, p.seuil_stock_alerte, s.fk_product
HAVING (((IF(p.desiredstock IS NULL,0,p.desiredstock) > SUM(IF(s.reel IS NULL,0,s.reel))
((SELECT IF(SUM(cd.qty) IS NULL,0,SUM(cd.qty)) as qty
FROM llx_commandedet as cd
LEFT JOIN llx_commande as c ON (c.rowid = cd.fk_commande)
WHERE c.entity IN (1) AND cd.fk_product = p.rowid AND c.fk_statut IN (1,2))
-
(SELECT IF(SUM(ed.qty) IS NULL,0,SUM(ed.qty)) as qty
FROM llx_expedition as e
LEFT JOIN llx_expeditiondet as ed ON (ed.fk_expedition = e.rowid)
LEFT JOIN llx_commandedet as cd ON (cd.rowid = ed.fk_origin_line)
LEFT JOIN llx_commande as c ON (c.rowid = cd.fk_commande) WHERE e.entity IN (1) AND cd.fk_product = p.rowid AND c.fk_statut IN (1,2)))
+
((SELECT IF(SUM(cd.qty) IS NULL,0,SUM(cd.qty)) as qty
FROM llx_commande_fournisseurdet as cd, llx_commande_fournisseur as c
WHERE c.rowid = cd.fk_commande AND c.entity IN (1) AND cd.fk_product = p.rowid AND c.fk_statut IN (3,4))
-
(SELECT IF(SUM(fd.qty) IS NULL,0,SUM(fd.qty)) as qty
FROM llx_commande_fournisseur as cf
LEFT JOIN llx_commande_fournisseur_dispatch as fd ON (fd.fk_commande = cf.rowid)
WHERE cf.entity IN (1) AND fd.fk_product = p.rowid AND cf.fk_statut IN (3,4))))) OR (p.seuil_stock_alerte >= 0 AND (p.seuil_stock_alerte > SUM(IF(s.reel IS NULL,0,s.reel))
-
((SELECT IF(SUM(cd.qty) IS NULL,0,SUM(cd.qty)) as qty
FROM llx_commandedet as cd
LEFT JOIN llx_commande as c ON (c.rowid = cd.fk_commande)
WHERE c.entity IN (1) AND cd.fk_product = p.rowid AND c.fk_statut IN (1,2))
-
(SELECT IF(SUM(ed.qty) IS NULL,0,SUM(ed.qty)) as qty
FROM llx_expedition as e
LEFT JOIN llx_expeditiondet as ed ON (ed.fk_expedition = e.rowid)
LEFT JOIN llx_commandedet as cd ON (cd.rowid = ed.fk_origin_line)
LEFT JOIN llx_commande as c ON (c.rowid = cd.fk_commande)
WHERE e.entity IN (1) AND cd.fk_product = p.rowid AND c.fk_statut IN (1,2)))
+
((SELECT IF(SUM(cd.qty) IS NULL,0,SUM(cd.qty)) as qty
FROM llx_commande_fournisseurdet as cd, llx_commande_fournisseur as c
WHERE c.rowid = cd.fk_commande AND c.entity IN (1) AND cd.fk_product = p.rowid AND c.fk_statut IN (3,4))
-
(SELECT IF(SUM(fd.qty) IS NULL,0,SUM(fd.qty)) as qty
FROM llx_commande_fournisseur as cf
LEFT JOIN llx_commande_fournisseur_dispatch as fd ON (fd.fk_commande = cf.rowid)
WHERE cf.entity IN (1) AND fd.fk_product = p.rowid AND cf.fk_statut IN (3,4))))))
ORDER BY p.ref ASC LIMIT 151
