Boucle & Update - Optimisation

[xanthius]

Bonjour,
Air Carrier commençant à se développer je cherche pas tous les moyens possibles à optimiser au mieux mes nombreuses requêtes Update.
Actuellement je suis la "méthode" consistant à insérer au sein d'une boucle une condition et en fonction de cette dernière d'effectuer un update.
Exemple :
Pour mes demandes je fais quelque chose de très simple
j'ai 90 en demande initiale et deux vols, l'un 80 sièges et l'autre 40.
je boucle et insère la condition suivante : si offre supérieure à la demande alors on prend toute la demande sinon on prend l'offre. On obtient ce que l'on a dans l'avion mais si c'est négatif bien évidement ça sera égal à 0.
Si on poursuit mon exemple on aurait pour pour le premier 80 sièges et le second 10 sièges soit 90.

En soit rien de compliqué or on arrive à un point où l'on se retrouve avec une requête contenant 30 milles UPDATE. Je vois partout qu'il ne faut pas faire de requête dans une boucle, je cherche donc une solution, d'où mon post, n'y a t'il pas moyen d'éviter autant de requête ?

[Xenos]

Je n'ai pas franchement compris l'exemple, mais le soucis des boucles de requête est réel et double:
• 1000 petites requêtes hors du SQL (dans le PHP) impliquent 1000 accès réseau
• Le SQL n'est pas optimisé pour traiter 1000 petites requêtes, mais plutôt pour traiter 1 grosse requête sur 1000 éléments

