Optimiser la mise à jour de la Base

Version imprimable

Voir 40 message(s) de cette discussion en une page

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:

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.

25/02/2014, 15h47
sabotage

Commence par afficher les requêtes dans ta boucle pour voir ce qui se passe.
25/02/2014, 16h40
nguim

ç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!
25/02/2014, 17h28
sabotage

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

Citation:

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.

Bonjour !

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

Voici la structure de ma table coordab:

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
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.

26/02/2014, 10h47
sabotage

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.

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:

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:

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.

Modifier une colonne d'une table de la BDD MySQL

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:

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 !

27/02/2014, 09h20
Spartacusply

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

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:

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); ?>

27/02/2014, 09h54
Spartacusply

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 !
27/02/2014, 10h06
nguim

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.
27/02/2014, 10h17
Celira

Citation:

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) :D

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.
27/02/2014, 10h39
nguim

Citation:

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.
27/02/2014, 10h48
Spartacusply

Citation:

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).

Citation:

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.

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:

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);
 
?>

27/02/2014, 11h26
nguim

Maintenant que je veux me lancer dans mysqli comme vous me demandés, comment est ce que mon problème sera résolu ?
27/02/2014, 11h49
Spartacusply

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.

Code:

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 ;)

27/02/2014, 12h17
nguim

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:

Citation:

( ! ) 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 !

Voir 40 message(s) de cette discussion en une page