Bulk Load

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

vr2
Beiträge: 106
Registriert: Fr 13. Apr 2018, 00:13

Es geht um das Laden von großen Datenmengen in eine Firebird-DB, was alle betrifft, die Reporting und BI machen. Wir hatten das Thema bisher hier Firebird 4 bulk api und hier Firebird 3 UDR in Pascal/Delphi und hier Firebird Performance Newsletter: Ausgabe 1. Es ist nicht so, dass Firebird das nicht kann, die Möglichkeiten sind nur so unbekannt oder dürftig dokumentiert, dass die meisten sie nicht nutzen können.

Konkret gibt es für bulk load in Firebird die folgenden Möglichkeiten (Parallelisierung außen vor gelassen, weil es zuerst darauf ankommt, einen Ladevorgang mit brauchbarer Geschwindigkeit zu haben). Bei den Eingangsdaten können wir von csv ausgehen als kleinstem gemeinsamen Nenner:
  • execute block mit "insert script"
  • UDR
execute block mit script

Die Grundidee ist dabei, dass csv-Daten als BLOB vorliegen. csv-Daten haben bereits eine Struktur, die einer Folge von insert-Statements ziemlich ähnlich sind. Es sind durch Trenner getrennte Werte, wie sie im values-Part eines insert-Statements vorkommen. Eine Zeile csv bedeutet ein insert-Statement. Vor die Werte muss eine Aufzählung der Spaltennamen. Die Zieltabelle kann eine gleichförmige Tabelle aus n varchar-Spalten sein, zb 20 oder 50 Spalten mit jeweils varchar(100). Dh, man behandelt die Eingangsdaten beim Import unterschiedslos wie im Webbereich als Text, was den Import vereinfacht.

Das ganze innerhalb einer SP import_csv_blob. Diese SP transformiert die csv-Blobdaten in eine Folge von insert-statements, setzt die als den body eines execute blocks und führt den dann aus. Allerdings kann der body eines execute blocks in Firebird 3 maximal 32K lang sein, deswegen muss man die csv-Daten in passende Happen < 32K partitionieren, das ist die Schwierigkeit bei diesem Ansatz. Da der execute block nur einmal übersetzt wird, ist der Ansatz trotz der Happenbildung wesentlich schneller als alle anderen normalen Stapel-inserts, auch als inserts über prepared statements. Das ganze macht die SP dann in Happen kleiner 32K, bis der csv-Blob importiert ist. Das geht selbst mit Firebird 2.5.

UDR

Ab Firebird 3 gibt es UDR, user defined routines. Das sind extern definierte Routinen, die aber im Unterschied zu UDFs vollen Zugriff auf DB-Objekte und -Kontext haben und innerhalb von Transaktionen laufen. Fortsetzung folgt.
Benutzeravatar
martin.koeditz
Beiträge: 292
Registriert: Sa 31. Mär 2018, 14:35

Hi Volker,

interessante Ansätze. Bin gespannt...

Gruß
Martin
Martin Köditz
it & synergy GmbH
bfuerchau
Beiträge: 278
Registriert: Mo 7. Mai 2018, 18:09

Allerdings muss man auch die andere Seite sehen, denn das Ausgeben von Daten erst in CSV dauert auch ebenso lange. Zusätzlich muss man dann noch berücksichtigen, dass
- NULL's nicht übermittelbar sind
- Codierungsschwierigkeiten auftreten (Datumsformat, Decimalpoint, u.ä.)
- Text/Binär-Blobs auf diesem Weg nicht kopiert werden können.

Das mit den Importscripts von iSQL habe ich auch gefunden, es wird aber auch empfohlen diese undokumentierte API's nicht zu verwenden.

Der SQL-Server unterstützt z.B. Multi-Values beim Insert:
insert into MyTabelle (F1, F2, ...,FN)
values(F1, F2, ..., FN)
,values(F1, F2, ..., FN)
,values(F1, F2, ..., FN)
:

Hierbei können ebenso in den Value-Listen Parametermarker verwendet werden.

