Laden...

Lücken in Aufzeichnungen füllen

Letzter Beitrag vor 11 Jahren 6 Posts 1.279 Views
Lücken in Aufzeichnungen füllen

verwendetes Datenbanksystem: MS SQL Server 2008 (IA64)

Ich habe eine Tabelle:

Farbwechsel
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Kabine] [int] NOT NULL,
[Zeitstempel] [datetime] NOT NULL,
[Farbe] [int] NOT NULL

Die Tabelle enthält jeden Farbwechsel (nur wenn sich eine Farbe ändert), synchronisiert auf die 5-Minuten-Zeitstempel

und eine Tabelle mit Zeitstempeln:

Zeitstempel
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Zeitstempel] [datetime] NOT NULL

Die Tabelle enthält ganz einfach Zeitstempel im 5-Minuten-Takt seit Inbetriebnahme der Anwendung.
(alternativ könnte ich dafür auch eine Funktion anlegen)

Ich habe also so etwas:

Kabine - Zeitstempel - Farbe
1 - 01.01.2013 00:10:00 - 100
1 - 01.01.2013 00:25:00 - 123
1 - 01.01.2013 00:35:00 - 200

Nun baue ich mir eine Abfrage, die mir sämtliche 5-Minuten-Werte ausgeben soll, nach dem Muster:
1 - 01.01.2013 00:10:00 - 100
1 - 01.01.2013 00:15:00 - 100 (Nicht in DB!)
1 - 01.01.2013 00:20:00 - 100 (Nicht in DB!)
1 - 01.01.2013 00:25:00 - 123
1 - 01.01.2013 00:30:00 - 123 (Nicht in DB!)
1 - 01.01.2013 00:35:00 - 200

Mein Ansatz sieht so aus:
SELECT FW.Kabine, Z.Zeitstempel, MAX(FW.Zeitstempel) FROM Zeitstempel Z
JOIN Farbwechsel FW ON FW.Zeitstempel≤ Z.Zeitstempel
GROUP BY FW.Kabine, Z.Zeitstempel

Natürlich muss ich dann nochmals die Farbwechsel joinen um die Farbe zu erhalten, aber die Performance ist jetzt schon dermaßen schlecht. Führe ich das ganze auf meiner lokalen Test-DB mit 100 eingetragenen Farbwechseln aus, gibt es keine Probleme. Allerdings habe ich im Produktivsystem schon 23.455.949 Farbwechsel erfasst. Hier dauert eine solche Abfrage > 30 Minuten. Es wird in der Realität natürlich niemals der komplette Datenbestand abgerufen, aber zum Beurteilen der Performance mache ich das schon einmal ganz gerne.

Wie muss ich die Indizes richtig zu setzen um das zu beschleunigen? Ich habe schon viele Varianten probiert, aber wirklich schneller wird das ganze nicht.

Vielen Dank.

Hi,

Dein Problem liegt m.E. darin, dass Du statt Zeiträumen nur Zeitpunkte hast. Entweder Du füllst Deine Tabelle schon mit Zeitstempel_von und Zeitstempel_bis, oder aber du musst das im SQL mit eínem (oft langsamen) Subselect und nachfolgender nested Table Expression machen.

Etwa so:

SELECT *
FROM ( SELECT Kabine
, Zeitstempel as Zeitstempel_von
, (select min(Zeitstempel) from Kabine T2 where T1.Kabine = T2.Kabine and T1.Zeitstempel < T2.Zeitstempel) AS Zeitstempel_bis
, Farbe
from Kabine T1
) KABI
JOIN Zeitstempel ZEIT
ON ZEIT.Zeitstempel ≥ KABI.Zeitstempel_von
AND ZEIT.Zeitstempel < KABI.Zeitstempel_bis

Das ist zwar wahrscheinlich auch nicht sonderlich perfomant, spart aber das GROUP BY und das große Zwischenergbnis im JOIN.

Ach ja: Evtl. Index Kabine ASC (od. DESC) , Zeitstempel DESC auf Kabine

Vielen Dank für deinen Anstoß.
Ich verstehe das so: Ich setze wenn ein neuer Farbwechsel eingetragen wird, eine Endzeit des letzten Farbwechsel auf den gleichen Zeitstempel wie den neuen Anfangsstempel und joine dann mit einem between?

Jein,

Between macht

a≤ x ≤b

was dann zu zwei Treffern führt. Daher besser wie beschrieben

a≤ x <b

Nennt sich rechtsoffens Intervall und hat den Vorteil, dass man eine nahtlose Kette von Gültigkeiten erhält. Wenn bis des Vorgängers ungleich von des Nachfolgers sein soll (wie bei BETWEEN nötig) muss man die kleinstmögliche Differenz der beiden Werte kennen (Zeitatom), und das kann sich durchaus mal ändern, wenn der DB-Hersteller eine neue Version auf den Markt bringt, oder man die Datenbank wechselt.

Bzgl. des setzens des Zeitstempel_bis hast Du das natürlich richtig verstanden.

Gruß
f_igy

Super, werde ich probieren. Du hast mir sehr geholfen, vielen Dank!

Gern geschehen,

im Übrigen sollten für diese Abfrage die DB-Statistiken representativ sein, vor allem, wenn Du die Abfrage nur auf Teilmengen der Kabine machst. Ich kenne den SQL Server nicht, aber wenn es da so etwas wie Datenhistogramme gibt könnte das auch hilfreich sein.

Hintergrund sind die unterschiedlichen JOIN Methoden wie z.B. merge scan (bei allen Werten) oder nested loop (bei einer Kabine) bzw. die Wahl der führenden Tabelle, die der Optimizer je nach Statisik wählt.

Gruß
f_igy