Optimisation et Simplification de Requêtes

Version imprimable

Hello à tous,

J'ai besoin de votre avis pour de l'optimisation de requêtes. J'ai déjà jeté un coup d'oeil à http://sqlpro.developpez.com/cours/optimiser/ mais je ne trouve pas mon bonheur.

Je vous donne tout de même le contexte même si je pense qu'en lisant simplement les requêtes il est facile de comprendre la chose : : une base de joueurs de basket (table JOUEUR), avec une liste d'équipe (table EQUIPE), des matchs d'une équipe contre une autre (table MATCH), et une table avec les tirs tenté/réussi de chaque joueur pendant les matchs (table TIR_PENDANT_MATCH).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Table JOUEUR: JOUEUR_ID NOM_JOUEUR Table EQUIPE: EQUIPE_ID Table MATCH: MATCH_ID EQUIPE_ID_1 EQUIPE_ID_2 Table TIR_PENDANT_MATCH: TIR_ID JOUEUR_ID EQUIPE_ID MATCH_ID NOMBRE_POINT_SHOOT_TENTE /* = 1 si lancer franc, =2 si shoot à 2 points, =3 si shoot à 3 points */ TIR_REUSSI_FLAG /* = 1 si shoot réussi, = 0 si shoot raté $/
Requêtes :
1/ Qui sont les joueurs n'ayant jamais marqué ?
2/ Quel joueurs a le plus marqué dans la saison ?
3/ Quel joueur a marqué le plus par match (donc ratio du total des points marqués par nombre de match joué) ?

J'ai mes requêtes de mon côté, mais je pense qu'il y a 10 000 moyens de faire chacune (au niveau des jointures, des sous-req, etc...) et j'aurai aimé avoir votre avis sur ce qui est le plus optimisé :

1/
Code:

1 2 3 4 5 SELECT NOM_JOUEUR FROM JOUEUR a INNER JOIN TIR_PENDANT_MATCH b ON a.JOUEUR_ID = b.JOUEUR_ID HAVING sum(CASE WHEN TIR_REUSSI_FLAG = '0' THEN '0' ELSE '1' END) = '0'
2/
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT a.JOUEUR_ID, NOM_JOUEUR, POINTS_MARQUE FROM JOUEUR a INNER JOIN (SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) AS POINTS_MARQUE FROM TIR_PENDANT_MATCH WHERE TIR_REUSSI_FLAG = '1' GROUP BY JOUEUR_ID) b ON a.JOUEUR_ID = b. JOUEUR_ID WHERE b.POINTS_MARQUE = (SELECT MAX(sub.POINTS_MARQUE) FROM (SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) AS POINTS_MARQUE FROM TIR_PENDANT_MATCH WHERE TIR_REUSSI_FLAG = '1' GROUP BY JOUEUR_ID) sub )
- Il doit y avoir moyen de faire quelque chose avec un RANK() OVER (PARTITION BY JOUEUR_ID ORDER BY SUM(NOMBRE_POINT_SHOOT_TENTE)) AS Partition_point
- Si il y a 2 joueurs ex aequo, je crois que ça ne prend pas le cas en compte

3/
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT a.JOUEUR_ID,NOM_JOUEUR, MOYENNE_POINT FROM JOUEUR a INNER JOIN (SELECT JOUEUR_ID , SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(DISTINCT MATCH_ID) AS MOYENNE_POINT FROM TIR_PENDANT_MATCH GROUP BY JOUEUR_ID) b ON a.JOUEUR_ID = b.JOUEUR_ID WHERE MOYENNE_POINT = (SELECT MAX(MOYENNE_POINT) FROM (SELECT JOUEUR_ID ,SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(DISTINCT MATCH_ID) AS MOYENNE_POINT FROM TIR_PENDANT_MATCH GROUP BY JOUEUR_ID) b )
- Si il y a 2 joueurs ex aequo, pas sur que ça ressorte les deux

Merci d'avance pour votre aide :)

19/07/2014, 08h23
tatayo
Bonjour,
Pour la première, il y a beaucoup plus simple: un joueur qui n'a jamais marqué, c'est celui qui n'a aucun tir réussi.
Donc en SQL:
Code:

1 2 3 4 5 6 SELECT NOM_JOUEUR FROM JOUEUR a left outer JOIN TIR_PENDANT_MATCH b ON a.JOUEUR_ID = b.JOUEUR_ID and b.TIR_REUSSI_FLAG = '1' where b.joueur_id is null
Surtout que dans ta requête, tu fais la somme de chaine, puis tu compares le résultat (qui est un numérique) avec une chaine...
D'ailleurs, vu que ton flag ne peut prendre que 2 valeurs, pourquoi ne pas prendre un booléen ?

Tatayo.
19/07/2014, 08h35
Herman

Yep, très vrai, je peux update vers un BOOLEAN. Est-ce qu'au niveau performance ça y change quelque chose ? Ou c'est plutôt pour rester cohérent avec le sens du champ ?

Merci pour la proposition d'optimisation de la requête 1, j'étais également parti sur du LEFT pour commencer à optimiser, Thanks!

En fait la 2/ et la 3/ n'étaient pas du tout correctes. Ça donne çà en MySQL :