Le premier problème peut être très simple à régler, par une procédure stockée (tu passes ton 90 et d'autres éventuelles entrées à la procédure stockée, qui contiendra le WHILE/la boucle de requête). C'est pas optimal, mais c'est généralement très rapide à faire et cela peut patcher un serveur avant qu'il ne se meurt (on dégage alors du temps pour repenser et refactorer proprement le réel problème).

Le second problème se règle par l'intelligence (donc, pas de méthode générale). Comme j'ai pas vraiment compris l'exemple (pour "l'intelligence", c'est rate ^^), je serai peut-être à côté de la réponse, mais il me semble que dans l'idée, tu as 90 passager qui veulent prendre un des avions parmi une liste d'avions possible (ici, 2, qui répondent à une clause genre WHERE avion.id_joueur = ?). Chaque avion a une capacité maximale (avion.capa, ici, 80 et 40 passagers) et éventuellement, il y a déjà des passagers dans l'avion (avion.passagers peut être nul ou positif).
Tu voudrais savoir combien de passagers on a au final dans chaque avion, et éventuellement, le nombre de passagers sans avion.
Je considèrerai qu'il y a une règle d'ordonnancement des avions à remplir (ORDER BY ...)

Jusqu'ici, tu fonctionnes selon (en gros):

Code :

$passagers = 90;
while ($passagers > 0) {
    UPDATE avion SET passagers = MIN(capa, $passagers) WHERE avion.id = NEXT_AVION();
    $passagers -= avion.capa
}

Je dirai que cela peut se réécrire sous la forme:

SET @passagers := 90;



UPDATE avion 

SET passagers = (@dansAvion := LEAST(capa, @passagers)) + 0*(@passagers := @passagers - @dansAvion)

WHERE ...

ORDER BY ...;



SELECT @passagers; -- Passagers sans avion

L'idée ici est de calculer, ligne par ligne, le nombre de gens qui prennent l'avion (@dansAvion), de définir que cette valeur est le nombre de passagers de l'avion, et décrémenter cette valeur du nombre total de passagers (@passagers).
Je ne sais pas si on peut esquiver l'astuce du "0*..." pour définir la nouvelle valeur de @passagers. Je suppose qu'on devrait aussi pouvoir optimiser un peu pour que le SQL comprenne qu'il n'a plus besoin de traiter les lignes de la table dès que @passagers est à 0.

[xanthius]

Bon je ré-explique alors ^^
je suis le joueur avec un ID 1
je possède une ligne, disons CDG-TLS auquel j'ai une demande pour 90 passagers
j'y place deux avions ayant deux capacités différentes, le premier peut transporter 80 passagers et le seconde 40.
Pour déterminer combien de passagers j'ai dans mes deux appareils je procède de la sorte :

je sélectionne la demande pour toutes les lignes ainsi que les avions qui y sont placés. Au sein de ma boucle je vais attribuer les passagers au sein des avions qui sont présents sur chacune des lignes. Quand la boucle arrivera à ma ligne (CDG-TLS) elle traitera cas par cas les avions utilisant cette ligne.
D'abord le premier ayant 80 sièges de disponibles. Le nombre de siège étant inférieur à 90, tout mon avion sera complet (80 sièges) - (petit traitement PHP pour savoir le nombre de passagers puis UPDATE). On arrive au suivant, là je vais calculer combien il me reste en demande, soit 10. Je compare avec la capacité de mon second appareil et là je ne peux pourrais en prendre que 10, de nouveau petit traitement PHP pour savoir le nombre de passagers puis UPDATE

Au final, dans cet exemple j'aurai effectué deux updates pour deux appareils. Le souci est qu'actuellement on a 30 milles vols, je ne peux pas faire 30 milles UPDATE, fin si je peux mais c'est long (évidement) et franchement pas optimisé (vu ce que je lis un peu partout)

[Xenos]

Ok, j'avais bien compris finalement, donc je maintiens ma réponse

Note que j'ai l'impression qu'il s'agit d'un algo de simulation de la planète entière (toutes les lignes existantes), donc à mon sens, tu peux déjà simplement le transcrire en procédure stockée et la lancer, au lieu de faire le calcul côté PHP.

C'est exactement le même problème qu'ECLERD v0 (où, en gros, je lisais toute la BDD pour ensuite simuler l'avancement de chacune des régions), au détail près que tu ne calcules pas des régions/pays mais des lignes d'avion. La solution de la procédure stockée est alors très efficace, d'autant plus quand tu la croise avec les queries SQL utilisant des variables (cf ma réponse précédente).

Et sinon, si tu veux rester sur ton archi actuelle, tu peux passer par INSERT INTO ... (id, passagers) VALUES (...) ON DUPLICATE KEY UPDATE passagers = VALUES(passagers), cf la doc MySQL: https://dev.mysql.com/doc/refman/5.7/en/...icate.html
L'idée est alors de batcher tes UPDATES coté PHP (tu ne fais pas l'update, tu stockes juste la valeur dans un tableeau PHP avec l'id de l'avion) et de ne réaliser les requête UPDATE qu'à la fin de l'algorithme, sous la forme de cet INSERT.

[xanthius]

J'ai pas trop compris ..
j'aurai ma boucle mais une seule requete ?
si par exemple j'ai 1000 vols sur 3 lignes différentes
je n'aurai plus qu'un UPDATE ou bien 1000 UPDATE ?
Si mise à jour il y a, comment determiner que c'est le vol avec l'ID 12 qui doit être avoir 80 passagers et l'ID 13 10passagers au sein de cette unique requete

Tu aurais un exemple ? car là c'est le flou ..


Edit :
Xenos MERCIIIII !!!! c'est fou ! D'après mes premiers tests ça semble super bien fonctionner, une seule requête, un temps d'exécution fou ! Mes joueurs vont être ravis !

[xanthius]

Bon j'ai encore quelques soucis, ça m'apprendra à crier victoire trop tôt ..
En fait je bloque à l'actualisation. Auparavant après chaque traitement je faisais un UPDATE pour générer la nouvelle demande restante après qu'on ait prélevé les sièges du précédent appareil et je recommençais or là je ne fais plus qu'une requête, plus d'UPDATE entre chaque traitement. Bon c'est pas compréhensible là ..
En gros par l'exemple vu ci dessus.
deux appareils, l'un de 80 et l'autre de 40 sièges sur une ligne avec une demande 90
Avant je traitais le premier puis modifié la demande restante avec un update pour recommencer mes calculs avec cette nouvelle valeur or là la valeur reste à 90 et ne sera modifié qu'à la fin..
Comment procéder ?

Edit précision :
j'ai fais une petite table de test pour voir et en fait je me rend compte que pour cette requête :
INSERT INTO demande_eco (id_demande_eco, lundi_eco_restant, mardi_eco_restant, mercredi_eco_restant,jeudi_eco_restant, vendredi_eco_restant, samedi_eco_restant, dimanche_eco_restant) VALUES (2,240 - 130,240,240,240,240,240,240), (2,240,240 - 130,240,240,240,240,240) ON DUPLICATE KEY UPDATE lundi_eco_restant = VALUES(lundi_eco_restant), mardi_eco_restant = VALUES(mardi_eco_restant), mercredi_eco_restant = VALUES(mercredi_eco_restant), jeudi_eco_restant = VALUES(jeudi_eco_restant), vendredi_eco_restant = VALUES(vendredi_eco_restant), samedi_eco_restant = VALUES(samedi_eco_restant), dimanche_eco_restant = VALUES(dimanche_eco_restant)

seule la dernière partie "(2,240,240 - 130,240,240,240,240,240)" est sauvegardée, n'y a t'il pas moyen d'enregistrer les deux à la suite ? ^^'

[Xenos]

C'est logique que "le dernier seulement soit enregistré": tu essaies d'updater la même ligne (id 2) deux fois, donc la dernière écrasera la précédente. Il faut que tu combines les deux avant de les enregistrer, pour avoir un VALUES (2, 240-230, 240-130, 240, 240, 240, 240, 240).

PS: je trouve très étrange d'avoir à moitié du calcul dans le SQL et à moitié dans le PHP... Soit le PHP fait le calcul (et tu sauves des 110 directement) soit le SQL fait le calcul (et ton 240-130 n'a plus besoin de boucle PHP)...

