Firebird Import aus Textdatei extrem langsam

[mkinzler]

Was soll dann geschehen ( bei doppelten PKs)?

[hoika]

Hallo,

die doppelten sollen doch ignoriert werden (?).

3 Fragen an den Thread-Ersteller:
- Server und Client auf der gleichen Maschine oder 2 Rechner ?
- Version der fbclient.dll stimmt mit Server über ein ?
Welche Version genau ?
- Welche Datenstruktur hat die Tabelle ?

Sonderpreis-Frage:
Mal den Virenscanner deaktiviert ?


Heiko

[mkinzler]

Überschreiben wäre natürlich einfacher (

SQL-Code:

update or insert into ....

)

[muenster]

Die Erfahrung der langsamen Transaktion habe ich auch.

Im Grunde nutze ich einen öhnlichen Code um aus der BDE die Daten in Firebird zu migriren.
Allerdings verwende ich die IBDAC.
Da ich die BDE Tabellen zerlegen muss, sind mehrere Aufrufe über TQuery notwendig um die jeweiligen Firebird Tabellen zu füllen. Am Ende des "Umbaus" steht ein INSERT INTO der mit Parametern gefüllt wird. Und die Performance ist ähnlich schlecht wie beschrieben. Das Ganze läuft bei mit komplett lokal.

Es liegt somit die Vermutung nah, dass das Absetzen einzelner Datensätze die Performance des lokalen Firebirdserver ziemlich ausbremst.

Wenn ich den Thread hier richtig verstehe, kann über die Transaktionskontrolle das Absetzen mehrerer Statements gebündelt werden. Ist es richtig, dass SQL.Text mehr als ein INSERT Statement enthalten darf?

[mkinzler]

Nein, ein normaler Query nimmt immer nur ein Statement auf. Es gibt aber in jeder Komponentensammlung auch eine skript-Komponente. Explizite Transaktionssteuerung bedeutet, dass kein Autocommit, nach jedem Insert ausgeführt wird, sondern dass dieser manuell erfolgt. Bei einzelnen Inserts ( unprepared; ohne Parameter) muss zusätzlich für jeden Insert ein Plan gebildet werden. Die Kombination von parametrisierten Abfragen mit Transaktionssteuerung sollte deshlab einiges bringen. Zudem könnte man überlegen die Dupletten vorher aus zu sortieren.

[muenster]

Danke, ist aber genau das was ich befürchtet hatte.
Doppelte Datensätze gibt es nicht, ich muss nur häufiger durch die Tabellen um diese in neue Häppchen zu zerlegen. Wenn man schon migriert, dann kann man ja auch gleich das Design überarbeiten.

[mkinzler]

Ich würde, wie gesagt prepared Queries verwenden. Man könnte sich auch überlegen für diesen Fall ein SP zu nehmen

Zitat:

Wenn man schon migriert, dann kann man ja auch gleich das Design überarbeiten.

Macht auf jeden Fall Sinn

[nahpets]

Hallo,

Zitat:

Zitat von muenster

Wenn ich den Thread hier richtig verstehe, kann über die Transaktionskontrolle das Absetzen mehrerer Statements gebündelt werden. Ist es richtig, dass SQL.Text mehr als ein INSERT Statement enthalten darf?

Jain, es kommt auf die Datenbank an.

Bei einem ähnlichen Problem mit 'nem SQL-Server bin ich hergegangen und habe 100 Inserts in einer Stringliste gesammelt und dann zusammen einer TAdoQuery zugewiesen und gemeinsam per ExecSQL ausgeführt. Das funktioniert, jedes Statement muss dann aber mit einem Semikolon enden. Es ist zumindest mal einen Versuch wert, ob Firebird damit auch zurecht kommt. So könnte man dann eben 100 (oder so) Statements in eine Transaktion packen. Bei meinem Rechner und der benutzen Datenbank habe ich mit 100er-Schritten das beste Ergebnis erhalten, bei weniger Inserts, wurde es deutlich langsamer, bei mehr musste ich mit Timeouts rechnen.

[hoika]

Hallo,

Zitat:

ich muss nur häufiger durch die Tabellen um diese in neue Häppchen zu zerlegen. Wenn man schon migriert, dann kann man ja auch gleich das Design überarbeiten.

Und dafür werden hoffentlich mind. prepared Queries benutzt ?

Ist denn der Häppchen-Code wirklich so kompliziert,
dass er nicht in eine SP passt ?


Heiko

[muenster]

Zitat:

Zitat von hoika

Und dafür werden hoffentlich mind. prepared Queries benutzt ?

Ist denn der Häppchen-Code wirklich so kompliziert,
dass er nicht in eine SP passt ?

Keine prepared Queries weil das Ganze nur einmal läuft.

Schön wär's wenn es in eine SP passen würde. Leider sind Ergänzungen und/oder Zerlegungen drin. Für eine Einmalige Umstellung viel zu aufwendig.

Gruß Karsten

[mkinzler]

Zitat:

Keine prepared Queries weil das Ganze nur einmal läuft.

Aber wohl mehrere Datensätze, oder?

[hoika]

Halo,

also ich gehe ohne SQL-Monitor nicht aus dem Haus.

Ausserdem:
Wenn es eine einmalige Sache ist, sch** auf die Zeit ;)


Heiko

[omata]

So ich habe mich jetzt nochmal dran versucht.

Zitat:

Zitat von 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)

da komme ich noch lange nicht ran ...

Ich muss meine Aussage leider etwas korrigieren...

Zitat:

Zitat von globetrotter77

Ca. 105000 Datensätze in knapp 4 Minuten

Ich schaffe immerhin 1000000 Datensätze (Datenvolumen von 60MB) in 4 Minuten (siehe Anhang).

Die Zeiten die ich oben angegeben hatte, hatte ich mit FlameRobin ausprobiert und eben nicht in Delphi. Das sollte eigentlich auch kein Problem darstellen, allerdings darf für meinen Vorschlag die Option "ExternalFileAccess" nicht auf None stehen. Dies ist die Voreinstellung und kann über die firebird.conf geändert bzw. überschrieben werden. Mit FlameRobin funktioniert das auch, aber eben leider nicht, wenn man selber aus Delphi heraus auf Firebird (Emmbedded) zugreift (vielleicht kann mir ja einer verraten wie das geht).
Deshalb konnte ich leider nicht meinen ersten Vorschlag umsetzen.

Zitat:

Zitat von globetrotter77

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

Nein, diesen Sonderfall habe ich jetzt noch nicht betrachtet.

[hoika]

Hallo,

Also

Delphi-Quellcode:

SQLDataSet.ExecSQL;
Application.ProcessMessages;

du rufst nach jedem Insert Application.ProcessMessages; auf.

Ich würde das ändern in

Delphi-Quellcode:

if (FZeile mod 100) then
begin
  Application.ProcessMessages;
end;

Da du ja hier eh einen Zähler (Fzeile) hast,
könnte man auch den Tip mit Commit/StartTransaction einbauen

Delphi-Quellcode:

if (FZeile mod 100) then
begin
  SQLConnection.Commit(TD);
  SQLConnection.StartTransaction(TD);
end;

Zusammen also wie folgt:

Delphi-Quellcode:

if (FZeile mod 100) then
begin
  Application.ProcessMessages;
end;

if (FZeile mod 100) then
begin
  SQLConnection.Commit(TD);
  SQLConnection.StartTransaction(TD);
end;

Ich würde es so wie hier getrennt lassen,
dann kann man am mod X noch rumspielen.

Zitat:

Firebird (Emmbedded)

Dazu muss einfach die FBClient.DLL ausgetauscht werden,
das Programm sollte im Idealfall davon gar nix mitbekommen.

Exe -> FBClient(SQL-Server) -> SQL-Server
Exe -> FBClient(Embedded)

Die embedded-Variante der FBClient-Dll implementiert den SQL-Server
selber und reicht die Befehle nicht weiter wie die normale DLL.

Zusätzlich müssen noch ein paar Dateien in ein Unterverzeichnis,
Infos dazu hier (Suche) oder einfach mal Frau Google fragen.


Heiko

[IBExpert]

