Insertions et mise à jour SQL à partir d'un fichier texte lourd : optimiser un script

**zorbalegrec** · 09/06/2009, 15h35

Bonjour,

j'ai une base de données compilée 4D de laquelle j'extrais régulièrement des données, pour mettre à jour une base MySQL.

Le fichier d'export en .txt pèse env. 5 Mo pour 37000 lignes d'enregistrement comme celle-ci

Code :

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

AAAA7520;1;ROBERT;Toto;17 AVENUE FONTENAILLE;BAT.B;26100;VALENCE;;21/01/02;toto.ex@wanadoo.fr;00/00/00;;1;24/12/2002;Faux;Faux;NR

Mon script uploade le .txt, formate et compare les données, et met éventuellement à jour la base MySQL.

MA QUESTION porte sur la méthode: en effet lorsque je faisais le boulot en une seule passe, j'avais des problèmes de temps d'execution et tutti quanti.

J'ai donc mis en place le système suivant: je découpe le gros fichier en x fichiers plus petits (de 1500 lignes par ex.) puis les traite un par un. Ca se passe mieux mais c'est encore hypra long et prend 99% des ressources pendant plusieurs mn.

Qu'est-ce qui dans mon script est si gourmand en ressources ?

Peut-être aurais-je dû faire autrement ? Est-ce plus efficace par ex de preparer les update et les insert et de les executer seulement à la fin du traitement?

