Discussion :

[marco62118]

Bonjour à toutes et tous

Je crée un Nouveau Post pour ma nouvelle question concernant la performance

j'ai enfin réussi à faire ce que je veux (faire un classement d'une donne précise) ce qui peut intéresser d'autres débutant
départ: faire 2 COUNT "NscoreSup et NMemescore" sur une même colonne "score" d'une table avec des auto-jointures, pour utiliser ces 2 COUNT pour faire un classement.
Ce qui est important c'est les différents GROUP BY des 2 auto-jointures et la première jointure LEFT et enfin le GROUP BY pour l'affichage

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT pl.pseudo, t1.score 
      ,((100*((@np:=(SELECT nbrePlayer FROM donnes WHERE id=1)-1) - COUNT(t2.id_players) - (0.5*(t3.NMemescore-1))))/(@np-1)) AS classement
FROM (SELECT* FROM donnes_players WHERE id_donnes=1) AS t1
    LEFT JOIN (SELECT id_players,score,id_donnes FROM donnes_players WHERE id_donnes=1 group by id_players)AS t2	
    ON  t2.score>t1.score 
    INNER JOIN (SELECT id_players,score,id_donnes,COUNT(id_players) AS NMemeScore FROM donnes_players WHERE id_donnes=1 GROUP BY score)AS t3	
    ON  t3.score=t1.score 
    INNER JOIN (SELECT pseudo FROM players) AS pl
    ON t1.id_players=pl.id
GROUP BY t1.id_players
ORDER BY t1.score DESC ,pl.pseudo ASC

Cette requête sera appelé par tous joueurs (un millier) pour voir son classement pour une donne précise avec id_donnes.
Maintenant mon problème est la performance car cette requête s'exécute en plus de 2 secondes avec l'affichage.

J'ai bien pensé aux Vues mais on ne peux pas passer de paramètres et là il me faut l'id_donnes que le joueur veut voir le classement.
IL y a les requêtes préparées mais je ne vois pas bien la rapidité car juste avant d’exécuter la requête il faut transmettre l'id_donnes et tous les joueurs peuvent vouloir un classement d'une donne différente. Un même joueur ne fera appel au résultat de la requête qu'une fois par connexion à la base.
Les requêtes stockées c'est la même chose à chaque fois qu'un joueur veut son classement il fournit l'id_donne qu'il veut consulter .

Si Vous avez des pistes pour améliorer la performance merci d'avance

[mathieu]

déjà j'ai l'impression qu'il y a top de "SELECT", il faudrait essayer de simplifier la requête en utilisant le maximum de jointures

ensuite il faudrait trouver dans le calcul du score ce qui ne change pas souvent et le mettre en cache.

[marco62118]

merci pour ton intérêt.

J'ai modifié légèrement mais ça n'a pas modifié le temps.
Je pense:
que le premier SELECT permet d'afficher ce que je cherche.
que les trois SELECT qui suivent, permettent de définir la même table "donnes_players" pour y effectuer les 2 auto-jointures
que les SELECT derrière les INNER permettent de limiter la table sur la quelle s'exerce la jointure

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT pl.pseudo, t1.score ,(((100*((d.nbrePlayer)-1) - COUNT(t2.id_players) - (0.5*(t3.NMemescore-1))))/(d.nbreplayer-1)) AS classement
FROM (SELECT* FROM donnes_players WHERE id_donnes=1) AS t1
	LEFT JOIN (SELECT id_players,score,id_donnes FROM donnes_players WHERE id_donnes=1 group by id_players)AS t2	
    ON  t2.score>t1.score 
    INNER JOIN (SELECT id_players,score,id_donnes,COUNT(id_players) AS NMemeScore FROM donnes_players WHERE id_donnes=1 GROUP BY score)AS t3	
    ON  t3.score=t1.score 
    INNER JOIN (SELECT id,pseudo FROM players) AS pl
    ON t1.id_players=pl.id
    INNER JOIN (SELECT id, nbrePlayer FROM donnes )AS d
    ON d.id=t1.id_donnes
    WHERE d.id=1
GROUP BY t1.id_players
 
ORDER BY t1.score DESC ,pl.pseudo ASC

ensuite il faudrait trouver dans le calcul du score ce qui ne change pas souvent et le mettre en cache.

1) je ne sais pas comment mettre en cache quelque chose.
2)justement dans le calcul du classement lorsqu'un joueur a fini une partie tous les paramètres changent:
- le nbrePlayers a été incrémenté au moins de 1 et lorsque le joueur interroge le classement le nbre de players a encore pu varier donc obligation de récupérer dans donne_players la dernière valeur.
- Le Nbre de score supérieur et le nombre de même score c'est idem il doivent être recalculés juste au moment ou le joueur consulte le classement

