AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Tabellen für viele kleine Datensätze optimieren
Thema durchsuchen
Ansicht
Themen-Optionen

Tabellen für viele kleine Datensätze optimieren

Ein Thema von Medium · begonnen am 9. Jul 2014 · letzter Beitrag vom 10. Aug 2014
Antwort Antwort
Seite 1 von 2  1 2      
Medium

Registriert seit: 23. Jan 2008
3.689 Beiträge
 
Delphi 2007 Enterprise
 
#1

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 10:48
Södale, ihr habt mich ganz gut davon überzeugen können, dass eine Spaltung in mehrere Tabellen nicht das Gelbe vom Ei ist. Den aktuellen PK, also das autoinc Feld, kann ich hier tatsächlich weg lassen. Hab mir das angewöhnt, weil ich es für unsere Hauptanwendung zwingend brauche, das hier ist aber ausser der Reihe. Der fliegt also schon mal.

Partitioning klingt sehr praktisch, danke auch für das Beispiel als Code. Gelesen habe ich ein wenig drüber, genutzt bisher nicht. Das einzige was mich an der gezeigten Variante stört ist, dass ich quasi jedes Jahr ein "Pflege-Statement" ausführen muss, was mit Sicherheit spätestens nach dem 2. Jahr im Tagesgeschäft unter geht. Kann man es so deichseln, dass mit Beginn eines neuen Jahres automatisch eine neue Partition begonnen wird?

Bezüglich Index: Hier fehlt mir auch noch ein wenig Erfahrung um genau sagen zu können, welche Indizes ich hier wirklich brauche. Einen PK braucht es glaube ich wirklich nicht, aber wenn, dass wäre vdate+valueID ein guter Kandidat. Die kürzesten Intervalle von Einträgen mit derselben valueID sind 1 Minute, also garantiert verschiedene Timestamps.
Bisher habe ich recht naiv immer über die einzelnen Felder einen Index angelegt, die ich nachher in meinen WHERE-Klauseln benutze. Das wäre in diesem Fall vdate und valueID, auch in Kombination. Es wird nachher sicherlich ein Statement der folgenden Struktur genutzt um die Daten zu holen:
SQL-Code:
SELECT
  h.vdate, h.valueID, h.maxValue, h.meanValue, v.valueName
FROM
  history h JOIN valueList v
    ON h.valueID = v.id
WHERE
  (h.vdate BETWEEN :sDate AND :eDate) AND
  (v.valueID IN ({idListe}))
Edit: Der Join macht eigentlich kaum Sinn, da ohnehin bei der Ausgabe nach valueID, vdate sortiert werden sollte. Die Messstellennamen kann ich mir also auch billiger in einem separaten Statement holen und als Überschrift/Legende einsetzen. Die müssen nicht bei jedem Datensatz einzeln bei stehen.

Ist es da besser einen kombinierten Index auf vdate und valueID zu legen, oder jedes Feld seinen eigenen? Oder gar beides? Welche Art von Index eigenet sich hier? (Beide Felder sind für sich genommen non-unique, in Kombi aber unique.) Mein MySQL Tabellen-Tool bietet mir hier die Optionen:
Index Kind: Index, Primary, Unique, Fulltext, Spatial
Index Type: Default, BTree, Hash, RTree
Ich habe mich bisher schämlich wenig damit befasst, welche Index-Arten für welche Fälle geeignet sind

Die angesprochene Trennung von Datum und Uhrzeit wird nicht nötig sein. Wenn, dann werden immer komplette Zeiträume am Stück abgefragt.

Eine separate Tabelle mit Daten zur Anzeige, und Umschaltung bei Zoom klingt zunächst sehr nett, aber ich weiss nicht, ob ich es mir leisten kann auf Dauer mehrere Granularitäten vollständig vorzuhalten. Klar ist, dass ich bei einem Zoom, der pro Pixel im Graphen z.B. 30min entspricht nicht alle minütlichen Werte übers Netzwerk jagen lassen wollen würde. Das Problem habe ich mir bisher nicht genauer angesehen, und hatte gehofft eine Möglichkeit zu finden vielleicht angeben zu können, dass ich nur jeden 2. oder 3. Datensatz einer Ergebnismenge vom Server bekomme. Gibt es sowas?
Wenn nicht, werden redundante Tabellen leider doch wieder interesant, weil vermutlich die letzte Möglichkeit dann.

