Discussion :

[fredo2009]

Bonjour à tous,

Pour la mise à jour d'un classement entre différents coureurs (au fur et à mesure du passage de la ligne d'arrivée), je déclenche régulièrement une requete.

Ca fonctionne, mais elle est longue (environ 55 sec).
La table à mettre à jour comporte 34000 enregistrements.
Est-ce qu'il y a moyen de l'optimiser?
Pour éviter de faire 34000 requetes update, j'ai tout mis dans 1 seule avec "SET CASE WHEN THEN" sur les 4 champs à modifier.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//initialisation des variables
	$tab_temps=$tab_moyenne=$tab_classement_scratch=$tab_classement_categorie="";
 
	$x=0;
	//on parcours tout le tableau
	foreach($tableau_des_courses as $course) {
		foreach($classement[$course] as $c=>$key) {
			$tab_temps .= "WHEN ".$key['idc_passage_inter']." THEN '".$key['temps']."' ";
			$tab_moyenne .= "WHEN ".$key['idc_passage_inter']." THEN ".$key['moyenne']." ";
			$tab_classement_scratch .= "WHEN ".$key['idc_passage_inter']." THEN ".$key['classement_scratch']." ";
			$tab_classement_categorie .= "WHEN ".$key['idc_passage_inter']." THEN ".$key['classement_categorie']." ";
		}
	}
	$tab_temps .= "ELSE `temps` END";
	$tab_moyenne .= "ELSE `moyenne` END";
	$tab_classement_scratch .= "ELSE `classement_scratch` END";
	$tab_classement_categorie .= "ELSE `classement_categorie` END";
 
	//Et voici la requete	
	$requete = "UPDATE c_passage_inter SET temps = CASE idc_passage_inter $tab_temps, moyenne = CASE idc_passage_inter $tab_moyenne, classement_scratch = CASE idc_passage_inter $tab_classement_scratch, classement_categorie = CASE idc_passage_inter $tab_classement_categorie";

Merci d'avance

[punkoff]

Bonjour,

Perso, je ne lis pas le PHP. Pouvez-vous présenter votre / vos requête(s) SQL uniquement ?

[Fred_34]

Si idc_passage_inter est une clé unique, tu peux tenter de voir combien de temps prends cette requête:

Code :

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

1
2
3
4
5
6
7
INSERT INTO c_passage_inter (`idc_passage_inter`, `temps`, `moyenne`,`classement_scratch`, `classement_categorie`) 
VALUES (x1, x2, x3,x4,x5), (y1, y2, y3,y4,y5), (z1, z2, z3,z4,z5)...
  ON DUPLICATE KEY UPDATE 
    temps=values(temps),
    moyenne=values(moyenne),
    classement_scratch=values(classement_scratch),
    classement_categorie=values(classement_categorie);

[fredo2009]

Alors voilà la requête totale:
http://dl.free.fr/rIIOhCBik

Voici la version dégrossie pour plus de visibilité:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
UPDATE c_passage_inter SET 
 
 temps = CASE idc_passage_inter 
 WHEN 16404 THEN '3:32:40' WHEN 16516 THEN '3:58:9' 
 ELSE `temps` END,
 
 moyenne = CASE idc_passage_inter 
 WHEN 16404 THEN 29.62 WHEN 16516 THEN 26.45 
 ELSE `moyenne` END, 
 
 classement_scratch = CASE idc_passage_inter 
 WHEN 16404 THEN 51 WHEN 16516 THEN 170  
 ELSE `classement_scratch` END,
 
 classement_categorie = CASE idc_passage_inter 
 WHEN 16404 THEN 1 WHEN 16516 THEN 2 
 ELSE `classement_categorie` END

idc_passage_inter est bien une clé primaire
Ca prend environ 8secondes de faire un insert de toutes les lignes avec un insert par paquet de 1000 lignes (donc 30 inserts en gros)
Mais je ne peut pas vider la table puis la remplir avec les mises à jour puisqu'on requête dedans via d'autres scripts.