et pour ce qui est des vues ,requêtes préparées ????


j'avais bien pensé à créer une vue où bien sûr je ne fixerais pas le numéro de la donne (id_donnes) à 1 et je fixerai le numéro de la donne lorsque j'appellerai cette vue.
Mais pour faire des vues il ne faut pas de sous requête j'ai donc modifié le code Et là le moteur pédale dans la choucroute depuis (je ne sais comment l'arrêter)
voici le code

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT pl.pseudo, t1.score ,(((100*(d.nbrePlayer-1) - COUNT(t2.id_players) - (0.5*(COUNT(t3.id_players)-1))))/(d.nbreplayer-1)) AS classement
FROM  donnes_players  AS t1
	LEFT JOIN  donnes_players  AS t2	
    ON  t2.score>t1.score 
    INNER JOIN  donnes_players   AS t3	
    ON  t3.score=t1.score 
    INNER JOIN   players AS pl
    ON t1.id_players=pl.id
    INNER JOIN  donnes AS d
    ON d.id=t1.id_donnes
	WHERE d.id=1
GROUP BY t1.id_players,t3.score
ORDER BY t1.score DESC ,pl.pseudo ASC

[Invité]

Bonjour,

Ta requête est imbitable incompréhensible, voire une usine à gaz.

De plus, si tu nous donnais des extraits utilisables des tes tables, on pourrait tester.
Là, un.

[marco62118]

je veux afficher un classement basé sur un calcul:
((100*((d.nbrePlayer -1) - (t2.NscoreSup) - (0.5*(t3.NMemescore-1))))/(d.nbrePlayer-1)).
-la table principale "donnes_players" affiche toutes les donnes joués par tous les joueurs (16 donnes jouées par 1000 players, par jour)
on y trouve le score de chaque joueur pour chaque partie ex id_donnes=1 id_players =1 --> score=37
pour ce score 37 du player 1 de cette donne 1 il y a 15 autres players qui le même score (NMemescore)
et aussi 273 players qui ont un score strictement supérieur à 37 (NscoreSup)
-dans la table "donnes" pour chaque identifiant de donne j'incrémente le nbre de joueur qui ont terminé cette partie ex pour cette donne 1 il y a 1000 players (nbrePlayer) qui ont terminés cette partie
-la table "player" permet de récupérer le pseudo.

j'ai mis des extraits d'images de mes tables. je ne sais pas comment vous fournir les tables elles mêmes

table donnes_players


table donnes


Table players


Je me suis basé sur des exemples trouvés sur le net que j'ai essayé d'adapter car aucun ne correspondait vraiment à mon problème faire :
1)compter le nombre de score supérieur au score de la ligne d'une table et pour chaque ligne de la table
2) compter aussi le nombre de même score au score de la ligne d'une table et pour chaque ligne de la table
j'ai galéré pour arriver à ce résultat "imbi..ble"

[Invité]

lol......
Que veux-tu qu'on fasse avec des copies d'écran des tables...

Mais si j'ai à peu près bien compris, la requête se résume à :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
  pl.pseudo, 
  t1.score,
  ( SELECT COUNT(*) FROM donnes_players AS t2 WHERE t2.score > t1.score ) AS NscoreSup,
  ( SELECT COUNT(*) FROM donnes_players AS t3 WHERE t3.score = t1.score ) AS NMemescore,
  ( ((100*(d.nbrePlayer-1) - NscoreSup - (0.5*(NMemescore-1))))/(d.nbreplayer-1) ) AS classement
FROM donnes_players AS t1
INNER JOIN donnes AS d
  ON d.id = t1.id_donnes
INNER JOIN players AS pl
  ON pl.id = t1.id_players
WHERE d.id = 1
GROUP BY t1.id_players, t1.score
ORDER BY t1.score DESC, pl.pseudo ASC

[marco62118]

Ah tout de même c'est un plus efficace 0.39 secondes

j'ai eut un message d'erreur NscoreSup n'est pas connu dans la liste !!
j'ai remplacé par tout ( SELECT COUNT(*) FROM donnes_players AS t2 WHERE t2.score > t1.score )
même chose pour NMemeScore.


Je croyais que j'étais obligé de faire une auto jointure pour avoir la table t2 et t3


encore Merci

[marco62118]

je n'ai pas réussi à envoyer les tables exportées au format sql, pourtant les fichiers ne sont pas si gros 181 ko

[marco62118]

petite rectification en fait le temps c'est 1,08 secondes
mais si j'enlèves les colonnes Nscoresup et Nmemescore je passe à 0.6 secondes

c'est impeccable encore merci