Discussion :

[Jhulk]

Bonjour à tous,

Je ne poste pas cette discussion dans le débat concernant les jointures car il parle plus des différences de syntaxes.

Ma question tourne plus autour des performances théoriques de comment on ordonne ses jointures.

Si on prend l'exemple d'une structure classique :
- Table commande : ORDER
- Table ligne de commande : ORDER_L

Donc avec plusieurs lignes de commandes par commande.

Même si (selon le SGBD) l'optimisateur est censé faire le taff je n'ai jamais sû s'il fallait rédiger sa requête :

Type 1 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT
	ORDER.ID_ORDER,
	ORDER.TITLE,
	ORDER_L.PRODUCT,
	ORDER_L.QUANTITY
 
FROM ORDER
 
JOIN ORDER_L ON
	ORDER_L.ID_ORDER = ORDER.ID_ORDER
 
WHERE ORDER.ID_ORDER = '424242'

Ou type 2 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	ORDER.ID_ORDER,
	ORDER.TITLE,
	ORDER_L.PRODUCT,
	ORDER_L.QUANTITY
 
FROM ORDER_L
 
JOIN ORDER ON
	ORDER_L.ID_ORDER = ORDER.ID_ORDER
 
WHERE ORDER.ID_ORDER = '424242'

J'ai toujours choisis par défaut le type 1 , parce qu'intuitivement je le trouve plus logique.

