Une SQL complexe = 1000 fois plus rapide que SQL Dolibarr standard : Optimisation!

dum77 · Mai 6, 2025, 4:46

Voici au passage une petite expérience au sujet de l’optimisation SQL :

Je voulais faire le bilan annuel de la TVA collectée pour chaque taux de TVA. Il y avait des erreurs au départ dans les produits dans le taux de TVA, il fallait donc aller vérifier si le taux a été modifié pour prendre en compte le taux modifié.

C’est un peu complexe parce qu’il faut prendre en compte la collecte réelle sur la période, donc les dates de paiements (parfois en plusieurs fois) mais sur les factures de la période, ou pas, et aussi la TVA sur les factures de la période (qu’elle soit collectée ou non), la TVA collectée pendant une période sur les factures de cette période…

Tout cela pour dire que j’ai travaillé un moment pour concevoir une requête SQL unique qui permet de calculer pour 4000 commandes de 15 lignes chacune soit 60 000 lignes de commandes environ.

Ce n’est qu’après que j’ai découvert que Dolibarr proposait à peu près la même fonction… mais avec un codage qui faisait deux ou trois requêtes par lignes de commandes, soit à plus de 120 000 requêtes entrecoupées d’aller/retour PHP.

Résultat des courses :

Outil Dolibarr standard : 15 minutes de calcul si on a la chance que cela ne plante pas !
Outil maison : 0,5 s et sans planter !

Je n’ai pas eu le temps de mettre cela sur Git, mais si cela intéresse quelqu’un, je peux partager la recette.

J’ai fait aussi d’autres requêtes SQL un peu complexe qui pourraient avantageusement remplacer des boucles sur des requêtes simples. Le temps de calcul est divisé par 10,100, ou 10 000 ! Je préfère que le maximum soit fait en SQL. Les A/R SQL/PHP m’ennuient… Et c’est toujours une énigme assez intéressante à résoudre !

erics · Mai 6, 2025, 5:20

Bonsoir,
publiez, partagez, c’est toujours intéressant !

par contre quand vous parlez de « commandes » ça ne serait pas plutôt de « factures » ?

ce qui faut voir c’est que le middleware php dolibarr est générique, ça ne sera jamais aussi efficace qu’une requête SQL taillée sur mesure pour remplir un objectif ultra spécifique.

publiez votre requête ça aidera probablement plus d’un utilisateur de dolibarr surtout dans la période critique d’établissement des bilans où il faut aller chercher des petites bêtes et croiser les informations pour trouver ce qui coince !

Éric

dum77 · Mai 7, 2025, 8:19

Oui bien sûr… comme je passe une partie de mon temps libre à adapter Dolibarr pour une coopérative, je n’ai pas toujours le temps de publier.

Sur la V16, la TVA d’une année me prenait 15 minutes et en général cela plantait.

Avec cette requête cela se fait en une fraction de seconde. Une seule requête (les multi-requêtes ne sont pas implémentés par $db, question de sécurité), mais un peu longue. Je l’ai testé depuis un an : pas de remarques de la comptable.

La logique :

Reprendre la TVA à jour dans le produit s’il existe : cela permet de corriger après coup des erreurs. Cela arrive souvent, surtout dans notre contexte avec des bénévoles. Sinon, utiliser la TVA telle que renseignée dans la table facture détail.
Faire les arrondis nécessaires
Rq : j’ai remplacé le « . » séparateur décimal par « , ». Il faut enlever le replace si on veut conserver le « . » comme séparateur pour une compatibilité générale.
On fait les sommes TVA 5,5%; 20%, 10%, autre TVA (cela arrive), TTC, HT, TVA tot.
Condition : sur un mois d’une année, ou sur une année : modifier clause WHERE
La clause WHERE se fait sur datep : la date effective du paiement. Si le paiement est en plusieurs étapes, on ne prend que ce qui a été payé.

Ici avec préfixe en dur : testable sur phpMyAdmin.

