[SQL] Grosse requête SQL + calculs

Version imprimable

12/01/2007, 16h32
nicopoal

[SQL] Grosse requête SQL + calculs

salut à tous
bon alors brevement
je dois remonter tous les enregistrement dune table CA = chiffres d'affaires
cette table contient les valeurs du chiffres d'affaire journalier de chaque magasin pour chaque jour de chaque année depuis 1994

CA :
id_ca int auto increment
num_mag varchar = numéro du magasin
date_ca date = date du ca
valeur_ca : montant du ca du jour pour le magasin

donc je fais un select sur ma table et avec une "moulinette" je dois calculer le ca mensuel de chaque magasin + le ca de lannée -1 et le ca de lannée -2 , avec les pourcentage daugmentation ou diminution
et bien sur afficher le tout sur ma page

et evidemment le script est super long ...

donc je chercher une solution pour diminuer le temps d'execution, sans changer le timeout du serveur bien evidemment

merci à tous
12/01/2007, 16h44
Amara

Pas facile d'optimiser du code sans le voir ;)

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
 
echo '<TABLE border width="98%" align="center" cellspacing="1"><tr align="center" bgcolor="#ffc21c"><th>Code Magasin</th><th>Nom Magasin</th><th>Chiffres d\'affaires</th><th>%</th><th>CA Années -1 lissé</th><th>CA Années -2 lissé</th><th>Derniere date saisie</th><th>Derniere valeur saisie</th></tr>';
 
 
 
		//requete pour recup les derniers infos du ca mag 
 
 
 
		$req_mag="select * from structure where login_struct like 'mag%' order by login_struct;";
		$result_mag=mysql_query($req_mag) or die(mysql_error().' - '.$req_mag);
 
		while ($ligne_mag=mysql_fetch_array($result_mag))
		{
			$ca_annee=0;
			$ca_annee_1=0;
			$ca_annee_2=0;
 
			//récupération ca en cours ou sélectionné
 
			$req_ca_mag="select valeur_ca, date_ca from ca where num_struct like '".substr($ligne_mag["login_struct"],4)."' and DAY(date_ca)>=1 and DAY(date_ca)<=".$jour." and YEAR(date_ca) = '".$annee."' and MONTH(date_ca)='".$mois."';";
 
			//$req_ca_mag="select sum(valeur_ca) as som from ca where num_struct like '".substr($ligne_mag["login_struct"],4)."' and date_ca between '".$annee."-".$mois."-01' and '".$annee."-".$mois."-".$jour."';";
			//echo $req_ca_mag.'<br>';
 
			$result_ca_mag=mysql_query($req_ca_mag) or die(mysql_error().' - '.$req_ca_mag);
			//$ligne_ca_mag=mysql_fetch_array($result_ca_mag);
			//$ca_annee=$ligne_ca_mag["som"];
 
			while($ligne_ca_mag=mysql_fetch_array($result_ca_mag))
			{
				$ca_annee+=$ligne_ca_mag["valeur_ca"];
				$last_valeur=$ligne_ca_mag["valeur_ca"];
				$last_date=$ligne_ca_mag["date_ca"];
			}
 
			//récupération ca -1 lissé
			$req_ca_mag_1="select sum(valeur_ca) as som from ca where num_struct like '".substr($ligne_mag["login_struct"],4)."' and YEAR(date_ca) = '".($annee-1)."' and MONTH(date_ca)='".$mois."';";
			//echo $req_ca_mag_1.'<br>';
			$result_ca_mag_1=mysql_query($req_ca_mag_1) or die(mysql_error().' - '.$req_ca_mag_1);
			$ligne_ca_mag_1=mysql_fetch_array($result_ca_mag_1);
			$ca_annee_1=$ligne_ca_mag_1["som"];
 
			//while($ligne_ca_mag_1=mysql_fetch_array($result_ca_mag_1))
			//{
			//	$ca_annee_1+=$ligne_ca_mag_1["valeur_ca"];
			//}
 
 
			//récupération ca-2 lissé
			$req_ca_mag_2="select sum(valeur_ca) as som from ca where num_struct like '".substr($ligne_mag["login_struct"],4)."' and YEAR(date_ca) = '".($annee-2)."' and MONTH(date_ca)='".$mois."';";
			//echo $req_ca_mag_2.'<br>';
			$result_ca_mag_2=mysql_query($req_ca_mag_2) or die(mysql_error().' - '.$req_ca_mag_2);
			$ligne_ca_mag_2=mysql_fetch_array($result_ca_mag_2);
			$ca_annee_2=$ligne_ca_mag_2["som"];
 
			//while($ligne_ca_mag_2=mysql_fetch_array($result_ca_mag_2))
			//{
			//	$ca_annee_2+=$ligne_ca_mag_2["valeur_ca"];
			//}
 
				$ca_lisse_1=round(($ca_annee_1/$nb_jour_mois)*($jour-1));
				$ca_lisse_2=round(($ca_annee_2/$nb_jour_mois)*($jour-1));
 
 
				//echo $ca_annee_1.' / '.$nb_jour_mois .' * '.($jour-1);
				echo '<TR align="center">';
				echo '<TD>'.substr($ligne_mag["login_struct"],4).'</TD>';
				echo '<TD>'.$ligne_mag["nom_struct"].'</TD>';
				if ($ca_annee<>0)
				{
					echo '<TD>'.round($ca_annee).'</TD>';
				}
				else
				{
					echo '<TD> - </TD>';
				}
 
 
 
				if (($ca_lisse_1<>0) and ($ca_annee<>0)) 
				{
					echo '<TD>'.round((($ca_annee-$ca_lisse_1)*100)/$ca_lisse_1,2).'&nbsp;%</TD>';
				}
				else
				{
					echo '<TD> - </TD>';
				}
				echo '<TD>'.$ca_lisse_1.'</TD>';
				echo '<TD>'.$ca_lisse_2.'</TD>';
				echo '<TD>'.$last_date.'</TD>';
				echo '<TD>'.round($last_valeur).'</TD>';
				echo '</TR>';
 
		set_time_limit(30);

voila j'espere que avec un peu de code ça sera plus "clair"

15/01/2007, 14h58
Invité

deja je separerais les requetes et l'affichage, je mexplique :
je fais mon traitement et je sauvegarde les valeurs dans un tableau
puis je libere les ressources sql
puis jaffiche mon tableau

mais bon a voir peut etre quil y a mieux a faire
15/01/2007, 15h00
Amara

Déjà je vois pas l'intérêt de l'utilisation des LIKE, une comparaison classique avec = (plus économique) semble suffire.
15/01/2007, 15h05
nicopoal

Citation:

Envoyé par rbaatouc

deja je separerais les requetes et l'affichage, je mexplique :
je fais mon traitement et je sauvegarde les valeurs dans un tableau
puis je libere les ressources sql
puis jaffiche mon tableau

mais bon a voir peut etre quil y a mieux a faire

a peut etre, faudrait essayer

Citation:

Envoyé par JWhite

Déjà je vois pas l'intérêt de l'utilisation des LIKE, une comparaison classique avec = (plus économique) semble suffire.

moi a lecole on ma dit , pour les chaines tu met un like, japplique betement ce que ma dit mon prof de sql lannée derniere, je savais meme pas que ça marchait avec "="
15/01/2007, 15h15
Invité

j'eviterais aussi les select * et je mettrais le noms des champs qui m'interessent
15/01/2007, 15h23
nicopoal

Citation:

Envoyé par rbaatouc

j'eviterais aussi les select * et je mettrais le noms des champs qui m'interessent

ils m'interessent tous , sauf l'id peut etre, tu crois que ça ferait une grosse différence?
15/01/2007, 15h39
Invité

dans ce cas la je pense pas
15/01/2007, 15h54
nicopoal

sinon pas d'autres idées pour alleger le traitement ?
passer par des vues sql peut etre ?
15/01/2007, 18h56
Antoun

les vues MySQL ne sont pas compilées, donc aucun gain de perf de ce côté-là
16/01/2007, 11h39
nicopoal

donc en gros je suis dans la merde quoi lol
obligé de faire patienter les internautes 40 secondes pour laffichage de la page
et encore 40 secondes en local, j'imagines meme pas ceux qui ont du 512 ou encore pire du 56k

enfin bref, merci a ceux qui m'ont repondu, et si d'autres ont des idées , n'hesitez surtout , mais alors surtout pas à m'en faire part
16/01/2007, 11h43
Amara

Citation:

Envoyé par nicopoal

donc en gros je suis dans la merde quoi lol
obligé de faire patienter les internautes 40 secondes pour laffichage de la page
et encore 40 secondes en local, j'imagines meme pas ceux qui ont du 512 ou encore pire du 56k

enfin bref, merci a ceux qui m'ont repondu, et si d'autres ont des idées , n'hesitez surtout , mais alors surtout pas à m'en faire part

Avec les 2/3 trucs qu'on t'a donné ça change rien ?

Pour le débit je pense pas que ça joue c'est pas le volume de donnés transférées qui pose problème mais la génération de la page côté serveur donc c'est de ce côté là que sont limitées matériellement les perfs.
16/01/2007, 11h45
J0r_x

Ton select donne combien de resultat peut < 20 ou beaucoup > 100 ?
16/01/2007, 11h47
nicopoal

Citation:

Envoyé par JWhite

Avec les 2/3 trucs qu'on t'a donné ça change rien ?

Pour le débit je pense pas que ça joue c'est pas le volume de donnés transférées qui pose problème mais la génération de la page côté serveur donc c'est de ce côté là que sont limitées matériellement les perfs.

ouai c'est pas faux
pourtant le serveur il est tout beau tout neuf en mode PATATE, mais bon ...

je pense que je vais faire une table temporaire qui contiendra les calculs deja faits, au moins il n'y aura que le premier user qui se tapera les calculs, les autres on ira taper directement dans les données de la table calculé, ça devrait etre plus rapide
16/01/2007, 15h33
nicopoal

Citation:

Envoyé par J0r_x

Ton select donne combien de resultat peut < 20 ou beaucoup > 100 ?

ma table de magasin contient pas loin de 200 entrées = 200 magasins

mais ma table CA, qui contient donc chaque chiffre d'affaire quotidien de tous les magasins depuis 1994, donc au final dans ma table CA j'ai pas loin de 400 000 enregistrements !

et dans le select qui me permet d'afficher ma page on a :

nombre de magasins ^ (numéro du jour choisi), tu captes ?

si tu choisis de voir le chiffre daffaire au 16 du mois, ben pour chaque magasin je dois faire la somme de tous ses ca entre le 1er et le 16 de l'annee, pareil pour lannée davant, et pareil pour lannée encore davant
16/01/2007, 15h39
J0r_x

Tu as essayé de mettre "Limit 0,30" à la fin de ta requette et de l'incrementer au fur et à mesure ou 100 parce que si tu fais un select de plus de 1000 result je comprend que tu mettes 40sec à afficher les résultats mais la solution la plus performante serait de stocker les calculs qui ne changent pas dans une table.
16/01/2007, 15h41
nicopoal

Citation:

Envoyé par J0r_x

Tu as essayé de mettre "Limit 0,30" à la fin de ta requette et de l'incrementer au fur et à mesure ou 100 parce que si tu fais un select de plus de 1000 result je comprend que tu mettes 40sec à afficher les résultats mais la solution la plus performante serait de stocker les calculs qui ne changent pas dans une table.

c'est à dire ? concretement ça donnerait quoi ?
paske dans ce cas la faudrait que jexecute la requete plusieurs fois ? certs a chaque fois elle serait plus rapide mais bon ...
16/01/2007, 15h44
J0r_x

Aprés reflecxion je pense que ca ne changerait pas grand chose, la table de 400 000 entrées restera longue à scanner.
16/01/2007, 15h54
nicopoal

Citation:

Envoyé par J0r_x

Aprés reflecxion je pense que ca ne changerait pas grand chose, la table de 400 000 entrées restera longue à scanner.

ouai mais au moins je ne ferais que de l'affichage à la place de calculer puis afficher
17/01/2007, 12h28
Invité

tu as placer des index sur ta table ça peut jouer, si t en mysql >4.0.1 tu peux aussi jouer sur le mysql cache
19/01/2007, 11h49
nicopoal

Citation:

Envoyé par rbaatouc

tu as placer des index sur ta table ça peut jouer, si t en mysql >4.0.1 tu peux aussi jouer sur le mysql cache

salut à toi, je reviens sur le forum justement pour dire que j'avais résolu mon probleme en mettant des index, mais merci quand meme pour ta proposition

c'est fou quand meme, juste en mettant des index, mon temps d'execution est passé d'environ 40 secondes à meme pas 5 secondes!! génial !!
19/01/2007, 14h18
Invité

ravi d'avoir pu taider.