Discussion :

[dorian53]

Bonjour,

Jeu de test

Table _produit

id	nom
1	TV
2	Voyage

Table _vente

id	prix	produit	date
1	500	TV	2000-01-01
2	600	TV	2010-01-01
3	1200	Voyage	2010-01-01
4	900	Voyage	2000-01-01

Objectif

Afficher chaque produit avec son prix vente le plus cher et sa date.

produit	prix	date
TV	600	2010-01-01
Voyage	1200	2010-01-01

Deux solutions

1/ Sous-requêtes dans INNER

Celle-ci se comprend très bien, on imagine que la sous-requête est exécutée en 1er pour constituer une "table virtuelle" puis la jointure est réalisée sur deux colonnes.

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM _produit p
INNER JOIN _vente v1
ON p.id = v1.produit
INNER JOIN (
	SELECT produit, max(prix) as prix
    FROM _vente v2
    GROUP BY produit
) as v
ON p.id = v.produit AND v.prix = v1.prix

2/ Sous-requête dans un IN

Celle-ci fonctionne également mais se comprend difficilement... Comment le traitement est-il exécuté ?

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *
FROM _vente v1
INNER JOIN _produit p1
ON p1.id = v1.produit
WHERE v1.prix IN (
	SELECT max(v2.prix)
    FROM _vente v2
    WHERE p1.id = v2.produit
)

3/ Quelle est la requête la plus performante ?

Merci

[al1_24]

3/ Quelle est la requête la plus performante ?

Beaucoup d'éléments entrent en jeu pour donner la réponse à cette question :
Quel est le nombre de lignes dans ces tables ?
Y a-t-il un ou plusieurs index sur ces tables ?
Y a-t-il des statistiques sur ces tables ? Sont-elles à jour ?
Quelle est la capacité de l'optimiseur à identifier le meilleur plan d'exécution ?

[dorian53]

Salut,

Beaucoup d'éléments entrent en jeu pour donner la réponse à cette question :
Quel est le nombre de lignes dans ces tables ?
Y a-t-il un ou plusieurs index sur ces tables ?

Difficile de répondre précisément, c'est une version simplifiée d'une requête beaucoup plus complexe une 15aine d'INNER JOIN sur la requête parent.

Y a-t-il des statistiques sur ces tables ? Sont-elles à jour ?
Quelle est la capacité de l'optimiseur à identifier le meilleur plan d'exécution ?

Le SGBD utilisé est Percona 5.6, quelles statistiques puis-je remonter et où les trouver ?
Comment juger de la capacité de l'optimiseur ?

Saurais-tu expliquer la logique d’exécution de la sous-requête dans un IN ?

Merci

[CinePhil]

À mon avis, le problème de la seconde requête est que la sous-requête dans le IN va être exécutée pour chaque identifiant de produit, alors que la première requête ne va exécuter qu'une fois la sous-requête de la jointure et procéder à la jointure sur le résultat de la sous-requête. La jointure étant une opération très optimisée dans les SGBD, la première requête me semble a priori plus efficace mais il faut voir le plan d'exécution de chaque requête. Ne connaissant pas Percona, je ne peux pas aider sur ce dernier point. Mais comme je vois que Percona semble basé sur MySQL, un EXPLAIN de la requête fonctionnera peut-être ?

[dorian53]

À mon avis, le problème de la seconde requête est que la sous-requête dans le IN va être exécutée pour chaque identifiant de produit

Oui, c'est ce que j'imagine également.

Requête 1 via INNER JOIN



Requête 2 via IN

[dorian53]

Je viens de tester avec plus de 3 000 000 de lignes de vente.

Requête 1 via INNER JOIN
Répond en 15 secondes.



Requête 2 via IN
Ne répond pas... timeout.



Vraiment curieux, l'EXPLAIN laisse penser le contraire en terme de performance.

C'est "DEPENDANT SUBQUERY" qui doit correspondre à l'itération de chaque produit pour trouver le MAX.

