Messdaten zu oder vor bestimmten Zeitpunkt finden

[e61_phil]

Hallo,

ich habe ein Tabelle in der Sensormesswerte beim Überschreiten einer gewissen Toleranz gespeichert werden. Die Tabelle sieht folgendermaßen aus:

logtime -> Zeitpunkt der Speicherung des Messwertes
value -> Messwert
SensorID -> ID des Sensors

Ich möchte jetzt diverse Sensorwerte zum Zeitpunkt einer Messung auslesen. Dafür würde ich gerne eine Liste von SensorIDs und den Zeitpunkt der Messung übergeben. Dabei sollte dann für jede SensorID der Messwert der zum Messzeitpunkt oder zuletzt davor gültig war zurückgegeben werden. Wie stell ich das am besten an?

Vielen Dank schon mal
Philipp

[fanderlf]

[highlight=sql]
SELECT *
FROM messwert m
JOIN (
SELECT MAX(maximum.logtime) as logtime, maximum.sensorid
FROM messwert maximum
WHERE maximum.logtime <= "Hier Dein gewünschter Zeitpunkt her"
GROUP BY maximum.sensorid
) max_time
WHERE max_time.logtime = m.logtime
AND max_time.sensorid = m.sensorid
[/highlight]

Sowas in der Art. Der JOIN sucht das Maximum aller Messwerte pro sensorid vor einem bestimmten Datum. Danach sucht Du die Einträge raus bei den Daten und SensorID übereinstimmen. Ich habs allerdings nicht probiert. Kann sein dass das so nicht funktioniert. Keine Garantie

[e61_phil]

Vielen Dank

SELECT *
FROM sensordata m
JOIN (
  SELECT MAX(Last_Log) AS logtime, maximum.SensorID
  FROM sensordata maximum
  WHERE Last_Log <= '2012-06-27 6:57:39'
  GROUP BY maximum.SensorID
) max_time
WHERE max_time.logtime  = m.Last_Log
AND max_time.SensorID = m.SensorID

So habe ich das jetzt mal gemacht. Das sieht auch sehr gut aus, allerdings dauert so ein Query im phpmyadmin nun 8,2s. Zuvor habe ich es mit 30 einzelnen Queries gemacht, die von LabVIEW nacheinander die einzelnen Sensoren geholt haben. Einfach mit Last_Log >= Messzeitpunkt, nach Zeit sortiert und dann Limit = 1. Mit dieser alten (und hässlichen) Methode dauerte das Laden eines Datensatzes etwas über eine Sekunde. Ich hatte eigentlich gehofft, dass ein einzelnes Query nun schneller wird und nicht langsamer

[e61_phil]

Warum reicht eigentlich nicht folgendes Query:

SELECT MAX(maximum.Last_Log) AS logtime, maximum.Value, maximum.SensorID
  FROM sensordata maximum
  WHERE Last_Log <= '2012-06-27 6:57:39'
  GROUP BY maximum.SensorID

Oder übersehe ich hier etwas?

[fanderlf]

Die Query funktioniert so nicht wie sie dasteht maximum.Value wird wohl automatisch mit in das GROUP BY Statement mit aufgenommen und dann wird auch noch nach maximum.Value gruppiert. Heisst wenn Du mehrere Messeinträge zu einem Sensor hast, dann hast Du auch mehrere in der Ausgabetabelle.
Soweit ich mich erinnere ist das auch kein gültiges SQL Kommando. Manche DBRMS fügen die Spalten im SELECT Teil allerdings automatisch zum GROUP BY hinzu wenn diese dort nicht auftauchen.

Was mich ein bischen wundert ist, dass es so extrem langsam ist. Dauert das innere Statement schon so lange? Du kannst mal versuchen das separat auszuführen. Das äussere (inklusive dem JOIN), dürfte eigentlich nicht wahnsinnig lange dauern. Ausserdem könnte man noch versuchen auf die log_time Spalte einen Index zu setzen, sofern das Deine DB beim Einfügen der Daten performancemäßig aushält.

[e61_phil]

Ich habe bereits einen Index auf der Logtime. Das Query, dass ich zuletzt gepostet habe liefert das gleiche Ergebnis wie das mit dem JOIN. Das ohne JOIN stellt ja quasi das Innere da und benötigt auch 8,2s. Momentan läuft die Software wieder so, dass 30mal ein SELECT mit order by lastlog und where lastlog <= messzeit ist und hinten dran wieder limit=1. Damit dauert das ganze dann "nur" 1,2s.

Das Problem ist, dass beim Laden dieser Daten teilweise 500 Messungen geladen werden. Da waren die 1,2s schon nicht so schön

