Firebird Import aus Textdatei extrem langsam

[globetrotter77]

Hallo zusammen!

Ich habe ein massives Problem beim Import von Daten aus einer Textdatei in eine Firebird-DB.
Die Laufzeit ist schlicht und einfach katastrophal.
Für je 100 Datensätze mit gerade mal 5 lumpigen Textfeldern benötigt das Programm ca. 1 Sekunde.
Macht bei ca. 110000 Datensätzen also 1100 Sekunden bzw. fast 20 Minuten.
Das Lesen der Daten inkl. Split der einzutragenden Felder sowie Aufbau eines passenden INSERT-Statements benötigt gerade mal 32 Sekunden.
Die gesamte Zeit scheint daher alleine von einem einzigen SQL-Statement pro Satz aufgefressen zu werden.
Woran kann das liegen?
Wäre für jede Idee dankbar ...

Gruß

globetrotter77

[globetrotter77]

oder ist das etwa normal, dass es so lange dauert?
liegt es an Firebird oder an der TSQLQuery-Komponente?
in der vorherigen Version habe ich noch mit der leider ja hoffnungslos veralteten BDE gearbeitet und durchaus vernünftige Laufzeiten erreicht.

hat jemand Erfahrung mit Datenimport in Firebird?
wie macht man's geschickterweise? und richtig schnell?

[Hansa]

Was ist TSqlQuery ? :shock: Faktor 10-100 ist allerdings nicht ungewöhnlich, sofern WinApi Funktionen verwendet werden. Memo, Listbox, Stringgrid etc. Die Aktualisierung / Anzeige dauert da ewig. 8)

[globetrotter77]

ist schon klar, dass manche Funktionen ewig dauern, aber bei ist der Unterschied tatsächlich reduzierbar auf die Ein-/Ausblendung von

Delphi-Quellcode:

        
        with DM_User.SQLQueryTemp do
        begin
          with SQL do
          begin
            Clear;
            Add(sqltxt);
          end;
          ExecSQL(true);
        end;

Gibt es eine andere Komponente, mit der man direkte SQL-Statements abgeben kann?
Ich gebe zu, dass ich mich da nicht besonders auskenne ... habe früher immer die BDE und Paradox benutzt und war eigentlich immer zufrieden ... nachdem die aber mittlerweile so richtig veraltet ist, wollte ich mal anders rangehen ... leider nur mit mässigem Erfolg

[Hansa]

Kombination von Clear und Add ist schon gut. :mrgreen:

[omata]

Vielleicht hilft dir ja...

Code:

CREATE TABLE tabelle EXTERNAL FILE 'file.txt' (...);

weiter.

Add: Oder du baust dir ein SQL-Statement, dass alle INSERTs auf einmal durchführt...

SQL-Code:

INSERT INTO tabelle
SELECT FIRST 1 feld1, feld2, ... FROM tabelle
UNION
SELECT FIRST 1 feld1, feld2, ... FROM tabelle
:
:

[globetrotter77]

über external file habe ich auch schon nachgedacht, aber ich habe das Problem, dass ich davon ausgehen muss, dass doppelte Schlüssel vorkommen ... wie geht der Import via external file damit um?
im Augenblick löse ich das durch ein try-except-Konstrukt ...
und wie definiere ich in einem external file Datumswerte?
hat jemand Erfahrung?

[globetrotter77]

Zitat:

Zitat von omata

Add: Oder du baust dir ein SQL-Statement, dass alle INSERTs auf einmal durchführt...

SQL-Code:

INSERT INTO tabelle
SELECT FIRST 1 feld1, feld2, ... FROM tabelle
UNION
SELECT FIRST 1 feld1, feld2, ... FROM tabelle
:
:

wie gesagt, ich habe eine Textdatei und keine Tabelle

[omata]

Zitat:

Zitat von Hansa

Kombination von Clear und Add ist schon gut. :mrgreen:

blödsinn...

Mach es so...

Delphi-Quellcode:

  SQLQueryTemp.SQL.Text:=sqltxt;
  SQLQueryTemp.ExecSQL(true);

Zitat:

Zitat von globetrotter77

...ich habe das Problem, dass ich davon ausgehen muss, dass doppelte Schlüssel vorkommen...

Lege die Tabelle ohne Primärschlüssel an...

SQL-Code:

CREATE TABLE tabelle EXTERNAL FILE 'file.txt' (feld1 INT, feld2 CHAR(10));

...und mache danach einen Import in eine andere Tabelle...

SQL-Code:

INSERT INTO richtige_tabelle
SELECT DISTINCT feld1, TRIM(feld2)
FROM tabelle

Da hier die Feldinhalte eine konstante Größe (mit Leerzeichen gefüllt) haben müssen, kannst du in diesem Schritt auch gleich die Zeichenketten trimmen.

Zitat:

Zitat von globetrotter77

wie gesagt, ich habe eine Textdatei und keine Tabelle

Das ist mir bewusst, du sollst ja auch das Statement zusammenbauen, der FROM-Teil mit gleicher Tabelle + First 1 ist nur ein Trick.

[globetrotter77]

Zitat:

Zitat von omata

Zitat:

Zitat von Hansa

Kombination von Clear und Add ist schon gut. :mrgreen:

blödsinn...

Mach es so...

Delphi-Quellcode:

  SQLQueryTemp.SQL.Text:=sqltxt;
  SQLQueryTemp.ExecSQL(true);

macht keinen Unterschied, jedenfalls nicht, was die Laufzeit angeht

Zitat:

Zitat von omata

Zitat:

Zitat von globetrotter77

...ich habe das Problem, dass ich davon ausgehen muss, dass doppelte Schlüssel vorkommen...

Lege die Tabelle ohne Primärschlüssel an...

SQL-Code:

CREATE TABLE tabelle EXTERNAL FILE 'file.txt' (feld1 INT, feld2 CHAR(10));

...und mache danach einen Import in eine andere Tabelle...

SQL-Code:

INSERT INTO richtige_tabelle
SELECT DISTINCT feld1, TRIM(feld2)
FROM tabelle

auch schon darüber nachgedacht, aber ich muss die doppelten Schlüssel zumindest protokollieren, daher ist eine Einzelsatzbearbeitung leider erforderlich ... und mit BDE hat das auch gut funktioniert ... wieso geht das hier nicht mehr?

Zitat:

Zitat von omata

Da hier die Feldinhalte eine konstante Größe (mit Leerzeichen gefüllt) haben müssen, kannst du in diesem Schritt auch gleich die Zeichenketten trimmen.

Zitat:

Zitat von globetrotter77

wie gesagt, ich habe eine Textdatei und keine Tabelle

Das ist mir bewusst, du sollst ja auch das Statement zusammenbauen, der FROM-Teil mit gleicher Tabelle + First 1 ist nur ein Trick.

[omata]

Zitat:

Zitat von globetrotter77

macht keinen Unterschied, jedenfalls nicht, was die Laufzeit angeht

...das ist klar, ist trotzdem der schönere und auch sicherere Weg.

Zitat:

Zitat von globetrotter77

...aber ich muss die doppelten Schlüssel zumindest protokollieren, daher ist eine Einzelsatzbearbeitung leider erforderlich...

Wenn du die Daten erstmal in der Datenbank hast, ist die Prüfung auf doppelte Einträge doch super einfach. Das Protokollieren muss doch nicht unbedingt beim Einfügen geschehen. Dieser Weg ist doch viel flexibler und die Daten können dann leichter weiterverarbeitet werden.

[globetrotter77]

Zitat:

Zitat von omata

Wenn du die Daten erstmal in der Datenbank hast, ist die Prüfung auf doppelte Einträge doch super einfach. Das Protokollieren muss doch nicht unbedingt beim Einfügen geschehen. Dieser Weg ist doch viel flexibler und die Daten können dann leichter weiterverarbeitet werden.

wieso ist das dann super einfach?
wie meinst du das?

[omata]

Wenn die Daten in der Datenbank sind, kannst du sie doch wesentlich einfacher per SQL formen, weiterverarbeiten und analysieren, als wenn du alles selber programmieren musst. Der Aufwand entfällt dann und du kannst alles über einfaches SQL lösen.

