SELECT, INSERT et UPDATE sur un très grand nombre de lignes pour faire un classement

Bonjour à tous,

je me pose certaines questions sur ma manière de procéder sur une problématique de classement à un QCM, voilà mon cas:

Je propose des QCM de 10 questions max avec entre 2 à 4 choix possibles, 1 seul des choix étant correct.
Les réponses des utilisateurs sont enregistrés sous la forme d'une chaine de caractères numériques où le numéro inscrit correspond à la radiobox coché (ex s'il coche que les 1ères radiobox de chaque question du QCM => 0000000....)

J'enregistre le tout sous la table suivante => membre_id mediumint(8), qcm_id smallint(4), reponse int(10)
où les champs membre_id, qcm_id sont des index et le couple membre_id,qcm_id est un index unique

(Noter que cette table peut devenir très volumineuse le nb d'utilisateur et de QCM augmentant)

Pour calculer le score des utilisateurs et leur classement, je compare leur réponse au résultat officiel (ex: $bonne_reponse='1234512345';)

Ma problématique est dans ma manière de faire, si celle ci me semble correcte quand il y a peu d'utilisateurs,
je me demande si ce serait toujours le cas, s'il devait y en avoir beaucoup plus (potentiellement des centaine de milliers) (soyons optimistes)

Je procède ainsi, ce sera plus facile à comprendre:

Code:

---

1 2 3 4

$a=strlen($bonne_reponse); // le nb de caractères de la bonne réponse correspondant au nombre de questions du QCM $quoi=array(); // ici sera inséré le score des utilisateurs $zql=array();

---

1) je sélectionne les réponses de tous les membres ayant répondu au questionnaire en une seule requête:

Code:

---

1 2 3 4

$sql='SELECT membre_id,reponse FROM ma_table WHERE qcm_id='.$value; $result=mysql_query($sql) or die('Echec 1'); $num=mysql_num_rows($result);

---

la table ma_table est là où sont enregistrées toutes les réponses des membres aux différents qcm


2) je calcule pour chaque ligne retourné, donc chaque membre, son score en comparant sa réponse à la bonne réponse (je schématise):

Code:

---

1 2 3 4 5 6

while ($Res = mysql_fetch_row($result) ){ $k=0; for($i=0;$i<$a;$i++)if($bonne_reponse[$i]==$Res[1][$i])$k++; $quoi[$Res[0]]=$k; // le score de l'utilisateur membre_id=$Res[0] vaut $k }

---

Notez que l'array quoi (ainsi que ci après l'array zql) pourrait avoir des centaines de millers d'entrée, y aura -t-il une problématique à cela ?

3) pour faire le classement je trie par ordre décroissant le tableau des scores en conservant les clés = id des membres

Code:

---

1 2 3 4 5

arsort($quoi); foreach ($quoi as $key => $val) { $zql[]='('.$key.','.($val*10).')'; // $key représente membre_id et $val son score (ici dans l'ordre décroissant) }

---

4) Après avoir préalablement créé une table spécifique à ce questionnaire-là (donc vide), j'y insère dans l'ordre voulu les membres_id et leur score

Code:

---

1 2 3

$sql2='INSERT INTO qcm_'.$value.' (membre_id,score) VALUES '.implode(',',$zql); $result2=mysql_query($sql2) or die('Echec 2');

---

Question => la requête INSERT multiple est-elle efficace pour des centaines de miliers de lignes à insérer ?

La table specifique serait ainsi faite => id (auto_increment), membre_id (index unique), score // ainsi en lisant l'id du membre, on a son classement

5) Enfin, je dois incrémenter le score total de chaque utilisateur sur une table_score existante, malheureusement je crois être obligé de passé par une boucle:

Code:

---

1 2 3 4 5 6

for($j=0;$j<$num;$j++){   $sql3="UPDATE table_score SET score=score+".$zql[$j][1]." WHERE membre_id='".$zql[$j][0]."'"; $result3=mysql_query($sql3) or die('Echec 3'); }

---

Bref, voilà donc comment je compte procéder, je n'ai finalement que 2 requêtes toutes simples mais pouvant porter sur un très grand nombre de lignes,
ainsi qu'une 3ème requête Update toute aussi simple mais répétée potentiellement un très grand nombre de fois

Voyez-vous des moyens d'améliorer tout cela, voyez-vous des erreurs ou des améliorations que je pourrais porter à ce script ?
Pour faire court, vos avis, critiques et conseils seront la bienvenue