[SQLpro]

Bonjour,

Deux solutions

1/ Sous-requêtes dans INNER

Celle-ci se comprend très bien, on imagine que la sous-requête est exécutée en 1er pour constituer une "table virtuelle" puis la jointure est réalisée sur deux colonnes.

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM _produit p
INNER JOIN _vente v1
ON p.id = v1.produit
INNER JOIN (
	SELECT produit, max(prix) as prix
    FROM _vente v2
    GROUP BY produit
) as v
ON p.id = v.produit AND v.prix = v1.prix

2/ Sous-requête dans un IN

Celle-ci fonctionne également mais se comprend difficilement... Comment le traitement est-il exécuté ?

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *
FROM _vente v1
INNER JOIN _produit p1
ON p1.id = v1.produit
WHERE v1.prix IN (
	SELECT max(v2.prix)
    FROM _vente v2
    WHERE p1.id = v2.produit
)

3/ Quelle est la requête la plus performante ?

Merci

Ces deux requêtes ne sont absolument pas équivalentes !
1) avec la requête JOIN et l'imbécile SELECT * vous vous retrouvez à avoir toutes les colonnes des 3 tables : _produit , _vente et la table dérivée aliassée v (colonnes aliassée produit et prix). Dans la IN, seules les colonnes des tables _produit et _vente y figure. Ceci a une influence sur les performances.
2) avec l'imbécile SELECT *, vous faites en fait un SELECT ALL *, qui incorpore tous les doublons. Dans le cas de la jointure il peut donc y avoir plusieurs fois potentiellement la même ligne... Alors que dans le cas du IN, une seule ligne sera retournée. Ceci a aussi une influence sur les performances.

Vos requêtes seraient équivalentes si vous les aviez écrites comme ceci :

Code :

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

1
2
3
4
5
6
7
8
SELECT DISTINCT P.*, v1.*
FROM   _produit p
       INNER JOIN _vente v1 
             ON p.id = v1.produit
       INNER JOIN (SELECT produit, max(prix) as prix
                   FROM   _vente v2
                   GROUP  BY produit) as v
             ON p.id = v.produit AND v.prix = v1.prix;

Code :

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

1
2
3
4
5
6
7
SELECT *
FROM   _vente v1
       INNER JOIN _produit p1
             ON p1.id = v1.produit
WHERE  v1.prix IN (SELECT max(v2.prix)
                   FROM   _vente v2
                   WHERE p1.id = v2.produit);

Sur un bon SGBDR, ces deux requêtes étant strictement mathématiquement équivalentes, il en résulterait un même plan d'exécution.

Bref, apprenez le langage SQL... Mon livre peut vous y aider :


A +

[dorian53]

Salut,

Merci pour ton retour.

Il est vrai que le SELECT * influe sur les temps de réponse, dans ces exemples je n'ai pas pris la peine de préciser les champs me concentrant sur la structure différente des deux requêtes. Comme indiquée c'est une version simplifiée d'une requête beaucoup plus complexe gérée par un ORM.

Bien vu pour le DISTNCT, s'il y a plusieurs vente du même produit, au même prix max à la même date.

En tout cas, cela ne remet pas en cause l'énorme différence de performance entre les deux.

[SQLpro]

Ce qui l'explique c'est le choix du SGBD qui est MySQmerde. Si vous étiez sous SQL Server par exemple, l'optimiseur aurait constaté que la demande est identique et l'algébriseur simplifié à même formule finale.
Ce qui aurait conduit au même plan et même performances.

Malheureusement MySQmerde est doté du plus mauvais optimiseur... Cela s'explique :

• L'un coute rien, on en a donc pour son argent
• l'autre coute plus cher et on en a aussi pour son argent !

Conclusion, si vous voulez des performances, et pas récrire toutes vos requêtes, évitez certains SGBDR, MySQL en tête !

A +