ADO Command mit mehreren Inserts

[Tomektor]

Hallo!

Habe folgende Aufgabe:
Bestimmte Datenpunkte (bis zu 10000 Stück!) müssen über Monate bis Jahre hinaus in einer Datenbank archiviert werden.
Die Zustandsänderung wird jede Sekunde archiviert.
Für jeden Datenpunkt wird eine Tabelle angelegt: dp1 bis dp9999. Die Tabelle besitzt nur zwei Spalten (Timestamp und Wert).
Natürlich muss ich bei der Anzahl von Tabellen unmöglich sein ADOCommando.Execute in einer Schlaufe laufen zu lassen (aus Spaß getestet ;) - bei 250 Executes pro Sekunde ist Schicht im Schacht)
Die Insert-Anweisungen sollten deshalb gesammelt werden und in Paketen über ein ADOCommando.Execute gesendet werden. (wie in einer Dump-Datei).
Und hier taucht schon ein Problem auf:

Dies funktioniert problemlos:

Delphi-Quellcode:

commando := 'INSERT INTO dp1 VALUES (' + unixstamp + ', ' + wert + ');';
ADOCommando.CommandText := commando;
ADOCommando.Execute;

Das Sammeln von Werten für einen Datenpunkt in einer Insert-Anweisung funktioniert ebenfalls:

Delphi-Quellcode:

commando := 'INSERT INTO dp1 VALUES (1137061871, 0.568),(1137061872, 0.654),(1137061873, 0.564)';
ADOCommando.CommandText := commando;
ADOCommando.Execute;

Das funktioniert gar nicht:

Delphi-Quellcode:

commando := 
'INSERT INTO dp1 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp2 VALUES (1137061871, 0.5); ' + 
'INSERT INTO dp3 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp4 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp5 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp6 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp7 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp8 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp9 VALUES (1137061871, 0.5); ' +
'INSERT INTO dp10 VALUES (1137061871, 0.5); ';
ADOCommando.CommandText := commando;
ADOCommando.Execute;

Hat jemand eine Ahnung woran es liegen mag?!?
Es schaut so aus, also ob man in einen CommandText des ADOCommand nur eine Tabelle ansprechen könnte, aber das ist doch Quatsch.
Ich will doch reines SQL über die Schnittstelle schieben...
Gebe ich dieselbe SQL-Anweisung an die Datenbank über ein Tool wie phpMyAdmin ein, werden die Inserts ausgeführt...




Falls jemand es ausprobieren möchte hier der Code für die Tabellen erstellung (für 10 Datenpunkte):

Delphi-Quellcode:

   
procedure TForm1.Button1Click(Sender: TObject);
var
i: Word;
table: String;
begin
   for i := 1 to 10 do
   begin
     table := 'dp' + IntToStr(i);
     ADOCommandDelete.CommandText := 'DROP TABLE IF EXISTS ' + table;
     eSQLBefehl.Text := ADOCommandDelete.CommandText;
     ADOCommandDelete.Execute;
   end;

   for i := 1 to 10 do
   begin
     table := 'dp' + IntToStr(i);
     ADOCommandDelete.CommandText :=
     'CREATE TABLE ' + table + ' ( ' +
     'tstemp int(12) NOT NULL default 0, ' +
     'dpvalue float(16,15) NOT NULL default 0.000000000000000, ' +
     'PRIMARY KEY  (tstemp) ' +
     ') TYPE=MyISAM;';
     ADOCommandDelete.Execute;
   end;

end;

Danke im Voraus für die Mühe!

[Bernhard Geyer]

Ich habe ein paar Anmerkungen:

1, Wieso nutzt du ADO? Für MySQL gibt es doch mit MyDAC eine sehr gute native Zugriffskomponente. Auch die ZEOS-Komponenten sind ganz gut für MySQL.

2, Hattest Du schon probiert mit Prepared Statements die Performance zu verbessern?

3, Falls Du MySQL 5.0 einsetzen könntest, könntest Du probieren mittels SP's die Performance nochmals zu verbessern.

[MarcoWarm]

IMHO ist dein Datenbankdesign recht... nunja... wie soll ich sagen... ungünstig :roll:

Warum legst du für jeden Punkt eine separate Tabelle an? Für deine Aufgabe reicht eine einzige Tabelle. Die lässt sich dann auch wesentlich leichter auswerten.

SQL-Code:

CREATE TABLE MESSPUNKTE(
    ID INTEGER NOT NULL,
    DATENPUNKTID INTEGER,
    DATUMZEIT TIMESTAMP,
    WERT DOUBLE PRECISION)

