Optimiser la mise à jour de la Base

**nguim** · 25/02/2014, 15h45

Bonsoir à tous !

Je suis débutant en PHP, et j'ai écrit un petit code pour pouvoir mettre à jour les données de ma BDD.
En fait ces informations sont contenues dans un fichier texte (de nom fichab.txt), la première colonne du fichier texte correspond à l'IMSI et la seconde colonne correspond à MEID. Et le but est de:

Pour chaque IMSI du fichier texte (même IMSI qui se trouve dans la BDD) remplacer son MED de la BDD par celui qui est contenu dans le ficher texte. Bref l'IMSI et MEID se trouvent dans une même table de la BDD et c'est la colonne MEID que l'on veut modifier.

Voici la structure de mon fichier texte qui a la structure que ma BDD.

(------IMSI---------------MEID)
624030743830000 1100001D7DEE6D
624030743830001 CC00001D7DEE6E
624030743830002 3100001D7DEE6F
624030743830003 4100001D7DEE70
624030743830004 CC00001D7DEE71

Voici mon script:

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
<?php
set_time_limit(0);
error_reporting(E_ALL ^ E_NOTICE);
mysql_connect('localhost','root','');
mysql_select_db('gestnum');
/On ouvre le fichier en question (en lecture seule)
$handle = fopen("fichab.txt", "r");
//on parcours tout le fichier (en faisant croire qu'il s'agit d'un csv pour faciliter le traitement)
//$i = 0;
while (($data = fgetcsv($handle, 0, " ")) !== FALSE) {
    $queryBase = 'UPDATE coordab SET MEID="'.$data[1].'", WHERE IMSI="'.$data[0].'"';
    mysql_query($queryBase);
} 
//on ferme le fichier
fclose($handle);
?>

Lorsque je l'exécute, il fait la mise à jour mais en prenant beaucoup de temps car le fichier contient 9999 lignes.

Je voudrais que vous m'aidiez avec des astuces pour pouvoir réduire le temps d'exécution de ce script.

Merci d'avance.

**sabotage** · 25/02/2014, 15h47

Commence par afficher les requêtes dans ta boucle pour voir ce qui se passe.

**nguim** · 25/02/2014, 16h40

ça fait déjà la mise à jour mais le temps d'exécution est très élevé.
S'il vous plait aidez moi à optimiser ce code.
Merci d'avance!

**sabotage** · 25/02/2014, 17h28

Si tu changes la question en cours de route aussi c'est difficile de te suivre

mais en prenant beaucoup de temps car le fichier contient 9999 lignes.

10000 lignes ce n'est rien du tout.
Donne nous la structure de ta table coordab.

**nguim** · 26/02/2014, 08h41

Bonjour !

Je m'excuse pour le temps de réponse. Je n'étais plus connecté.

Voici la structure de ma table coordab:

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
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- phpMyAdmin SQL Dump
-- version 4.0.4
-- <a href="http://www.phpmyadmin.net" target="_blank">http://www.phpmyadmin.net</a>
--
-- Client: localhost
-- Généré le: Mer 26 Février 2014 à 07:33
-- Version du serveur: 5.6.12-log
-- Version de PHP: 5.4.12
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Base de données: `gestnum`
--
CREATE DATABASE IF NOT EXISTS `gestnum` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `gestnum`;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `coordab`
--
 
