Requête de date > optimisation

**Dev@lone** · 25/05/2012, 17h15

Bonjour à tous,

J'ai un traitement qui s'effectue en 3 temps en PHP :
1/ Recherche de maVariable toutes les dates MAX de chaque jour = traitement1

2/ Recherche de maVariable de la date MAX de la veille de chaque réponse de traitement1 = traitement2

3/ Soustraction maVariable(traitement1) à maVariable(traitement2)

Le problème c'est ça devient très très lourd, car on fait une requête par jour, donc sur la fin de l'année on est à 365 requêtes pour un affichage.

Je cherche une solution pour optimiser ça, mais mes connaissances en SQL sont limitées.

Voici ma première requête :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT MAX(date) as date, 
  CAST(maVariable as DECIMAL(10,3)) as var" . $i . "  
FROM `maTable` 
WHERE champDate LIKE '2012%'  
  AND file='process' 
GROUP BY Day(champDate) 
ORDER BY date ASC

Ma seconde (celle qui boucle) :

Code :

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

1
2
3
4
5
6
7
 
SELECT MAX(date), maVariable 
FROM `maTable` 
WHERE champDate LIKE '2012-01-01%' 
  AND file='process' 
  AND maVariable != '' 
ORDER BY date DESC";

Création de la table

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE IF NOT EXISTS `MaTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `maVariable` varchar(255) NOT NULL,
  `maVariable2` varchar(255) NOT NULL,
  `maVariable3` varchar(255) NOT NULL,
  `champDate` varchar(255) NOT NULL,
  `file` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

D'avance merci de votre aide pour optimiser ceci.

**Scriuiw** · 25/05/2012, 17h29

Tout d'abord, utiliser un champs VARCHAR pour stocker une date va vous pénaliser lors de la manipulation des données.

Ensuite, ce que vous décrivez peut être fait en une seule requête SQL mais il nous faudrait connaître votre SGBD ainsi que la version pour proposer une solution adaptée

**Dev@lone** · 25/05/2012, 18h01

Merci de la réponse.

Pourquoi cela va me pénaliser ?

Je suis sous MySql.

**CinePhil** · 25/05/2012, 19h26

J'ai du mal à comprendre quel est ton besoin qui justifierait un processus aussi complexe.
Pourrais-tu exprimer ce besoin en français ?

**Dev@lone** · 25/05/2012, 19h29

Ca va être compliqué en français

Il s'agit de différence d'index de compteurs ERDF entre deux dates. Je ne sais pas si ça te parle plus...

**CinePhil** · 25/05/2012, 19h34

Pourquoi nous avoir donné une table aussi abstraite ? C'est quoi variable1, variable2 et variable3 ?

Donne la vraie table, ce sera plus simple pour la compréhension et pour donner la bonne réponse.

Un petit jeu de données serait utile aussi parce que caster des varchar(255) en DECIMAL, ça sent le mauvais choix de type de données pour ça aussi !

**Dev@lone** · 25/05/2012, 19h36

La table est vraiment constituée ainsi, mis à part que j'ai var0 à var29 qui sont des index renseignés ou non.

Mis à part le champ file qui est effectivement un peu hors contexte, je vois mal comment simplifier ma demande.

**ericd69** · 25/05/2012, 19h44

salut,

rien n'empêche de faire tout ça purement en mysql procédural... pas besoin d'allers-retours php/mysql

déjà explique clairement ce que tu dois faire (algorithme avec formule si tu as du mal en français) et donne les create de tes tables...

car comme dit cinephil, c'est pas clair...

c'est destiné à du cron ou à un besoin web donc avec des contraintes de temps d'exécution

et enfin si la structures des table n'est pas optimale peux tu la retoucher?

ça va déjà t'occuper un peu pour répondre

**Dev@lone** · 25/05/2012, 19h57

Héhé

Alors, j'essaie d'expliquer plus clairement.

C'est destiné à du web, pour l'affichage sur un graphique highcharts.

Voici la structure réèlle de la table :

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
 
CREATE TABLE IF NOT EXISTS `centrale` (
  `tic_id` int(11) NOT NULL AUTO_INCREMENT,
  `var0` varchar(255) NOT NULL,
  `var1` varchar(255) NOT NULL,
  `var2` varchar(255) NOT NULL,
  `var3` varchar(255) NOT NULL,
  `var4` varchar(255) NOT NULL,
  `var5` varchar(255) NOT NULL,
  `var6` varchar(255) NOT NULL,
  `var7` varchar(255) NOT NULL,
  `var8` varchar(255) NOT NULL,
  `var9` varchar(255) NOT NULL,
  `var10` varchar(255) NOT NULL,
  `var11` varchar(255) NOT NULL,
  `var12` varchar(255) NOT NULL,
  `var13` varchar(255) NOT NULL,
  `var14` varchar(255) NOT NULL,
  `var15` varchar(255) NOT NULL,
  `var16` varchar(255) NOT NULL,
  `var17` varchar(255) NOT NULL,
  `var18` varchar(255) NOT NULL,
  `var19` varchar(255) NOT NULL,
  `var20` varchar(255) NOT NULL,
  `var21` varchar(255) NOT NULL,
  `var22` varchar(255) NOT NULL,
  `var23` varchar(255) NOT NULL,
  `var24` varchar(255) NOT NULL,
  `var25` varchar(255) NOT NULL,
  `var26` varchar(255) NOT NULL,
  `var27` varchar(255) NOT NULL,
  `var28` varchar(255) NOT NULL,
  `var29` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL,
  `date_insert` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `file` varchar(255) NOT NULL,
  PRIMARY KEY (`tic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Il s'agit de compteurs ERDF qui envoient toutes les 15 minutes une valeur que l'on enregistre sur une nouvelle ligne de cette table.