Allerdings habe ich hier doch noch einen Ansatz gefunden:
https://firebirdsql.org/refdocs/langref ... block.html

Somit kann ich eine Prozedur mit Parametern deklarieren und in den multiplen Inserts darauf Bezug nehmen.
Das werde ich dann morgen direkt mal ausprobieren.

Wenn ich mich recht erinnere, ist ab 3.0 eine Länge von bis 10 MB für ein Script möglich?
Die Aussage widerspricht sich ein wenig, dass ein CommandText max. 64KB lange sein kann. Aber was solls, man kann mit kurzen Namen anfangen, so wie es der SQL-Server auch macht, P1, ..., PN.
Länger sind da schon die diversen Casts.
Ich werde berichten.
bfuerchau
Beiträge: 278
Registriert: Mo 7. Mai 2018, 18:09

So, nun habe ich nach den obigen Prinzipien einen Execute Block generiet (62 KB) und es hat im Prinzip keinen Vorteil gebracht.
Durch die lange Syntax (Parameter Definition, Insert-Befehle) habe ich mit z.B. 72 Spalten nur 16 Inserts zusammen gebastelt.

Grundsätzlich funktioniert es, allerdings kommt nichts dabei raus.
Normale Inserts: ca. 2700.
1 Insert via Execute: ca. 2500.
2 Insert via Execute: ca. 2900.
3 Insert via Execute: ca. 2900.

Ab dem 2. Satz merkt man kein Wachstum mehr. Es ist eine leichte Verbesserung gegenüber dem einfachen Insert, aber das lohnt kaum den Aufwand. Ich denke mal, der Rechenaufwand sowie Zuordnung der Parameter in dem Excute-Block frist den Vorteil wieder auf. Immerhin müssen 16 x 72 Parameter geschickt und konvertiert und wieder in Inserts verpackt werden.
Würde man die Inserts mit Strings statt mit Parametern bauen, wird der SQL u.U. zu lang und dann bekommt man nicht mal 1 Insert generiert.

Schade eigentlich. Vielleicht sollte man mal eine Anforderung stellen, einen FbBulkCopy mit systemnahen API's ggf. besser hin zu bekommen.
Ich für meinen Teil höre damit auf.
Wer möchte soll sich bei mir melden, sie oder er kann das C#-Projekt haben, vielleicht hat ja irgendwer noch mal andere Ideen.
bfuerchau
Beiträge: 278
Registriert: Mo 7. Mai 2018, 18:09

Nachtrag und neueste Erkenntnisse:
Die Performancc hängt schon wesentlich von der Anzahl Spalten und somit auch von der Anzahl Parameter ab.
Dabei bin ich allerdings auf ein Limit gekommen:

Eine Prozedur/Block darf bis 64KB sein.
Die Summe der Parameterfelder darf die aktuelle Zeilenlänge (64K) aber ebenso nicht übersteigen, BLOB's ausgenommen.
Je kürzer also der Datensatz, desto mehr Inserts passen in den Block.

Die Ergebnisse sind zwar nicht so beeindruckend wie beim SQL-Server, aber sie sind verwendbar.

Beispiel:
Einzelinsert mit 8 Feldern => 8.300 Zeilen, BulkInsert => 38.000
Einzelinsert mit 72 Feldern => 3.800, Bulkinsert => 5.500

Die Klasse unterstützt noch nicht alle möglichen Feldtypen, aber das ist schon mal ein Anfang.
Wer möchte, kann sich die Quelle mal ansehen und für sich nutzen/portieren:
http://tracker.firebirdsql.org/secure/a ... ulkCopy.cs
vr2
Beiträge: 106
Registriert: Fr 13. Apr 2018, 00:13

bfuerchau hat geschrieben: Do 15. Apr 2021, 17:02 Die Performancc hängt schon wesentlich von der Anzahl Spalten und somit auch von der Anzahl Parameter ab.
Ja. Entscheidend ist die Satzbreite der Zieltabelle in Bytes. Je kleiner, desto besser ist die Kennzahl Sätze/sek. Dabei ist es egal, on man in 80 varchar(800)-Spalten schreibt oder in 2 varchar(32000)-Spalten.

