Alternative zur Abfrage mit mehreren outer joins?

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

Antworten
KoBraSoft
Beiträge: 4
Registriert: Di 9. Feb 2021, 15:17

Hallo,
ich habe eine einfache Tabelle mit Messwerten
in der Form:
ID integer
ZEITPUNKT timestamp
Wert1 integer
Wert2 integer
Wert3 integer
Wert4 integer
...
Die Messwerte werden seit Jahren jede Viertelstunde gespeichert dh ca 35000 Datensätze pro Jahr.
Ich möchte diese Werte jeweils mit den Vorjahreswerten vergleichen bzw in Diagrammen darstellen.
Die Antwort der Abfrage für Wert1 sollte ungefähr so aussehen:

Code: Alles auswählen

Zeitpunkt       2021	2020	2019	...
1.1 0:15	1	2	3	...
1.1 0:30	4	null	6	...
 
Mein erster Ansatz war eine Abfrage:

Code: Alles auswählen

SELECT r.ZEITPUNKT , r.Wert1 as Wert2021, l.Wert1 as Wert2020
FROM WART_DATEN r join WART_DATEN l on r.ZEITPUNKT = l.ZEITPUNKT
where ... 
Die Antwortzeit ist kurz, das Ergebnis wie erwartet.
Bei Abfragen über mehrere Jahre (mehrere joins) steigt die Antwortzeit jedoch stark an.

Dramatisch wird die Lage dadurch das es in der Aufzeichnung Lücken gibt (verursacht durch Ausfälle der Messung)
Ich habe den (inner) join durch einen left (outer) join ersetzt, das Ergebnis ist ok aber die Berechnung dauert nun mehrere Stunden oder gar Tage. (Index auf Zeitpunkt ist gesetzt)

Hat jemand eine Idee wie ich das gewünschte Resultat schneller erhalte?

Konrad

Datenbank: Firebird 3.0
OS: Linux
Benutzeravatar
martin.koeditz
Beiträge: 283
Registriert: Sa 31. Mär 2018, 14:35

Hallo Konrad,

mir ist nicht ganz klar woher du die Jahreszahlen nimmst. Das geht aus deiner Abfrage nicht hervor.

Code: Alles auswählen

SELECT r.ZEITPUNKT , r.Wert1 as Wert2021, l.Wert1 as Wert2020
FROM WART_DATEN r join WART_DATEN l on r.ZEITPUNKT = l.ZEITPUNKT
where ... 
Du könntest deine Joins direkt mit den gewünschten Jahren verbinden.

Code: Alles auswählen

SELECT
   DAT2020.ZEITPUNKT, 
   DAT2020.WERT1 as WERT2020,
   DAT2021.WERT1 as WERT2021,
   DAT2022.WERT1 as WERT2022
   FROM WART_DATEN DAT2020
   LEFT JOIN WART_DATEN DAT2021 ON CAST(DAT2020.ZEITPUNKT AS TIME) = CAST(DAT2021 AS TIME) 
   	AND EXTRACT(DAY FROM DAT2020.ZEITPUNKT) = EXTRACT(DAY FROM DAT2021.ZEITPUNKT) 
   	AND EXTRACT(MONTH FROM DAT2020.ZEITPUNKT) = EXTRACT(MONTH FROM DAT2021.ZEITPUNKT) 
   	AND EXTRACT(YEAR FROM DAT2021.ZEITPUNKT) = 2021
   LEFT JOIN WART_DATEN DAT2021 ON CAST(DAT2020.ZEITPUNKT AS TIME) = CAST(DAT2022 AS TIME) 
   	AND EXTRACT(DAY FROM DAT2020.ZEITPUNKT) = EXTRACT(DAY FROM DAT2022.ZEITPUNKT) 
   	AND EXTRACT(MONTH FROM DAT2020.ZEITPUNKT) = EXTRACT(MONTH FROM DAT2022.ZEITPUNKT) 
   	AND EXTRACT(YEAR FROM DAT2021.ZEITPUNKT) = 2022
   WHERE ...
Eine gute Idee wäre sicherlich eine eigene Tabelle für die Ausgangswerte zu erstellen. Diese enthält nur die abzufragenden Zeitstempel. Hierauf werden dann die Joins wie o.a. gebildet. Index nicht vergessen. Das sollte dann relativ performant laufen.

Wenn du uns mal den Tabelleninhalt oder ein DDL zeigen könntest, finden wir sicherlich etwas Sinnvolles.

Gruß
Martin
Martin Köditz
it & synergy GmbH
bfuerchau
Beiträge: 276
Registriert: Mo 7. Mai 2018, 18:09

Eine sehr schlechte Konstellation in einer Join-Beziehung auf beiden Seiten Berechnungen durchzuführen, denn damit wird auf jeden Fall ein Tablescan erzwungen. Es sei denn, für den Ausdruck in der Zieltabelle gibt es explizit einen berechneten Index.

Für die Darstellung würde ich so gar nicht SQL wählen.
Lade die Werte einzeln in der Form

Zeitpunkt - Jahr - Wert

Und packe das Ergebnis in Excel (Import via ODBC). Dort kannst du dann simpel eine Pivot-Tabelle draus machen. Du hast Filter/Drilldown-Möglichkeiten und kannst auch Charts erstellen.
bfuerchau
Beiträge: 276
Registriert: Mo 7. Mai 2018, 18:09

