Wie bekomme ich einen performanten Firebird?

[jobo]

Spannende Infos von IBExpert!

Auch wenn ich auf einem Laptop mit einem kommerziellen Standard DB System auf ähnliche oder bessere Werte komme (jenachdem wie man's macht) als hier genannt (4 Mio records ca 6 Minuten oder 11 Minuten):

Zitat von Union:

8,364,632 records

Verbindung	Index	Dauer hh:mm
remote	aktiviert	05:53
remote	deaktiviert	02:05
lokal	deaktiviert	00:21

Es zeigt doch, wie sehr es sich lohnt, sich mit den Interna eines DB Systems auseinanderzusetzen!
Mich wundert immer wieder mal, wie egal das vielen hier scheinbar meist ist.

Mich würde für einen genaueren Vergleich noch der originale Tabellenaufbau interessieren und ob dort ein Primärschlüssel definiert ist. Oder ist "Index deaktiviert" gleich "nicht mal Primärschlüssel"?

[Union]

Untenstehend die DDL für die betreffende Tabelle. Die Indizes waren beim letzten Import komplett deaktiviert mit ALTER INDEX <name> INACTIVE. In der usprünglichen DB gibt es keine PK und keine RI. Grosse Integer werden durch DOUBLE dargestellt, da die ursprünglich verwendeten DB keine BIGINT bzw. INT64 unterstützen - nicht lachen, das System ist in den Ursprüngen knapp 30 Jahre alt.

Code:

CREATE TABLE BUCHUNG
(
 "ID" DOUBLE PRECISION,
 "VORG_NR" DOUBLE PRECISION,
 "LFD_BNR" DOUBLE PRECISION,
 "MAND_ID" DOUBLE PRECISION,
 "ART_ID" DOUBLE PRECISION,
 "PAL_NR" CHAR ( 10 ),
 "ZUG_NR" CHAR ( 20 ),
 "ZUG_POS" INTEGER,
 "ABG_NR" CHAR ( 20 ),
 "ABG_POS" INTEGER,
 "LIEFER_ID" DOUBLE PRECISION,
 "KD_ID" DOUBLE PRECISION,
 "BELEG_ID" DOUBLE PRECISION,
 "BUCH_DAT" DATE,
 "ZUG_DAT" TIMESTAMP,
 "MENGE" DOUBLE PRECISION,
 "PLATZ_ID" DOUBLE PRECISION,
 "DATUM" DATE,
 "ZEIT" CHAR ( 8 ),
 "VORGANG" CHAR ( 3 ),
 "AUTO_VORG" CHAR ( 3 ),
 "ART" CHAR ( 4 ),
 "USER" CHAR ( 8 ),
 "STATUS" CHAR ( 4 )
);
CREATE INDEX "BUCHUNG_ID" ON BUCHUNG (ID);
CREATE INDEX "BUCHUNG_MAND_ID" ON BUCHUNG (MAND_ID, ART_ID, DATUM, ZEIT);
CREATE INDEX "BUCHUNG_ART_ID" ON BUCHUNG (ART_ID, MAND_ID, DATUM, ZEIT);
CREATE INDEX "BUCHUNG_ZUG_NR" ON BUCHUNG (ZUG_NR, MAND_ID, ART_ID);
CREATE INDEX "VORG_NR" ON BUCHUNG (VORG_NR);
CREATE INDEX "BUCHUNG_PLATZ_ID" ON BUCHUNG (PLATZ_ID);
CREATE INDEX "VORGANG" ON BUCHUNG (VORGANG);
CREATE INDEX "BUCHUNG_STATUS" ON BUCHUNG (STATUS);

[IBExpert]

Abschliessend im Thread nur noch mal als Hinweis an alle, die viel
importieren und exportieren:

Der Weg über external files bringt bei Firebird erheblich mehr speed
als man denkt.

Die folgende Tabelle mit 20 Feldern, PK und 3 weiteren Indizes und
1.000.000 Datensätze braucht für die Übertragung von drinnen nach
draußen oder von draußen nach drinnen jeweils ca. 1 Minute (der server
hatte bei dem Test nur eine normale Festplatte eingebaut).

Ganz wichtig:

External files definieren geht immer, aber benutzen könnt Ihr die erst,
wenn Ihr in der Firebird.conf den Parameter ExternalFileAccess angepasst
habt und den Firebird Server noch mal neu startet.

Code:

ExternalFileAccess = Restrict C:\export

Bei diesem Beispiel könnt ihr dann im pfad c:\export dateien einlesen oder
auch von firebird erstellen lassen.

Das Feld crlf ist im external file nur definiert, damit man im Texteditor
auch zeilenvorschübe sehen würde, wenn man die denn dort öffnet. Bei allen
weiteren Typen am besten wegen Lesbarkeit auf char(m) umwandeln.

Die Datei sieht dann so aus (Die Boardsoftware schummelt da weitere crlf
rein, ist aber pro datensatz immer eine Zeile).

Code:

1                 JENNIFER                                          WOMMACK                                           MARTIN LUTHER KING JR AVE                         -                                                 PITTSFIELD                                        MO                                                14960             USA                                               3                 [e-mail]                        1(435)703-9855                                    4                 1389 4408 4192 3925                               6/2016                                            JENNIFER.WOMMACK1000001                           567373                                            36                45000             F
2                 MARIA                                             DOHERTY                                           NE NEFF ROAD                                      -                                                 ROCKVILLE                                         CT                                                67979             USA                                               5                 [e-mail]                           1(924)920-6861                                    1                 7739 7739 5985 5952                               12/2019                                           MARIA.DOHERTY1000002                              726542                                            31                79000             F
3                 RICKEY                                            HUFFMASTER                                        FIRST AVENUE EAST                                 -                                                 BERKELEY HEIGHTS                                  MN                                                12931             USA                                               11                [e-mail]                       1(659)169-7730                                    2                 4666 5413 7709 9617                               3/2016                                            RICKEY.HUFFMASTER1000003                          709595                                            31                41000             M
....

Es rechnet zeitlich sich sehr oft, wenn man Daten für einen Import bekommt, die mit
einem kleinen Delphi oder Lazarus Hilfsprogramm in ein passendes Fixed format zu
konvertieren und danach den Import in die Datenbank auf diesem Weg zu machen.

Beim Export kann man auf einfach csv Dateien erzeugen, in
dem man die Daten einfach als varchars trimt und mit trenner in
einen char(255) einträgt. Das füllt Firebird dann zwar bis zum Ende mit
Leerzeichen auf, ist aber für jedes csv Import normalerweise unkritisch
(char(2) für crlf nixht vergessen)

Direkter csv Import ohne externe Tools via external file ginge zwar auch,
ist aber relativ umständlich, in dem man das als Stored Procedure
auseinanderfrickelt.

Hier der Befehl, um Daten aus der internen Tabelle in die externe zu kopieren
[CODE]
insert into customer_ext_c (ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE,
CREDITCARDTYPE, CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER,crlf)
select ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE, CREDITCARDTYPE,
CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER,'
'
from CUSTOMER
[CODE]

Hier der Befehl, um Daten aus der externen Tabelle in die interne zu kopieren

Code:

insert into customer (ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE,
                      CREDITCARDTYPE, CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER)
select ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE, CREDITCARDTYPE,
       CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER
from customer_ext_c

Metadaten

Code:

/******************************************************************************/
/****              Generated by IBExpert 27.04.2014 20:58:34               ****/
/******************************************************************************/

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/

CREATE TABLE CUSTOMER (
    ID                    NUMERIC(18,0) NOT NULL,
    FIRSTNAME             VARCHAR(50) NOT NULL,
    LASTNAME              VARCHAR(50) NOT NULL,
    ADDRESS1              VARCHAR(50) NOT NULL,
    ADDRESS2              VARCHAR(50),
    CITY                  VARCHAR(50) NOT NULL,
    STATE                 VARCHAR(50),
    ZIP                   INTEGER,
    COUNTRY               VARCHAR(50) NOT NULL,
    REGION                SMALLINT NOT NULL,
    EMAIL                 VARCHAR(50),
    PHONE                 VARCHAR(50),
    CREDITCARDTYPE        SMALLINT NOT NULL,
    CREDITCARD            VARCHAR(50) NOT NULL,
    CREDITCARDEXPIRATION  VARCHAR(50) NOT NULL,
    USERNAME              VARCHAR(50) NOT NULL,
    PASSWD                VARCHAR(50) NOT NULL,
    AGE                   SMALLINT,
    INCOME                NUMERIC(18,0),
    GENDER                VARCHAR(1)
);


CREATE TABLE CUSTOMER_EXT_C EXTERNAL 'C:\EXPORT\CUSTOMER.DAT' (
    ID                    CHAR(18),
    FIRSTNAME             CHAR(50),
    LASTNAME              CHAR(50),
    ADDRESS1              CHAR(50),
    ADDRESS2              CHAR(50),
    CITY                  CHAR(50),
    STATE                 CHAR(50),
    ZIP                   CHAR(18),
    COUNTRY               CHAR(50),
    REGION                CHAR(18),
    EMAIL                 CHAR(50),
    PHONE                 CHAR(50),
    CREDITCARDTYPE        CHAR(18),
    CREDITCARD            CHAR(50),
    CREDITCARDEXPIRATION  CHAR(50),
    USERNAME              CHAR(50),
    PASSWD                CHAR(50),
    AGE                   CHAR(18),
    INCOME                CHAR(18),
    GENDER                CHAR(1),
    CRLF                  CHAR(2)
);



/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX CUSTOMER_IDX1 ON CUSTOMER (FIRSTNAME);
CREATE DESCENDING INDEX CUSTOMER_IDX3 ON CUSTOMER (ID);
CREATE UNIQUE INDEX IX_CUSTOMER_USERNAME ON CUSTOMER (USERNAME);

[Union]

Acuh wieder sehr informativ, danke. Leider habe ich damit zwwei bereits geschilderte Probleme, nämlich NULL und BLOB SUB_TYPE TEXT korrekt importiert zu bekommen.

[IBExpert]

naja, nullwerte werde mit coalesce umgewandelt in Leerstrings. Falls im Ziel dann auch wieder nullwerte gebraucht werden, können die dort ja via trigger auch wieder von einem Spezialstring, z.B. 'NULL' wieder in einen Nullwert umgewandelt werden.

Blobs sind eine eigene Baustelle, da würde ich wenn es geht die blobdaten in einen varchar packen (geht ja bis 32765 Zeichen Länge, macht dann aber das external file sehr groß), dann mit substring(feldname from 1 for 32765) noch begrenzen.

Oder noch besser, aus dem original Import entfernen und später gesondert einspielen. Entweder baust du dir dafür ein eigenes Tools oder benutzt so was wie in IBExpert eingebaut http://ibexpert.net/ibe/index.php?n=...sIntoADatabase

[mensch72]

Tolle Ansätze auf aktuellem Stand... ist zwar noch nicht auf dem Level was Anwendungen in meinem Gebiet brauchen, aber als "gute" Referenzwerte zur Argumentation bei hyperschlauen Admins/IT-Beratern super zu verwenden.

1Mio Datensätze pro Minute ist für SQL schon nicht schlecht... Da ich pro Tabelle "sehr sehr viele" kleine Buchungsdatensätze(100..300Mio Records) in Simulationen schneller 1..2Mio Datensätze pro Sekunde incl. (Zeit)Index streamen/kopieren muss(das ist nochmal Faktor 100+ schneller!), geht das wohl weiter nur mit eigenen auf LowLevel programmierten binären File-Strukturen.


Wenn es einen "realistischen"/"bezahlbaren" SQL-Datenbankserver mit einer Transferleistung von 1Mio Records/Sekunde(oder besser gäbe, würde mich das interessieren... wäre für jegliche Vorschläge offen, egal ob hier oder als PM.

[Dejan Vu]

Microsoft SQL-Server mit den einschlägigen Optimierungen sowie diversen Tricks, die man sonst noch im Internet findet (meist Hardware) ist per bulk load schon sehr schnell. Mich würde es wundern, wenn es ein RDBMS gibt, das diese Performance ohne bulk load hinbekommt.

Mit einem einfachen Key-Value Store (vor Jahren mal geschrieben) habe ich vor längerer Zeit ohne Optimierungen ca. 0.5 Mio Datensätze im regulären Betrieb hinbekommen (Datensätze ist übertrieben. 32 Byte-Chunks waren das und embedded war das Teil auch noch). Ich kann mir gut vorstellen, das gängige IMDB oder ausgewachsene KVS die Performance hinbekommen, die Du wünschst. Aber bezahlbar ist das dann irgendwann nicht mehr, weil der Server dann ziemlich teuer wird (und persistent ist das dann ja sowieso nicht).

Allerdings hat mein KVS das auch auf Pladde geschrieben. Bei den aktuellen (z.B. Redis) scheint das auch der Fall zu sein. Insofern sollte ich die nicht in einem Atemzug mit IMDB nennen.

[IBExpert]

Zitat von mensch72:

1Mio Datensätze pro Minute ist für SQL schon nicht schlecht... Da ich pro Tabelle "sehr sehr viele" kleine Buchungsdatensätze(100..300Mio Records) in Simulationen schneller 1..2Mio Datensätze pro Sekunde incl. (Zeit)Index streamen/kopieren muss(das ist nochmal Faktor 100+ schneller!), geht das wohl weiter nur mit eigenen auf LowLevel programmierten binären File-Strukturen.

In diese Bereiche wird ein SQL Server aufgrund der notwendigen Overheads (Recordversionen, Transaktion, Multiuserfähigkeit usw.) wohl eher nicht hinnkommen, daher ist das für deinen Anwendungsbereich sicherlich besser in eigener Lowlevel Implementation aufgehoben, bei der dich der Overhead sicherlich sowieso nicht interessiert. Welcher Bereich ist das denn, in dem man immer mal eben 300 mio records hin und herschubsen muss

[Union]

Zitat von IBExpert:

Welcher Bereich ist das denn, in dem man immer mal eben 300 mio records hin und herschubsen muss

Vielleicht ein LHC Programm zur Aufzeichnung von Detektorsignalen?

[vagtler]

Aber dann bewegen wir uns ja auch eher im Bereich von Big Data denn in relationalen DBMS.