Optimisation requête

**vandman** · 15/09/2015, 17h08

Bonjour,

J'ai écri une requette qui est je pense un peu lourde. Je voudrais l'optimiser pour:

une meilleur lecture
avoir un traitement plus rapide. actuellement, la requette prend 27 secondes. je voudrais que ça soit plus rapide.

Comme je ne sais pas ce dont vous avez besoin, je vous mets le code:

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- BalanceCPT => genere un tableau qui sont la balance comptable de mes regs. ces données sont mises à jour quotidiennement.
With BalanceCPT AS
(
SELECT	rcptdet.idg_reg
	,rcptdet.numcpt
	,rcptdet.totdebex + rcptdet.totdebper											AS ReportDebitCPT
	,rcptdet.totcredex + rcptdet.totcredper 										AS ReportCreditCPT
	,rcptdet.totdebj													AS CumulDebitJourCPT
	,rcptdet.totcredj													AS CumulCreditJourCPT
	,rcptdet.totdebex + rcptdet.totdebper + rcptdet.totdebj									AS CumulDebitCPT
	,rcptdet.totcredex + rcptdet.totcredper + rcptdet.totcredj								AS CumulCreditCPT
	,CASE	WHEN 	(rcptdet.totdebex + rcptdet.totdebper + rcptdet.totdebj)-(rcptdet.totcredex + rcptdet.totcredper + rcptdet.totcredj) > 0 THEN
			(rcptdet.totdebex + rcptdet.totdebper + rcptdet.totdebj)-(rcptdet.totcredex + rcptdet.totcredper + rcptdet.totcredj)
		ELSE 0.00
		END 														AS SoldeDebitCPT
	,CASE	WHEN 	(rcptdet.totcredex + rcptdet.totcredper + rcptdet.totcredj)-(rcptdet.totdebex + rcptdet.totdebper + rcptdet.totdebj) > 0 THEN
			(rcptdet.totcredex + rcptdet.totcredper + rcptdet.totcredj)-(rcptdet.totdebex + rcptdet.totdebper + rcptdet.totdebj)
		ELSE 0.00
		END														AS SoldeCreditCPT
FROM base.rcptdet
ORDER BY idg_reg, numcpt
),
-- avec ces données, je calcule la sommes pour chaque reg => Balance.
Balance AS
(
SELECT	BalanceCPT.idg_reg		AS Numreg
	,SUM(ReportDebitCPT) 		AS ReportDebit
	,SUM(ReportCreditCPT) 		AS ReportCredit
	,SUM(CumulDebitJourCPT)		AS CumulDebitJour
	,SUM(CumulCreditJourCPT)	AS CumulCreditJour
	,SUM(CumulDebitCPT)		AS CumulDebit
	,SUM(CumulCreditCPT)		AS CumulCredit
	,SUM(SoldeDebitCPT)		AS SoldeDebit
	,SUM(SoldeCreditCPT)		AS SoldeCredit
FROM BalanceCPT
GROUP BY BalanceCPT.idg_reg
)
-- la, j'affiche les regs qui sont vides ou qui n'ont jamais dépensé ou encaisser de recette.
Select	--RR.idreg
	PCT.intitule		AS NomPlanComtable
	,CATR.libelle		AS Categorie
	,RPM.typereg		AS Typereg
	,RR.intitule		AS Nomreg
	,RR.num_reg		AS ID_reg_chor
	,RPM.dteuser		AS Datereg
	,COUNT(CASE WHEN RH.numbenef > 0 THEN 1 ELSE NULL END)	AS Nb_de_tiers
	,RU.nom			AS Nom
	,RU.prenom		AS Prenom
	,RU.nomuser		AS identifiant
	,RG.nomgroupe		AS GroupeUtilisateur
	,Balance.SoldeDebit	AS SoldeDebit
--	,Balance.SoldeCredit	AS SoldeCredit
FROM base.rreg AS RR
	LEFT JOIN base.asso_cat_reg AS ACATR  -- catégorie
		ON RR.idreg = ACATR.idreg
		LEFT JOIN base.cat_reg AS CATR
			ON ACATR.id_cat_reg = CATR.id
	LEFT JOIN base.rpasse AS RPASS  -- utilisateur et groupe
		ON RR.idreg = RPASS.idg_reg
		LEFT JOIN base.rutils AS RU
			ON RPASS.numuser = RU.numuser
		LEFT JOIN base.rgroupes AS RG
			ON RPASS.numgroupe = RG.numgroupe
	LEFT JOIN base.plancpt_reg AS PCPTR --nom plan comptable
		ON RR.idreg = PCPTR.id_reg
		LEFT JOIN base.plancpt_lib AS PCT
			ON PCPTR.id_plancpt = PCT.id
	LEFT JOIN Balance --balance
		ON RR.idreg = Balance.Numreg
	LEFT JOIN base.rparam AS RPM --date reg
		ON RR.idreg = RPM.idg_reg
	LEFT JOIN base.rhabitue AS RH --nombre de reg
		ON RR.idreg = RH.idg_reg
