Tabellen für viele kleine Datensätze optimieren

[Medium]

Jetzt machst du mich aber spontan unglücklich, p80286. Ich habe soeben folgende Prozedur im Testlauf, die da alle 10 Sekunden* vom selben Programm ausgeführt wird, dass auch die originale Historie schreibt, und für das Pollen meiner Energiewerte sorgt:

Delphi-Quellcode:

procedure TForm1.MakeGranularValues;
  procedure ProcessGranularity(aTableName, aPriorGranularityTableName: String; aTimeDeltaInMinutes: Integer);
  var
    d1, d2: TDateTime;
  begin
    // Einen Zeitpunkt finden, zu dem der End-Zeitpunkt d2 in der Vergangenheit liegt, und
    // dessen Start-Zeitpunkt dem Raster gehorcht, dass entsteht, wenn man ab 00:00:00 des Tages an
    // in "aTimeDeltaInMinutes" Schritten hochgezählt hat. Beginne mit 0 Uhr des gestrigen Tages, da
    // 24 Stunden das größte Raster sind, und der gestrige Tag somit noch erfasst wird.
    d1 := Trunc(Now)-1;
    d2 := IncMinute(d1, aTimeDeltaInMinutes);
    while (d2 < Now) do
    begin
      d1 := d2;
      d2 := IncMinute(d1, aTimeDeltaInMinutes);
    end;
    d1 := IncMinute(d1, -aTimeDeltaInMinutes);
    d2 := IncMinute(d2, -aTimeDeltaInMinutes);

    Qry.SQL.Text := 'SELECT COUNT(valueID) FROM '+aTableName+' WHERE vdate BETWEEN :d1 AND :d2';
    Qry.ParamByName('d1').AsDateTime := d1;
    Qry.ParamByName('d2').AsDateTime := d2;
    Qry.Open;
    if Qry.Fields[0].AsInteger = 0 then
    begin
      Qry.Close;
      Qry.SQL.Text :=
        'INSERT INTO '+aTableName+' (vdate, valueID, maxValue, meanValue) '+
        '(SELECT :d1, valueID, MAX(maxValue), SUM(meanValue)/COUNT(meanValue) '+
        'FROM '+aPriorGranularityTableName+' '+
        'WHERE (vdate BETWEEN :d1 and :d2) '+
        'GROUP BY valueID)';
      Qry.ParamByName('d1').AsDateTime := d1;
      Qry.ParamByName('d2').AsDateTime := d2;
      Qry.Execute;
    end
    else
    begin
      Qry.Close;
    end;
  end;
begin
  ProcessGranularity('history_g10min', 'history',          10);
  ProcessGranularity('history_g30min', 'history_g10min',   30);
  ProcessGranularity('history_g1h',    'history_g30min',   60);
  ProcessGranularity('history_g6h',    'history_g1h',     360);
  ProcessGranularity('history_g24h',   'history_g6h',    1440);
end;

Klar ist, dass wenn diese irgendwo mal auf einen Hammer läuft, dann kummulierte Daten fehlen. Jedoch werde ich da noch diverse Schutzblöcke einarbeiten, und es spricht nichts dagegen im Bedarfsfall eine komplette Rekunstruktion der kummulierten Tabellen von der originalen Historie an zu bilden. Oder gar nur punktuelle für fehlende Zeiträume.

Bei Views wüsste ich jetzt nicht so genau, wie ich die Anforderung erfüllen sollte, die in dem längeren Kommentar beschrieben ist. Und Views werden doch auch dynamisch ausgeführt oder? Die Abfrage auf die originalen "feinen" Daten würde damit ja trotzdem nötig werden. Zwar dann verdichtet über's Netzwerk gehen, aber der Server hätte dennoch die volle Arbeit zu tragen. Bin da skeptisch.


*) Alle 10min sollte genügen, zum testen und schauen wie es sich auf die Performance auswirkt hab ich's mal schneller gemacht

[Dejan Vu]

jasocul meinte 'materialized views' oder 'indexed views', wie sie bei SQL-Server heißen. Das sind keine Views im klassischen Sinn, also einfach nur kompilierte Queries, sondern wirkliche Tabellen (weswegen sie bei Oracle eben 'materialized' heißen). Bei Oracle muss man die Aktualisierung selbst anstubsen, soweit ich mich erinnere, bei SQL-Server nicht.

Da das normale Tabellen sind, kann man eben auch einen Index draufpacken, was bezüglich der Auswertungen wirklich eine tolle Sache ist.

Ich sehe gerade, das geht auch irgendwie mit mySQL:
http://www.fromdual.com/mysql-materialized-views