Requête 2/

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a.JOUEUR_ID, NOM_JOUEUR, POINTS_MARQUE
FROM JOUEUR a
INNER JOIN 
	(SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) as POINTS_MARQUE
	FROM TIR_PENDANT_MATCH
	WHERE TIR_REUSSI_FLAG = '1'
	GROUP BY JOUEUR_ID) b
ON a.JOUEUR_ID = b. JOUEUR_ID
WHERE b.POINTS_MARQUE = (SELECT MAX(sub.POINTS_MARQUE) 
						FROM
							(SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) as POINTS_MARQUE
							FROM TIR_PENDANT_MATCH
							WHERE TIR_REUSSI_FLAG = '1'
							GROUP BY JOUEUR_ID) sub
						)

Du coup pour celle-ci, ça me parait très étonnant que je doive parcourir 2 fois la table TIR_PENDANT_MATCH :/

Requête 3:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a.JOUEUR_ID,NOM_JOUEUR, MOYENNE_POINT
FROM JOUEUR a
INNER JOIN 
	(SELECT JOUEUR_ID 
	, SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(distinct MATCH_ID) as MOYENNE_POINT
	FROM TIR_PENDANT_MATCH
	GROUP BY JOUEUR_ID) b
ON a.JOUEUR_ID = b.JOUEUR_ID
WHERE MOYENNE_POINT = (SELECT MAX(MOYENNE_POINT)
					   FROM 
							(SELECT JOUEUR_ID 
							,SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(distinct MATCH_ID) as MOYENNE_POINT
							FROM TIR_PENDANT_MATCH
							GROUP BY JOUEUR_ID) b
						)

J'ai toujours besoin d'aide pour optimiser tout ça :)

22/07/2014, 05h56
Herman

Ça n'inspire personne ? :D

Bonjour

Vous pouvez en effet utiliser RANK()

Pour la 2/ :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 WITH CTE AS ( SELECT NOM_JOUEUR ,RANK() OVER(ORDER BY SUM(NOMBRE_POINT_SHOOT_TENTE) DESC) AS Rang FROM JOUEUR J INNER JOIN TIR_PENDANT_MATCH T ON T.JOUEUR_ID = J.JOUEUR_ID WHERE TIR_REUSSI_FLAG GROUP BY NOM_JOUEUR ) SELECT NOM_JOUEUR FROM CTE WHERE Rang = 1
S'il y a deux exæquo, les deux ressortiront (sinon, vous pouvez utiliser ROW_NUMBER() au lieu de RANK() )

Et donc pour la 3/, il suffit de diviser les points par le nombre de matchs distincts :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 WITH CTE AS ( SELECT NOM_JOUEUR ,RANK() OVER(ORDER BY SUM(NOMBRE_POINT_SHOOT_TENTE) / COUNT(DISTINCT ) DESC) AS Rang FROM JOUEUR J INNER JOIN TIR_PENDANT_MATCH T ON T.JOUEUR_ID = J.JOUEUR_ID WHERE TIR_REUSSI_FLAG GROUP BY NOM_JOUEUR ) SELECT NOM_JOUEUR FROM CTE WHERE Rang = 1
Attention, si un joueur a joué un match pendant lequel il n'a pas marqué de point, son ratio sera faussé... mais avec votre modèle, vous ne pouvez pas faire autrement

Euh... Pour la seconde... Je suis con ou un bête ORDER BY + LIMIT suffit ?

Code:

1
2
3
4
5
6
7
 
SELECT J.JOUEUR_ID, J.JOUEUR_NOM, COUNT(T.NOMBRE_POINT_SHOOT_TENTE) BUTS
FROM JOUEUR J
INNER JOIN TIR_PENDANT_MATCH T ON T.JOUEUR_ID = J.JOUEUR_ID AND TIR_REUSSI_FLAG = '1'
GROUP BY J.JOUEUR_ID, J.JOUEUR_NOM
ORDER BY BUTS DESC
LIMIT 1,1;

Quant à la troisième :

Code:

1
2
3
4
5
6
7
8
9
 
SELECT J.JOUEUR_ID, J.JOUEUR_NOM, SUM(T.NOMBRE_POINT_SHOOT_TENTE) / COUNT(DISTINCT M.MATCH_ID) RATIO
FROM JOUEUR J
LEFT JOIN TIR_PENDANT_MATCH T ON T.JOUEUR_ID = J.JOUEUR_ID AND TIR_REUSSI_FLAG = '1'
INNER JOIN EQUIPE E ON E.EQUIPE_ID = J.EQUIPE_ID
INNER JOIN MATCH M ON M.EQUIPE1_ID = E.EQUIPE_ID OR M.EQUIPE2_ID = E.EQUIPE_ID
GROUP BY J.JOUEUR_ID, J.JOUEUR_NOM
ORDER BY RATIO DESC
LIMIT 1,1;

24/07/2014, 17h54
al1_24

La clause LIMIT n'est pas prise en charge par tous les SGBD et Herman n'a pas précisé celui qu'il utilisait...
24/07/2014, 20h18
StringBuilder

Il a parlé de MySQL il me semble.

Citation:

Envoyé par Herman

En fait la 2/ et la 3/ n'étaient pas du tout correctes. Ça donne çà en MySQL :