[omata]

So, ich habe das jetzt mal selber ausprobiert...

Code:

1.000.000   Zeilen = 0:18 (Datei = 16MB)
1.000.000.0 Zeilen = 2:48 (Datei = 166MB)

Also für einen Datensatz ca. 18µs (auf meiner Maschine = P4, 3GHz)

[mjustin]

Delphi-Quellcode:

        
        with DM_User.SQLQueryTemp do
        begin
          with SQL do
          begin
            Clear;
            Add(sqltxt);
          end;
          ExecSQL(true);
        end;

Ich würde die Query nur einmal zu Beginn des Einlesens erzeugen und den SQL-Text zuweisen, dabei dann mit Parametern arbeiten:

SQL-Code:

insert into tabelle(f1, f2, f3, ...) values (:f1, :f2, :f3, ...)

In der Einleseschleife dann die Parameter zuweisen und ExecSQL aufrufen. Vorteil: die Query wird vom Server nur einmal erzeugt und dann für jedes Insert wiederverwendet. ("Prepared Statement").

Eventuell hilft es auch noch, die ganze Sache in einer Transaktion laufen zu lassen.

Viele Grüße,

[mkinzler]

Und eine explizite Transaktionssteuerung verwenden. Denn sonst wird dank autocommit jeder Insert insert in eine eigene Transkation gepackt.

[globetrotter77]

Zitat:

Zitat von mkinzler

Und eine explizite Transaktionssteuerung verwenden. Denn sonst wird dank autocommit jeder Insert insert in eine eigene Transkation gepackt.

Aha! Das könnte natürlich ein Problem sein!
und wie macht man das mit der expliziten Transaktionssteuerung?

[mkinzler]

Mit dbExpress kenne ich mich nicht aus, aber versuche mal
SQLConnection.StartTransaction() und SQLConnection.Commit()

[globetrotter77]

StartTransaction erwartet einen "benutzerdefinierten Transaktionsbezeichner" - was ist denn damit gemeint??
und wie macht man das?

[globetrotter77]

Zitat:

Zitat von mjustin

Ich würde die Query nur einmal zu Beginn des Einlesens erzeugen und den SQL-Text zuweisen, dabei dann mit Parametern arbeiten:

SQL-Code:

insert into tabelle(f1, f2, f3, ...) values (:f1, :f2, :f3, ...)

In der Einleseschleife dann die Parameter zuweisen und ExecSQL aufrufen. Vorteil: die Query wird vom Server nur einmal erzeugt und dann für jedes Insert wiederverwendet. ("Prepared Statement").

Bin schon ganz meschugge ...
wie war das nochmal mit den Params?
wie ordne ich denen Werte zu?
geht das mit

Delphi-Quellcode:

SQLQuery.Params[0].Value:='blabla';

und muss ich das Prepare selbst auslösen?

[nahpets]

Hallo,

hast Du schonmal probiert, was passiert, wenn Du mehrere Insert gleichzeitig absetzt?
Davon ausgehend, dass das in einer Schleife passiert:

Delphi-Quellcode:

with DM_User.SQLQueryTemp do 
  begin 
    with SQL do 
      begin 
        Clear; 
        Add(sqltxt); 
      end; 
      ExecSQL(true); 
    end;

einmal in der Art ändern:

Delphi-Quellcode:

DM_User.SQLQueryTemp.Sql.Clear; 
... // irgendeine Schleife
with DM_User.SQLQueryTemp do 
  begin 
    SQL.Add(sqltxt); 
    If SQL.Count Mod 100 = 0 then begin
      ExecSQL(true); 
      SQL.Clear;
    end; 
  end;
... // Ende der Schleife
// Hier nicht vergessen den Rest wegzuschreiben, wenn die Satzzahl nicht durch 100 zu teilen ist.
If DM_User.SQLQueryTemp.SQL.Count > 0 then DM_User.SQLQueryTemp.SQL.ExecSQL(true);

