Discussion :

[AQkinkin]

Bonjour à tous.

Voici la requête de départ :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT  t1.anode, sum(t2.size)
FROM t4, t3, t2, t1
WHERE
    t4.onode = t1.onode AND
    t3.inode = t4.inode AND
    t3.snode = t2.snode
GROUP BY t1.anode;

Elle fonctionne bien sur les BDD de petite taille, mais sur les très gros volumes il y a un temps important avant d’obtenir la réponse.

On me demande de tester un découpage de la requête pour vérifier que ce n'est pas plus rapide.
Je me suis dit facile ... je fais une boucle. Puis je m'aperçois que : faire un tableau double entré avec un "if exit la valeur" pour stocker le résultat d'une requête de plusieurs lignes me pose un gros problème.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT  t1.anode, sum(t2.size)
FROM t4, t3, t2, t1
WHERE
    t4.onode = t1.onode AND
    t3.inode = t4.inode AND
    t3.snode = t2.snode AND
    t2.snode BETWEEN Var_Min_snode AND Var_Min_snode + Var_count_Division
GROUP BY t1.anode;

Si quelqu'un peut me donner des pistes ?

[escartefigue]

Bonjour,

Plutôt que de faire le produit cartésien des 4 tables puis d'appliquer une restriction sur le résultat, il est préférable d'utiliser des jointures normalisées avec l'opérateur JOIN :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT t1.anode
     , sum(t2.size)
FROM t4
inner join t3 
   on t3.inode = t4.inode
inner join t2
   on t2.snode = t3.snode
inner join t1
   on t1.onode = t4.onode
GROUP BY t1.anode;

Si ce n'est toujours pas suffisamment performant, vérifiez que les critères de jointure sont bien de même type et de même longueur dans chaque table et qu'ils sont bien indexés.

Les SGBD relationnels sont d'autant plus performants qu'on utilise des requêtes ensemblistes : faire 3 requêtes au lieu d'une est une mauvaise solution qui au mieux ne changera rien et au pire augmentera le temps total d'exécution

[Séb.]

Je rejoins escartefigue sur le JOIN à utiliser.

Elle fonctionne bien sur les BDD de petite taille, mais sur les très gros volumes il y a un temps important avant d’obtenir la réponse.

C'est possible d'avoir une idée des chiffres ?
Petite taille : combien de lignes dans les tables ?
Très gros volume : combien de lignes ?
Temps important : de combien à combien ?

Parce que bon, le diag peut aller de "c'est normal que la requête prenne quelques secondes si tu as quelques centaines de millions de lignes à brasser" à "en effet, il n'est pas normal que le temps d'exécution soit multiplié si tu passes de 50 k à 1 M de lignes"

[Waldar]

Plutôt que de faire le produit cartésien des 4 tables puis d'appliquer une restriction sur le résultat

C'est surtout conceptuel, le plan d'exécution sera le même. Mais oui c'est une bonne pratique.

Si ce n'est toujours pas suffisamment performant, vérifiez que les critères de jointure sont bien de même type et de même longueur dans chaque table et qu'ils sont bien indexés.

Compte-tenu qu'il n'y a aucun filtre les index n'ont ici probablement aucun intérêt et au contraire il faut vérifier qu'ils ne sont pas utilisés.

Les SGBD relationnels sont d'autant plus performants qu'on utilise des requêtes ensemblistes : faire 3 requêtes au lieu d'une est une mauvaise solution qui au mieux ne changera rien et au pire augmentera le temps total d'exécution

Faux. À partir du moment où l'exécution d'une requête consomme plus de RAM que ce qui est disponible, le SGBD va écrire sur disque pour pouvoir continuer ses opérations.
Si le découpage permet de rester en RAM, 3 * 1/3 sera plus rapide que 1 * 1.

La difficulté résidant dans trouver le bon découpage... et bien entendu que le résultat le permette.

[escartefigue]

C'est surtout conceptuel, le plan d'exécution sera le même. Mais oui c'est une bonne pratique.