A chaque ligne, nous remplissons aléatoirement une ou plusieurs valeurs de var0 à var29 (en fonction des périodes, heures pleines, heures creuses, heure pointe, etc...)

Le but pour moi, et d'afficher sur le graphique web, la différence du cumul de var0 à var4, de var5 à var 9, de var10 à var14, etc... pour chaque jour (entre les dates max de chaque jour).

La valeur de référence est celle de fin de journée à 23:59:59, mais la requête doit aussi être adaptée au jour courant, qui lui ne dispose pas de la date 23:59:59.

Je tente beaucoup de trucs depuis tout à l'heure, mais rien ne fonctionne

**Maljuna Kris** · 25/05/2012, 20h07

C'est tout bonnement hallucinant comme structure !

Et qui en est l'auteur(e) ? C'est juste pour éviter d'avoir affaire à lui (ou elle), on ne sait jamais.

Franchement, tout est à revoir.

Déjà, puisqu'il s'agit de compteurs ERDF, quelle colonne dans tout ce fatras permet d'identifier un compteur ?

Ensuite, si bon nombre des colonnes var0 à var29 sont optionnelles à chaque ligne, c'est bien la preuve que la structure est à revoir.

Y-en a qui vont pouffer, voire tousser.

**Dev@lone** · 25/05/2012, 20h18

Je n'ai jamais dis que la structure était bonne, mais j'ai fais avec mes connaissances pour m'en sortir.

Pour info, c'est un peu plus complexe que de dire une colonne = 1 compteur. Les 29 colonnes représente un compteur, découpé en groupe de colonnes var0 à var4, var5 à var9, etc...
Chaque colonne peut être renseignée à n'importe quel moment. Plusieurs à la fois.

En te remerciant pour ta réponse très constructive qui me permet d'avancer.

**SQLpro** · 25/05/2012, 20h24

Ma rigolade du jour, je la trouve toujours dans ce forum consacré à MySQL !!!
Voir des tables aussi débiles, me fait irrésistiblement penser à un retour en arrière aux années 60... les fameux fichiers COBOL.

A lire sur le sujet : http://blog.developpez.com/sqlpro/p1...ances-petites/

Évidemment parler d’optimisation et montrer une talle table me fait pisser de rire !!! J'adore ce forum...

A +

**ericd69** · 25/05/2012, 20h36

essayons de comprendre quelle logique (quelque peu, voire totalement, tordue) t'amène à ça...

pourquoi remplir aléatoirement plutôt que juste enregistrer un ligne à chaque fois?
pourquoi découper le compteur en 29 colonnes? ça t'est imposé par le compteur?

est ce que tu peux changer ce ...modèle... si on trouve mieux (ce qui est évident)?

**Dev@lone** · 25/05/2012, 20h36

Envoyé par SQLpro