dann wird alle 100 Datensätze das Insert für diese Sätze abgesetzt. Bei der 100 kannst Du dann auch ausprobieren, ob eine größere oder kleinere Anzahl von Sätzen die Geschwindigkeit erhöht oder verringert.
Wie sieht der Indexaufbau der Tabelle aus, auch hier kann bei satzweisem Einfügen ein Laufzeitproblem auftreten, wenn die Pflege des Index zu kompiliziert wird. Eventuell auch den Index vor dem Import wegwerfen und nach dem Import neuerstellen, dies geht allerdings nur, wenn der Index nicht zur Doppeltenprüfung... benötigt wird.

[globetrotter77]

langsam, aber sicher, glaube ich, nicht mehr programmieren zu können!

beim Versuch, mit Params zu arbeiten, bekomme ich jetzt bei jeder Zeile folgenden Fehler:
Datenbank-Server-Fehler: Column unknown
ARTNR
At line 1, column 38

das SQL-Statement habe ich auf das Minimum gekürzt:

SQL-Code:

INSERT INTO ARTIKEL (ARTNR) VALUES (:ARTNR)

Sehr seltsam, da er die ARTNR in der Feldliste offenbar sehr wohl kennt, erst in der Werteliste passt es ihm nicht mehr!!!

[nahpets]

Hallo,

zeig' uns bitte mal den Quelltext, mit dem Du die Parameter füllst, da scheint irgendetwas schief zu laufen. Mir sieht das so aus, als würden der Parameter nicht gefüllt werden.

[globetrotter77]

Delphi-Quellcode:

DM_User.SQLQueryImport.Params[paramnr].Value:=feldtxt;

nicht wundern über den veränderten Namen, ich habe fürs Testen einen neuen SQLQuery angelegt.

Der Parameter ist definiert mit

Delphi-Quellcode:

        DataType = ftString
        Name = 'artnr'
        ParamType = ptInput
        Size = 17
        Value = ''

[nahpets]

Hallo,

was Du gemachts hast, sieht eigentlich nicht wirklich falsch aus, daher hier mal ein paar Vorschläge zur Lösung des Rätsels:

Sind Datenbank und/oder Komponenten bei den Tabellen-, Feld- und/oder Parameternamen casesensitive?

Schreib' mal in den Statements alle Tabellen- und Spaltennamen genau so, wie die Datenbank. Ebenso die Parameternamen im Statement so, wie Du sie im Programm definiert hast.

Was steht in paramnr? Bist Du sicher, das Du da den richtigen Parameter erwischst? Kannst Du das im Programm auch mit ParamByName oder ähnlichem umsetzen?

[omata]

Zitat:

Zitat von globetrotter77

StartTransaction erwartet einen "benutzerdefinierten Transaktionsbezeichner" - was ist denn damit gemeint??
und wie macht man das?

Delphi-Quellcode:

var TD:TTransactionDesc; (aus DBXpress)
begin
  SQLConnection.StartTransaction(TD);
  try
    SQLConnection.ExecuteDirect('INSERT INTO ...');
    SQLConnection.Commit(TD);
  except
    SQLConnection.Rollback(TD);
  end;
end;

[globetrotter77]