En fait, ca dépend des optimiseurs, au mieux c'est neutre, au pire, c'est bien une restriction post produit cartésien qui est appliquée

Compte-tenu qu'il n'y a aucun filtre les index n'ont ici probablement aucun intérêt et au contraire il faut vérifier qu'ils ne sont pas utilisés.

Il n'y a plus de restriction (WHERE) si on écrit les jointures proprement, mais les index sont ici utilisés pour les jointures

Faux. À partir du moment où l'exécution d'une requête consomme plus de RAM que ce qui est disponible, le SGBD va écrire sur disque pour pouvoir continuer ses opérations. Si le découpage permet de rester en RAM, 3 * 1/3 sera plus rapide que 1 * 1.

La difficulté résidant dans trouver le bon découpage... et bien entendu que le résultat le permette.

En effet, je n'avais pas pensé à ce cas de débordement de la RAM, ok en effet si c'est le cas.

[AQkinkin]

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT t1.anode, sum(t2.size) FROM t4
inner join t3 on t3.inode = t4.inode
inner join t2 on t2.snode = t3.snode
inner join t1 on t1.onode = t4.onode
GROUP BY t1.anode;

J'ai testé les 3 types de requêtes avec "EXPLAIN", elles ont le même comportement et le même temps d’exécution sur les petites tables.
Liste des 3 types de jointures:
1 - jointure dans le FROM séparé par des virgules
2 - jointure avec le mot clé "join"
3 - jointure avec le mot clé "WITH"

Petite taille : combien de lignes dans les tables ?
Très gros volume : combien de lignes ?
Temps important : de combien à combien ?

Petite taille : 52 000 000 row pour t2 & t4 et t3 40 000 000
Très gros volume : 609 000 000 row pour t2 & t4 et t3 560 000 000
Temps important : 4h à 7h selon les machines (de 16 à 64 cores et de la ram en suffisance, mais disque non-SSD)

Pour information : les temps sont analogues sur maxDB, mais dans le passé l'éclatement des requêtes sur maxDB a montré de beaux gains de performance.


Je suis vos réponses et je vais continuer les tests, car je souhaite apprends.
Je ne rentre pas dans les détails, mais je n'ai plus la réservation de ma machine de test. J'ai donc finalisé le POC en me rabattant sur un script Perl pour faire le taf.

Une machine a été préparée avec une base 1.7To pour faire une flopée de tests de performance.

Pour tout vous dire, j'ai commencé un script qui utilise une table temporaire pour stocker le résultat de chaque boucle. Puis pour optenir le resultat, un:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT anode, sum(size) FROM TEMPTABLE GROUP BY anode;

que je finirais ce week-end pour vérifier la différence du temps de traitement du script Perl et de la fonction ....

[escartefigue]

Quel types d'accès l'explain donne -t-il pour chacune des tables ?

[SQLpro]

Déjà il faudrait savoir de quelle(s) table(s) viennent les colonnes Var_Min_snode, Var_Min_snode, Var_count_Division car je soupçonne une jointure triangulaire !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT  t1.anode, sum(t2.size)
FROM    t4 
        JOIN t1 ON t4.onode = t1.onode
        JOIN t3 ON t3.inode = t4.inode
        JOIN t2 ON t3.snode = t2.snode
WHERE   t2.snode BETWEEN ???.Var_Min_snode 
                     AND ???.Var_Min_snode + ???.Var_count_Division
GROUP   BY t1.anode;

Dans un tel cas aucune optimisation n'est possible et il est probable que le modèle de données soit foireux (irrespect des formes normales). Donc, remodéliser la base de données...

Autre solution, utilisée une vue matérialisée. Mais à ce jeux là PostGreSQL est assez mauvais...

A +

[Waldar]

Je testerai aussi une dernière requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
with cte_t2_agg (snode, size) as
(
  select snode, sum(size)
    from t2
group by snode
)
  select t1.anode, sum(t2.size)
    from t1
    join t4               on t4.onode = t1.onode
    join t3               on t3.inode = t4.inode
    join cte_t2_agg as t2 on t2.snode = t3.snode
group by t1.anode;