[xanthius]

Oui c'est vrai que c'est logique en effet..
Du coup pour combiner comment procéder ?

Là je suis paumé

pour le calcul en fait au début je ne comprenais pas donc j'essayai à taton ^^'

[xanthius]

A force de chercher, de tester une multitude de choses, je suis parvenu à faire ce que tu m'as dis.
je stocke dans un tableau les données de ma table
je fais ma boucle et mes calculs en cascade en faisant mes UPDATES sur mon tableau php et donc pas d'utilisation de MYSQL
à la fin quand j'ai toutes mes données, je fais un énorme INSERT ON DUPLICATE KEY UPDATE.
Je viens de finir, je ne crie pas victoire, du moins pas encore. Je test encore sur mon serveur de développement mais d'après ce que je peux voir tout est bon ! J'ai les bonnes données et cela dans un temps hyper réduit c'est hallucinant ! Reste encore à tester et étendre à mes deux autres classes.

Chapeau ! J'ai eu du mal à comprendre ce que tu me disais mais en cherchant je suis parvenu à ton résultat !

[Xenos]

C'est l'essentiel, si t'as réussi à comprendre le principe du ON DUPLICATE KEY UPDATE.

Après, comme dans toute approche où on extrait les données du SQL pour calculer dessus avant de les renvoyer dedans, tu finiras peut-être par avoir des emmerdes dans les accès concurrents aux données, type "que se passe-t-il si ce script est lancé deux fois par deux joueurs différents? Et deux fois par le même joueur? Si un des avion change pendant que PHP est dans sa boucle de calcul?". Pour un jeu, osef un peu et cela se résout avec un SELECT FOR UPDATE ( https://dev.mysql.com/doc/refman/5.7/en/...reads.html ) mais professionnellement, c'est la raison pour laquelle je n'aime pas cette façon de sortir les données du SQL pour calculer dessus avant de les y réinjecter.