MariaDB: in Stored Procedure auf Werte von einem Select zugreifen

M

Muschelpuster

Benutzer
Beiträge
22
Moin zusammen,

ich möchte eine Prozedur bauen, welche mit Select Daten aus einer Tabelle einliest und diese in einem 2. Statement verwendet.
Hintergrund: Ich bekomme Daten mit einer Start- und einer Endzeit im Unix-Format. Diese schreibe ich in eine Tabelle und die Start- und Endzeit nochmal gesondert in eine 2. Tabelle. Nun konnte ich bereits mit der freundlichen Unterstützung hier ein schönes Statement bauen mit dem ich die Zeitreihe aus der 2. Tabelle lesen und in der 1. Tabelle zähle wie viele Datensätze jetzt in diesem Zeitraum liegen. Das funktioniert perfekt, jedoch geht das mit zunehmender Zahl von Datensätzen bzw. längeren Auswertezeiträumen mächtig auf die Performance. Daher will ich jetzt eine 3. Tabelle mit Schnellsummen erstellen, welche über die Prozedur gefüllt wird. Dazu soll diese immer bei einem neuen Datensatz getriggert werden und Schnellsummen zu dem neuen Messpunkt erzeugen.
Hier gibt es jedoch eine kleine Ungenauigkeit: Wenn nach Datensatz A ein weiterer Datensatz folgt, welcher eine Startzeit vor der Endzeit von A hat, so stimmt die zuvor ermittelte Schnellsumme nicht mehr. Daher soll die Prozedur alle Schnellsummen aktualisieren, welche einen Zeitstempel größer der Startzeit haben. Also müsste ich da ein Select auf meine Zeitreihe machen in der ich eine Menge von Zeitstempeln zurück bekomme, die nun in einer schleife neu berechnet werden müssen.
Ist das überhaupt mit MariaDB möglich?

Niels
 
Werbung:
SQL in Schleifen ist fast immer ein guter Weg zu sehr schlechter Performance. Was Du suchst klingt für mich eher nach materialized Views, aber ob das Deine DB kann weiß ich nicht. Performance-Probleme in Abfragen lassen sich gut mit EXPLAIN (ANALYSE) untersuchen, nur ist die Ausgabe davon unter MySQL und Co. ja eher für die Tonne.
 
Muschelpuster schrieb:
möchte eine Prozedur bauen, welche mit Select Daten aus einer Tabelle einliest und diese in einem 2. Statement verwendet.
Zum Vergrößern anklicken....

Das ist so wie es schon da steht, eine sehr „prozedurale“ Herangehensweise. Nicht das, was eine Datenbank mag.

Du möchtest nichts einlesen, wie so ein kleiner Wintervorrat Nüsse, die man dann bei Gelegenheit durchkaut.

Du möchtest eigentlich nur etwas aktualisieren, sprich ein Update durchführen. Du möchtest die Datensätze einschränken, die Du aktualisieren willst und evtl. einen dynamischen Wert aus Deinem Datenbestand für die Änderung verwenden.

Das nennt man korreliertes Update / correlated Update.

Ein Befehl, alles in einem, keine SP notwendig, die nötige Schleife dreht die DB selbst (ich glaube, das kann auch Maria).



Du kannst damit beginnen,

- ein Select Statement zu schreiben, dass (genau) die benötigten Daten liefert, also passende Einschränkung und benötigten Update Wert

- ein Update Statement zu schreiben, das so tut, als kennte es bereits, die neuen Werte.



Dann beides zusammen bauen.
 
Ich danke Euch für die Anregungen.

akretschmer schrieb:
SQL in Schleifen ist fast immer ein guter Weg zu sehr schlechter Performance. Was Du suchst klingt für mich eher nach materialized Views, aber ob das Deine DB kann weiß ich nicht. Performance-Probleme in Abfragen lassen sich gut mit EXPLAIN (ANALYSE) untersuchen, nur ist die Ausgabe davon unter MySQL und Co. ja eher für die Tonne.
Zum Vergrößern anklicken....
Das mit der nicht optimalen Perfomance ist mir bewusst. Der Gedanke dabei ist nur, lieber ein permanentes 'Grundrauschen' auf der DB zu haben, anstatt eines großen langen Peaks, wenn die Auswertung über einen längeren Zeitraum abgerufen wird.

