Insertion en grande quantité avec MySQL et Oracle

**Kralby** · 19/06/2014, 15h00

Bonjour,

J'ai un fichier contenant 30000 lignes à rentrer dans ma BDD, le problème évidemment, c'est le temps d’exécution, je l'ai augmenté, mais j'aimerai le réduire considérablement, car là pour 5min, il ne rentre que 10000 lignes dans la base de données, donc il faudrait 15min pour faire rentrer je pense le fichier complet.

Une ligne du fichier ressemble à cela :
2013-01-29,"2013-01-28,"Terminïe","R","AD","Lille","","Berlin","","CAC32/2013","04:00:00",\N,"0","0","C2","03:30:00","04:00:00","CRTC_FGC","CRTC","","0","0"

Donc, j'ai fait ceci :

Code php :

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
 
$tab = file("upload/".$_FILES['monfichier']['name']); 
$nbligne = count($tab)-1;
 
$id = 0 ; $i = 0 ;
 
// Creation de l'incrementation des id dans la base de donnee
$incremente = $connexion->query("SELECT count(*) FROM mouvement");
$recuperation = $incremente->fetch();
 
if($recuperation == 0)
{
	$id = 1 ;
}
else
{
	$record = $connexion->query("SELECT MAX(id_movement) FROM mouvement");
	$result = $record->fetch();
	$id = $result[0] ;
}
// On ouvre le fichier 
$fichier = fopen("upload/".$_FILES['monfichier']['name'], "r");
 