da bin ich mir sogar sehr sicher, weil ich nämlich nach dem Zurechtstutzen auf das Wesentliche versehentlich noch einen anderen Parameter füllen wollte, der in der Abfrage gar nicht mehr vorkommt - das führt sofort zu einem Systemfehler!
Andererseits führt auch die konsequente Verwendung der Kleinschreibung beim Parameter immer zur selben Fehlermeldung mit groß geschriebener ARTNR.
ParamByName etc. habe ich auch ausprobiert - keine Veränderung
bin langsam mit meinem Latein am Ende ... :(

[globetrotter77]

Zitat:

Zitat von omata

Delphi-Quellcode:

var TD:TTransactionDesc; (aus DBXpress)
begin
  SQLConnection.StartTransaction(TD);
  try
    SQLConnection.ExecuteDirect('INSERT INTO ...');
    SQLConnection.Commit(TD);
  except
    SQLConnection.Rollback(TD);
  end;
end;

das führt zu folgendem Fehler:
"Ungültige Transaktions-ID"
Wie kann man die ID zuordnen? Ich hätte eigentlich gedacht, dass das die DB für mich übernimmt, oder?

[omata]

Oh, den Fehler hatte ich noch nie. TD ist aber nur ein einfaches Record. Einfach mal TD. eingeben...

[hoika]

Hallo,

schau mal hier

1



Heiko

[globetrotter77]

sehr schön, wenn auch unverständlich:
mit StartTransaction(TD) und Commit(TD) geht's plötzlich einigermaßen schnell.
Vorher ist allerdings noch
TD.TransactionID:=1;
erforderlich.
Ca. 105000 Datensätze in knapp 4 Minuten
Kein Vergleich mit dem Ergebnis von omata, aber immerhin.

Allerdings dauert danach ein Open eines ClientDataSets schlappe 15 (fünfzehn!) Minuten!!!

Abgesehen davon, dass es für mich nach wie vor unverständlich bleibt, wieso es
a) über ein ClientDataSet ewig lange dauert
b) mit Hilfe von SQLQuery nicht möglich ist bzw. unverständliche Fehlermeldungen liefert
ist es natürlich völlig unzumutbar für den User, nach dem Datenimport so lange zu warten, bis sich wieder was tut.

Wo ist denn hier der Fallstrick versteckt?
Ich kapiere es nicht mehr!
Wie gesagt - mit BDE und Interbase-Komponenten war das alles überhaupt kein Problem.
Wo kann ich denn bei den Einstellungen überhaupt so nen Bock schießen?
Ich gehe zumindest mal davon aus, dass ich ihn selber geschossen habe ...

[hoika]

Hallo,

benutzt du denn jetzt das hier ?

Delphi-Quellcode:

var 
  TD:TTransactionDesc; (aus DBXpress)
begin
  TD. // usw.

  SQLConnection.StartTransaction(TD);
  try
    SQLConnection.ExecuteDirect('INSERT INTO ...');
    SQLConnection.Commit(TD);
  except
    SQLConnection.Rollback(TD);
  end;
end;

Du musst das mit den Parametern und der Query hinbekommen.
Eine prepared Query ist etwa doppelt so schnell wie das da oben.

Man könnte auch noch folgendes machen:
- je nach 500 Datensätzen Commit/StartTransaction
- per Alter Index XXX Inactive alle nicht benötigten Indizes deaktivieren


Heiko

[mkinzler]

Oder alles in Kombination.

[globetrotter77]

Zitat:

Zitat von hoika

Hallo,

benutzt du denn jetzt das hier ?

Delphi-Quellcode:

var 
  TD:TTransactionDesc; (aus DBXpress)
begin
  TD. // usw.

  SQLConnection.StartTransaction(TD);
  try
    SQLConnection.ExecuteDirect('INSERT INTO ...');
    SQLConnection.Commit(TD);
  except
    SQLConnection.Rollback(TD);
  end;
end;

Genau das benutze ich, allerdings zunächst mal nur in einer einzigen großen Transaktion.
Das werde ich noch abändern. Pro Datensatz ist allerdings auch zu langsam.

Zitat:

Zitat von hoika

Du musst das mit den Parametern und der Query hinbekommen.
Eine prepared Query ist etwa doppelt so schnell wie das da oben.

Das würde ich gerne tun, aber mit SQLConnection.ExecuteDirect('INSERT INTO ...') habe ich doch gar keine Parameter zur Verfügung, oder?
Oder wäre es möglich, innerhalb von SQLConnection.StartTransaction und SQLConnection.Commit wieder auf SQLQuery-Methoden zurückzugreifen? (Mal abgesehen davon, dass die unbekannte Spalte angemeckert wird)

Zitat:

Zitat von hoika

Man könnte auch noch folgendes machen:
- je nach 500 Datensätzen Commit/StartTransaction

das habe ich auf jeden Fall vor

Zitat:

Zitat von hoika

- per Alter Index XXX Inactive alle nicht benötigten Indizes deaktivieren
Heiko

tja, leider habe ich schon alles soweit abgespeckt, dass nur noch ein PRIMARY KEY vorhanden ist, den ich auch brauche, weil mit doppelten Schlüsseln zu rechnen ist.