CREATE TABLE IF NOT EXISTS `coordab` (
  `ida` int(60) NOT NULL AUTO_INCREMENT,
  `IMSI` varchar(255) NOT NULL,
  `MEID` varchar(255) NOT NULL,
  `ESN1` varchar(255) NOT NULL,
  `PUK1` varchar(255) NOT NULL,
  `AKY` varchar(255) NOT NULL,
  `MDN` varchar(255) NOT NULL,
  PRIMARY KEY (`ida`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
 
--
-- Contenu de la table `coordab`
--
 
INSERT INTO `coordab` (`ida`, `IMSI`, `MEID`, `ESN1`, `PUK1`, `AKY`, `MDN`) VALUES
(1, '624030743830000', 'A100001D7DEE6D', '80E5FA73', '37312379', '0E47EA7A6F444640', '33830000'),
(2, '624030743830001', 'A100001D7DEE6E', '80AE089F', '91944192', 'F9052B04E3969977', '33830001'),
(3, '624030743830002', 'A100001D7DEE6F', '809A1A9F', '29861661', '4617481A74D864B5', '33830002'),
(4, '624030743830003', 'A100001D7DEE70', '80E68950', '20303707', '1C45F0418B552C45', '33830003'),
(5, '624030743830004', 'A100001D7DEE71', '80C275C6', '70591541', 'EA26D0523114FFF2', '33830004');
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

S'il vous plait aidez moi.

Merci d'avance.

**sabotage** · 26/02/2014, 10h47

Utilise des types de colonnes adaptées et pas varchar(255) partout.
ISMSI ca serait un CHAR(15) par exemple et MEID un CHAR(14).
Ensuite ajoute un index sur ISMSI.

**nguim** · 26/02/2014, 14h50

Bonsoir !

J'ai modifié la structure de la BDD comme vous m'avez demandé, mais rien n'a changé.

Voici la nouvelle structure de ma table coordab:

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
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- phpMyAdmin SQL Dump
-- version 4.0.4
-- <a href="http://www.phpmyadmin.net" target="_blank">http://www.phpmyadmin.net</a>
--
-- Client: localhost
-- Généré le: Mer 26 Février 2014 à 13:45
-- Version du serveur: 5.6.12-log
-- Version de PHP: 5.4.12
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Base de données: `gestnum`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `coordab`
--
 
CREATE TABLE IF NOT EXISTS `coordab` (
  `ida` int(10) NOT NULL AUTO_INCREMENT,
  `IMSI` char(15) NOT NULL,
  `MEID` char(14) NOT NULL,
  `ESN1` char(8) NOT NULL,
  `PUK1` char(8) NOT NULL,
  `AKY` char(16) NOT NULL,
  `MDN` char(10) NOT NULL,
  PRIMARY KEY (`ida`),
  KEY `IMSI` (`IMSI`),
  KEY `IMSI_2` (`IMSI`),
  KEY `ida` (`ida`),
  KEY `IMSI_3` (`IMSI`),
  KEY `IMSI_4` (`IMSI`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10001 ;
 
--
-- Contenu de la table `coordab`
--
 
INSERT INTO `coordab` (`ida`, `IMSI`, `MEID`, `ESN1`, `PUK1`, `AKY`, `MDN`) VALUES
(1, '624030743830000', '1100001D7DEE6D', '80E5FA73', '37312379', '0E47EA7A6F444640', '33830000'),
(2, '624030743830001', 'BB00001D7DEE6E', '80AE089F', '91944192', 'F9052B04E3969977', '33830001'),
(3, '624030743830002', '3100001D7DEE6F', '809A1A9F', '29861661', '4617481A74D864B5', '33830002'),
(4, '624030743830003', '4100001D7DEE70', '80E68950', '20303707', '1C45F0418B552C45', '33830003'),
(5, '624030743830004', 'BB00001D7DEE71', '80C275C6', '70591541', 'EA26D0523114FFF2', '33830004');
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Voici mon script:

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
21
22
<?php
 
set_time_limit(0);
error_reporting(E_ALL ^ E_NOTICE);
mysql_connect('localhost','root','');
mysql_select_db('gestnum');
 
//On ouvre le fichier en question (en lecture seule)
 
$handle = fopen("fichab.txt", "r");
//on parcours tout le fichier (en faisant croire qu'il s'agit d'un csv pour faciliter le traitement)
//$i = 0;
while (($data = fgetcsv($handle, 0, " ")) )
{
	$queryBase = 'UPDATE coordab SET MEID="'.$data[1].'"  WHERE IMSI="'.$data[0].'"';
	mysql_query($queryBase);
}
//on ferme le fichier
fclose($handle);
 
 
?>

Que fait encore ? Aidez moi je vous en prie.

**nguim** · 27/02/2014, 08h58

Bonjour à tous !

J'ai un souci, en fait j'ai une table de ma BDD qui a cinq colonnes et je voulais insérer les données dans les deux premières colonnes.
Mais lorsque j'exécute mon script, il insère ces données à la fin de la table c'est à dire à partir de la dernière ligne de la table:

Exple:

ida-----IMSI----MEID----ENS1----PUK1----MDN

1------------------------20-------AD-----13
2------------------------21-------AE-----14
3------A22------G40-----22-------AF-----15
4------A23------G4A------------------------
5------A24------G4B------------------------
6------A25------G4C------------------------

Les colonnes IMSI et MEID sont celles dont j'ai mis les valeurs à travers mon script.
Mon souci est de savoir comment faire pour que les données entrent dans ces deux colonnes à partir de la première ligne (ida=1).

Voici mon script:

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
<?php
 
set_time_limit(0);
error_reporting(E_ALL ^ E_NOTICE);
 
mysql_connect('localhost','root','');
mysql_select_db('gestnum');
 
 
//On ouvre le fichier en question (en lecture seule)
 
$handle = fopen("fichab.txt", "r");
 
$i=0;
 
        $queryBase = 'INSERT INTO coordab (IMSI, MEID) VALUES ';
        $query = $queryBase;
        while (($data = fgetcsv($handle, 0, " ")) !== FALSE)
        {
            $query .= "('$data[0]', '$data[1]'),";
            if ($i % 5000 == 0)
            {
                // On enlève la dernière virgule
                $query = substr($query, 0, -1);
                mysql_query($query);
                $query = $queryBase;
 
            }
            $i++;
        }
        // Pour les derniers enregistrements
        if ($query != $queryBase) 
		{
               $query = substr($query, 0, -1);
               mysql_query($query);
        }
 
 //on ferme le fichier
fclose($handle);
 
 
?>

Mes données viennent d'un fichier texte (fichab.text).

S'il vous plait aidez moi à résoudre ce problème.

Merci d'avance !

**Spartacusply** · 27/02/2014, 09h20

Il faut que tu fasses un UPDATE dans ta base de données, et non un INSERT

**nguim** · 27/02/2014, 09h37

Merci pour votre aide !

Voici j'ai d'abord essayé de faire avec UPDATE mais le temps d'exécution est trop élevé (plus d'une dizaine de minute), mon fichier a 9999 lignes.

C'est pour cela que j'ai voulu exploité le code que vous m'avez envoyé récemment.

Voici mon script avec UPDATE:

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
21
22
23
24
25
26
 
 
<?php
 
set_time_limit(0);
error_reporting(E_ALL ^ E_NOTICE);
mysql_connect('localhost','root','');
mysql_select_db('gestnum');
 
//On ouvre le fichier en question (en lecture seule)
 
$handle = fopen("fichab.txt", "r");
 
//on parcours tout le fichier (en faisant croire qu'il s'agit d'un csv pour faciliter le traitement)
 
 while (($data = fgetcsv($handle, 0, " ")))
 {	
 
$req = 'UPDATE coordab SET MEID="'.$data[1].'" WHERE IMSI ="'.$data[0].'"';    
mysql_query($req);				
 
 }
//on ferme le fichier
fclose($handle);
 
?>

**Spartacusply** · 27/02/2014, 09h54

Le soucis c'est que mon code ne peut être adapté avec un UPDATE. Un UPDATE est spécifique, et dans ton cas, doit être exécuté 10 000 fois.

Je t'aurais bien proposé d'utiliser les requêtes préparées qui sont fortement recommandées dans ton cas d'utilisation mais la biblitohèque mysql (devenue obsolète au passage) que tu utilises ne les supportes pas...

Pas de solution miracle malheureusement !

**nguim** · 27/02/2014, 10h06

Merci pour votre aide.

Est ce qu'il est possible donc d'insérer les données dans ces deux colonnes en commençant à la première ligne (ida=1)

Faites quelque chose pour m'aider une fois de plus. Je ne connais plus comment faire encore.

Merci d'avance.

**Celira** · 27/02/2014, 10h17

Envoyé par Spartacusply

Je t'aurais bien proposé d'utiliser les requêtes préparées qui sont fortement recommandées dans ton cas d'utilisation mais la biblitohèque mysql (devenue obsolète au passage) que tu utilises ne les supportes pas...

Solution : ne plus utiliser les fonctions mysql_xxxx et passer à mysqli (ou PDO)

Autre possibilité : tu peux essayer de parcourir la totalité de ton fichier pour regrouper les éléments par même valeur, de façon à faire des updates en masse.

**nguim** · 27/02/2014, 10h39

Autre possibilité : tu peux essayer de parcourir la totalité de ton fichier pour regrouper les éléments par même valeur, de façon à faire des updates en masse.

Merci pour votre réponse.

Je suis nouveau en php et j'ai commencé un petit projet. Et la première possibilité est un peu complexe pour moi. Mais pour ce qui concerne la deuxième possiblité, toutes les chaines de mon fichier sont différentes. Que faire ?

Est ce qu'il est possible donc d'insérer les données dans ces deux colonnes en commençant à la première ligne (ida=1) ?

Aidez moi s'il vous plait.

**Spartacusply** · 27/02/2014, 10h48

Et la première possibilité est un peu complexe pour moi.

Détrompe toi, il n'y a rien de très compliqué. Il s'agit (presque) de juste remplacer toutes tes fonctions mysql_xxxx par mysqli_xxxxxx. Je te recommande également comme Cerilia d'utilser l'extension mysqli (ou éventuellement PDO qui fait la même chose, mais c'est un peu plus complexe et étant donné que tu es débutant, mysqli sera très bien).

Est ce qu'il est possible donc d'insérer les données dans ces deux colonnes en commençant à la première ligne (ida=1) ?

Cette insertion revient toujours à faire un UPDATE au final, cela reste la même problématique.

**Celira** · 27/02/2014, 11h23

Si tu viens de commencer ton projet, passe directement à mysqli. En mode procédural, c'est quasiment la même syntaxe que les fonctions mysql_xxxx et ça te permettra de faire des requêtes préparées.

(voir cette discussion en cours sur le sujet : http://www.developpez.net/forums/d14...nnees-valable/)
ça donnerait quelque chose du genre :

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php
set_time_limit(0);
error_reporting(E_ALL ^ E_NOTICE);
 
$db = mysqli_connect('localhost','root','', 'gestnum');
 
 
//On ouvre le fichier en question (en lecture seule)
$handle = fopen("fichab.txt", "r");
 
// on prépare la requête
$req = 'UPDATE coordab SET MEID=? WHERE IMSI =? ';  
$stmt = mysqli_prepare($link, $req);
// variables : attention à l'ordre des paramètres
$meid = '';
$imsi = '';
mysqli_stmt_bind_param($stmt, "s", $meid);
mysqli_stmt_bind_param($stmt, "s", $imsi);
 
 
//on parcours tout le fichier (en faisant croire qu'il s'agit d'un csv pour faciliter le traitement)
while (($data = fgetcsv($handle, 0, " ")))
{   
    // renseignements des valeurs
    $meid = $data[1];
    $imsi = $data[0];
    // execution 
    mysqli_stmt_execute($stmt);
}
//on ferme le fichier
fclose($handle);
// on ferme la requête et la connextion
mysqli_stmt_close($stmt);
mysqli_close($link);
 
?>

**nguim** · 27/02/2014, 11h26

Maintenant que je veux me lancer dans mysqli comme vous me demandés, comment est ce que mon problème sera résolu ?

**Spartacusply** · 27/02/2014, 11h49

Grâce aux requêtes préparées. Cerilia t'as donné un exemple de ce que cela pourrait donné juste au dessu. Mais fait fonctionner déjà l'ensemble de ton code avec mysqli.

**sabotage** · 27/02/2014, 12h15

Code sql :

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

1
2
3
4
5
6
 PRIMARY KEY (`ida`),
  KEY `IMSI` (`IMSI`),
  KEY `IMSI_2` (`IMSI`),
  KEY `ida` (`ida`),
  KEY `IMSI_3` (`IMSI`),
  KEY `IMSI_4` (`IMSI`)

Je ne crois pas avoir dit de mettre 4 index par colonne

**nguim** · 27/02/2014, 12h17

Merci beaucoup pour cette aide.

Je viens d'exécuter ce code que vous m'avez envoyés, mais il m'affiche beaucoup d'erreur que je n'arrive pas à comprendre:

( ! ) Warning: mysqli_prepare() expects parameter 1 to be mysqli, null given in C:\wamp\www\etudephp\mysqli.php on line 14
( ! ) Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, null given in C:\wamp\www\etudephp\mysqli.php on line 20
( ! ) Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, null given in C:\wamp\www\etudephp\mysqli.php on line 21
( ! ) Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, null given in C:\wamp\www\etudephp\mysqli.php on line 32

Sincèrement je suis juste en train d'exécuter comme un automate. Pour le moment je ne comprends pas encore le fonctionnement et c'est au fur et à mesure que j'apprends.

Je vous en prie, ne vous lasser pas de m'aider, car je ne connais pas.

Merci pour tout !

Optimiser la mise à jour de la Base

PHP & Base de données

Discussions similaires

Partager

Partager