und wenn du deinen import noch mal richtig beschleunigen willst, dann bau in deinem Delphi Programm deine Textdatei auf ein fixedformat um (also pro spalte eine feste anzahl an zeichen, nicht genutzte mit Leerzeichen auffüllen). Danach schaust du dir die Deaklaration eines external files in Firebird an (geht in IBExpert mit einem assisetnten im Table editor, wichtig: in der Firebird conf vorher ein directory für external files freigeben.

im sql editor geht das auch direkt

create table imp external file 'C:\imp.txt'
(ID CHAR(18),
TXT1 CHAR(80),
TXT2 CHAR(80),
TXT3 CHAR(80),
CRLF CHAR(2))

usw, dann kannst du mit "insert into tabx select * from imp" extrem schnell daten importieren, je nach hardware durchaus bis 100.000 records pro sekunde!

alternativ ab fb2 einfach dein SQL mit

execute block
as
insert .....;
insert .....;
insert .....;
.....
insert .....;
insert .....;
end

aufbauen, damit gehen mehrere Inserts pro sql

aber immer wichtig: aktive Transaktionssteuerung

[omata]

Zitat:

Zitat von hoika

du rufst nach jedem Insert Application.ProcessMessages; auf.

Ich würde das ändern

Das hatte ich getestet und das brachte keine zeitliche Verbesserung.

Zitat:

Zitat von hoika

Da du ja hier eh einen Zähler (Fzeile) hast,
könnte man auch den Tip mit Commit/StartTransaction einbauen

Hast du das mal ausprobiert, ich könnte mir vorstellen, dass das gar nichts bringt.

Zitat:

Zitat von hoika

Dazu muss einfach die FBClient.DLL ausgetauscht werden,
das Programm sollte im Idealfall davon gar nix mitbekommen.

Die embedded-Variante der FBClient-Dll implementiert den SQL-Server
selber und reicht die Befehle nicht weiter wie die normale DLL.

Hast du mal in mein Archiv geschaut, da sind alle möglichen, benötigten DLLs drin. Ich hatte auch geschrieben, dass ich das mit FlameRobin ausprobiert hatte, die selbe DLL und auch im Embedded-Modus.

Zitat:

Zitat von hoika

Zusätzlich müssen noch ein paar Dateien in ein Unterverzeichnis,
Infos dazu hier (Suche) oder einfach mal Frau Google fragen.

Ja wunderbar, selbst wird man mal konkret und erhält nur nicht getestete "womöglich Vorschläge".

Zitat:

Zitat von IBExpert

und wenn du deinen import noch mal richtig beschleunigen willst, dann bau in deinem Delphi Programm deine Textdatei auf ein fixedformat um (also pro spalte eine feste anzahl an zeichen, nicht genutzte mit Leerzeichen auffüllen). Danach schaust du dir die Deaklaration eines external files in Firebird an (geht in IBExpert mit einem assisetnten im Table editor, wichtig: in der Firebird conf vorher ein directory für external files freigeben.

Genau das war meine erste beschriebene Idee, die (wie ebenfalls geschrieben) mit FlameRobin zu den oben erwähnten Zeiten kommt, aber eben leider nicht aus Delphi heraus funktioniert.

Was soll das? Habt ihr auch mal gelesen, was ich geschrieben habe? Habt ihr auch mal eure Vorschläge ausprobiert? Ich versuche hier zu helfen, in diesem Fall mal wieder mit einem konkreten Beispiel, wie wäre mit konkreten Vorschlagen/Erweiterungen/Verbesserungen und eigenen Tests.

Wenn man das so ließt, meint ihr wohl, ich bin da der totale Volldepp. (Eben nicht! ich bin der, der es ausprobiert hat!)

Und da ihr ja alle so schlau seit, werde ich jetzt nichts mehr zum Thema sagen.

[IBExpert]

warum sollte ein create table mit external file aus delphi heraus nicht funktionieren? das mach ich andauernd.
Das external file sollte immer eine temporäre tabelle sein, die dann z.B. via "update or insert" oder stored
proc in die reale tabelle übertragen wird.

Ich will keineswegs deine Postings kritisieren und das man den einen oder anderen Eintrag 4 seiten vorher
mal übersieht ist ja auch möglich oder? Von volldeppen spricht hier keiner, ich jedenfalls nicht.
nicht jeder Input aus diesem Forum hat den Anspruch 100% sauberen kompilierbaren code zu liefern, weil nicht
jeder die zeit dazu hat, das für jede Anfrage umzusetzen. Wenn du das zu jedem deiner Postings machst finde ich
das sehr lobenswert.

das nur dazu, und meinen Hinweis auf execute block ist auch problemlos innerhalb von Delphi benutzbar, jedenfalls
mit brauchbaren aktuellen Zugriffskomponenten.

[mkinzler]

Zitat:

warum sollte ein create table mit external file aus delphi heraus nicht funktionieren?

IBExpert ist ja wohl auch in Delphi erstellt worden.

[IBExpert]

Zitat:

Zitat von mkinzler

Zitat:

warum sollte ein create table mit external file aus delphi heraus nicht funktionieren?

IBExpert ist ja wohl auch in Delphi erstellt worden.

sehr guter Hinweis, danke, warum bin ich denn da nicht drauf gekommen :spin2:

[omata]

Zitat:

Zitat von omata

... Das sollte eigentlich auch kein Problem darstellen, allerdings darf für meinen Vorschlag die Option "ExternalFileAccess" nicht auf None stehen. Dies ist die Voreinstellung und kann über die firebird.conf geändert bzw. überschrieben werden. Mit FlameRobin funktioniert das auch, aber eben leider nicht, wenn man selber aus Delphi heraus auf Firebird (Emmbedded) zugreift (vielleicht kann mir ja einer verraten wie das geht).

Wer lesen kann ist klar im Vorteil...

Zusatz: Zugriff über DBExpress (so wie der TE auf die DB zugreift)

Darauf wird natürlich mit keiner einzigen Silbe mal eingegangen, echt bedauerlich.

[hoika]

Hallo,

Zitat:

Da du ja hier eh einen Zähler (Fzeile) hast,
könnte man auch den Tip mit Commit/StartTransaction einbauen

Hast du das mal ausprobiert, ich könnte mir vorstellen, dass das gar nichts bringt.

Ja, habe ich, allerdings nicht mit deinem Programm.


BTW:
Hast du das mit dem SQL-Monitor denn nun mal ausprobiert ?

Kleine Info:
Ich arbeite seit IB4 (Linux-Version) mit Interbase ...
Und ich benutze bei Performance-Problemen zuallerst den SQL-Monitor.
Deshalb mein Hinweis dazu.


Heiko