A adapter avec le préfixe bdd pour une page Dolibarr (remplacer llx_ par ‹ .MAIN_DB_PREFIX. ›
A adapter pour la période choisie (clause WHERE)
A supprimer replace (xxxxxx,‹ . ›,‹ , ›) pour intégrer dans une version générale en conservant le séparateur « . »

A part le replace « . »,« , », cela fonctionne dans les cas génériques pour la France. A adapter pour prendre en considération différents taux de TVA, mais la logique de la requête SQL unique reste la même.

$sql='
			select
			replace(round(sum(detail.total_ttc),2),".",",") as total_ttc,
			replace(round(sum(detail.total_ht),2),".",",") as total_ht,
			replace(round(sum(detail.tva),2),".",",") tva_tot,
			replace(round(sum(case when detail.tva_tx=5.5 then detail.total_ht else 0 END),2),".",",") as ht_55,
			replace(round(sum(case when detail.tva_tx=20 then detail.total_ht else 0 END),2),".",",") as ht_20,
			replace(round(sum(case when detail.tva_tx=0 then detail.total_ht else 0 END),2),".",",") as ht_0,
			replace(round(sum(case when detail.tva_tx<>0 AND detail.tva_tx<>5.5 AND detail.tva_tx<>20 then detail.total_ht else 0 END),2),".",",") as ht_autre,
			replace(round(sum(case when detail.tva_tx=5.5 then detail.tva else 0 END),2),".",",") as tva_55,
			replace(round(sum(case when detail.tva_tx=20 then detail.tva else 0 END),2),".",",") as tva_20,
			replace(round(sum(case when detail.tva_tx=0 then detail.tva else 0 END),2),".",",") as tva_0,
			replace(round(sum(case when detail.tva_tx<>0 AND detail.tva_tx<>5.5 AND detail.tva_tx<>20 then detail.tva else 0 END),2),".",",") as tva_autre,
			replace(round(sum(case when detail.tva_tx=5.5 then detail.total_ttc else 0 END),2),".",",") as ttc_55,
			replace(round(sum(case when detail.tva_tx=20 then detail.total_ttc else 0 END),2),".",",") as ttc_20,
			replace(round(sum(case when detail.tva_tx=0 then detail.total_ttc else 0 END),2),".",",") as ttc_0,
			replace(round(sum(case when detail.tva_tx<>0 AND detail.tva_tx<>5.5 AND detail.tva_tx<>20 then detail.total_ttc else 0 END),2),".",",") as ttc_autre
			from (
			SELECT distinct f.ref as ref_fact,   
			fd.total_ttc as total_ttc, 
			fd.total_ht as total_ht, 
			CASE WHEN NOT ISNULL(pr.ref) THEN pr.tva_tx ELSE fd.tva_tx END as tva_tx,
			CASE WHEN NOT ISNULL(pr.ref) THEN fd.total_ttc - (fd.total_ttc / (1 + (pr.tva_tx / 100)))
				WHEN ISNULL(pr.ref) THEN fd.total_tva                                          
				END as tva,
			fd.rowid as id_fd
			from llx_paiement as p inner join llx_paiement_facture as pf on pf.fk_paiement=p.rowid 
			inner join llx_facture as f on f.rowid=pf.fk_facture
			inner join llx_facturedet as fd on fd.fk_facture=f.rowid
			left join llx_product as pr on pr.rowid=fd.fk_product
			WHERE year(p.datep)='.$annee.' AND month(p.datep)='.$i.' AND f.paye=1
			) as detail;
		';

(Rq : je ne comprend pas pourquoi dans Dolibarr les requêtes sont écrites avec des « $sql .= … » pour chaque ligne. C’est plus difficile à lire et plus difficile à tester dans phpMyAdmin si on n’a pas xdebug en version locale)

AGI · Mai 8, 2025, 7:17

Bonjour

Cela permet à mon sens (et à priori pas que moi car c’est structuré pratiquement partout comme ça dans Dolibarr) de bien séparer chaque étapes et de bien voir les champs sélectionné et conditions.
Et généralement c’est toujours suivie d’un
//var_dump($sql);
Qui permet un rapide debug en affichant la commande sql réel qui va être passée. Un copier/coller dans php est pas trop difficile normalement si on su aller jusque là.

dum77 · Mai 8, 2025, 8:25

Oui en effet… c’est mais je trouve plus pratique comme je fais, puisque les éditeurs et php permettent de passer à la ligne sans que cela soit pris en compte par le moteur. On n’est pas avec Python ! A condition de bien mettre chaque fonction à la ligne (ici ce n’est pas un très bon exemple).

C’était juste une petite remarque en passant.

et l’optimisation, qu’en pensez-vous ?

AGI · Mai 8, 2025, 9:02

J’avoue c’est également un vieux réflexe, une ligne une action…

L’optimisation j’avoue que maintenant on y fait plus trop attention et on multiplie l’utilisation de variables sans jamais les libérer…
Quand on en parle aujourd’hui ça fait rire tout le monde, "un caractère c’est 1 octet ça représente quoi pour des serveurs ou la mémoire se chiffre en multiples de 1 048 576 octets et je vous parle même pas de la fibre ! " ouai la fibre vous savez c’est le truc qu’on est encore plein aujourd’hui à pas avoir quand on est pas dans des grandes villes mais au milieu des campagnes et des montagnes

erics · Mai 8, 2025, 10:33

c’est bon ! j’avais une erreur dans le copier/coller de la requête … les tabulations posent pb dans ma copie vers le terminal

bfaliere · Mai 8, 2025, 2:42

Hello,

Entre les requête à optimiser et les optimisations possibles au niveau de la base de données, il y aurait tout un sujet à traiter.
J’ai commencé de mon côté à regarder ce qui pouvait être fait, mais j’ai pas encore assez avancé pour le partager correctement. Si j’ai assez de temps je continuerai d’avancer là-dessus, mais le temps reste une denrée rare.

Beers · Mai 8, 2025, 3:04

Bonjour,

puisqu’on est dans l’optimisation

si pr.ref est NULL alors pr.tva_tx est NULL

donc

COALESCE(pr.tva_tx, fd.tva_tx) AS tva_tx,

au lieu de

CASE WHEN NOT ISNULL(pr.ref) THEN pr.tva_tx ELSE fd.tva_tx END as tva_tx

dum77 · Mai 9, 2025, 1:34

Merci pour le conseil ! Cela permet une meilleure lisibilité également

Avec les versions plus récentes de Dolibarr cela va beaucoup plus vite, ouf… 6 secondes pour une année, donc on évite le pire.

Par contre je trouve un résultat proche mais avec un écart significatif (en enlevant la correction de TVA), pourtant j’avais bien vérifié que le résultat correspondait avec ma requête, la méthode est bien la même (date paiement). Je regarderais pourquoi. J’ai vu la requête, elle fait le détail, le total doit être fait ailleurs

En effet certains ont gardé le réflexe d’économiser les ressources… cela peut surtout diminuer l’empreinte carbone du secteur informatique si on parvient à y penser dès le départ.

Beers · Mai 9, 2025, 2:46

On peut aussi utiliser FORMAT au lieu de REPLACE et ROUND pour simplifier un peu

REPLACE(ROUND(SUM(detail.total_ttc),2),".",",") as total_ttc,

devient

FORMAT(SUM(detail.total_ttc), 2, 'fr_FR') as total_ttc,

regis · Mai 10, 2025, 7:14

Je vais mettre mon grain de sel, il ne faut pas oublier la compatibilité sql avec postgresql

dum77 · Mai 10, 2025, 9:43

Merci, oui c’est plus clair.

Je me réponds au sujet de la différence dans le total. Cela ne vient pas des clauses WHERE. La sql de Dolibarr renvoie toutes les lignes mêmes s’il y a des paiements en plusieurs fois (pas de clause DISTINCT), mais elle renvoie aussi le montant payé et le total de la facture et ensuite le script applique un ratio entre le total facturé et le montant payé pour ventiler la part non payée sur tous les produits de la facture.

On a ainsi avec Dolibarr une meilleure estimation de la TVA collectée ou payée. Avec ma méthode si le paiement est partiel on considérait que tout était collecté. Dolibarr est donc plus précis.

Par contre cela pourrait être bien de mettre une option pour prendre en compte la TVA à jour pour les produits.