Leider hat das alles aber gar nichts damit zu tun, dass sich das System danach (!) völlig unzumutbar verhält.

[hoika]

Hallo,

Zitat:

danach unzumutbar verhält

Langsam ?

Was passiert, wenn du den Server-Dienst (FBGuardian) anhälst und neustartest ?
Ist es danach wieder "normal" schnell,
dann hast du eine offene Transaktion (also eine nicht comittete).

Zur Query.
Was war noch mal das Problem, nicht die SQLQuery zu benutzen ?
Wie weit warst du denn da schon.
Hast du noch den Quellcode davon (nicht nur die Splitter hier) ?


Heiko

[globetrotter77]

Zitat:

Zitat von hoika

Hallo,

Zitat:

danach unzumutbar verhält

Langsam ?

gnadenlos langsam ... CLientDataSet.Active:=true ===> ca. 15 Minuten, bis es weitergeht

Zitat:

Zitat von hoika

Was passiert, wenn du den Server-Dienst (FBGuardian) anhälst und neustartest ?
Ist es danach wieder "normal" schnell,
dann hast du eine offene Transaktion (also eine nicht comittete).

werde ich probieren, glaube aber nicht, dass es daran liegt

Zitat:

Zitat von hoika

Zur Query.
Was war noch mal das Problem, nicht die SQLQuery zu benutzen ?
Wie weit warst du denn da schon.
Hast du noch den Quellcode davon (nicht nur die Splitter hier) ?

da bekam ich den Fehler "column unknown" an der Stelle, wo der Parameter angesprochen wurde - warum, weiß der Kuckuck!

den Quellcode habe ich mittlerweile so sehr gemetzelt, das er kaum noch zu erkennen ist, nur um die verschiedenen Vorschläge auszuprobieren.
ich habe den Eindruck, dass ich ein grundsätzliches Einstellungsproblem vorliegen habe, habe aber leider keine Ahnung, wo das liegen könnte.

was mir ohnehin nicht gefällt, ist die Verwendung der dbExpress-Komponenten (sind mir schon wegen der Unidirektionalität sehr suspekt), aber ich wüsste nicht, was ich sonst nehmen könnte.

wenn ich es richtig in Erinnerung habe, muss ich das ClientDataSet verwenden, um auch rückwärts gehen zu können, nicht wahr?

irgendwie zieht hier eine Krücke die nächste nach sich, und diese gleich noch mal eine usw. usw.

Was für Connection-Möglichkeiten gibt es denn sonst noch? und ist da auch was Empfehlenswertes dabei?

[nahpets]

Hallo,

wie wäre es mit den Zeos-Komponenten?

ein Tutorial
Die Komponenten zur ZeosLib

Tutorial zu ClientDataSet
Beim ClientDataSet darfst Du nicht vergessen, dass er die gesamten Daten im Arbeitsspeicher vorhält, Deine 100 und ein paartausend Sätze werden also erstmal in die Arbeitsspeicher geladen, ist das erforderlich, da ja offensichtlich eine ziemlich arge "Spassbremse".

[mkinzler]

Hierfür sollte auch IBX ausreichen

[hoika]

Hallo,

also hier

1

wird auf einen SQL-Monitor verwiesen.


Heiko

[globetrotter77]

Zitat:

Zitat von omata

So, ich habe das jetzt mal selber ausprobiert...

Code:

1.000.000   Zeilen = 0:18 (Datei = 16MB)
1.000.000.0 Zeilen = 2:48 (Datei = 166MB)

Also für einen Datensatz ca. 18µs (auf meiner Maschine = P4, 3GHz)

da komme ich noch lange nicht ran ...
könntest du mir vielleicht mal verraten, welche Komponenten und Methoden du verwendet hast?
evtl. den Quelltext und/oder die Einstellungen im Object Inspector?
vielleicht komme ich dann dahinter, was ich falsch gemacht habe ...

wäre toll!

Edit:
hast du auch doppelte Primary Keys dabei?
die muss ich nämlich berücksichtigen