Ma rigolade du jour, je la trouve toujours dans ce forum consacré à MySQL !!!
Voir des tables aussi débiles, me fait irrésistiblement penser à un retour en arrière aux années 60... les fameux fichiers COBOL.

A lire sur le sujet : http://blog.developpez.com/sqlpro/p1...ances-petites/

Évidemment parler d’optimisation et montrer une talle table me fait pisser de rire !!! J'adore ce forum...

A +

Merci.

**Dev@lone** · 25/05/2012, 20h42

Envoyé par ericd69

essayons de comprendre quelle logique (quelque peu, voire totalement, tordue) t'amène à ça...

pourquoi remplir aléatoirement plutôt que juste enregistrer un ligne à chaque fois?
pourquoi découper le compteur en 29 colonnes? ça t'est imposé par le compteur?

est ce que tu peux changer ce ...modèle... si on trouve mieux (ce qui est évident)?

Merci Eric, sympa de voir qu'il reste au moins une personne qui ne vient pas poster pour se poiler de mon manque de connaissance en sql (et ne servir à rien par la même occasion).

Alors pour te répondre, effectivement, je n'ai pas trouvé de moyen technique me permettant de faire face à cette problématique de remplissage aléatoire.

Le remplissage des var0 à 29 est totalement aléatoire, de 1 donnée sur une ligne à la casi-totalité des données.

Je peux effectivement changer ce modèle si on trouve mieux, je suis à l'écoute sans problème.
Par contre, insérer une ligne à chaque fois, la base risque de se retrouver avec un nombre de ligne énorme.
On a en moyenne 6 données toutes les 15 minutes.

Merci encore de ton aide.

**ericd69** · 25/05/2012, 20h53

c'est direct sorti du compteur?

c'est quoi ce qui est mis?

tu sors ça d'un csv à l'origine?

**Dev@lone** · 25/05/2012, 20h55

Oui ça provient d'un csv déposé sur un ftp.

Les données sont "direct" sorties du compteur en quelque sorte.

**ericd69** · 25/05/2012, 21h09

je me doutais qu'il y a qu'un bouffon d'ingénieur électronicien pour pondre un truc comme ça... j'ai bouffer des systèmes comme ça pendant 10 ans...

pas la peine de remonter au années 60, c'est souvent comme ça que beaucoup de systèmes industriel pondent leur infos de status...

heureusement avec le temps et une meilleure informatisation dés le départ ça tend à disparaitre...

tu vois sqlpro avant de critiquer va donc pourrir les sombres idiots qui fabriquent les compteurs... et qui sont pas foutus de faire un système d'export de données explicite...

j'attendais que tu me confirme l'origine pour te défendre....

bref...

donc y a rien d'aléatoire dans ces déclenchements...

est ce que tu sais à quoi correspondent ces différentes colonnes?

quelle type de valeurs dedans?

quels traitements tu en fais?

montre quelques lignes de ton csv?

**Dev@lone** · 25/05/2012, 21h26

Voici quelques lignes d'un CSV :

Code :

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

1
2
3
4
5
6
7
8
 
