Discussion :

[zorbalegrec]

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]

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]

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]

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

[jnore]

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]

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]

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]

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]

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]

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 ?

[jnore]

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 ?

Bonsoir,

Avant de modifier ton script qui n'a pas l'air mauvais en soi, juste une question:
As-tu mis un index sur le champ 'code_4D' dans ta base de données ?
Vu que tu es en mise à jour, et que chaque update s'appuie sur une clause Where dans ton cas, donc une valeur précise, il est plus que nécessaire d'avoir ce champ indexé!

Si ca n'est pas le cas, mets en un tout de suite, logiquement il y aura une différence.

[zorbalegrec]

Bonjour,

l'eau a coulé quelques semaines sous les ponts, mais c'est sans doute le propre des PME d'avoir des webmasters multitâches... tour à tour graphistes, codeurs, rédacteurs...

Pour ce qui est de mon script, j'ai donc appliqué plusieurs de vos conseils:

mon champ "code 4D" était mis en index, mais partagé avec le champ "clientid", qui lui est la clef primaire... pas clean... j'ai donc gardé "clientid" en PK et "code4D" en index, et ça a tout de suite boosté le script.

Ensuite j'ai concaténé mes Insert, pour les éxecuter en une seule passe à la fin,
et j'ai concaténé les Update que j'ai exécuté 100 par 100, au fur et à mesure...

J'ai laissé tomber le découpage des fichiers car après test, le temps d'exéc est sensiblement le même, voire un peu plus long en découpant les fichiers.

Laissé tombé aussi mysql_pconnect, car les mises en garde sont trop lourdes dans la Doc...

BILAN: j'ai ramené l'exécution du script :
de presque 10mn (en non 4mn comme j'avais indiqué par erreur)
à 2mn env. hors upload du fichier

ce qui est sensible également c'est que sql prend moins de ressources au serveur, la charge ne dépasse plus 0.70 alors que j'atteignais des seuils abominables auparavant qui paralysaient la machine.

Cool, et merci pour vos conseils donc. EN PJ le script modifié pour les amateurs du genre littéraire.

Globalement je trouve que mysql tourne comme un avion depuis que j'utilise tuning-primer.sh et mysql tuner pour améliorer les paramètres globaux, mais quand même surpris que la machine ne sache faire plus vite ce script précis... Peut-être ensuite s'agit -il des limites de la config matérielle?