WHERE (RG.parent = 1 OR RG.parent = 2 OR RG.parent = 3271)
-- condition permettant d'avoir que les regs vide = 0€ et les regs avec de l'avance 50 000€.
AND (
(Balance.ReportDebit = 0 AND Balance.ReportCredit = 0 AND Balance.CumulDebitJour = 0 AND Balance.CumulCreditJour = 0 AND Balance.CumulDebit = 0 AND Balance.CumulCredit = 0 AND Balance.SoldeDebit = 0 AND Balance.SoldeCredit = 0)
OR  (Balance.ReportDebit = 0 AND Balance.ReportCredit = 0 AND Balance.CumulDebitJour = 50000 AND Balance.CumulCreditJour = 50000 AND Balance.CumulDebit = 50000 AND Balance.CumulCredit = 50000 AND Balance.SoldeDebit = 50000 AND Balance.SoldeCredit = 50000)
OR  (Balance.ReportDebit = 50000 AND Balance.ReportCredit = 50000 AND Balance.CumulDebitJour = 0 AND Balance.CumulCreditJour = 0 AND Balance.CumulDebit = 50000 AND Balance.CumulCredit = 50000 AND Balance.SoldeDebit = 50000 AND Balance.SoldeCredit = 50000)
)
--AND RR.type_reg = 'RR'
GROUP BY PCT.intitule, CATR.libelle, RPM.typereg, RR.intitule, RR.num_reg, RPM.dteuser, RU.nom, RU.prenom, RU.nomuser, RG.nomgroupe, SoldeDebit--, SoldeCredit
ORDER BY NomPlanComtable, Categorie, Nomreg asc

Cordialement,
vandman

**SQLpro** · 16/09/2015, 10h42

Dans votre première CTE vous avez un ORDER BY. Ceci est syntaxiquement illégal et je m'étonne que PostGreSQL l'accepte !
Supprimez le, il ne sert a rien.

De plus fournissez nous la description de vos tables avec ses index. Sans cela pas de conseil possible.

A +

**vandman** · 16/09/2015, 11h25

Bonjour,
Sous-quel format vous les voulez?

Cordialement,
Vandman

**escartefigue** · 16/09/2015, 11h30

Bonjour,

Pour la description des tables et index, le plus simple est de communiquer le DDL

Quelle est la volumétrie de chacune des tables, vu le nombre de jointures outer, la volumétrie collectée peut être très importante

De plus, je vois que vous avez des conditions de filtrage (where) sur des montants et utilisant des conditions "OR" , ce sont donc à double titre des critères non sargables !

**vandman** · 16/09/2015, 13h31

Bonjour,

J’espère que ça fait l'affaire.

J'ai mis la volumétrie actuelle en commentaire et au début de chaque création de table.

Cordialement,
vandman

**escartefigue** · 16/09/2015, 14h04

Envoyé par vandman

Bonjour,

J’espère que ça fait l'affaire.

J'ai mis la volumétrie actuelle en commentaire et au début de chaque création de table.

Cordialement,
vandman

Le format fourni n'est pas très pratique à lire

une mise en colonne aurait facilité la lecture
il faut vérifier que les jointures portent sur des colonnes de même format, je l'ai controlé pour les 1eres jointures, mais pas pour toute la requete vu le nombre de tables mises en œuvre.

A priori c'est probablement le where/or sur les montants qui est le plus couteux
Vous pourriez essayer de pré-filtrer dès création des CTE en ajoutant une clause HAVING sur chacun des montants, puisque vous ne conservez finalement que les valeurs 0 et 50000, ce qui limiterait la casse dans le where de filtrage de la requete principale

**vandman** · 16/09/2015, 16h28

Bonjour,

Quelqu'un peut-il me donner un exemple du format désiré?

Cordialement,
Vandman

**vandman** · 16/09/2015, 17h28

Bonjour,

L’idée du HAVING fonctionne très bien. je suis passé de 30200ms à 6000ms.

Je ne sais pas si il y a moyen d'optimiser encore la requette.

Cordialement,
Vandman

**escartefigue** · 17/09/2015, 09h07

Pour en savoir plus, il faut faire un explain de vos requetes

**SQLpro** · 17/09/2015, 13h54

Beaucoup d'horreur dans votre structure de base :
1) la présence de colonne avec le type "character varying" sans préciser de de longueur
2) une table sans clef primaire base.rcptdet
3) une table monstrueusement obèse : base.rparam (pourquoi ne pas utiliser un tableur ? Vous y serez plus à l'aise !!!) avec de surcoit une clef primaire composite (PRIMARY KEY (idk, idg_reg)...)
4) une autre table obèse : base.rhabitue... Et avec aussi une clef primaire composite !

Si vous voulez des performances, commencez par modéliser correctement...

A me lire : http://blog.developpez.com/sqlpro/p1...mances_petites

A +

**vandman** · 17/09/2015, 19h18

Bonjour,

Je ne développe pas la base de donnée et l'application. Je suis pour faire simple, le client.
Je fais juste des requettes pour avoir des informations qui ne sont pas fournis par défaut.

Cordialement,
Vandman

**escartefigue** · 18/09/2015, 12h01

Concernant le DDL, vous pouvez tout de même adresser des critiques (constructives et argumentées) à votre fournisseur de base de données, avec un peu de chance, il en tiendra compte dans une version ultérieure.

Avez vous fait un explain de votre requete ?

**SQLpro** · 20/09/2015, 14h09

Envoyé par vandman

Bonjour,

Je ne développe pas la base de donnée et l'application. Je suis pour faire simple, le client.
Je fais juste des requettes pour avoir des informations qui ne sont pas fournis par défaut.

Cordialement,
Vandman

le problème est que :
modèle de merde => performance de merde et optimisation quasi impossible !

A +

Optimisation requête

Requêtes PostgreSQL

Discussions similaires

Partager

Partager