Jeder Rechner hat natürlich eine andere insert-Performance, das kommt hinzu. Das hängt hauptsächlich an der Single-Thread-Performance der CPU. Um da mal ein Maß zu bekommen, kann man so vorgehen:

Code: Alles auswählen

CREATE DOMAIN FTEXT AS
VARCHAR(800);

CREATE GLOBAL TEMPORARY TABLE T_RAM (
  ID  VARCHAR(1000),
  F1  FTEXT, F2  FTEXT,  F3  FTEXT,  F4  FTEXT,  F5  FTEXT,  F6  FTEXT,  F7  FTEXT,  F8  FTEXT,  F9  FTEXT,  F10 FTEXT,
  F11 FTEXT,  F12 FTEXT,  F13 FTEXT,  F14 FTEXT,  F15 FTEXT,  F16 FTEXT,  F17 FTEXT,  F18 FTEXT,  F19 FTEXT,  F20 FTEXT,
  F21 FTEXT,  F22 FTEXT,  F23 FTEXT,  F24 FTEXT,  F25 FTEXT,  F26 FTEXT,  F27 FTEXT,  F28 FTEXT,  F29 FTEXT,  F30 FTEXT,
  F31 FTEXT,  F32 FTEXT,  F33 FTEXT,  F34 FTEXT,  F35 FTEXT,  F36 FTEXT,  F37 FTEXT,  F38 FTEXT,  F39 FTEXT,  F40 FTEXT,
  F41 FTEXT,  F42 FTEXT,  F43 FTEXT,  F44 FTEXT,  F45 FTEXT,  F46 FTEXT,  F47 FTEXT,  F48 FTEXT,  F49 FTEXT,  F50 FTEXT,
  F51 FTEXT,  F52 FTEXT,  F53 FTEXT,  F54 FTEXT,  F55 FTEXT,  F56 FTEXT,  F57 FTEXT,  F58 FTEXT,  F59 FTEXT,  F60 FTEXT,
  F61 FTEXT,  F62 FTEXT,  F63 FTEXT,  F64 FTEXT,  F65 FTEXT,  F66 FTEXT,  F67 FTEXT,  F68 FTEXT,  F69 FTEXT,  F70 FTEXT,
  F71 FTEXT,  F72 FTEXT,  F73 FTEXT,  F74 FTEXT,  F75 FTEXT,  F76 FTEXT,  F77 FTEXT,  F78 FTEXT,  F79 FTEXT,  F80 FTEXT
);

create procedure bench (
  num integer
)
returns (
  status varchar(1000)
)
AS
declare i int;
declare ts1 timestamp;
declare ts2 timestamp;
begin
  i = 0;
  status = 'ok';
  ts1 = cast('now' as timestamp);
  while (:i < :num) do
  begin
    insert into t_ram(id, f1) values ('bench', 'A');
    i = i + 1;
  end
  ts2 = cast('now' as timestamp);
  status = (num * 1000 / (datediff(millisecond, ts1, ts2))) || ' Sätze/sek';
  suspend;
end;

Code: Alles auswählen

select * from bench(100000)
Liefert dann die normierte insert-Performance bzgl dieses Szenarios, bei diesem Aufruf werden 100000 Sätze in die GTT eingefügt. 100000 ist ein guter Default, weniger ist zu wenig für eine Beurteilung und mehr ist nicht nötig. bench dauert normalerweise nur einige Sekunden. Damit läßt sich auch prima einer Virtualisierung auf den Zahn fühlen. Denn wenn die so konfiguriert ist, dass sie auch die CPU nach Last zuteilt, ist schon alles zu spät, denn bei solchen Anforderungen braucht man die CPU ab Sekunde 0 voll, da darf sich keine VM-Logik reinhängen und erst mal schauen, was die Anwendung denn so an Ressourcen braucht und die scheibchenweise nachlegen. Falls die VM so konfiguriert ist, kann man das daran erkennen, dass die Anzahl Sätze/sek, die bench misst, stark schwankt. Auf einem nicht virtualisierten Rechner hingegen oder bei einer VM, die bzgl CPU auf Durchzug steht, bekommt man bei mehrfacher Ausführung von bench annähernd den gleichen Durchsatz.