[defo]

Ich habe bereits einen Index auf der Logtime. Das Query, dass ich zuletzt gepostet habe liefert das gleiche Ergebnis wie das mit dem JOIN. Das ohne JOIN stellt ja quasi das Innere da und benötigt auch 8,2s.

Du solltest sicherstellen, dass der Index auch greift. D.h. Ausführungsplan anzeigen lassen und kontrollieren. Vermutlich greift er nicht. Das ist aber ohne Angaben von Datenmengengerüst und Serverleistung Spekulation.
1. Der Vergleich datumswert <= 'Textdatumswert' wird ggF. suboptimal analysiert und umgesetzt. Textdatumswert durch eine Funktion ersetzen, die auch einen Datumstyp zurückliefert.
2. Angenommen, es ist eine sehr große Tabelle und es werden nur die letzten 48h oder so benötigt, diese Gegebenheit in der Query nutzen und die Grundmenge einschränken. Z.B. in dem die Tabelle 'Messwert' durch einen View 'VMesswert' ersetzt wird, der lediglich eine Datumsbeschränkung auf 'Messzeit'>Systemdatum-2 Tage durchführt. Das kann man allerdings nur machen, wenn sichergestellt ist, dass dieser Grenzwert nicht an anderer Stelle verletzt wird.

[Falk Prüfer]

Hallo,

evtl. könnte man die Abfrage auch noch umformulieren, da MySQL bei der JOIN-Variante mit Sicherheit einen Full-Table-Scan macht und den Index auf Last_Log nicht effizient nutzen kann.
[highlight=sql]
SELECT *
FROM sensordata m
WHERE m.Last_Log <= '2012-06-27 6:57:39'
AND m.Last_Log = (
SELECT MAX(maximum.Last_Log) logtime
FROM sensordata maximum
WHERE maximum.Last_Log <= '2012-06-27 6:57:39'
AND maximum.SensorID = m.SensorID
GROUP BY maximum.SensorID
)
[/highlight]
Ein Index auf SensorID bringt hier sicher auch was und ansonsten müsste man sich mal den Explain-Plan ansehen. Auch stellt sich mir die Frage nach einer Logischen Optimierung. Eine Abfrage bis zu einem bestimmten Datum liefert u.U. (je nach Größe der Logtabelle) sehr viele Datensätze. Davon interessieren jedoch nur die jeweils Letzten! Die Frage ist also: Innerhalb welchen Zeitraums um den Abfragezeitraum herum befinden sich die interressanten Daten mit Sicherheit? Wäre also eine zusätzliche Einschränckung der Form
[highlight=sql]
...
WHERE m.Last_Log BETWEEN '2012-06-27 6:57:39' - X AND '2012-06-27 6:57:39'
...
[/highlight]
möglich? (Das - X steht hier nur symbolisch für das "Abfrageintervall")

Gruß Falk

[fanderlf]

Leider kann ich Dir nicht sagen warum Deine Abfrage auch funktioniert. Wenn sie das tut ist es super Für mich sieht es nur nicht ganz richtig aus. Wie sieht das der Rest hier? Ich kann mich ja auch täuschen und lerne gerne dazu

[Falk Prüfer]

...Wie sieht das der Rest hier? Ich kann mich ja auch täuschen und lerne gerne dazu

Die Abfrage
[highlight=sql]
SELECT MAX(maximum.Last_Log) AS logtime, maximum.Value, maximum.SensorID
FROM sensordata maximum
WHERE Last_Log <= '2012-06-27 6:57:39'
GROUP BY maximum.SensorID
[/highlight]
ist Murks und weder nach altem noch nach neuem SQL-Standard gültig und damit nur unter MySQL lauffähig. Der Wert der hier bei maximum.Value angezeigt wird, ist mehr oder weniger zufällig und damit das Ergebnis nicht nachvollziehbar. Das ein erwartetes Ergebnis erzielt wird, ist reiner Zufall und ich würde mich darauf nicht verlassen!

Gruß Falk

Anmerkung @e61_phil: Wenn du Tabellen-Aliase verwendest, dann solltest du dies konsequent tun! Dein Select-Teil hat Aliase, die Where-Klausel nicht. Das erhöht nicht unbedingt die Lesbarkeit!

[e61_phil]

Hallo,

vielen Dank für die Unterstützung hier. Ich hab jetzt den JOIN wieder drin und den Zeitraum in beide Richtungen eingegrenzt, somit dauert die Abfrage nun ca. 30ms und nicht mehr 8200ms. Ich werde mich mal informieren, was unter Explainplan und Ausführungsplan überhaupt zu verstehen ist.

Viele Grüße
Philipp