PS: Kennt mySQL kein 'AVG' (als Ersatz zu SUM(**)/COUNT(**))? Wobei das eben auch in die Hose gehen kann, wenn Count(*) = 0 ist...

[Medium]

Wenn COUNT() 0 ist, werden keine Sätze selektiert, also auch die Funktion nicht ausgeführt. Gut möglich, dass es AVG() gibt - ich schaue mal. Hab nicht daran gedacht, dass es das ja schon fertig geben könnte

Die materialized Views schaue ich mir mal an, klingt "spacig".

[p80286]

Zitat von Medium:

Jetzt machst du mich aber spontan unglücklich, p80286.

War nicht meine Absicht. Ich kann Dir nur sagen, das ich mit Datawarehouse bisher nur Ärger habe, was auch daran liegt, das die DatenAnforderer sich nicht darüber im klaren sind, daß die Daten -in meinem Fall- vom Vortag sind. Sogar bei statistischen Auswertungen ist das schon aufgefallen.

Darum
1. es kommt darauf an
2. solange es läuft ist es in Ordnung
3. Ein Patentrezept gibt es nicht.

Gruß
K-H

[Namenloser]

Mal ganz ketzerisch gefragt: Braucht man da überhaupt SQL? Ich meine, bei Webservern und generell unter Unix ist es ja auch Gang und Gäbe, Zugriffslogs (das können auch gerne mal mehrere Eintrage pro Sekunde werden) einfach als Textdatei mit einem Datensatz pro Zeile abzuspeichern und in regelmäßigen Abständen, z.B. einmal am Tag, ein neues Logfile anzufangen und das alte ggf. zu komprimieren. So hat man dann eine Reihe von Logfiles, für jeden Tag eins.

Wenn man eigentlich eh nur den Verlauf plotten will und nicht wirklich komplizierte relationale Abfragen hat, dann reicht das doch im Grunde.

[Dejan Vu]

Die Frage ist gar nicht ketzerisch, denn man soll ja immer den einfachsten und kürzesten Weg gehen: jede Form der Abfrage, sogar das speichern selbst, ist per Textdatei mit einem höheren Aufwand verbunden, als ein RDBMS damit zu beauftragen.

[Namenloser]

Ich habe mich halt nur gefragt, ob SQL wirklich das richtige Werkzeug ist... meiner Meinung nach glänzt SQL, wenn man komplexe Beziehungen, auch über verschiedene Tabellen hinweg, auswerten will. Für einfache Messreihen sehe ich nicht so den Vorteil.

NoSQL wurde in den letzten Jahren ja sehr gehyped, aber ich denke das hier könnte mal ein echter Anwendungsfall für so etwas sein. Allerdings, bevor man sich den ganzen Overhead antut, vielleicht tut es ja auch schon eine einfache, bewährte Log-Datei, so ganz KISS. Das war so mein Gedankengang.

[mkinzler]

NoSQL eignet sich imho eher für uneinheitliche Daten. In diesem Fall sind diese ja sehr standardisiert.

Textdateien sind schwerer auszuwerten.

[DSP]

Moin Medium,

weisst du welches Datenvolumen du da im Jahr generierst?

Ich würde hier eine rollierende Datenhaltung aufbauen mit passenden Summentabellen. Die Details, einfach im Verzeichnis ablegen und bei bedarf einlesen. Hier reicht eine CSV bei weiten

Innerhalb eines Jahres würde ich 12 Monatsummentabellen aufbauen, welche jedes Jahr wieder neu überschrieben werden. Das Datum würde ich einfach einen Verweis auf eine Datumstabelle setzen und den PK als Integer ausgestalten.

Eine rollierende Monatstabelle hätte dabei wohl in etwa die folgende Definition:

{@zähler, @Tag; @Zeitinkrement; MaxV, MeanV}

Wobei ein Zeitinkrement einfach ein Zähler ist, der einen Tag zerteilt, als bspw. 10 Minuten inkrement von 0 bis 24 Uhr. Wenn Du dann ein Jahr durch hast, wird dann einfach die Monatstabelle ausgelagert und initialisiert... dann gehts wieder von vorne los. Werden Details benötigt oder ältere Zeiträume als ein Jahr benötigt, wird eben im File Archiv nachgelesen. Wenns nicht mehr interessiert, kann das von der Platte geputzt werden und wenn deine Auswerteroutine da nichts mehr findet, gibts einfach eine Meldung, keine weiteren Daten verfügbar. Das kann dann so die nächsten hundert Jahre, wartungsarm, laufen.

Grüssle
DSP

[Dejan Vu]

Wieso wollt ihr bloß immer irgendwelche eigenen Datenformate, bei denen man Aggregierung und Filterung wieder von Hand programmieren muss?