Lass mal die Konvertierung/Behandlung verschiedener Datentypen außen vor. Der Ansatz ist, erstmal die Daten so schnell wie möglich als Text in die DB zu bekommen. Sobald sie da sind, kann man weitersehen.

Auch muss man aufpassen, dass man den csv-Blob nicht zu oft anpackt. Eine Stringzerlegung darf den nur einmal anpacken, sonst wird immer der gesamte Riegel in den Speicher geladen und dann ist die Performance hinüber, aber nicht wegen dem Ansatz, sondern wegen wiederholter BLOB-Verarbeitung.

In dem execute block gilt übrigens auch die Einschränkung 255 Kontexte, er kann nur max 255 insert-statements haben.
Zuletzt geändert von vr2 am So 25. Apr 2021, 02:07, insgesamt 1-mal geändert.
bfuerchau
Beiträge: 278
Registriert: Mo 7. Mai 2018, 18:09

Auf 255 inserts komme ich ja noch nicht mal.
a) komme ich da über 64 KB
b) arbeite ich mit Parameter-Markern und da ist eine Pufferlänge beschränkt.
Leider finde ich dazu keine Berechungsmethode.

Außerdem wird eine global temorary table gar nicht in die DB geschrieben sondern in den Temp-Ordner, und der ist per se besser, da keine DB-Blöcke gesucht und verkettet werden müssen.

Mach mal einen Test direkt in eine echte Tabelle.

Mein Interesse ist nicht die interne Leistung sondern die Insert-Leistung in eine DB-Tabelle mit/ohne einen Unique-Key.
Der Umweg über eine CSV macht ja keinen Sinn. Für die Erstellung einer CSV benötige ich auch schon auf meiner Umgebung ca. 6-8000 Writes/Sekunde in die Textdatei. Da ist auch scon mal die Umwandlung in UTF8 zusätzlich dabei.
Dann reicht es mir ja schon, wenn ich 6-8000 Inserts direkt in die DB schaffe.
Da aber die Lese- höher als die Schreibgeschwindigkeiten sind, möchte ich ähnliches wie beim SQL-Server erreichen.
Ich bleibe dran.
bfuerchau
Beiträge: 278
Registriert: Mo 7. Mai 2018, 18:09

Alle Versuche einen dynmaschin BulkCopy durchzuführen scheitert an divesen Fehlermeldungen der Firebird.

Die entsprechenden Beispiele die man so findet, gehen immer von einem Textblock mit eingebetteten Werten statt Parametermarkern aus.
Also einen
Execute Block as begin
insert into mytable values('A', 12.5);
:
end;

Ausgehend von aktuell 64K Commandtext (ab 3.0 bis 10MB), bleibt es für den Execute Block bei 64KB. Wobei hier die BLR-Umsetzung auf 64-KB beschränkt ist.
D.h., ein SQL-Text "Execute Block" mit vielen Inserts (immer noch max. 255, bei 256 gibts einen Fehler) und wirklich nur 63K wird mit einem Fehler "attempts .... read only columns". Dies deutet nach Firebirdtracker auf eine BLR > 64 K hin.

DIese Einschränkungen sind allerdings so nicht kalkulierbar.
Bei kurzen Inserts (z.B. 8 Spalten) kann man locker 255 Inserts unterbringen.
Bei einem Insert mit 72 Spalten, ist man bereits auf 20K eingeschränkt, sonst gibts obige Fehlermeldung.

Statt des direkten Inserts kann man auch einen
execute statement 'insert ....';
durchführen, wobei hier eine Verdoppelung der Hochkomma zu beachten ist. Dann kann man auch wieder 64K verwenden.
Allerdings ist diese Varianten nicht schneller als die Einzelinserts, da ja für jeden Execute die komplette Syntaxprüfung usw. anfällt.

