[MySQL] Optimisation ou revoir le code

Optimisation ou revoir le code

Bonjour

Je fait un export en csv, ce code fonctionne mais il sature ma mémoire de mon dédié
D'autant plus que prochainement la base de données va prendre plusieurs milliers d'entrées !

Ma question:
Est-ce que je code avec mes pieds il faut tout revoir ?
Ou y a des possibilité d'optimiser, je pensais par exemple à passer par des tableaux intermédiaires plutot que les boucles ou me passer de la librairie phpexcel ?

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
 
<?php
include('../include/config.php');
 
	$jour_debut = $_POST['jour_debut'];
	$mois_debut = $_POST['mois_debut'];
	$annee_debut = $_POST['annee_debut'];
 
	$date_debut = $annee_debut.'-'.$mois_debut.'-'.$jour_debut.' 00:00:00';
 
 
	$jour_fin = $_POST['jour_fin'];
	$mois_fin = $_POST['mois_fin'];
	$annee_fin = $_POST['annee_fin'];
 
	$date_fin = $annee_fin.'-'.$mois_fin.'-'.$jour_fin.' 00:00:00';
 
 
	$tab_default_style	= array
					(
						'font' => array
						(
							'name'	=>  'Arial',
							'size'	=>  10,
							'bold'	=> false
						)
					);
 
 
 
require $_SERVER['DOCUMENT_ROOT'] .'librairies/php/phpexcel/PHPExcel.php';
require $_SERVER['DOCUMENT_ROOT'] .'librairies/php/phpexcel/PHPExcel/Writer/Excel5.php';
 
 
$workbook = new PHPExcel;
 
$sheet = $workbook->getActiveSheet();
$sheet->setTitle('Besoin en stock');
 