[CinePhil]

C'est monstrueux comme requête !

SQL est un langage ensembliste et les SGBD sont optimisés pour travailler sur des paquets de données, pas pour envisager l'un après l'autre des cas pour affecter une valeur.

Il serait probablement plus rapide de faire des tables temporaires avec les donnée des conditions et de faire un UPDATE avec jointure sur cette ou ces tables temporaires.

Comme on ne peut pas comprendre la raison des nombres et des valeurs d'UPDATE avec ce que vous nous donnez, on ne peut pas en dire davantage pour le moment.

Quelle logique y a t-il derrière cette série de CASE WHEN ?

Expliquez mieux votre processus et ce que vous essayez de faire avec cette requête.

[fredo2009]

Hum, effectivement ça peut paraître monstrueux.

Voici la requete normal:

Code :

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

1
2
3
 UPDATE c_passage_inter SET temps='3:32:40', moyenne=29.62, classement_scratch=51, classement_categorie=1 WHERE idc_passage_inter=16404;
 UPDATE c_passage_inter SET temps='3:58:9', moyenne=26.45, classement_scratch=170, classement_categorie=2 WHERE idc_passage_inter=16516;
....

Mais je ne peux pas faire 34000 fois cette requête, 1 pour chaque clé (c'est trop long)

La logique du CAS WHEN était d'imbriquer toutes les requêtes update en une seule. Ca a effectivement fait gagner beaucoup de temps, mais peut-on faire mieux et plus propre?

[CinePhil]

Mais d'où viennent ces nombres ?

idc_passage_inter=16404

C'est l'identifiant du coureur qui vient de franchir la ligne d'arrivée ?

temps='3:32:40'

C'est le temps qu'il a réalisé ?

Et ça, c'est quoi ?

moyenne=29.62, classement_scratch=51, classement_categorie=1

Quel est le but de la requête ?

S'il s'agit de reclasser la table à chaque arrivée de coureur, ça ne sert à rien !
Une table SQL est comme un sac de billes : on y enfourne des lignes de données (des billes) mais ensuite il n'y a pas d'ordre a priori dans la table (on ne peut pas retrouver la 20ème bille qu'on a inséré dans le sac).

Il faut penser "ensemble de données". Il sera toujours temps de faire un tri à l'affichage des données.

[fredo2009]

Alors
idc_passage_inter -> C'est la clé primaire. L'identifiant du coureur est dans un autre champ
temps -> c'est le temps qu'il a mis sur le parcours
moyenne -> c'est sa vitesse moyenne en km/h (il est pas à pied mais en vélo)
classement_scratch -> son classement au général
classement_categorie -> son classement dans sa catégorie

Le traitement des données est fait en php et ensuite je met à jour la table

Mais comme tout le monde ne part pas en même temps, à chaque arrivée le classement peut évoluer.

On pourrait effectivement refaire les tris à chaque affichage au lieu d'insérer en dur dans la table.

C'est donc peut-être un mauvais choix au départ de vouloir mettre à jour la table. Mais ça n'empêche pas d'essayer d'optimiser cette requête.

[CinePhil]

Le principe normal est d'insérer au fur et à mesure qu'elles arrivent les données sur l'identifiant du coureur et son temps de parcours.
Ensuite, on calcule en masse le classement et on l'affiche.

Encore une fois, il n'y a pas d'ordre dans une table et en tout cas, on ne met pas à jour toute une table ligne par ligne à chaque fois qu'un coureur arrive !

Si tu veux vraiment enregistrer son classement, tu ne modifie en masse que les coureurs classés après lui Si par exemple après son arrivée il se retrouve classé 30ème, tu fais une requête de ce genre :

Code :

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

1
2
3
UPDATE c_passage_inter
SET classement_scratch = classement_scratch + 1
WHERE classement_scratch > 29

[fredo2009]

Ok merci CinePhil

je vais essayer de mettre à jour au fur et à mesure de l'arrivée.
Ca devrait effectivement mieux passer