Discussion :

[tutomania]

Bien le bonjour à tous.

Je viens solliciter vos lumières au sujet d'un problème de performance dans une boucle php.

Je lis un fichier CSV qui fait 15000 lignes

Je parcours donc ligne par ligne pour récupérer la valeur qui m’intéresse et je fais un update de ma table

Le souci, vous l'aurez compris c'est que je dois faire 15000 update, ce qui représente 0.141 seconde par update .. donc 35 minutes d'éxécution

Je vous met ici le code simplifié

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
 
<?php
$u = $db->prepare("UPDATE table set qte = :stock where id_produit = :id_produit AND taille = :taille AND code_interne = :matiere");	
 
$fic = fopen("export.csv", "r");
while($tab = fgetcsv($fic,1024,';')){
 
$libelle =  $tab['18']; /* Colonne LIBELLE */
$matiere =  $tab['36']; /* Colonne COULEUR  */
$taille =  $tab['42']; /* Colonne TAILLE */
$stock =  $tab['46']; /* Colonne STOCK */
 
$u->bindParam(':stock', $stock, PDO::PARAM_INT);
$u->bindParam(':id_produit', $id_produit, PDO::PARAM_INT);
$u->bindParam(':taille', $taille, PDO::PARAM_STR);
$u->bindParam(':matiere', $matiere, PDO::PARAM_STR);
$u->execute();
}
?>

Je vous demande donc si il est possible d'optimiser tout cela ?
Toute méthode est bonne à prendre, sachant qu'aujourd'hui il y a 15000 lignes, mais demain il y en aura peux être 30000

Merci infiniment pour votre aide

Cdt, Xavier

[darkstar123456]

Salut Xavier,

Difficile de proposer des solutions. Il faudrait voir à quoi sert ce CSV et comment il est modifié ?

Perso, je proposerais une interface en ligne qui permettrait à n'importe qui (qui aurait les accès) de pouvoir mettre à jour la base de données.
Le CSV, s'il a une réelle utilité, pourrait alors être généré depuis la base de données.

[tutomania]

Bonjour darkstar123456, merci pour ton message.

En fait ce CSV est un export du stock usine, d'un logiciel fournisseur.

Je dois lire chaque ligne pour updater le stock sur le site internet, pour avoir un stock temps réel.
J'ai donc une cron qui s’exécute toutes les heures.

Ce process est non modifiable, il faut que je trouve un moyen de faire ces 15k update de manière la plus performante possible

Soit en splittant le fichier, en faisant des requêtes multiples ou autre, je ne sais pas trop comment faire mieux que ce que j'ai déjà mis en place.

[sabotage]

Déjà 0,1s par update ça me parrait énorme.
Est-ce que le colonnes dans ton WHERE sont de bons types et sont indexées ?

Si ce n'est pas le cas, désactive également l'émulation de préparation (PDO::ATTR_EMULATE_PREPARES).


Une autre méthode de traitement serait de charger ton CSV avec LOAD DATA INFILE vers une table temporaire puis de faire la mise à jour vers l'autre table.

[tutomania]

Bonsoir Sabotage,

Et bien je dois dire que j'ai pris en compte tes recommandations et je suis passé à 0.054ms

Mon script s’exécute beaucoup plus vite !

J'ai indexé les champs de ma clause where et désactivé PDO::ATTR_EMULATE_PREPARES.

Cela te semble cohérent en terme de temps d'éxecution ?

En tous les cas merci infiniment Sabotage

[sabotage]

Si tu peux nous fournir un dump de la structure de ta table, on pourrait faire un test pour comparer les temps.

[tutomania]

Bonjour Sabotage,

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `pro_matiere_taille` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `id_matiere` int(6) DEFAULT NULL,
  `taille` varchar(10) DEFAULT '',
  `quantite` int(2) DEFAULT '0',
  `id_produit` int(6) DEFAULT '0',
  `code_interne` varchar(50) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `taille` (`taille`),
  KEY `id_produit` (`id_produit`),
  KEY `code_interne` (`code_interne`)
);

Est-ce ceci dont tu as besoin ?

J'ai créé les index avec Navicat, ai-je bien reproduit tes recommandations ? Pardonne mes lacunes en SGBD

Tiens par ailleurs, aurais-tu un nom d'un soft permettant d'administrer ces bases Mysql comme Navicat le fait, un produit performant et gratuit si possible

[Bovino]

MySQL Workbench.

[grunk]

Tu peux également faire tes updates dans des transaction par lot de 100 voir 1000 (à tester), ca devrait encore accélérer le processus.

Voir PDO::beginTransaction PDO#commit PDO#rollback

En pseudo code ca donnerais quelque chose comme :

Code :

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

1
2
3
4
5
6
7
POUR X PAQUETS DE X LIGNES
    COMMENCER TRANSACTION
    POUR X LIGNES
        UPDATE
    COMMIT
    SI EXCEPTION
        ROLLBACK

[tutomania]

@Bovino : Merci pour le soft, je télécharge et test de ce pas.
@grunk : Super, merci je vais me documenter la dessus !

Modérateurs au top sur ce forum !
Merci à vous tous.

Xavier

[ABCIWEB]

1/ Ton premier code donné en exemple n'est pas optimisé. Tu perd le gain de vitesse que peuvent procurer les requêtes préparées en bindant tes variables à l'intérieur de la boucle. Essaie plutôt :

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
<?php
$u = $db->prepare("UPDATE table set qte = :stock where id_produit = :id_produit AND taille = :taille AND code_interne = :matiere");	
 
$u->bindParam(':stock', $stock, PDO::PARAM_INT);
$u->bindParam(':id_produit', $id_produit, PDO::PARAM_INT);
$u->bindParam(':taille', $taille, PDO::PARAM_STR);
$u->bindParam(':matiere', $matiere, PDO::PARAM_STR);
 
$fic = fopen("export.csv", "r");
while($tab = fgetcsv($fic,1024,';')){
 
$stock =  $tab['46'];
$id_produit = $tab['...'];
$taille =  $tab['42'];
$matiere =  $tab['36']; 
 
$u->execute();
}
?>

2/

J'ai indexé les champs de ma clause where et désactivé PDO::ATTR_EMULATE_PREPARES.

C'est vivement conseillé pour tous les scripts en général... de sorte qu'on désactive souvent l'émulateur de préparation dans les paramètres de la connexion

[tutomania]

Merci ABCIWEB