Je ne parle pas des cas d'exceptions ou on tombe sur une commande qui n'a pas de ligne ( de toute façon je n'ai pas mis de jointure en outer dans mon exemple).

Mais de votre expérience : faut-il partir de la table qui renverra "n" records pour 1 ID et joindre vers la table qui n'a qu'un record pour un ID ou l'inverse ?

[Oishiiii]

Il faut bien distinguer le SQL, qui se situe à un niveau logique; le QUOI et l'aspect algorithmique, l'optimiseur, qui traite du COMMENT.

C'est le principe d'indépendance physique qui a fait le succès de la Théorie Relationnelle, en permettant à l'utilisateur d'écrire une jointure de la même façon depuis 40 ans, tout en laissant les SDGB modifier leurs algorithmes au fil des années pour améliorer les performances.

Donc en termes de SQL, l'opérateur de jointure étant commutatif, c'est bonnet blanc, blanc bonnet.

Côté performance, l'optimiseur fera toujours le meilleur choix, après en fonction du SGBD, des statistiques, de l’environnement, la technique choisie peut varier. Il faut vérifier en comparant les plans d’exécution.

[Jhulk]

Donc pour faire simple, il n'y a pas de règle générale, il faut benchmarker chaque requête en regardant les coûts dans les plans d'exec.

C'est bien ce que je craignais. :-/

[Oishiiii]

Pour des requêtes extrêmement simples comme celles-ci il n'y a même pas besoin de se poser de question.

De manière générale dans 99.9% des cas, il n'y a pas besoin de vérifier le plan d’exécution pour chaque requête SQL que vous écrivez.
C'est seulement au cas par cas, quand le temps d’exécution semble anormalement long que l'on va investiguer et chercher à optimiser (en modifiant la requête, en posant des index, etc..).

[SQLpro]

Donc pour faire simple, il n'y a pas de règle générale, il faut benchmarker chaque requête en regardant les coûts dans les plans d'exec.

C'est bien ce que je craignais. :-/

NON !!!!

Il n'y a qu'une seule règle : l'ordre des jointures n'a STRICTEMENT AUCUN INTÉRÊT !

A +

[SergioMaster]

Bonjour,

Il n'y a qu'une seule règle : l'ordre des jointures n'a STRICTEMENT AUCUN INTÉRÊT !

sauf peut être à la lecture humaine

[aieeeuuuuu]

Il n'y a qu'une seule règle : l'ordre des jointures n'a STRICTEMENT AUCUN INTÉRÊT !

ça, c'est dans la théorie. Dans la pratique, avec un nombre de tables plus élevé, ça peut influencer l'optimiseur et deux requêtes sémantiquement identiques avec juste une inversion de l'ordre des jointures peuvent - rarement - conduire à deux plans d’exécution différents.

[SQLpro]

Oui si tu as un mauvais SGBD comme MySQ merde.... Sinon, c'est plus que très rare dans un bon SGBDR... Pour ma part jamais vu !

A +

[StringBuilder]

Oui si tu as un mauvais SGBD comme MySQ merde...

Ou Oracle...

Avec des jointures non normées, jusqu'en 11g au moins, l'ordre des jointures a une important extrême sur les performances...

Code sql :

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

1
2
3
4
5
 
select *
from tableA, tableB
where tableA.id = tableB.id
and tableA.nom = 'plop';

Aura des performances totalement différentes de :

Code sql :

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

1
2
3
4
5
 
select *
from tableB, tableA
where tableA.id = tableB.id
and tableA.nom = 'plop';

C'est d'autant plus vrai lorsque le volume de données des tables est important et différent d'une table à l'autre.
J'ai toujours été obligé à réécrire, à l'époque où je n'utilisais pas la norme 92 en raison de collègues frigides, en rejetant en fin de clause "FROM" les tables retournant le moins de lignes (soit car plus petites, soit car plus filtrées).

Sinon les performances étaient épouvantables.

[SQLpro]

Ou Oracle...

Je n'avais pas spécifiquement limité les mauvais SGBDR à MySQMerde.... Mais je ne voulais pas faire de tort à Oracle.... Difficile de jeter la pierre sur un invalide vu les casseroles qu'il se paye en ce moment !!!!!
Mais c'est vrai que son optimiseur est quand même globalement assez mauvais et le recours aux indicateurs de requêtes courant, ce qui n'est pas le cas de SQL Server...

Avec des jointures non normées, jusqu'en 11g au moins, l'ordre des jointures a une important extrême sur les performances...

Code sql :

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

1
2
3
4
5
 
select *
from tableA, tableB
where tableA.id = tableB.id
and tableA.nom = 'plop';

Aura des performances totalement différentes de :

Code sql :

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

1
2
3
4
5
 
select *
from tableB, tableA
where tableA.id = tableB.id
and tableA.nom = 'plop';

Rectifie ta requête tu as laissé la jointure dans le WHERE !

C'est d'autant plus vrai lorsque le volume de données des tables est important et différent d'une table à l'autre.
J'ai toujours été obligé à réécrire, à l'époque où je n'utilisais pas la norme 92 en raison de collègues frigides, en rejetant en fin de clause "FROM" les tables retournant le moins de lignes (soit car plus petites, soit car plus filtrées).

Sinon les performances étaient épouvantables.

A +

[StringBuilder]

Rectifie ta requête tu as laissé la jointure dans le WHERE !

C'est volontaire

Avec ce type de requête (certes, mal écrite) le simple fait de changer l'ordre des tables dans le FROM, sans absolument rien toucher d'autre, change le plan d'exécution.

En effet, Oracle va bêtement lire les tables dans l'ordre de gauche à droite, en filtrant progressivement avec ce qu'il a déjà trouvé.

Donc si tu recherches les lignes d'une commande, et que tu fais :

Code sql :

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

SELECT * FROM order, line WHERE line.order_id = order.id and order.reference= '1234'

Alors Oracle va lire séquentiellement TOUTES LES LIGNES de la table "line", car aucun filtre dans le WHERE ne permet, avec les informations déjà chargées en mémoire, de filter quoi que ce soit.

Et une fois qu'il a chargé toute la table en mémoire, il passe à "order", se dit "tiens, j'ai qu'une ligne à charger, et je dois mettre 99,999% des lignes de "line" que j'ai lu à la poubelle car finalement j'en avais pas besoin.

A l'inverse :

Code sql :

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

SELECT * FROM line, order WHERE line.order_id = order.id and order.reference= '1234'

A ce moment, Oracle charge la ligne de "order" qui correspond au numéro.
Puis quand il passe à la table "line", il sait qu'il doit utiliser l'index sur order_id et filtre comme il faut.

En gros, le boulot de l'optimiseur, c'est le développeur qui se le coltine...
Et ça devient franchement merdique quand tu as des requêtes plus complexes où en fonction des paramètres reçus une table filtre plus ou moins.
=> Le SQL dynamique est alors inefficace dans la moitié des cas.

Par exemple :
- Je recherche par numéro de commande :

Code sql :

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

SELECT * FROM line, order WHERE line.order_id = order.id and order.reference= '1234'

- Je recherche par date de livraison des lignes :

Code sql :

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

SELECT * FROM order, line WHERE line.order_id = order.id and line.delivery_date = to_date('20160902', 'YYYYMMDD')

Donc si dans un moteur de recherche, on peut charger les lignes par date de livraison ou par numéro de commande, on a un cas où la requête va être lente, à moins d'écrire chaque requête possible une à une et laisser tomber le SQL dynamique...

[aieeeuuuuu]

Je n'avais pas spécifiquement limité les mauvais SGBDR à MySQMerde.... Mais je ne voulais pas faire de tort à Oracle....

HA... devra-t-on dorénavant dire Merdacle ?

et dans la lignée... MS SQMerde Server 2008R2 ? :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT T1.a
FROM	T1
INNER JOIN T2	ON	T2.a = T1.a
INNER JOIN T3	ON	T3.a = T2.a
INNER JOIN T4	ON	T4.a = T3.a
INNER JOIN T5	ON	T5.a = T4.a
INNER JOIN T6	ON	T6.a = T5.a
WHERE CAST(T6.b AS VARCHAR(50)) LIKE 'A%'

Table 'T1'. Nombre d'analyses 0, lectures logiques 62, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T2'. Nombre d'analyses 0, lectures logiques 152, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T4'. Nombre d'analyses 0, lectures logiques 152, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T5'. Nombre d'analyses 0, lectures logiques 152, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T6'. Nombre d'analyses 0, lectures logiques 3071, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T3'. Nombre d'analyses 1, lectures logiques 6, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

Alors que :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT T1.a
FROM	T6
INNER JOIN T5	ON	T5.a = T6.a
INNER JOIN T4	ON	T4.a = T5.a
INNER JOIN T3	ON	T3.a = T4.a
INNER JOIN T2	ON	T2.a = T3.a
INNER JOIN T1	ON	T1.a = T2.a
WHERE CAST(T6.b AS VARCHAR(50)) LIKE 'A%'

Table 'T6'. Nombre d'analyses 0, lectures logiques 223, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T5'. Nombre d'analyses 0, lectures logiques 153, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T4'. Nombre d'analyses 0, lectures logiques 153, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T3'. Nombre d'analyses 0, lectures logiques 140, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T2'. Nombre d'analyses 0, lectures logiques 140, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T1'. Nombre d'analyses 1, lectures logiques 2, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

Pour ma part jamais vu !

Bah voilà, c'est fait

[skuatamad]

StringBuilder, vérifie le paramétrage de l'optimiseur.

Vu l'application boulet que tu te tapes dans ta boîte, je ne serais pas surpris qu'ils préconisent d'utiliser le mode RULE...

[SQLpro]

HA... devra-t-on dorénavant dire Merdacle ?

et dans la lignée... MS SQMerde Server 2008R2 ? :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT T1.a
FROM	T1
INNER JOIN T2	ON	T2.a = T1.a
INNER JOIN T3	ON	T3.a = T2.a
INNER JOIN T4	ON	T4.a = T3.a
INNER JOIN T5	ON	T5.a = T4.a
INNER JOIN T6	ON	T6.a = T5.a
WHERE CAST(T6.b AS VARCHAR(50)) LIKE 'A%'

Table 'T1'. Nombre d'analyses 0, lectures logiques 62, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T2'. Nombre d'analyses 0, lectures logiques 152, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T4'. Nombre d'analyses 0, lectures logiques 152, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T5'. Nombre d'analyses 0, lectures logiques 152, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T6'. Nombre d'analyses 0, lectures logiques 3071, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T3'. Nombre d'analyses 1, lectures logiques 6, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

Alors que :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT T1.a
FROM	T6
INNER JOIN T5	ON	T5.a = T6.a
INNER JOIN T4	ON	T4.a = T5.a
INNER JOIN T3	ON	T3.a = T4.a
INNER JOIN T2	ON	T2.a = T3.a
INNER JOIN T1	ON	T1.a = T2.a
WHERE CAST(T6.b AS VARCHAR(50)) LIKE 'A%'

Table 'T6'. Nombre d'analyses 0, lectures logiques 223, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T5'. Nombre d'analyses 0, lectures logiques 153, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T4'. Nombre d'analyses 0, lectures logiques 153, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T3'. Nombre d'analyses 0, lectures logiques 140, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T2'. Nombre d'analyses 0, lectures logiques 140, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'T1'. Nombre d'analyses 1, lectures logiques 2, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

Bah voilà, c'est fait

Commence par avoir des stats à jour (en FULLSCAN) !

A +

[aieeeuuuuu]

tout le monde n'a pas des stats à jour... je démontrais juste que l'ordre d'écriture des jointure pouvait influer sur la plan de requêtes (mais je suis d'accord, c'est extrêment rare, surtout dans des vrais cas pratiques)

Enfin, si tu veux avec des stats à jour, et des clef étrangères dûment déclarées (ce n'était volontairement pas le cas de mon cas de test précédent), j'ai un cas encore pire :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT T1.a
FROM	T1
INNER JOIN T2	ON	T2.a = T1.a
INNER JOIN T3	ON	T3.a = T2.a
INNER JOIN T4	ON	T4.a = T3.a
INNER JOIN T5	ON	T5.a = T4.a
INNER JOIN T6	ON	T6.a = T5.a

Table 'T1'. Nombre d'analyses 1, lectures logiques 38
Temps UC = 0*ms, temps écoulé = 36*ms.

Code :

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

1
2
3
4
5
6
7
8
 
SELECT T1.a
FROM	T6	
INNER JOIN T5	ON	T5.a = T6.a
INNER JOIN T4	ON	T4.a = T5.a
INNER JOIN T3	ON	T3.a = T4.a
INNER JOIN T2	ON	T2.a = T3.a
INNER JOIN T1	ON	T1.a = T2.a

Table 'T5'. Nombre d'analyses 0, lectures logiques 40634...
Table 'T3'. Nombre d'analyses 0, lectures logiques 30634...
Table 'T1'. Nombre d'analyses 1, lectures logiques 38...

Temps UC = 16*ms, temps écoulé = 90*ms.