128;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
05/03/12-23:10:00;BASE_A5;06/03/12 00/10/45;0Wh;0varh;3;;;06/03/12 00/10/00;1kW;06/03/12 00/00/00;1kW;05/03/12 23/50/00;0kW;05/03/12 23/40/00;1kW;05/03/12 23/30/00;1kW;05/03/12 23/20/00;1kW;01/03/12 02/00/00;01/01/92 00/00/00;5;189kWh;;0kWh;540kWh;;0kWh;;0kWh;0kWh;367kWh;0kWh;;;;01/02/12 02/00/00;01/03/12 02/00/00;5;2kvarh;;0kvarh;0kvarh;;0kvarh;;0kvarh;0kvarh;3kvarh;0kvarh;;;;167kvarh;;0kvarh;199kvarh;;0kvarh;;0kvarh;0kvarh;357kvarh;0kvarh;;;;90%;80%;4kW;;4kW;0kW;4kW;0kW;4kW;4kW;0kW;;;;;;0kW;1kW;0kvar;0kvar;0,00;20400V;INJECTION;;;;;0varh;;;;06/03/12 00/10/00;0kW;06/03/12 00/00/00;0kW;05/03/12 23/50/00;0kW;05/03/12 23/40/00;0kW;05/03/12 23/30/00;0kW;05/03/12 23/20/00;0kW;;11639kWh;86kvarh;3765kvarh;;731kW;0kW;0kW;0kvar;0kvar;0,000A;0,000A;0,000A;;;;;
05/03/12-23:20:11;BASE_A5;06/03/12 00/20/55;0Wh;0varh;3;;;06/03/12 00/20/00;1kW;06/03/12 00/10/00;1kW;06/03/12 00/00/00;1kW;05/03/12 23/50/00;0kW;05/03/12 23/40/00;1kW;05/03/12 23/30/00;1kW;01/03/12 02/00/00;01/01/92 00/00/00;5;189kWh;;0kWh;540kWh;;0kWh;;0kWh;0kWh;367kWh;0kWh;;;;01/02/12 02/00/00;01/03/12 02/00/00;5;2kvarh;;0kvarh;0kvarh;;0kvarh;;0kvarh;0kvarh;3kvarh;0kvarh;;;;167kvarh;;0kvarh;199kvarh;;0kvarh;;0kvarh;0kvarh;357kvarh;0kvarh;;;;90%;80%;4kW;;4kW;0kW;4kW;0kW;4kW;4kW;0kW;;;;;;0kW;1kW;0kvar;0kvar;0,00;20400V;INJECTION;;;;;0varh;;;;06/03/12 00/20/00;0kW;06/03/12 00/10/00;0kW;06/03/12 00/00/00;0kW;05/03/12 23/50/00;0kW;05/03/12 23/40/00;0kW;05/03/12 23/30/00;0kW;;11639kWh;86kvarh;3765kvarh;;731kW;0kW;0kW;0kvar;0kvar;0,000A;0,000A;0,000A;;;;;
05/03/12-23:30:09;BASE_A5;06/03/12 00/30/55;0Wh;0varh;3;;;06/03/12 00/30/00;0kW;06/03/12 00/20/00;1kW;06/03/12 00/10/00;1kW;06/03/12 00/00/00;1kW;05/03/12 23/50/00;0kW;05/03/12 23/40/00;1kW;01/03/12 02/00/00;01/01/92 00/00/00;5;189kWh;;0kWh;540kWh;;0kWh;;0kWh;0kWh;367kWh;0kWh;;;;01/02/12 02/00/00;01/03/12 02/00/00;5;2kvarh;;0kvarh;0kvarh;;0kvarh;;0kvarh;0kvarh;3kvarh;0kvarh;;;;167kvarh;;0kvarh;199kvarh;;0kvarh;;0kvarh;0kvarh;357kvarh;0kvarh;;;;90%;80%;4kW;;4kW;0kW;4kW;0kW;4kW;4kW;0kW;;;;;;0kW;1kW;0kvar;0kvar;0,00;20400V;INJECTION;;;;;0varh;;;;06/03/12 00/30/00;0kW;06/03/12 00/20/00;0kW;06/03/12 00/10/00;0kW;06/03/12 00/00/00;0kW;05/03/12 23/50/00;0kW;05/03/12 23/40/00;0kW;;11639kWh;86kvarh;3765kvarh;;731kW;0kW;0kW;0kvar;0kvar;0,000A;0,000A;0,000A;;;;;
05/03/12-23:40:08;BASE_A5;06/03/12 00/40/55;0Wh;0varh;3;;;06/03/12 00/40/00;1kW;06/03/12 00/30/00;0kW;06/03/12 00/20/00;1kW;06/03/12 00/10/00;1kW;06/03/12 00/00/00;1kW;05/03/12 23/50/00;0kW;01/03/12 02/00/00;01/01/92 00/00/00;5;189kWh;;0kWh;540kWh;;0kWh;;0kWh;0kWh;367kWh;0kWh;;;;01/02/12 02/00/00;01/03/12 02/00/00;5;2kvarh;;0kvarh;0kvarh;;0kvarh;;0kvarh;0kvarh;3kvarh;0kvarh;;;;167kvarh;;0kvarh;199kvarh;;0kvarh;;0kvarh;0kvarh;357kvarh;0kvarh;;;;90%;80%;4kW;;4kW;0kW;4kW;0kW;4kW;4kW;0kW;;;;;;0kW;1kW;0kvar;0kvar;0,00;20466V;INJECTION;;;;;0varh;;;;06/03/12 00/40/00;0kW;06/03/12 00/30/00;0kW;06/03/12 00/20/00;0kW;06/03/12 00/10/00;0kW;06/03/12 00/00/00;0kW;05/03/12 23/50/00;0kW;;11639kWh;86kvarh;3765kvarh;;731kW;0kW;0kW;0kvar;0kvar;0,000A;0,000A;0,000A;;;;;
05/03/12-23:50:07;BASE_A5;06/03/12 00/50/55;0Wh;0varh;3;;;06/03/12 00/50/00;1kW;06/03/12 00/40/00;1kW;06/03/12 00/30/00;0kW;06/03/12 00/20/00;1kW;06/03/12 00/10/00;1kW;06/03/12 00/00/00;1kW;01/03/12 02/00/00;01/01/92 00/00/00;5;189kWh;;0kWh;540kWh;;0kWh;;0kWh;0kWh;367kWh;0kWh;;;;01/02/12 02/00/00;01/03/12 02/00/00;5;2kvarh;;0kvarh;0kvarh;;0kvarh;;0kvarh;0kvarh;3kvarh;0kvarh;;;;167kvarh;;0kvarh;199kvarh;;0kvarh;;0kvarh;0kvarh;357kvarh;0kvarh;;;;90%;80%;4kW;;4kW;0kW;4kW;0kW;4kW;4kW;0kW;;;;;;3kW;1kW;0kvar;0kvar;0,00;20600V;INJECTION;;;;;0varh;;;;06/03/12 00/50/00;0kW;06/03/12 00/40/00;0kW;06/03/12 00/30/00;0kW;06/03/12 00/20/00;0kW;06/03/12 00/10/00;0kW;06/03/12 00/00/00;0kW;;11639kWh;86kvarh;3765kvarh;;731kW;0kW;0kW;0kvar;0kvar;0,000A;0,000A;0,000A;;;;;
06/03/12-00:00:05;BASE_A5;06/03/12 01/00/50;0Wh;0varh;3;;;06/03/12 01/00/00;1kW;06/03/12 00/50/00;1kW;06/03/12 00/40/00;1kW;06/03/12 00/30/00;0kW;06/03/12 00/20/00;1kW;06/03/12 00/10/00;1kW;01/03/12 02/00/00;01/01/92 00/00/00;5;189kWh;;0kWh;540kWh;;0kWh;;0kWh;0kWh;367kWh;0kWh;;;;01/02/12 02/00/00;01/03/12 02/00/00;5;2kvarh;;0kvarh;0kvarh;;0kvarh;;0kvarh;0kvarh;3kvarh;0kvarh;;;;167kvarh;;0kvarh;199kvarh;;0kvarh;;0kvarh;0kvarh;357kvarh;0kvarh;;;;90%;80%;4kW;;4kW;0kW;4kW;0kW;4kW;4kW;0kW;;;;;;0kW;1kW;0kvar;0kvar;0,00;20400V;INJECTION;;;;;0varh;;;;06/03/12 01/00/00;0kW;06/03/12 00/50/00;0kW;06/03/12 00/40/00;0kW;06/03/12 00/30/00;0kW;06/03/12 00/20/00;0kW;06/03/12 00/10/00;0kW;;11639kWh;86kvarh;3765kvarh;;731kW;0kW;0kW;0kvar;0kvar;0,000A;0,000A;0,000A;;;;;

La première ligne ce sont des index qui sont identifiés dans un fichier de définition annexe, et qui nous permet de les identifier.
Les autres lignes, ce sont les données. Il y en a beaucoup, on en prend certaines pour remplir la bdd.

Les données qui sont entrées en base sont des entiers ou vide si le csv ne nous donne rien.

Le traitement que j'en fais c'est de ressortir ces données pour des graphiques.
Soit un graphique quotidien avec chaque donnée, soit une différence entre une journée et une autre, soit une différence entre un mois et un autre.
(Je ne sais pas si c'est très clair

)

**ericd69** · 25/05/2012, 21h50

montre le fichier d'entête...

c'est quoi le séparateur ; ou espace car les contenus de colonnes sont variables...?

l'idée va être de passer par une table temporaire d'import puis peupler la table finale plus "parlante"

est-ce que tu as besoin de tout garder? pour d'autres traitements? ou tu n'auras besoin que toujours des mêmes?

la génération des graphs c'est relativement facile...

Requête de date > optimisation

Requêtes MySQL

Discussions similaires

Partager

Partager