while (!feof($fichier))
{
	$uneLigne = fgets($fichier);
	if(mb_strlen(trim($uneLigne),'UTF-8'))
	{
		if($i>0) // test pour ne pas lire la premiere ligne
		{  
			$tableauValeurs = explode(',', $uneLigne);
			// insertion des donnees dans la table  
			$id++ ;    
			$connexion->exec('SET NAMES "UTF8"');               
			$connexion->exec("INSERT IGNORE INTO mouvement VALUES 
			('".$id."','".$tableauValeurs[0]."','".$tableauValeurs[1]."','".$tableauValeurs[2]."','"
			.$tableauValeurs[3]."','".$tableauValeurs[4]."','".$tableauValeurs[5]."','"
			.$tableauValeurs[6]."','".$tableauValeurs[7]."','".$tableauValeurs[8]."','"
			.$tableauValeurs[9]."','".$tableauValeurs[10]."','".$tableauValeurs[11]."','"
			.$tableauValeurs[12]."','".$tableauValeurs[13]."','".$tableauValeurs[14]."','"
			.$tableauValeurs[15]."','".$tableauValeurs[16]."','".$tableauValeurs[17]."','"
			.$tableauValeurs[18]."','".$tableauValeurs[19]."','".$tableauValeurs[20]."','"
			.$tableauValeurs[21]."')");
		}
		else
		{
			$i++;          
		}
	}
}

On m'a conseillé de faire plutôt un LOAD DATA INFILE, j'ai regardé la documentation, et au début j'ai cru que cela aurait pu fonctionner, cependant je fais mes tests sur une serveur local MySQL, mais après, l'application sera lancé sur un serveur Oracle, et on m'a dit qu'il n'y a pas d'auto-incrémentation dans une base Oracle. Donc je ne peux pas utilisé cela.

Je ne sais plus trop par où prendre le problème, si quelqu'un pouvait m'indiquer une solution. Merci d'avance.

**Celira** · 20/06/2014, 12h40

Malgré tout ce qu'on peut dire sur PDO et le fait qu'on puisse changer de SGBD en cours de route juste en changeant le driver, c'est vrai uniquement si l'application n'utilise aucune fonctionnalité spécifique. Et c'est fou le nombre de petites choses spécifiques qu'on utilise sans faire attention.

Typiquement, les clés auto-incrémentées. C'est possible avec MySQL et avec Oracle, mais pas de la même façon.
MySQL gère des colonnes de table auto-incrémentée, alors que Oracle utilise des séquences dans lesquelles il faut piocher avant de faire l'insertion.

Pour des insertions en bloc avec Oracle, tu peux te pencher sur INSERT ALL, qui te permettrait de faire quelque chose comme 50 insertions en une seule requête. Mais ça demande de bien construire la requête (avec découpage des données en paquets) et ça ne fonctionnera pas sur MySQL.

Du coup, ce serait beaucoup plus simple pour toi de faire le développement avec une bdd Oracle (sauf si tu veux vraiment faire une appli avec SGBD interchangeable)

Quelques remarques sur le code proprement dit :
Si ton fichier est un fichier CSV (ça y ressemble beaucoup en tout cas), utilise plutôt fgetcsv.

Si tu utilises PDO pour exécuter des requêtes identiques à la chaine, mieux vaut utiliser des requêtes préparées (après tout, c'est faire pour ça) :

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
$connexion->exec('SET NAMES "UTF8"');
// préparation de la requête :
$sql =  "INSERT INTO mouvement VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $connexion->prepare($sql);
 
while (!feof($fichier))
{
    $uneLigne = fgets($fichier);
    if(mb_strlen(trim($uneLigne),'UTF-8'))
    {
        if($i>0) // test pour ne pas lire la premiere ligne
        {
            $tableauValeurs = explode(',', $uneLigne);
            // insertion des donnees dans la table
            $id++ ;
            // on ajoute l'id au début du tableau de valeurs
            array_unshift($tableauValeurs, $id);
            // on exécute la requêtes avec les valeurs du tableau (dans l'ordre)
            $stmt->execute($tableauValeurs);
        }
        else
        {
            $i++;
        }
    }
}
// on ferme la requête préparée
$stmt->closeCursor();

**mr_samurai** · 21/06/2014, 11h47

Bonjour,

voici un exemple de l'idée que je te propose:

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
// Daily report statistics ( on script top ) 
$sql = "SELECT * FROM ..........................";
$st = $db->prepare($sql);
$st->execute(array('status' => 0));
$res = $st->fetchAll(PDO::FETCH_ASSOC);
$sqlZero = "INSERT INTO statistics_day (site_id, user_id, score, i_display, i_clic, o_display, o_clic, stat_date) VALUES ";
$sqlArray = array();
foreach ($res as $k => $v) {
    $tmp = array($v["site_id"], $v["user_id"], $v["score"], $v["i_display"], $v["i_clic"], $v["o_display"], $v["o_clic"], $yesterdayTime);
    $sqlArray[] = "(" . implode(',', $tmp) . ")";
    if ($k % 300 == 299) {   // One query each 300 INSERT
        $sql = $sqlZero . implode(',', $sqlArray);
        $st = $db->prepare($sql);
        $st->execute();
        $sqlArray = array();
    }
}
if (count($sqlArray)) {
    // execute last beam
    $sql = $sqlZero . implode(',', $sqlArray);
    $st = $db->prepare($sql);
    $st->execute();
}

++

**Kralby** · 03/07/2014, 16h57

Merci à tout les deux pour vos réponses, désolé pour le délai de la mienne, j'ai pas eu le temps de me consacrer à mon boulot ces deux dernières semaines.

Celira, en fait, je développe l'application et ensuite, je le donne à une personne qui se charge de l'intégrer. Donc au tout début, j'ai démarré avec les mysql_connect de base et tout ce qui suit. Quand j'ai eu fini, je me suis dit, je passe en PDO, comme ça, ça ira plus vite pour lui pour changer etc... Mais après il m'a dit que le PDO ne fonctionne pas ici. Donc je suis passé sur les oci_connect. Mais je n'ai pas le droit de faire les tests avec la base Oracle, et j'avais une version avec PDO, donc je l'ai gardé. C'est pour cela que je ne peux tester avec INSERT ALL et oui le fichier est un csv.

J'ai fais cela au début d’après-midi, avec la méthode de mr_samurai.

Code php :

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
 
while (!feof($fichier))
{ 
	$uneLigne = fgets($fichier);
	if(mb_strlen(trim($uneLigne),'UTF-8'))
	{
		if($i>0) // test pour ne pas lire la premiere ligne
		{
			$tableauValeurs = array() ;
			$tableauValeurs = explode(',', $uneLigne);
			// insertion des donnees dans la table
			$id++ ;
			// on ajoute l'id au début du tableau de valeurs
			$res = array_unshift($tableauValeurs, $id);
			// on exécute la requêtes avec les valeurs du tableau (dans l'ordre)
			$sqlZero = "INSERT INTO mouvement(id_movement,movement_date,last_modified_time,statut,service_type,sens,origin,via_origin,destination,via_destination,pop_name,platform_time,platform_actual_time,service_open,day_shift,poste,occupancy_start_time,occupancy_end_time,service,operator,commander,pilot,tug) VALUES ";
			$sqlArray = array();
 
			foreach ($tableauValeurs as $k => $v)
			{
				$tmp = array($v["id_movement"], $v["movement_date"], $v["last_modified_time"], $v["statut"], $v["service_type"], $v["sens"], $v["origin"], $v["via_origin"], $v["destination"], $v["via_destination"], $v["pop_name"], $v["platform_time"], $v["platform_actual_time"], $v["service_open"], $v["day_shift"], $v["poste"], $v["occupancy_start_time"], $v["occupancy_end_time"], $v["service"], $v["operator"], $v["commander"], $v["pilot"], $v["tug"]);								
				$sqlArray[] = "(" . implode(',', $tmp) . ")";
				if ($k % 300 == 299)
				{   // One query each 300 INSERT
					$sql = $sqlZero . implode(',', $sqlArray); 
					$st = $db->prepare($sql);
					$st->execute();
					$sqlArray = array();
				}
			}
			if (count($sqlArray))
			{
				// execute last beam
				$sql = $sqlZero . implode(',', $sqlArray);
				$st = $db->prepare($sql);
				$st->execute();
			}		
		}
		else
		{
			$i++;
		}
	}
}

Je pense avoir plus ou moins compris ce que tu fais, et j'ai adapté avec mes informations.
Le problème est que j'ai une erreur au niveau de $tmp, il me dit Illegal string offset '...' ce qui est dans les crochets.

J'ai regardé un peu ce que cela signifier, on dit que c'est parce que c'est un String au lieu d'un array mais $v correspond bien au $tableauValeurs qui est un array non ?

**mr_samurai** · 08/07/2014, 05h17

Bonsoir,

je ne passe pas souvent par le forum, donc désolé du retard.

Enleve le foreach et tu aura :

Code :

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

1
2
 
$tmp = array($tableauValeurs[0],$tableauValeurs[1], ....);

Ceci peut aussi marcher (sans $tmp):

Code :

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

$sqlArray[] = "(" . $uneLigne . ")";

**Kralby** · 08/07/2014, 10h20

Ce n'est pas grave, c'est déjà bien que tu prennes de ton temps pour m'aider.

Sinon, j'ai donc bien fait ce que tu m'as dit.

Donc, je n'ai plus le problème de offset.

Cependant, au niveau de cette partie là

Code php :

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

1
2
3
4
5
6
7
if ($k % 300 == 299)
{   // One query each 300 INSERT
$sql = $sqlZero . implode(',', $sqlArray); 
$st = $connexion->prepare($sql);
$st->execute();
$sqlArray = array();
}

Cela sert bien a découpé les 30000 lignes en requête par paquet de 300 et donc faire juste 100 requête au lieu de 30000 que j'avais essayé de faire au début.

Mais maintenant que j'ai le foreach qui n'est plus là comment utilisé ton if ($k % 300 == 299) sachant que $k n'est plus existant.

Et j'aurai une petite question SQL, car j'ai une erreur qui est

Erreur : SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'platform_actual_time' cannot be null
N° : 23000

Donc ok, le problème est que la colonne platform_actual_time n'est pas vide, j'ai regardé ma requête et elle comme cela.

Code sql :

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

1
2
INSERT INTO mouvement(id_movement,movement_date,last_modified_time,statut,service_type,sens,origin,via_origin,destination,via_destination,pop_name,platform_time,platform_actual_time,service_open,day_shift,poste,occupancy_start_time,occupancy_end_time,service,operator,commander,pilot,tug)
VALUES (1,"2013-01-29","2013-01-28 08:23:07","Termin�","R","AD","Calais","","Douvres","","POP29/2013","04:00:00",\N,"0","0","P6","03:30:00","04:00:00","DFDS_DSW","DFDS","","0","0" )

L'erreur serait provoqué au niveau du /N, mais cette valeur est dans le CSV de base, donc je suis obligé de la rajouté, le champ est donc bien rempli, je ne comprends pas pourquoi on me dit qu'il est null

**mr_samurai** · 08/07/2014, 13h14

bonjour,

voici la technique classique pour avoir un entier qui s’incrémente:

Code :

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

1
2
3
4
5
6
7
 
$k = -1;
while (!feof($fichier)) {
  $k++;
  ....
 
}

pour le second problème, je te propose:

Code :

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

1
2
INSERT INTO mouvement(id_movement,movement_date,last_modified_time,statut,service_type,sens,origin,via_origin,destination,via_destination,pop_name,platform_time,platform_actual_time,service_open,day_shift,poste,occupancy_start_time,occupancy_end_time,service,operator,commander,pilot,tug)
VALUES (1,"2013-01-29","2013-01-28 08:23:07","Termin�","R","AD","Calais","","Douvres","","POP29/2013","04:00:00",NOW(),"0","0","P6","03:30:00","04:00:00","DFDS_DSW","DFDS","","0","0" )

**Kralby** · 08/07/2014, 15h27

Merci,

J'ai résolu les problème techniques grâce au conseil apportés donc comme ça, cela fonctionne.

Le temps d’exécution a aussi été réduit mais toujours pas suffisamment, je suis a 25000 lignes rentrés en 5min, après le navigateur arrête et :"Le délai d'attente est dépassé." Il me faudrait gagné une minute de plus, et normalement tout est rentré.

Si j'augmente au niveau du if ($k % 300 == 299)
En gros en divise $k par 300 et le reste de cette division doit être équivalente à 299. Donc je dois augmenter 300 pour faire une requête plus grosse et augmenter la vitesse d’exécution ?

Edit : j'ai essayé d'augmenter en remplacant 300 par 500 et puis par 1000 et 299 par 499 et 999 mais le résultat est toujours le meme, Le délai d'attente est dépassé. Donc je sais pas j'ai pas l'impression qu'on puisse aller plus vite

**mr_samurai** · 08/07/2014, 18h29

le plus simple est de modifier php.ini :

Code :

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

max_execution_time = 600

**Kralby** · 09/07/2014, 08h31

Je l'avais déjà mis a 1000 au début du développement de l'application, parce que je savais que cela prendrais un peu de temps. Si c'est le plus simple, le plus compliqué cela se passe comment ?

**mr_samurai** · 09/07/2014, 20h47

bonjour,

il faut Raisonner avec vos problémes pour trouver des solutions, et ainsi se construire une expérience dans le domaine

.

une solution possible serait s’exécuter le script 2 fois : 15000 à la fois :

Code :

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

1
2
3
4
5
6
7
8
$k = -1;
while (!feof($fichier)) {
  $k++;
   // Execution 1  ( ne pas commenter lors de la 1er execution, puis commenter pour la seconde )
   if($k > 15000) {  break; }
   // Execution 2  ( commenter lors de la 1er execution, puis décommenter pour la seconde )
   if($k <= 15000) { continue;  }
}

si tu veux aller plus loin, tu peut optimiser mySQL en modifiant my.cnf

**Kralby** · 11/07/2014, 10h07

Ok ça fonctionne à merveille Merci pour tout Mr_Samurai

Insertion en grande quantité avec MySQL et Oracle [PDO]

PHP & Base de données

Discussions similaires

Partager

Partager