Solange also die Möglichkeit nicht besteht, mehrere Statements ohne execute block ausführen zu lassen, macht ein Bulk-Load mit klassichen Methoden keinen Sinn. Die nativen Inserts insgesamt sind ja schon schneller geworden.
bfuerchau
Beiträge: 278
Registriert: Mo 7. Mai 2018, 18:09

Falls es überhaupt noch jemanden interessiert:

Firebird 2.5:
Die SQL-Länge ist auf 64K beschränkt, das wissen wir ja, allerdings bei der Verwendung von UTF8 auf 16K.
Kommen im SQL-Text keine UTF8-Zeichen vor (wie Umlaute), können 64K verwendet werden. Falls doch Umlaute o.ä. vorkommen, wird in UTF8 übertragen und dann darf das SQL 16K nicht mehr übersteigen sonst kommt der Fehler 335544721 = Unable to complete network request to host "xxxxx".
Wenn der Fehler auftaucht, muss man die Connection schließen und wieder erneut öffnen.

Die Verwendung von Parametern im Execute Block beschränkt sich auf die max. Zeilenlänge von 64K, bei UTF8 wieder auf 16K.

Firebird 3.0:
Die SQL-Länge kann zwar 10Mb sein, beschränkt sich allerdings allerdings bei UTF8 wieder auf 2,5MB.
Dies reicht aus, um die obigen Probleme zu minimieren.
Somit kann man dann durchaus wieder längere SQL's absetzen.

Insgesamt bleibt die Beschränkung auf 255 Inserts im Execute Block.
In beiden Fällen lohnt kein Execute Block mit parametrierten Inserts.
Es wird also ein Execute Block mit Insert's und Feldwerten gearbeitet:
execute block as
begin
insert into MyTable values('A', 1);
insert into MyTable values('B', 1);
end

Mit maximal Anzahl Zeilen (255) die noch in 16K/2MB in UTF8 passen.

Nun zu den Testergebnissen auf einem I7, 2,6GHz:

Firebird 2.5:
a) Read aus DB1, Insert DB2 => 72 Felder, 3550/Sekunde
b) Read aus DB1, BulkInsert DB2 => 72 Felder, 2540/Sekunde

Firebird 3.0 (ander DB):
a) Read/Insert => 33 Felder, 4.500/Sekunde
b) Read/Bulk => 33 Felder, 3.800/Sekunde

Die reine Ausführungszeit des Command.ExecuteNonQuery liegt bei ca. 50Ms, die Aufberitung der SQL's als String für 255 Inserts, ca. 60Ms.

Da frage ich mich nun mal, was mir eine DB bringt, die 100.000 Zeilen/Sekunde einfügen kann wenn man immer dieselbe Zeile einfügt?
vr2
Beiträge: 106
Registriert: Fr 13. Apr 2018, 00:13

bfuerchau hat geschrieben: Mo 19. Apr 2021, 16:41 Nun zu den Testergebnissen auf einem I7, 2,6GHz:

Firebird 2.5:
a) Read aus DB1, Insert DB2 => 72 Felder, 3550/Sekunde
b) Read aus DB1, BulkInsert DB2 => 72 Felder, 2540/Sekunde

Firebird 3.0 (ander DB):
a) Read/Insert => 33 Felder, 4.500/Sekunde
b) Read/Bulk => 33 Felder, 3.800/Sekunde
Danke für die Testergebnisse, kam die letzten Tage nicht dazu, hier weiterzumachen, das kann leider immer mal sein, sorry.

Mir kommen die gemessenen Bulkinsert-Werte zu niedrig vor. Um Deine Zahlen ins Verhältnis setzen zu können - kannst Du bitte

1. den bench aus meinem Posting mal ausführen, am besten auf beiden Servern. Dann haben wir ein Maß für die CPU.
2. von beiden Messungen die Satzlänge in Bytes angeben (die 72 bzw 33 Felder). Das ist der andere bestimmende Faktor.

Grüße, vr2
Antworten