Danke zwischendurch schon mal für die rege und produktive Teilnahme!
"When one person suffers from a delusion, it is called insanity. When a million people suffer from a delusion, it is called religion." (Richard Dawkins)

Geändert von Medium (10. Jul 2014 um 11:08 Uhr)
  Mit Zitat antworten Zitat
Benutzerbild von Sir Rufo
Sir Rufo

Registriert seit: 5. Jan 2005
Ort: Stadthagen
9.454 Beiträge
 
Delphi 10 Seattle Enterprise
 
#2

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 11:21
Du kannst beim MySQL Events definieren, die zu einem bestimmten Zeitpunkt laufen, also erstellst du dir einen Event, der einmal im Jahr losläuft und die entsprechende Partition neu erstellt.

Hier auch ein entsprechender SO-Beitrag

Der Pflegeaufwand geht also auch gegen Null
Kaum macht man's richtig - schon funktioniert's
Zertifikat: Sir Rufo (Fingerprint: ‎ea 0a 4c 14 0d b6 3a a4 c1 c5 b9 dc 90 9d f0 e9 de 13 da 60)
  Mit Zitat antworten Zitat
Dejan Vu
(Gast)

n/a Beiträge
 
#3

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 12:33
Ist es da besser einen
Es wird nur ein Index verwendet, ergo dürfte der kombinierte Index der richtige sein. In welcher Reihenfolge? ich würde ValueID+vDate nehmen, wenn Du nur von wenigen der 650 Stationen Werte über einen Zeitraum haben möchtest. Aber *wetten* würde ich nicht.
Zitat:
... Gibt es sowas? Wenn nicht, werden redundante Tabellen leider doch wieder interesant, weil vermutlich die letzte Möglichkeit dann.
Wieso eigentlich 'leider'? Das ist mit ein paar Triggern oder cron-Jobs gemacht (also alle paar minuten die Daten der letzten paar Minuten aggregieren und in die Reporttabellen schubsen)
  Mit Zitat antworten Zitat
Benutzerbild von Jasocul
Jasocul

Registriert seit: 22. Sep 2004
Ort: Delmenhorst
1.378 Beiträge
 
Delphi 11 Alexandria
 
#4

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 13:06
Bei den Datenmengen würde ich mit dem automatischen Partitioning vorsichtig sein. Es wird wohl in der Regel problemlos funktionieren, aber ich würde das lieber auf Termin legen und unter eigener Aufsicht laufen lassen. Ist aber Geschmackssache.

Was für dich auch interessant sein könnte, sind Indexed Views. Dort hast du dann automatisch deine aggregierten Werte und brauchst keine Trigger oder Events. Es gibt aber wohl ein paar Einschränkungen, soweit ich das auf die schnelle gelesen habe. Ich kenne das unter Oracle unter dem Begriff "Materialized Views" und habe damit gute Erfahrungen gemacht. Allerdings habe ich die nicht automatisch aktualisieren lassen, da ich entsprechende Auswertungen nur monatsweise benötigt habe. Da bot sich ein Refresh einmal im Monat an.
Peter
  Mit Zitat antworten Zitat
Dejan Vu
(Gast)

n/a Beiträge
 
#5

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 13:48
... sind Indexed Views. Dort hast du dann automatisch deine aggregierten Werte und brauchst keine Trigger oder Events. Es gibt aber wohl ein paar Einschränkungen, ...
Achtung! mySQL... nicht SQL-Server
  Mit Zitat antworten Zitat
Benutzerbild von Jasocul
Jasocul

Registriert seit: 22. Sep 2004
Ort: Delmenhorst
1.378 Beiträge
 
Delphi 11 Alexandria
 
#6

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 14:07
Achtung! mySQL... nicht SQL-Server
Ups. Sorry.
Peter
  Mit Zitat antworten Zitat
Benutzerbild von p80286
p80286

Registriert seit: 28. Apr 2008
Ort: Stolberg (Rhl)
6.659 Beiträge
 