$sheet->setCellValueExplicit('A1', 'ID ARTICLE', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('B1', 'Marque', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('C1', 'Ref fournisseur', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('D1', 'Ref fournisseur ancien', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('E1', 'LIB Article', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('F1', 'Prix HA HT avant Remise', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('G1', 'Coefficient', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('H1', 'Tx remise', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('I1', 'Besoin', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('J1', 'Prix HA HT net', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('K1', 'Q CDE 1', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('L1', 'Photo oui/non', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('M1', 'Actif Oui/Non', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('N1', 'Id commande', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('O1', 'Id liste établissement', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit('P1', 'Id enfant', PHPExcel_Cell_DataType::TYPE_STRING);
 
 
$sheet->getDefaultStyle()->applyFromArray($tab_default_style);
 
 
 
//selection des commandes seulement dans tranche de date
// $i=0;
// $data4 = array();
// $result4 = null;
// $tab_commandes_concernees = array();
// $query4 = 'SELECT 	id_commande	FROM tb_commandes 
 
					// WHERE date_commande >= "'.$date_debut.'"
 
					// AND date_commande <= "'.$date_fin.'"
								// ';
 
 
 
// $result4 = mysql_query($query4) or die($query4 . " - " . mysql_error());
// Si aucune commande n'est trouvée
 
	// if(mysql_num_rows($result4) == 0)
			// {
				// echo 'Aucune commande trouvée du '.$jour_debut.'/'.$mois_debut.'/'.$annee_debut.' 00:00:00 au '.$jour_fin.'/'.$mois_fin.'/'.$annee_fin.' 00:00:00';
				// echo '<br /><a href="index_exports.php">Retour</a>';
				// exit();
 
			// }
 
// while($data4 = mysql_fetch_object($result4))
// {
	// $id_commande[$i] = $data4->id_commande;
	// array_push($tab_commandes_concernees,$id_commande[$i]);
// }
// print_r($tab_commandes_concernees);echo "oooooooooooooo";
//construcion d'un tableau [id_produit][qtite_produit] pour commande liste etablissement
 
 
$besoin_total_produit = array();
$i=0;
$data = array();
$result = null;
$query = 'SELECT 	
					cc.id_commande,
					cc.id_article,
					cc.type_article,
					cc.qtite_article,
					cc.id_enfant,
					
					co.date_commande

					FROM tb_commandes_composition cc
					
					LEFT JOIN tb_commandes co
					ON co.id_commande = cc.id_commande
					
					WHERE cc.type_article = "liste_etablissement" 
					
					AND co.date_commande >= "'.$date_debut.'"
					
					AND co.date_commande <= "'.$date_fin.'"
					
					';
 
 
 
$result = mysql_query($query) or die($query . " - " . mysql_error());
 
	//si pas de commande pour cette tranche de date
	if(mysql_num_rows($result) == 0)
			{
				echo 'Aucune commande liste établissement trouvée du '.$jour_debut.'/'.$mois_debut.'/'.$annee_debut.' 00:00:00 au '.$jour_fin.'/'.$mois_fin.'/'.$annee_fin.' 00:00:00';
				echo '<br /><a href="index_exports.php">Retour</a>';
				exit();
 
			}
		$ligne = 2;	
 
 
while($data = mysql_fetch_object($result))
{
	$id_commande[$i] = $data->id_commande;flush();
	$id_liste_etablissement[$i] = $data->id_article;
	$qtite_liste_etablissement[$i] = $data->qtite_article;
	$id_enfant[$i] = $data->id_enfant;
 
 
			//RECUPERATION DES ARTICLES DANS LISTE ETABLISSMENENT
			$k=0 ;
			$data2 = array();
			$result2 = null;
			$query2 = 'SELECT  
			
								lec.id_produit,
								lec.qtite_produit,
								
								p.id_produit,
								p.marque_produit,
								p.reference_produit,
								p.ancienne_reference,
								p.coefficient,
								p.nom_produit,
								p.prix_achat,
								p.remise_fournisseur,
								p.quantite_commande1,
								p.actif
															
								FROM tb_listes_etablissements_composition  lec
								
								LEFT JOIN tb_produits p
								ON lec.id_produit = p.id_produit								
								
								WHERE lec.id_liste_etablissement = '.$id_liste_etablissement[$i].'
								
								ORDER BY p.id_produit';
 
 
			$result2 = mysql_unbuffered_query($query2) or die($query2 . " - " . mysql_error());
 
 
 
 
			while($data2 = mysql_fetch_object($result2))
			{
 
					$id_produit[$i][$k] = $data2->id_produit;//echo "::";
					$qtite_produit[$i][$k] = $data2->qtite_produit;//echo "<br />";
 
					//incrémentation tableau des quantités de produit nécessaire
					$besoin_total_produit[$id_produit[$i][$k]] += ( $qtite_produit[$i][$k] * $qtite_liste_etablissement[$i] );
 
 
					$marque_produit[$i][$k] = $data2->marque_produit;
					$reference_produit[$i][$k] = $data2->reference_produit;
					$ancienne_reference[$i][$k] = $data2->ancienne_reference;
					$coefficient[$i][$k] = $data2->coefficient;
					$nom_produit[$i][$k] = $data2->nom_produit;
					$prix_achat[$i][$k] = $data2->prix_achat;
					$remise_fournisseur[$i][$k] = $data2->remise_fournisseur;
					$quantite_commande1[$i][$k] = $data2->quantite_commande1;
					$actif[$i][$k] = $data2->actif;
 
					$prix_achat_apres_remise[$i][$k] = $prix_achat[$i][$k] - ( $prix_achat[$i][$k] * $remise_fournisseur[$i][$k] / 100 );
 
					$besoin_en_stock[$i][$k] = $qtite_produit[$i][$k] * $qtite_liste_etablissement[$i];
 
					$sheet->setCellValueExplicit('A'. $ligne, html_entity_decode($id_produit[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('B'. $ligne, html_entity_decode($marque_produit[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('C'. $ligne, html_entity_decode($reference_produit[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('D'. $ligne, html_entity_decode($ancienne_reference[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('E'. $ligne, html_entity_decode($nom_produit[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('F'. $ligne, html_entity_decode($prix_achat[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('G'. $ligne, html_entity_decode($coefficient[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('H'. $ligne, html_entity_decode($remise_fournisseur[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('I'. $ligne, html_entity_decode($besoin_en_stock[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
 
					if ( $remise_fournisseur[$i][$k] != '')
					{
						$sheet->setCellValueExplicit('J'. $ligne, $prix_achat_apres_remise[$i][$k], PHPExcel_Cell_DataType::TYPE_STRING);
					}
					else
					{
						$sheet->setCellValueExplicit('J'. $ligne, $prix_achat[$i][$k], PHPExcel_Cell_DataType::TYPE_STRING);
 
					}
					$sheet->setCellValueExplicit('K'. $ligne, $quantite_commande1[$i][$k], PHPExcel_Cell_DataType::TYPE_STRING);
 
 
					if ( (file_exists( $url_images_produits . $id_produit[$i][$k] . '_1_t.jpg')) )
					{ 
						$sheet->setCellValueExplicit('L'. $ligne, 'oui' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
					else
					{
						$sheet->setCellValueExplicit('L'. $ligne, 'non' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
 
 
					if ( $actif[$i][$k] == '1' )
					{ 
						$sheet->setCellValueExplicit('M'. $ligne, 'oui' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
					else
					{
						$sheet->setCellValueExplicit('M'. $ligne, 'non' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
 
					$sheet->setCellValueExplicit('N'. $ligne, html_entity_decode($id_commande[$i], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('O'. $ligne, html_entity_decode($id_liste_etablissement[$i], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('P'. $ligne, html_entity_decode($id_enfant[$i], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
 
 
					$ligne++;
					$k++;
 
 
			}
 
			$i++;
 
}
 
 
 
//recap des besoins totaux par produits			
$ligne2 = $ligne + 5;
 
	$sheet->setCellValueExplicit('A'. $ligne2, 'id produit', PHPExcel_Cell_DataType::TYPE_STRING);
	$sheet->setCellValueExplicit('I'. $ligne2, 'besoin total', PHPExcel_Cell_DataType::TYPE_STRING);
	$ligne2++;
 
foreach ( $besoin_total_produit as $id_produit2 => $quantite )
{	//echo  $produit . '--'. $quantite ."<br />";
 
$query = 'SELECT * FROM tb_produits WHERE id_produit = '.$id_produit2;
$result = mysql_unbuffered_query($query) or die($query . " - " . mysql_error());
 
 
  $nom_produit = mysql_result($result,0,"nom_produit");
  $description = mysql_result($result,0,"description");
  $prix_achat = mysql_result($result,0,"prix_achat");
  $coefficient = mysql_result($result,0,"coefficient");
  $reference_produit = mysql_result($result,0,"reference_produit");
  $ancienne_reference = mysql_result($result,0,"ancienne_reference");
  $marque_produit = mysql_result($result,0,"marque_produit");
  $remise_fournisseur = mysql_result($result,0,"remise_fournisseur");
  $lot_achat = mysql_result($result,0,"lot_achat");
  $quantite_commande1 = mysql_result($result,0,"quantite_commande1");
  $quantite_commande2 = mysql_result($result,0,"quantite_commande2");
  $quantite_commande3 = mysql_result($result,0,"quantite_commande3");
  $stock_produit = mysql_result($result,0,"stock_produit");
  $actif = mysql_result($result,0,"actif");
  $poids = mysql_result($result,0,"poids");
  $categorie = mysql_result($result,0,"categorie");
  $tva = mysql_result($result,0,"tva");
 
  $prix_achat_apres_remise = $prix_achat - ( $prix_achat * $remise_fournisseur / 100 );
 
 
	// $sheet->setCellValueExplicit('A'. $ligne2, html_entity_decode($produit, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
	// $sheet->setCellValueExplicit('B'. $ligne2, html_entity_decode($quantite , ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
 
	$sheet->setCellValueExplicit('A'. $ligne2, html_entity_decode($id_produit2, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('B'. $ligne2, html_entity_decode($marque_produit, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('C'. $ligne2, html_entity_decode($reference_produit, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('D'. $ligne2, html_entity_decode($ancienne_reference, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('E'. $ligne2, html_entity_decode($nom_produit, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('F'. $ligne2, html_entity_decode($prix_achat, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('G'. $ligne2, html_entity_decode($coefficient, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('H'. $ligne2, html_entity_decode($remise_fournisseur, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('I'. $ligne2, html_entity_decode($quantite, ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
 
					if ( $remise_fournisseur != '')
					{
						$sheet->setCellValueExplicit('J'. $ligne2, $prix_achat_apres_remise, PHPExcel_Cell_DataType::TYPE_STRING);
					}
					else
					{
						$sheet->setCellValueExplicit('J'. $ligne2, $prix_achat, PHPExcel_Cell_DataType::TYPE_STRING);
 
					}
					$sheet->setCellValueExplicit('K'. $ligne2, $quantite_commande1, PHPExcel_Cell_DataType::TYPE_STRING);
 
 
					if ( (file_exists( $url_images_produits . $id_produit2 . '_1_t.jpg')) )
					{ 
						$sheet->setCellValueExplicit('L'. $ligne2, 'oui' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
					else
					{
						$sheet->setCellValueExplicit('L'. $ligne2, 'non' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
 
 
					if ( $actif == '1' )
					{ 
						$sheet->setCellValueExplicit('M'. $ligne2, 'oui' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
					else
					{
						$sheet->setCellValueExplicit('M'. $ligne2, 'non' , PHPExcel_Cell_DataType::TYPE_STRING);
					}
 
					$sheet->setCellValueExplicit('N'. $ligne2, html_entity_decode($id_commande[$i], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('O'. $ligne2, html_entity_decode($id_liste_etablissement[$i], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
					$sheet->setCellValueExplicit('P'. $ligne2, html_entity_decode($id_enfant[$i], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
 
 
	$ligne2++;
}
 
// print_r($besoin_total_produit);
 
$tab_style_bold = array
				(
					'font' => array
					(
						'bold' => true
					)
				);
 
$sheet->duplicateStyleArray($tab_style_bold, 'A1:P1');
 
 
 
$writer = new PHPExcel_Writer_Excel5($workbook); 
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition:inline;filename=Besoin_en_stock_produit.xls ');
$writer->save('php://output');
 
 
 
?>

Si ton but est de faire un CSV, tu n'as pas besoin d'utiliser PHPExcel. Tu peux parfaitement te contenter d'un fichier texte alimenté avec fputcsv. A priori, ça devrait manger moins de ressources que PHPExcel.

Autre point : tu crées et alimente un tas de tableaux mais tu ne les utilises visiblement pas à l'extérieur de la boucle. Par exemple :
Code:

1 2 $marque_produit[$i][$k] = $data2->marque_produit; $sheet->setCellValueExplicit('B'. $ligne, html_entity_decode($marque_produit[$i][$k], ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
Tu pourrais utiliser simplement une variable pour chaque donnée et la réinitialiser à chaque tour de boucle.

Merci pour ta réponse rapide

Tu veux dire que comme je n'utilise pas la valeur par la suite dans le code plutot que de ls stocker dans un tableau je pourrais la stocker dans une variable?

J'ai remplacé mysql_query par mysql_unbuffered_query et le résultat est incroyablement rapide par contre je ne sais pas si le résultat est encore bon ?

Citation:

Envoyé par networkinfo

Tu veux dire que comme je n'utilise pas la valeur par la suite dans le code plutot que de ls stocker dans un tableau je pourrais la stocker dans une variable?

Exactement, ça évite de garder le tableau en mémoire alors que tu n'en as pas l'utilité?.

Citation:

Envoyé par networkinfo

J'ai remplacé mysql_query par mysql_unbuffered_query et le résultat est incroyablement rapide par contre je ne sais pas si le résultat est encore bon ?

A priori oui. Tu dois juste traiter tous les résultats d'une requête avant d'en lancer une autre.

Merci pour tes conseils ça ma permit de comprendre d'autre chose

Mon script montait jusqu'a 3Go de mémoire

C'est en supprimant la librairie PHPExecel et en générant le csv à coup de fwrite que mon script est quasiment instantané

Encore merci

Citation:

Envoyé par networkinfo

C'est en supprimant la librairie PHPExcel et en générant le csv à coup de fwrite que mon script est quasiment instantané

Tu n'as pas utilisé fputcsv comme mentionné plus haut ?

A part ça évidemment il ne faut éventuellement utiliser PHPExcel qu'en cas de besoin absolu.

En utilisant fwrite je n'ai pas eu à retoucher mon code j 'ai l'impression qu'avec fputcsv j'aurais dû adapter ?

hum... fputcsv prend un tableau représentant la ligne en paramètre. Il faudrait donc que tu construises ton tableau-ligne avant d'écrire le fichier.
L'avantage serait que tu serais sûr d'avoir une ligne bien formée et de ne pas avoir oublié une virgule quelque part ;)

Citation:

A part ça évidemment il ne faut éventuellement utiliser PHPExcel qu'en cas de besoin absolu.

Que dans le cas ou on veut manipuler des fichiers Excel plutôt.

bonjour,

pourquoi ne pas passer par select into outfile?
Code:

1 2 3 $export_query ="SELECT 'LIBELLE_COLONNE_1', etc UNION select * INTO OUTFILE '/chemin/nom_du_fichier' FIELDS TERMINATED BY ';' FROM LA_TABLE"; mysql_query($export_query );
Le premier select sert à insérer la ligne des libellés de colonnes.

Le problème avec ces commandes directement depuis mysql est qu'il faut avoir les droits mysql sur les fichiers et ce n'est jamais le cas sur un mutualisé par exemple.