Nachtrag:
Wie wärs denn mit einem SQL der vollkommen ohne Joins auskommt?
Den habe ich mal vor über 10 Jahren von einem Informatik-Studenten bekommen (die lernen ja doch schon was):

select
<Zeitpunkt> as Zeitpunkt
, sum(case when <Jahr> = 2018 then <value> end) as W2018
, sum(case when <Jahr> = 2019 then <value> end) as W2019
:
:
from MyTable
group by 1

Der Nachteil der Joins ist, dass nur alle Zeitpunkte der Haupttabelle gewählt werden.
Es kann aber für diverse Jahre auch unterschiedliche Zeitpunkte geben.
Mit diesem SQL erwischt man alle und er ist sauschnell.
Dieses Verfahren lässt sich für alles mögliche verwenden, um Pivot-ähnliche Strukturen zu erhalten, wenn sie von der Datenbank native (wie SQL-Server) nicht unterstützt werden.
Natürlich lassen sich mehrere Gruppenstufen als auch diverse andere Aggregate bilden.
Für die <xxx>-Ausdrücke sind natürlich ebenso beliebige Berechnungen einsetzbar.
Benutzeravatar
martin.koeditz
Beiträge: 283
Registriert: Sa 31. Mär 2018, 14:35

Wie visualisierst du die Daten? Über eine Web-Anwendung oder offline, z.B. mit Excel. Bei letzterem wäre der Ansatz von bfuerchau sinnvoll.

Gruß
Martin
Martin Köditz
it & synergy GmbH
KoBraSoft
Beiträge: 4
Registriert: Di 9. Feb 2021, 15:17

Hallo Martin,


martin.koeditz hat geschrieben: Di 9. Feb 2021, 19:16 Eine gute Idee wäre sicherlich eine eigene Tabelle für die Ausgangswerte zu erstellen. Diese enthält nur die abzufragenden Zeitstempel. Hierauf werden dann die Joins wie o.a. gebildet. Index nicht vergessen. Das sollte dann relativ performant laufen.
Eine eigene Tabelle für die Ausgangswerte zu erstellen ist mein Plan B (Ich möchte mich noch mit der Antwort vom bfuerchau auseinander setzen). Erstmalig würde ich sie mit einer Abfrage füllen (da spielen ein paar Stunden keine Rolle) und dann die laufend hereinkommenden Messwerte mit einem Trigger.

Ich bräuchte dann entweder so eine Tabelle für jeden Wert einzeln und müsste diese dann in der Abfrage verknüpfen, da ich

Code: Alles auswählen

Zeitpunkt       2021	2020	2019	...
1.1 0:15	1	2	3	...
1.1 0:30	4	null	6	...
oder eine Tabelle für alle Werte (der Zeit ca 50, die Tabelle würde sehr breit )

Code: Alles auswählen

Zeitpunkt       W1_2021	W1_2020	W1_2019	...	W2_2021	W2_2020	W2_2019	...
1.1 0:15	1	2	3	...	7	7	8	...
1.1 0:30	4	null	6	...	1	2	3	...	
Danke
Konrad
KoBraSoft
Beiträge: 4
Registriert: Di 9. Feb 2021, 15:17

martin.koeditz hat geschrieben: Mi 10. Feb 2021, 10:39 Wie visualisierst du die Daten?
Erstmal mit einer fat client Anwendung die ich mit Lazarus schreibe. Der Vorteil ist, dass ich dort gut filtern und im Diagramm gut und schnell zoomen und verschieben kann. Später soll noch eine Webseite für Fernzugriff mit Tablet und Handy dazu kommen.
KoBraSoft
Beiträge: 4
Registriert: Di 9. Feb 2021, 15:17

bfuerchau hat geschrieben: Mi 10. Feb 2021, 10:02 Nachtrag:
Wie wärs denn mit einem SQL der vollkommen ohne Joins auskommt?

Den habe ich mal vor über 10 Jahren von einem Informatik-Studenten bekommen (die lernen ja doch schon was):

select
<Zeitpunkt> as Zeitpunkt
, sum(case when <Jahr> = 2018 then <value> end) as W2018
, sum(case when <Jahr> = 2019 then <value> end) as W2019
:
:
from MyTable
group by 1
Mit diesem SQL erwischt man alle und er ist sauschnell.
Dieses Verfahren lässt sich für alles mögliche verwenden, um Pivot-ähnliche Strukturen zu erhalten, wenn sie von der Datenbank native (wie SQL-Server) nicht unterstützt werden.
Natürlich lassen sich mehrere Gruppenstufen als auch diverse andere Aggregate bilden.
Für die <xxx>-Ausdrücke sind natürlich ebenso beliebige Berechnungen einsetzbar.
Hört sich gut an. Das werde ich mir genauer anschauen und testen.

Dein Vorschlag bringt mich auf die Idee, ob ich die Messwerte evtl. in ein (teil)dynamisches dreidimensionales Array laden soll.
x-Achse Messstellen
y-Achse Viertelstunden
z-Achse Jahre
Wenn das Array im Arbeitsspeicher ist geht es kaum schneller, das erstmalige Laden könnte dauern, ist aber nicht schlimm. Evtl könnte ich das Array als Filestream auf der Platte des Clients cachen und nur neue Werte nachladen.

Überschlagsrechnung Arraygröße:
30000 Messwerte pro Jahr x 10 Jahre x 50 Messstellen x 4 byte (int32) = 60000000 byte (60 Mb) -> sollte kein Problem sein. Oder?

danke, hast mir gut weitergeholfen

Konrad
Antworten