PS: ce script sera executé en tâche cron pendant la nuit vers les 3h du mat', l'idéal est biensur qu'il dure le moins longtemps possible,
mais si vous pensez qu'il durerea un temps raisonnable sur le serveur dédié suivant http://www.soyoustart.com/fr/offres/sys-e32-1.xml , cela me conviendra

D'ailleurs si vous pourriez me conseillé des modif à effectuer du côté de mysql je suis preneur aussi

Merci d'avance
A bientôt

En essayant de lire attentivement ta méthode, deux, trois réflexions pour ma part :
Le champ reponse doit être varchar 10 et non int
Je ne vois pas bien l'intérêt de faire un cron.
On peut traiter tout ça en sql à travers une vue :
1 tuple= 1 score par joueur et par qcm //surtout pas de order by à ce stade, trop consommateur

Code:

---

1 2 3 4 5 6 7 8

create or replace view v_qcm as select matable.qcm_id, matable.user_id, ( (if( substr(b_rep,1,1)=substr(rep,1,1),1,0)) + (if( substr(b_rep,2,1)=substr(rep,2,1),1,0)) + (if( substr(b_rep,3,1)=substr(rep,3,1),1,0)) + etc) as score from matable inner join bonne_rep on matable.qcm_id=bonne_rep.qcm_id group by matable.qcm_id,matable.user_id

---

La dernière opération se résumerait alors à une requête sur ta vue (pour cumuler tous les scores qcm par joueur)

Code:

---

1 2 3 4 5 6 7

select user_id, sum(score*10) as total /*J'ai cru comprendre que c'était 10 pts par bonne réponse ?*/ from v_qcm group by user_id order by total desc

---

Bonjour et merci de t'être intéressé à mon pb,

je n'utilise jamais les view et ne m'y connais donc pas trop, mais en me renseignant sur le net j'ai pu constater que seule la requête était stocké et non ses résultats. Il semblerait donc - corrige moi si je me trompe - qu'on ne gagne absolument rien en terme de performance en utilisant une vue plutôt qu'en faisant une requête directement sur les tables.

Par exemple pour savoir son classement, chaque membre devrait effectuer à chaque fois cette requête sur l'ensemble des tables concernées.
Je pense que ça deviendrait une vraie usine à gaz passé un certain nombre de membres simultanés

C'est pour cela que je voulais faire mon procédé en tâche cron la nuit, je créé une seul fois une table avec le classement des membres et cette table plus petite ne serait accessible qu'en lecture et plus jamais modifiée.

J'avais un soucis sur ma phase 5) pour l'UPDATE des scores des membres, j'utilisais une boucle for.
Il semblerai qu'en utilisant une reqûete préparé, je gagne grandement en performance. Le premier appel à cette requête serait traité plus longuement, mais les autres beaucoup plus rapidement. Quand il peut y avoir potentiellement des centaines de milliers d'UPDATE à faire, je vais creuser de ce côté là.

Bref, pense tu que mes 3 requêtes du premier post sont d'un point de vue des performances intéressantes ou j'ai tout faux ?

J'entend par là :
- selectionner potentiellement des centaines de milliers de lignes (et faire un traitement avec les resultats retournés)
- faire un insert multiple avec implode portant toujours sur ce très grand nombre de ligne
- enfin l'update final mais en utilisant une requête préparée

Encore merci

Citation:

---

je n'utilise jamais les view et ne m'y connais donc pas trop, mais en me renseignant sur le net j'ai pu constater que seule la requête était stocké et non ses résultats. Il semblerait donc - corrige moi si je me trompe - qu'on ne gagne absolument rien en terme de performance en utilisant une vue plutôt qu'en faisant une requête directement sur les tables.

---

Tu as tout à fait raison, je ne te proposais pas la vue pour alléger tes performances, mais pour faire porter par SQL ce qui peut l'être, bref, pour y gagner en clarté dans ton code. Une vue est une simple requête. Ceci dit, il faut que tu passes (au moins une fois par jour) par cette requête.

Maintenant, que les autres m'arrêtent si je dis une bêtise grosse comme moi, mais ton cron de classement chaque nuit ferait simplement ceci :

J'écris juste l'algo :