Ci-dessous une synthèse du script (j'ai voulu éviter de poster une tartine de code indigeste). Le script complet est en PJ pour les courageux.

Merci à ceux qui voudront bien s'y pencher et commenter.

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
<?php require('include/fonction.php');
 
//DECLARATION DES VARIABLES	
 
			//some code	
 
//UPLOAD
 
			//some code	
 
//CONVERSION DU FICHIER EN X FICHIERS DE 1500 LIGNES
 
			//some code	
 
//IMPORT DU CONTENU DE CHAQUE FICHIER CREE DANS LA DB 
	$dir = opendir($dossier); 
 
	while($file = readdir($dir)) {
		if($file != '.' && $file != '..' && !is_dir($dossier.$file)) 	{
 
			//TRAITEMENT DE CHAQUE FICHIER
			$arrayTxt = file($dossier.$file);
			for( $i=0; $i<count($arrayTxt); $i++ ) {
 
				$arrayProd[$i] = explode(";",$arrayTxt[$i]);
 
				//FORMATAGE DES DONNEES
				$code = addslashes( trim( $arrayProd[$i][0] )) ;  
				$tel = eregi_replace("[^0-9]", "", str_replace('+', '00', $arrayProd[$i][12] ) ); 
 
						//etc... 				
 
				//COMPARAISON DES DONNEES AVEC LA BASE MYSQL: ON DOIT VERIFIER DANS L'ORDRE CERTAINES CORRESPONDANCES
 
				//CAS N°1 ?
				$c = returnChamp(' `code_4D`', 'client' , 'code_4D ="'.$code.'"'); //SELECT ...FROM ... WHERE ...
				if($c[0]) {
 
					//UPDATE DATABASE
 
				}
 
				else {//CAS N°2 ?
					$c2 = returnChamp('clientid', 'client', 'email <> "" AND email ="'.$email.'" AND code_4D =""' ); //SELECT ...FROM ... WHERE ...
					if($c2[0]) {
 
						//UPDATE DATABASE
 
					}
					else {//CAS N°3 ?
						$c3 = returnChamp('`clientid` , `email`' , 'client', 'nom ="'.$nom.'" AND prenom = "'.$prenom.'" AND ( tel = "'.$tel.'" OR dateNaiss = "'.$dateNaiss.'" OR ( cp = "'.$cp.'" AND ville = "'.$ville.'"  ) )');//
						if($c3[0]) {
 
							//UPDATE DATABASE
 
						}
						else {//CAS N°4 ?
							$c4 = returnChamp('clientid', 'client', 'nom ="'.$nom.'" AND prenom = "'.$prenom.'"');
							if($c4[0]) {
 
								//UPDATE DATABASE
 
							}
							else {
									//INSERTION NVO CLIENT
							}
						}
					}
				}
			}
		}
	}
	closedir($dir);
 
	//SUPPRESSION DES FICHIERS 
?>

**Xunil** · 09/06/2009, 19h55

C'est clair que si tu fais une requête par ligne, ça fera 37 000 requêtes

Normal que ce soit long.

Concatènes et fais une seule et unique requête à la fin.

INSERT INTO(..,.. ,.., ..) VALUES(xxx), (yyyy), etc...

**zorbalegrec** · 09/06/2009, 20h11

Merci.

c'est en forgeant ...

Je vais tester en concaténant les insertions. Mais comme j'ai une grande majorité de update, je crains que cette modif ne change guère la donne ?

Et pour les update je ne vois pas comment concatener? Sachant qu'on a chaque fois:

UPDATE table SET champ = $var , champ2 = $var2, etc... WHERE conditions changeantes

Existe t'il une autre syntaxe?

Je reprendrai le fil demain, car je quitte le burô . Merci

**Xunil** · 09/06/2009, 20h28

Non, pour les updates, tu seras contraint de les faire un à un, pas d'autres solutions.

jnore · 09/06/2009, 22h46

Envoyé par Xunil

C'est clair que si tu fais une requête par ligne, ça fera 37 000 requêtes

Normal que ce soit long.

Concatènes et fais une seule et unique requête à la fin.

INSERT INTO(..,.. ,.., ..) VALUES(xxx), (yyyy), etc...

37000 requêtes ce n'est rien, je traite des fichiers bien plus volumineux.
Dans son cas une connection permanente est nécessaire.

Personnellement, je n'aurais pas découpé le fichier.
Je l'aurais analysé d'un seul coup. Par contre j'aurais envoyé mes requetes au serveur par batch de 100 (100 updates par 100 updates).

**Xunil** · 10/06/2009, 09h04

Envoyé par jnore

37000 requêtes ce n'est rien, je traite des fichiers bien plus volumineux

Rien 37000 requêtes ?

On ne doit pas avoir la même conception du rien.

jnore · 10/06/2009, 12h13

Envoyé par Xunil

Rien 37000 requêtes ?

On ne doit pas avoir la même conception du rien.

Bien sûr que ce n'est rien, pour un SGBD digne de ce nom, c'est une bricole.
Je viens de faire un script presque identique au sien et j'en suis à plus de 100 000 lignes sous postgres...et là encore ce n'est rien.

Il faut savoir tout de même que les sgbd sont faits pour ca !

**zorbalegrec** · 10/06/2009, 14h21

Envoyé par jnore

Par contre j'aurais envoyé mes requetes au serveur par batch de 100 (100 updates par 100 updates).

Ok Jnore, je suis preneur. Je gère ça comment dans mon script ?

jnore · 10/06/2009, 22h52

Envoyé par zorbalegrec

Ok Jnore, je suis preneur. Je gère ça comment dans mon script ?

Je n'ai pas le temps ce soir, je reviens te donner un bout de code demain.
En attendant essaye de ton coté:
1) en ouvrant une connection permanente
2) en scrutant tout ton fichier via une boucle.
3) dans ta boucle concaténer dans une variable 100 requetes à la fois.
4) faire une mysql_query de tes 100 requetes
5) continuer l'analyse et refaire une concaténation de 100 requetes.
6) rebelote, mysql_query des 100 requetes
.......
jusqu'à la fin du fichier

Cela devrait améliorer tes perfs, mais pour autant cela ne fera pas de miracle.
Ce qu'il serait intéressant, c'est de nous dire le temps d'execution de ton script...à la louche ..on est pas à la seconde près.

**zorbalegrec** · 11/06/2009, 16h49

Envoyé par jnore

Cela devrait améliorer tes perfs, mais pour autant cela ne fera pas de miracle. Ce qu'il serait intéressant, c'est de nous dire le temps d'execution de ton script...à la louche ..on est pas à la seconde près.

Merci pour ces précisions. Je ne vais malheureusement pas pouvoir tester ces jours-ci car je pars en congés et je vais essayer de décrocher du clavier qq jours

.
Mais c'est bien d'un miracle dont j'ai besoin, car on n'est plus à qq secondes près dans mon cas : avant tentative d'optimisation, je fais tourner mes 37.000 lignes en 4mn environ !!!

D'où ce post! M'étant formé sur le tas, voire sur le tard, je me doute bien que la même OP bien écrite devrait s'effectuer en ... 20 secondes ?

Insertions et mise à jour SQL à partir d'un fichier texte lourd : optimiser un script [MySQL]

PHP & Base de données

Vue hybride

Discussions similaires

Partager

Partager