Je n’ai pas voulu mettre optimisation dans le titre de l’article, c’est pourtant bien ce qui nous est souvent demandé.
Avant de chercher à optimiser les requêtes, il est utile de vérifier que quelques bonnes pratiques de base sont respectées dans l’écriture de la requête !
Dans le cas traité, on s’intéresse particulièrement à plusieurs éléments :
- critères de jointure
- critères de sélection
- critères de groupage
Objectif recherché : que tous ces critères soient exprimés, si possible, sans calcul !
Une zone calculée ne peut être prise en charge via un index par l’optimiseur … nous allons donc réécrire tout ce qui a été écrit d’une façon « humaine » !
Exemple
La requête utilisée dans cet article est un extrait d’un requête réelle, pour laquelle les noms de tables et colonnes ont été modifiées …
Critère de jointure
Ce point n’impacte que peu les performances, car le moteur SQL réécrit la requête pour nous, mais apporte plus de lisibilité.
On évite ce genre de syntaxe (produit cartésien) :
SELECT …
FROM ADHENT,
ADHDET
WHERE (ADHENT.GJCMP = ADHDET.GACMP#
AND ADHENT.GJGL# = ADHDET.GAGL#A
AND ADHDET.GAACCD = '1')
AND ((ADHDET.GATYPE IN (
'EX',
'IN'
))
Pour exprimer sur la jointure les critères :
select ADHENT.*
from ADHENT
join ADHDET ON (ADHENT.GJCMP = ADHDET.GACMP#
AND ADHENT.GJGL# = ADHDET.GAGL#A
AND ADHDET.GAACCD = '1')
WHERE (ADHDET.GATYPE IN (
'EX',
'IN'
)) ;
Critères de sélection
Requête de départ :
select ADHENT.*
from ADHENT
join ADHDET ON (ADHENT.GJCMP = ADHDET.GACMP#
AND ADHENT.GJGL# = ADHDET.GAGL#A
AND ADHDET.GAACCD = '1')
WHERE ADHDET.GATYPE IN ( 'EX', 'IN' )
AND (ADHENT.GJACMO <> 13)
AND ADHENT.GJCMP IN ('10')
AND CASE
WHEN ADHENT.GJJLDT = 0 THEN '01/01/9999'
ELSE DATE( SUBSTR(ADHENT.GJJLDT, 5, 2) || '/' ||
SUBSTR(ADHENT.GJJLDT, 7, 2) || '/' ||
SUBSTR(ADHENT.GJJLDT, 1, 4))
END BETWEEN '2016-01-01' AND '2017-12-31'
AND SUBSTR(ADHENT.GJGL#, 1, 6) BETWEEN '615540' AND '615540'
AND CASE
WHEN TRIM(ADHENT.GJLTG#) = '' THEN 'N'
ELSE 'Y'
END = 'N'
Il est possible de remplacer toutes les valeurs calculées et de les inverser !
AND CASE
WHEN ADHENT.GJJLDT = 0 THEN '01/01/9999'
ELSE DATE( SUBSTR(ADHENT.GJJLDT, 5, 2) || '/' ||
SUBSTR(ADHENT.GJJLDT, 7, 2) || '/' ||
SUBSTR(ADHENT.GJJLDT, 1, 4))
END BETWEEN '2016-01-01' AND '2017-12-31'
Devient :
AND ADHENT.GJJLDT between int(date('2016-01-01')) AND int(date('2017-12-31'))
Sélection sur code :
AND SUBSTR(ADHENT.GJGL#, 1, 6) BETWEEN '615540' AND '615540'
Devient :
AND ADHENT.GJGL# BETWEEN '615540' ||'0000000000' AND '615540' || '9999999999'
Sélection sur code :
AND CASE
WHEN TRIM(ADHENT.GJLTG#) = '' THEN 'N'
ELSE 'Y'
END = 'N'
Devient :
AND ADHENT.GJLTG# = ''
Ce dernier exemple illustre bien la capacité du cerveau humain à raisonner et non pas à exprimer des critères techniques !
Critères de groupage (et mise en forme)
Le groupage est souvent effectué en dernier, c’est-à-dire après l’ensemble des jointures. Vous êtes alors contraints d’ajouter un nombre important de colonnes dans le groupage, colonnes faisant l’objet de mise en forme pour un affichage adapté à l’utilisateur, et donc sur des zones calculées !
Nous proposons l’inverse :
- d’abord on calcule les données, nécessitant groupage
- ensuite on va chercher, par des jointures, des éléments complémentaires et on met en forme (calcul) les valeurs
Pour cela les CTE (Common Table Expressions) nous sont d’un grand secours.
Par exemple :
with tmp as (
SELECT ADHENT.GJCMP,
ADHENT.GJGL#,
ADHENT.GJJLTP,
ADHENT.GJJLCD,
ADHENT.GJJLNO,
ADHENT.GJINV#,
ADHENT.GJDESC,
ADHENT.GJJLDT,
ADHENT.GJMVM#,
ADHENT.GJTYPE,
ADHENT.GJLTG#,
ADHENT.GJLTGD,
SUM(ADHENT.GJAMT$) as somme1,
SUM( CASE
WHEN ADHENT.GJAMT$ > 0.00 THEN ADHENT.GJAMT$
END) as somme2,
SUM(
CASE
WHEN ADHENT.GJAMT$ < 0.00 THEN (-1 * ADHENT.GJAMT$)
END) as somme3
from ADHENT
join ADHDET ON ...
WHERE ...
GROUP BY ADHENT.GJCMP,
ADHENT.GJGL#,
ADHENT.GJJLTP,
ADHENT.GJJLCD,
ADHENT.GJJLNO,
ADHENT.GJINV#,
ADHENT.GJDESC,
ADHENT.GJJLDT,
ADHENT.GJMVM#,
ADHENT.GJTYPE,
ADHENT.GJLTG#,
ADHENT.GJLTGD )
select tmp.GJCMP,
SUBSTR(tmp.GJGL#, 7, 4),
SUBSTR(tmp.GJGL#, 1, 6),
tmp.GJJLTP,
tmp.GJJLCD || '-' || RIGHT(CONCAT('00000000', TRIM(CHAR(tmp.GJJLNO))), 8),
tmp.GJJLNO,
TRIM(tmp.GJINV#),
tmp.GJDESC,
CASE WHEN tmp.GJJLDT = 0 THEN '01/01/9999'
ELSE DATE( SUBSTR(tmp.GJJLDT, 5, 2) || '/' ||
SUBSTR(tmp.GJJLDT, 7, 2) || '/' ||
SUBSTR(tmp.GJJLDT, 1, 4))
END,
tmp.GJMVM#,
TRIM(tmp.GJTYPE),
tmp.GJLTG#,
tmp.GJGL#,
ADHCMP1.ZLARGN,
TRIM(ADHCMP2.ZRNAME),
CASE WHEN tmp.GJLTGD = 0 THEN '01/01/9999'
ELSE DATE( SUBSTR(tmp.GJLTGD, 5, 2) || '/' ||
SUBSTR(tmp.GJLTGD, 7, 2) || '/' ||
SUBSTR(tmp.GJLTGD, 1, 4))
END,
somme1,
somme2,
somme3
from tmp
LEFT OUTER JOIN ADHCMP1 ON ADHCMP1.ZLCMP = tmp.GJCMP
AND ADHCMP1.ZLLOC = SUBSTR(tmp.GJGL#, 7, 4)
LEFT OUTER JOIN ADHCMP2 ON ADHCMP1.ZLCMP = ADHCMP2.ZRCMP
AND ADHCMP1.ZLIRGN = ADHCMP2.ZRRGN ;
Avec ces quelques règles, simples dans leur principe, vous vous assurez que le moteur SQL pourra utiliser pleinement vos index. Cela ne signifie pas qu’il ne sera pas nécessaire d’optimiser par la suite.
Pour finir le cas concret évoqué ici :
– 4 fichiers dans la jointures :
– 500 Millions , 1 Million, 1.000 et 70 enreg
– 4.600 enreg en retour
Requête d’origine : 2 min 40 s (ce qui est déjà très bien, avec un scan de table sur le plus gros fichier).
Après réécriture : 40 ms
Bien sûr, les index nécessaires étaient déjà en place pour atteindre ce niveau de temps de réponse.
Encore une fois, SQL est le meilleur moyen d’accéder à la donnée, aussi complexe soit elle.
Par le meilleur, j’entends :
- le plus simple : écrire un programme RPG/COBOL équivalent demanderait une quantité de code importante (et donc probabilité de bug)
- le plus efficace (40 ms) : à condition que l’on donne à SQL les moyens d’être efficace
En conclusion : travailler d’abord sur la donnée, occupez vous ensuite de la mise en forme !