connexion
taper dans la v_qcm des tuples du genre (user_id,score) order by score desc,user_id (il y aura sans doute plein d'ex-aequo... pour l'instant, on gère pas)
(voir la requête plus haut, et bien sûr, une requête sur une vue est tout simplement : une sous requete, mais moi, je suis fan des vues parce que j'aime bien comprendre ce que je fais. :) )

boucler dessus

avec une sous boucle compteur de tous les 1000

et insérer ça dans une table classement (avec un id auto-_increment qui sera précisément le numéro de classement, c'est ici que tu pourras rajouter un champ si tu veux vraiment faire apparaître les ex-aequo plutôt que de te servir de l'auto increment) table qui sera remplacée tous les soirs...

Ainsi tu figes effectivement ton classement une fois par nuit.
Combien de temps va tourner ce cron avec des centaines de milliers d'enregistrements ? J'ai hâte que tu nous le dises. :)
Peut-être faudra-t-il aussi penser à rendre le site indispo pendant que le cron tourne ?
En tous cas, quand les gens naviguent, ça roule bien, une simple requête dans cette table classement par user_id, tu rentres en session... ça devrait rester bien fluide.

Un dernier truc qui me semble important : tu dois vraiment indexer (seul) le champ reponse indépendamment de ton index sur 2 colonnes pour l'unicité... Que les autres me disent encore une fois si je me trompe, mais il me semble que c'est un plus vu comment tu vas solliciter ce champ précis.

Et c'est moi qui te remercie pour ton sujet car je vais bientôt avoir un cas similaire à développer.

Re, si ce sujet peut te rendre service, tout le monde est gagnant ;-)

Citation:

---

Envoyé par Dendrite

connexion
taper dans la v_qcm des tuples du genre (user_id,score) order by score desc,user_id (il y aura sans doute plein d'ex-aequo... pour l'instant, on gère pas)
(voir la requête plus haut, et bien sûr, une requête sur une vue est tout simplement : une sous requete, mais moi, je suis fan des vues parce que j'aime bien comprendre ce que je fais. :) )

boucler dessus

avec une sous boucle compteur de tous les 1000

et insérer ça dans une table classement (avec un id auto-_increment qui sera précisément le numéro de classement, c'est ici que tu pourras rajouter un champ si tu veux vraiment faire apparaître les ex-aequo plutôt que de te servir de l'auto increment) table qui sera remplacée tous les soirs...

---

En fait je ne fais aucun order by, je sélectionne uniquement l'user_id et sa réponse au QCM qcm_id, ceci dans la table principale.
A partir des donnés retournées, je calcule le score en l'insérant dans un array. C'est cet array que je trie via php de façon décroissante avec la fonction arsort(), avec laquelle j'obtiens un nouvel array contenant dans l'ordre voulu l'user_id et son score (zql[]=(user_id,son_score), voir post1 - 3))

Soucis 1: l'array peut contenir potentiellement des centaines de milliers d'entrées, problème mémoire en vue ??

Ensuite, j'insère effectivement les données de ce tableau dans une table de classement tel quel, le tri dans l'ordre décroissant étant déjà fait. Mais c'est vrai que la problématique sur les ex-aequo se posera. Je pense les départager par leur score total, score résultant de tous les QCM précédents (celui ayant le plus grand score est forcément meilleur), faudra m'arranger.

Citation:

---

boucler dessus

avec une sous boucle compteur de tous les 1000

---

Par contre ici, je n'arrive pas à te suivre, si tu pouvais m'éclairer ?

Citation:

---

Un dernier truc qui me semble important : tu dois vraiment indexer (seul) le champ reponse indépendamment de ton index sur 2 colonnes pour l'unicité... Que les autres me disent encore une fois si je me trompe, mais il me semble que c'est un plus vu comment tu vas solliciter ce champ précis.

---

Je n'ai pas indexé le champs réponse étant donné que je ne m'en sers jamais dans la clause WHERE. Quels que soient les 3 requêtes que j'utilise, le WHERE porte toujours sur l'user_id et/ou le qcm_id, qui sont eux indexés.

Citation:

---

Combien de temps va tourner ce cron avec des centaines de milliers d'enregistrements ? J'ai hâte que tu nous le dises. :)
Peut-être faudra-t-il aussi penser à rendre le site indispo pendant que le cron tourne ?

---

C'est justement le but de cette démarche, minimiser au max le temps du script. Mais lançant le cron à 3h du mat, il ne risque pas d'y avoir grand monde au début (soyons optimiste, le succès international donc multi fuseaux horaires est possible ^^). J'espère toutefois que le temps d'éxecution du script sera faible, sous les 5mn se serait bien, tu penses cela possible ?