FreePascal / Lazarus
 
#7

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 14:35
Da ich bisher nur gute Erfahrungen mit views gemacht habe, würde ich diese auch bevorzugen (oracle).
Jedesmal wenn ich irgendwelche kumulierten Daten in die Finger bekomme, gibt es ein paar Abweichungen, weil immer der eine oder andere Job nicht gelaufen ist oder einen Fehler hatte oder...
(Es kommt immer auf die Daten - die gespeicherten und die gefragten - an)

Gruß
K-H
Programme gehorchen nicht Deinen Absichten sondern Deinen Anweisungen
R.E.D retired error detector
  Mit Zitat antworten Zitat
Benutzerbild von himitsu
himitsu

Registriert seit: 11. Okt 2003
Ort: Elbflorenz
44.829 Beiträge
 
Delphi 12 Athens
 
#8

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 14:57
Aber bei vielen Daten ist ein View doch auch nicht so gut, wenn er erst stundenlang rumrechnen würde, bei jeder Abfrage.

Drum auch der Vorschlag mit dem Data-Warehouse.
Die Dinger sind erstmal für viele Daten ausgelegt und sie bieten auch sowas wie Views, nur daß Diese job-/zeitgesteuert die Daten der Ansichten vorberechnen, was dann die Abfragen schneller macht.
Ein Therapeut entspricht 1024 Gigapeut.
  Mit Zitat antworten Zitat
Medium

Registriert seit: 23. Jan 2008
3.689 Beiträge
 
Delphi 2007 Enterprise
 
#9

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 15:11
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
"When one person suffers from a delusion, it is called insanity. When a million people suffer from a delusion, it is called religion." (Richard Dawkins)

Geändert von Medium (10. Jul 2014 um 15:17 Uhr)
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#10

AW: Tabellen für viele kleine Datensätze optimieren

  Alt 10. Jul 2014, 20:43
Ist es da besser einen kombinierten Index auf vdate und valueID zu legen, oder jedes Feld seinen eigenen? Oder gar beides? Welche Art von Index eigenet sich hier? (Beide Felder sind für sich genommen non-unique, in Kombi aber unique.) Mein MySQL Tabellen-Tool bietet mir hier die Optionen:
Index Kind: Index, Primary, Unique, Fulltext, Spatial
Index Type: Default, BTree, Hash, RTree
Ich habe mich bisher schämlich wenig damit befasst, welche Index-Arten für welche Fälle geeignet sind
Ich würde es mit getrennten Indizes machen. Das ist flexibler. Ein kombinierte Index greift u.U. nicht, wenn weitere Bedingungen ins Spiel kommen.
Die uniqueness kannst Du separat per constraint definieren. NOrmalerweise tuen RDBMS einem den "Gefallen" abhängig vom Constraint direkt auch einen Index zu bauen, aber das kann man unterbinden, löschen, ändern.

Aber bei vielen Daten ist ein View doch auch nicht so gut, wenn er erst stundenlang rumrechnen würde, bei jeder Abfrage.
Das kann man pauschal nicht sagen. Ein View ändert per se nichts an der Abfragegeschwindigkeit.
Ausnahme wäre bspw. wenn ein Abfrage Paramter besser "innerhalb eines Views" aufgehoben ist, weil er dort eine größere Selektivität entfaltet (stärkere Datenreduktion bringt, Folgekosten spart, ..)

Jetzt machst du mich aber spontan unglücklich,
..
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.
Ein Materialized View, der für kumulierte Daten verwendet werden könnte ist m.E. auch nicht unbedingt effizient. Kommt drauf an, wie das Refresh arbeitet. In Deinem Fall ist klar, es müssen immer nur Daten nachgetragen werden, nicht eine ganzer Mat.View refreshed werden.
Das kann man auch gezielt je Tag oder so manuell in eine eigene Tabelle kippen. Falls dort Indizes definiert sind, dann eben die Indizes disablen und nachher wieder aktualisieren, ist etwas schneller.
Gruß, Jo
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 1 von 2  1 2      


Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 16:37 Uhr.
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024-2025 by Thomas Breitkreuz