Optimisation Update ou Delete + Insert

Version imprimable

29/12/2009, 17h57
Invité

Optimisation Update ou Delete + Insert

Bonjour

Je développe un programme de consolidation de données dont le principe est le suivant :
Une application récupère des valeurs correspondants à différentes grandeurs chaque minute et les stocke dans une table de valeur, avec les identifiants de la valeur et la date - heure de mesure - les id + le date_time sont la clé de la table.

Pour consulter les données sous la forme de sommes, moyennes, minimum ou maximum plus rapidement (par 5 minutes, par heure ou encore par jour ou par mois), on a fait le choix de créer des tables "en dur" des valeurs groupées qui sont calculés simplement avec un Select AVG(valeur) et un Group By sur FLOOR(Minute(valeur.Date_Time)/5)*5) pour les 5 minutes par exemples.

Ca marche très bien jusque là, et au lancement de l'application de consolidation, toutes les valeurs sont recalculées en à peine 1 minute sur 1 machine qui n'est pourtant pas super puissante. La table des valeurs compte actuellement environ 1 million d'enregistrements pour 60 grandeurs.

Là où ça se corse, c'est lorsque je veux faire des mises à jours, puisque chaque minute de nouvelles valeurs sont insérés dans ma table principale :
Chaque 5 minutes, je récupère donc pour chaque grandeur l'heure de la dernière mise à jour puis réalise un INSERT avec un ON DUPLICATE KEY UPDATE. Les valeurs insérées sont issues d'un SELECT similaire au premier. Ca fonctionne mais c'est vraiment très lent : 5 fois plus de temps la méthode DELETE puis INSERT, alors que le volume de données traitées est bien plus faible !

Comment pourrais je faire pour optimiser cela ? Sans passer par une appli mais directement avec des triggers sur ma table des valeurs dans mysql ?

Merci d'avance.
30/12/2009, 02h38
Antoun

Comme je ne comprends pas tout... à quoi ressemble ton INSERT... ON DUPLICATE KEY UPDATE ?

Voila ma requête pour la mise à jour des dernières valeurs moyennes par heures :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
INSERT INTO valeur1h 
	SELECT valeur.id_site, 
	Concat(Year(valeur.Date_time),'-', Month(valeur.Date_time),'-', Day(valeur.Date_time),' ', HOUR(valeur.Date_Time),':',"00:00") as 1heure,
	valeur.id_grandeur,
	ROUND(AVG(valeur.Valeur),4) as valeurCalc 
	FROM valeur 
	WHERE id_site=1 AND id_grandeur=18
	AND date_time>Date_sub(2009-12-25 12:00,INTERVAL "02:00" HOUR_MINUTE)
	GROUP BY 1heure 
ON DUPLICATE KEY 
UPDATE valeur1h =(
	SELECT ROUND(AVG(valeur.Valeur),4)
	FROM valeur 
	WHERE id_site=1	AND id_grandeur=18
	AND date_time>Date_sub(2009-12-25 12:00,INTERVAL "02:00" HOUR_MINUTE)
	AND valeur1h.date_time = Concat(Year(valeur.Date_time),'-', Month(valeur.Date_time),'-', Day(valeur.Date_time),' ', HOUR(valeur.Date_Time),':',"00:00")
	GROUP BY 1heure)

30/12/2009, 11h42
Antoun

Qu'est-ce que ça donne avec un REPLACE ?
30/12/2009, 17h23
Invité

Je viens de tester, c'est beaucoup beaucoup plus lent :(
Je n'ai même pas laisser finir la boucle, supérieur à 1 heure facilement...

Merci en tout cas.
31/12/2009, 01h28
Antoun

la boucle ? s'il y a une boucle quelque part, il est clair qu'il faut lui tordre le cou ! Faire des boucles en SQL revient à rouler en première sur l'autoroute...
31/12/2009, 10h16
Invité

parce que j'ai n grandeurs. Je fais donc ma requête pour chacune des grandeurs, dans un boucle (en c#).
31/12/2009, 11h14
Antoun

et tu as combien de grandeurs ?
31/12/2009, 14h59
Invité

actuellement 70

Peut être 300 dans quelques mois.