dabadepdu schrieb:
Das nennt man korreliertes Update / correlated Update.
Zum Vergrößern anklicken....
Damit habe ich begonnen. Ich bin gescheitert :-(

Hier mal meine derzeitige Grundstruktur mit ein paar Daten:
Code: 
CREATE TABLE `messzeitpunkte` (
  `m_zeit` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
ALTER TABLE `messzeitpunkte`
  ADD PRIMARY KEY (`m_zeit`);

INSERT INTO `messzeitpunkte` (`m_zeit`) VALUES
(11),
(18),
(19),
(20),
(22),
(30),
(31),
(40);

CREATE TABLE `daten` (
  `d_start` bigint(20) NOT NULL,
  `d_ende` bigint(20) NOT NULL,
  `d_messpunkt` varchar(20) COLLATE utf8mb4_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
ALTER TABLE `daten`
  ADD PRIMARY KEY (`d_start`,`d_ende`,`d_messpunkt`);

INSERT INTO `daten` (`d_start`, `d_ende`, `d_messpunkt`) VALUES
(11, 20, 'P2'),
(18, 40, 'P1'),
(19, 31, 'P2'),
(20, 30, 'P1'),
(22, 40, 'P1');

Derzeit sieht eine Auswertung wie folgt aus:
Code: 
SELECT `m_zeit` as Zeit, (SELECT COUNT(`d_messpunkt`) FROM `daten` where `d_start`<= Zeit and `d_ende`> Zeit AND `d_messpunkt`= 'P1') as Zaehler FROM `messzeitpunkte` as M WHERE `m_zeit` >= '8' and `m_zeit` < '39';

Bei den Spieldaten natürlich kein Problem, aber wenn die Datenreihen lang werden...
Das kann ich natürlich so in die von mir gewünschte Schnellsummentabelle pumpen. Aber die bräuchte ja etwas mehr, denn ich brauche ja auch noch die Daten für die anderen Messpunkte. Daher schwebt mir folgende Struktur vor:
Code: 
CREATE TABLE `schnellsummen` (
  `s_zeit` bigint(20) NOT NULL,
  `s_zaehler` int(11) NOT NULL,
  `s_messpunkt` varchar(20) COLLATE utf8mb4_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
ALTER TABLE `schnellsummen`
  ADD PRIMARY KEY (`s_zeit`,`s_messpunkt`);

Ich kann zwar mit alle verfügbaren Messpunkte mit 'GROUP BY' lesen, aber das lässt sich nicht verknüpfen, da das eingebettete SELECT dann mehr als eine Datenreihe zurück gibt.
Zudem fügt ein neuer Datensatz 2 Werte in Tabelle Messzeitpunkte ein (Start & Ende) und es reicht nicht diese Punkte über einen Trigger neu zu berechnen, alle ggf. schon berechneten Messpunkte dazwischen stimmen ja auch nicht mehr.
Ich könnte natürlich annehmen, dass das Start & Ende maximal um den Wert X auseinander liegen. Dann könnte ich (z.B. Nachts oder auch bei jedem neuen Insert in die Tabelle Daten) eine Berechnung der mindestens um X in der Vergangenheit liegenden Zeitpunkte triggern. Um nicht immer alle Daten wieder zu berechnen könnte ich in der Tabelle Messpunkte eine neue Spalte m_in_schnellsummen schaffen, über die ich die Werte als berechnet markiere. Die Auswertung müsste dann die Summe aus beiden Tabellen liefern. Aber das Problem mit den unterschiedlichen (und nicht fest definierbaren) Messpunkten bleibt.

Niels
 
Zuletzt bearbeitet:
Oh Mann - manchmal hat man nicht nur ein Brett vor dem Kopf, sondern einen ganzen Wald!
Ich muss ja nur die Werte für den Messpunkt aktualisieren, für den gerade ein neuer Datensatz aufgeschlagen ist - bei den Anderen kann sich ja nichts verändert haben. Von daher fällt das Auslesen aller verfügbaren Messpunkte flach. Auch kenne ich Start und Ende aus dem Datensatz. Damit habe ich doch alles, was ich brauche in der Tabelle 'daten' und kann hier einen Trigger setzen, welcher mir alle erforderlichen Informationen in die Stored Procedure liefert...

Niels
 
Bleibt nur noch die Frage, warum der Trigger nicht triggert:
Code: 
DELIMITER $$
CREATE TRIGGER Schnellsummen AFTER INSERT ON daten FOR EACH ROW BEGIN
       REPLACE INTO `schnellsummen` SELECT `d_start` as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start`<= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) as Zaehler, `d_messpunkt` as Messpunkt FROM `daten` WHERE `d_start` >= NEW.d_start and `d_ende` < NEW.d_start and `d_messpunkt` = NEW.d_messpunkt;
       REPLACE INTO `schnellsummen` SELECT `d_ende` as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start`<= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) as Zaehler, `d_messpunkt` as Messpunkt FROM `daten` WHERE `d_start` >= NEW.d_ende and `d_ende` < NEW.d_ende and `d_messpunkt` = NEW.d_messpunkt;
END$$
DELIMITER ;

Niels
 
mit der MySQL-Syntax kann ich Dir nicht helfen, aber beachte, daß der Trigger wohl auch bei UPDATE feuern sollte. Und auch, falls diese Trigger-Procedure längere Zeit braucht, sich Dein INSERT on daten dementsprechend verlängert.
 
Traue keiner Statistik, die Du nicht selber gefälscht hast ;-)
Die Messwerte stehen und sollten nachträglich nicht verändert werden, aber natürlich bestätigen die Ausnahmen bekanntlich ja die Regel, also Danke für den Hinweis. Da werde ich nochmal drauf rumkauen, wenn das Ganze erstmal läuft.
Irgendwo klemmt es noch in den Statements. Ich habe die Zeiten jetzt mal stumpf über den Trigger in die Tabelle messzeitpunkte geschrieben - das klappt...

Niels
 
Werbung:
So, jetzt aber etwas im Kopf aufgeräumt, den Müll runter gebracht und ohne die alten Statements von vorne begonnen:
Code: 
CREATE TRIGGER `Schnellsummen` AFTER INSERT ON `daten`
 FOR EACH ROW BEGIN
REPLACE INTO `messzeitpunkte` (`m_zeit`) VALUES (NEW.d_ende);
REPLACE INTO `messzeitpunkte` (`m_zeit`) VALUES (NEW.d_start);
REPLACE INTO schnellsummen SELECT d_start as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) AS Zaehler, d_messpunkt AS Messpunkt FROM `daten` WHERE d_start >= NEW.d_start AND d_start <= NEW.d_ende AND d_messpunkt = NEW.d_messpunkt;
REPLACE INTO schnellsummen SELECT d_ende as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) AS Zaehler, d_messpunkt AS Messpunkt FROM `daten` WHERE d_ende >= NEW.d_start AND d_ende <= NEW.d_ende AND d_messpunkt = NEW.d_messpunkt;
END

Niels
 
Zurück
Oben