[Tomektor]

Zitat:

Zitat von Bernhard Geyer

Ich habe ein paar Anmerkungen:

1, Wieso nutzt du ADO? Für MySQL gibt es doch mit MyDAC eine sehr gute native Zugriffskomponente. Auch die ZEOS-Komponenten sind ganz gut für MySQL.

2, Hattest Du schon probiert mit Prepared Statements die Performance zu verbessern?

3, Falls Du MySQL 5.0 einsetzen könntest, könntest Du probieren mittels SP's die Performance nochmals zu verbessern.

1. Es ist ein Testbalon. Ich mache die Vorarbeit mit MySQL. Das Tool soll aber mit MSSQL, SAPDB, ORACLE usw. über Standard-ODBC funktionieren.

2. Nein noch nicht

3. Ich nutze MySQL4 zum Test. Alles soll aber auf Standard-SQL laufen, also keine Spezial-Performance Funktionen... :(

[Bernhard Geyer]

Zitat:

Zitat von Tomektor

1. Es ist ein Testbalon. Ich mache die Vorarbeit mit MySQL. Das Tool soll aber mit MSSQL, SAPDB, ORACLE usw. über Standard-ODBC funktionieren.

Vergiss ODBC. Unter Win64 gibt es kein ODBC mehr und wer weiss wie stabil M$ ODBC32 pflegt. Hab da meine Erfahrungen mit ODBC16.

Zitat:

Zitat von Tomektor

3. Ich nutze MySQL4 zum Test. Alles soll aber auf Standard-SQL laufen, also keine Spezial-Performance Funktionen... :(

Wenn du das letzte Prozent Performance benötigst wirst Du nicht darum herumkommen.

[Tomektor]

Zitat:

Zitat von MarcoWarm

IMHO ist dein Datenbankdesign recht... nunja... wie soll ich sagen... ungünstig :roll:

Warum legst du für jeden Punkt eine separate Tabelle an? Für deine Aufgabe reicht eine einzige Tabelle. Die lässt sich dann auch wesentlich leichter auswerten.

SQL-Code:

CREATE TABLE MESSPUNKTE(
    ID INTEGER NOT NULL,
    DATENPUNKTID INTEGER,
    DATUMZEIT INTEGER,
    WERT DOUBLE PRECISION)

Hast Recht, aber ich muss mich an Vorgaben halten :(
Es ist eine Umstetzung eines bereits vorhandenen Tools, welches für jeden Datenpunkt eine Textdatei erzeugt.
Die Dateien (hier einzelne Tabellen) müssen separat gesichert werden können.
Das zweite Problem ist:
Es entstehen pro Datenpunkt 31.536.000 Datensätze pro Jahr (und das ist die Mindestzeit der Archivierung).
Damit ist jede Tabelle ca. 0,5 GB groß und läßt sich einzeln ganz gut wegsichern.
Würde ich alle 10.000 Datenpunkte in eine Tabelle packen und über die DatenpunktID ansprechen, hätte ich im Januar 2007 eine Tabelle mit 315.360.000.000 Datensätzen und > 5.000 GB. Das können wir gleich vergessen...

Das alles könnte man ja noch lösen, aber das blödeste sind die Vorgaben...
Und ich stehe schon am Anfang vor einem Problem, welches so einfach erscheint...

Trotzdem danke!

[Jelly]

Zitat:

Zitat von Tomektor

Zitat:

Zitat von Bernhard Geyer

1, Wieso nutzt du ADO? Für MySQL gibt es doch mit MyDAC eine sehr gute native Zugriffskomponente. Auch die ZEOS-Komponenten sind ganz gut für MySQL.

1. Es ist ein Testbalon. Ich mache die Vorarbeit mit MySQL. Das Tool soll aber mit MSSQL, SAPDB, ORACLE usw. über Standard-ODBC funktionieren.

Dann vergiss aber mal ganz schnell so Konstrukte wie

SQL-Code:

INSERT INTO dp1 VALUES (1137061871, 0.568),(1137061872, 0.654),(1137061873, 0.564)

die sind nämlich MySQL spezifisch.

Mal so ne Frage... Was sind das für Daten, dass nach 1 Jahr über 300 Milliarden Datensätze anfallen... Die kannst Du doch unmöglich noch auswerten.

[MarcoWarm]

Zitat:

Zitat von Tomektor

Hast Recht, aber ich muss mich an Vorgaben halten!

Welche Vorgaben hast du denn. Auftraggeber sollten das mit dem Datenbankdesign besser dem Fachman (sprich: Dir) überlassen, wie du deine Daten hälst.

Zitat:

Zitat von Tomektor

Es ist eine Umstetzung eines bereits vorhandenen Tools, welches für jeden Datenpunkt eine Textdatei erzeugt.
Die Dateien (hier einzelne Tabellen) müssen separat gesichert werden können.

Du würdest doch sowieso als Datensicherung nur die gesamte Datenbank sichern. So macht man das gewöhnlich.
Außerdem bist du mit der Eintabellenlösung flexibler auf die Änderung der Messpunktanzahl vorbereitet.

Zitat:

Zitat von Tomektor

Es entstehen pro Datenpunkt 31.536.000 Datensätze pro Jahr (und das ist die Mindestzeit der Archivierung).
Damit ist jede Tabelle ca. 0,5 GB groß und läßt sich einzeln ganz gut wegsichern.
Würde ich alle 10.000 Datenpunkte in eine Tabelle packen und über die DatenpunktID ansprechen, hätte ich im Januar 2007 eine Tabelle mit 315.360.000.000 Datensätzen und > 5.000 GB.

Ihr quält eure Server ja... lol. Also, das Datenaufkommen hast du ja so oder so, egal in wieviele Tabellen du schreibst. Und wenn du die Daten dann archivieren sollst (sprich exportieren), kannst du ja per Query nur einen einzigen Messpunkt rausholen, und dessen Daten archivieren. Ich sehe da kein Problem. Naja bis auf eine 5000GB große Datenbank ... in MySQL. Vielleicht solltest du mal fragen, ob jemand schon Erfahrung hat überhaupt so viele Daten in eine MySQL Datenbank zu packen. Die Panzer der USA setzen für derartige Dinge (speichern von Daten) Interbase ein, aber das nur am Rande (ich geh mal davon aus, daß du keinen Panzer programmieren sollst *lach*)

[Bernhard Geyer]

Bei der Anzahl der Datensätze und "einfachheit" eines Datensatzes ist es eh fraglich ob man das Sinnvoll überhaupt jeden Wert als einzelnen Tabelleneintrag eintragen sollte? Evtl. ist eine Speicherung als Blob für größere Zeiträume sinnvoller oder direkt die Meßdaten extern speichern. Es ist nämlich Fraglich ob du noch performant bei mehreren Mio./Mrd. Datensätzen Einträge vornehmen kannst bzw. in endlicher Zeit Reports fahren kannst und zwar ganz unabhängig ob es MySQL oder auch ein MS-SQL oder Oracle wäre.

Ich würde erstmal die Datenbank über mehrere Tage mit Testdaten füllen um zu sehen wie sie überhaupt mit diesem Ansatz reagiert. Die angedachte Trennung ist m.E. bei der Datenmenge sinnvoll (Hab ich schon mal in ähnlicher Form aber bei komplexeren Records auch gemacht). Denn es ist fraglich ob eine Tabelle mit 31 Mio * 10.000 Datensätzen auch mit passenden Indexen auf "normalen" PC's noch vernünftig abfragbar ist. Indexe müssen um schnell zu sein möglichst komplett im Hauptspeicher liegen. Und ob du da mit ein paar GB Hauptspeicher bei voller Datenbank auskommmst? :gruebel:

[nieurig]

Ich würde Bernhard Aussage unterstützen ...

Bei dieser Anzahl von Datensätzen helfen Dir auch die Möglichkeiten zur Segmentierung der Daten (Aufteilung z.B. in zwei Tabellen mit neuen bzw. alten Daten) nicht weiter um Abfragen in vernünftiger Zeit durchführen zu können.

Nimm z.B. ein BLOB-Feld in dem Du den Inhalt einer Textdatei mit allen Messdaten je Tag (oder Stunde?) speicherst. Die Datenbank enthält denn je Zeitintervall einen Datensatz mit dem Inhalt der Datei und ggf. zusammenfassenden Daten die sich aus den Messdaten ergeben und deine Abfragen sind sehr schnell ...

Falls Du wirklich die einzelnen Daten eines Zeitintervalls brauchst, mußt Du eben den gespeicherten Text "auseinandernehmen".

Viel Erfolg.
Niels

P.S.
Die "Vorgaben", die vom Auftraggeber sollte selbst dann hinterfragen wenn dieser EDV-Experte ist. In den meisten Fällen steht hinter der Vorgabe "Jeder Messpunkt soll gespeichert werden" lediglich eine gewünschte Funktionalität. Diese kann man auf unterschiedlichsten Wegen gewährleisten ...