Toutes les requêtes sont en elle-mêmes vraiment très simple, et l'update du score de chaque membre qui provoque potentiellement des centaines de milliers de requêtes utilisera une requête préparée...

Merci encore

Citation:

---

Soucis 1: l'array peut contenir potentiellement des centaines de milliers d'entrées, problème mémoire en vue ??

---

C'est bien pour ça que moi, j'utiliserai une vue : cela réduit la contrainte PHP. (bon, comme tu vas voir plus loin, elle va bien arriver un jour, mais le moins souvent est le mieux)...
La contrainte mysql est bien suffisante. Les calculs ou l'ordonnancement : c'est mysql.
Si quelqu'un de plus compétent que moi peut te conseiller une procédure stockée pour rester intégralement en SQL, je suppose que c'est cela qu'il faut viser...
Je crois que pour ma part, je me tournerai vers ça quand j'en serai à coder. Fais une recherche procédure stockée mysql : n'oublie pas que toutes tes infos pour figer ton classement sont déjà dans ta base au moment du cron : du coup, pourquoi s'embêter avec du PHP ?

Perso, je vais bosser ça :
http://dev.mysql.com/doc/refman/5.0/...re-syntax.html

En attendant :
Toute ma réponse est axée sur la vue, si tu ne prends pas la vue... mes réponses ne te concernent pas.
Mais je ne vois pas ce qui te bloque : dans un phpmyadmin, tu lances la requête de création proposée ci-dessus et zou, elle s'affiche un peu comme une table, à part l'icône.

Citation:

---

avec une sous boucle compteur de tous les 1000

---

Tu souhaitais que je développe ceci, que tu devras utiliser même si tu ne prends pas la vue.

Tu as une boucle s'appuyant sur une requête ordonnée en score desc, du genre

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

$sql='SELECT user_id, sum(score) AS total FROM v_qcm GROUP BY user_id ORDER BY total DESC;'; //j'abrège hein tu sais boucher les trous $scores=array(); //Et là, ça passe ou ça casse :) //un tableau php potentiellement énorme, mais avec deux champs seulement... while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){ array_push($scores,$row); } //après, ça ne peut que passer //tu places un compteur $i=1; $sql=''; //bien sûr, ici, il faudra vérifier la taille de $scores //et s'il est conséquent, vider la table classement pour la remplir avec ce qui suit foreach($scores as $row){ //tu concatènes tes insertions $sql.=$row['user_id'].','.$row['total'].','; if($i%1000==0){ //tu rédiges ta requête et tu lances vraiment ton insertion dans ta table classement en auto-increment $insert='insert into blabla '.$sql; //ici tu rédiges l'éxécution de l'insertion $sql=''; } $i++; } //bien sûr à la fin, ne pas oublier son dernier paquet if($sql!=''){ $insert='insert into blabla '.$sql; //ici tu rédiges l'éxécution de l'insertion }

---

Merci encore de ta réponse,

j'utilise déjà les procédures stockées pour tout autre chose de bien plus simple, il faudrait que je m'y penche dessus pour voir comment m'en servir pour cette problématique.

Pour la contrainte php portant sur la taille des array, je l'ai résolu en test en augmentant sur le php.ini la valeur memory_limit (Maximum amount of memory a script may consume).
En la portant à 2048MB soit 2 Go, j'ai pu créer un array à plus de 20 millions d'entrées en 6s en local sur mon simple laptop sous windows 7 avec 4Go et wampserver.

Mon serveur dédié dispose de 32 Go, et le script sera utilisé en tache cron à 3h du mat, ça devrait aller de ce point de vue là.

Je ne suis pas trop fan des Vues, il faudrait vraiment que je me penche dessus, comme je te l'avais dit je ne les utilise jamais.

Bref, j'ai amélioré mon script de classement qui prend désormais en compte les ex-aequos, l'array zql contiendra désormais 3 entrées: user_id, son score et son rang. Si le score est identique à celui précédant, la variable rang n'est pas incrémentée sinon oui.

Pour l'update de la table des scores, j'utilise une requête préparée et pour le premier select, je vais partitionner la table horizontalement de façon à ce que les QCM actifs soient séparés de ceux inactifs

En théorie mon script devrait fonctionner et être relativement rapide pour faire le classement d'un grand nombre d'utilisateur, au moins quelques dizaines de milliers.

Merci encore pour tes conseils