100.000 Datensätze in 22 Minuten - geht das noch schneller?

[mkinzler]

Und du mich:
Es gibt in FireBird update or insert als ein erweitertes Insert-Statement, welches bei Bedraf ein Update macht:

SQL-Code:

update or insert into PRODUCT (ID, CATEGORY_ID, TITLE, ACTOR, PRICE, SPECIAL)
      values (:ID, :CATEGORY_ID, :TITLE, :ACTOR, :PRICE, :SPECIAL) matching (id);

[jobo]

Nein Das hab ich schon verstanden, aber wohl auch zu undeutlich gesagt. Mir ist schon aufgefallen, dass Du Or und nicht oder geschrieben hast, also dass Du die Anweisung meinst.

Das ändert aber auch nichts daran, dass es dem TE wahrscheinlich nicht weiterhilft. Während wir hier mit verschiedenen RDBMS basteln, steht diese schräge Sache mit dem satzweisen Verfahren im Raum, ein wie ich finde prinzipiell falsches.
Aber vielleicht sollte der TE sowieso mal was dazu sagen.

[FredlFesl]

Will der Fragesteller wirkklich seine Stored Procedure verwenden? Ist doch blöd.

So gehts in ein paar Sekunden.

Code:

-- CSV-Datei importieren
BULK INSERT #Temp 
FROM 'c:\file.csv' 
WITH ( 
     FIELDTERMINATOR = ';', 
     ROWTERMINATOR = '\n' 
     )
-- Index auf Primärschlüssel setzen (kann die Sache noch ordentlich beschleunigen)
CREATE INDEX Temporaer ON #Temp (Key) 

-- Existierende Daten ersetzen
UPDATE ZielTabelle SET Feld = #Temp.Inhalt, 
       NochEinFeld = #Temp.NochEinInhalt
 FROM Temp WHERE #Temp.Key = ZielTabelle.Key

-- Neue Daten anhängen
INSERT INTO ZielTabelle
  (Feld1, NochEinFeld)
SELECT Inhalt, NochEinInhalt
  FROM #Temp LEFT JOIN 
       ZielTabelle ON #Temp.Key = ZielTabelle.Key
 WHERE ZielTabelle.Key IS NULL

[IBExpert]

Ich hab in meinem Beispiel explizit eine Stored Procedure verwendet, weil ein Datenimport in 90 % der Fälle leider nicht nur mal eben mit "update or insert" oder merge über Primary Key ausreicht, auch wenn meine Demo Prozedur nicht mehr macht. Daher läuft bei mir der Import immer zweistufig, erst mal die Daten rein in die DB, dann in der DB via SP verteilen. Und das hat sich in erster Linie deshalb bewährt, weil das mit SP eben so schnell ist.

Wenn du die DB1 Demo DB auf deinem Rechner hast, dann starte einfach mal die Prozedur initall mit dem Parameter 10000. Damit werden aus den TMP_* Tabellen Demodaten generiert. Wenn man jetzt mal die TMP_* Tabellen als Import und die anderen Tabellen als finale Daten betrachtet, dann sieht man, was im Rahmen einer SP an speed möglich ist, zumindest bei Firebird, ich weiß von anderen Plattformen, wo SP weniger verbreitet sind, weil meistens auch nicht so schnell.

konkrete Daten und Performanceanalyse aus Firebird/IBExpert:

Code:

BEFEHL

execute procedure INITALL(10000);

Query
------------------------------------------------


Plan
------------------------------------------------


Query Time
------------------------------------------------
Prepare       : 0,00 ms
Execute       : 8.612,00 ms
Avg fetch time: 0,00 ms

Memory
------------------------------------------------
Current: 337.372.048
Max    : 337.833.024
Buffers: 20.000

Operations
------------------------------------------------
Read   : 114
Writes : 570
Fetches: 3.570.581


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|        Table Name        |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts |
|                          |   Total   |   reads   |    reads    |         |         |         |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|                  CUSTOMER|         0 |         2 |           0 |       0 |       0 |   10000 |
|                 INVENTORY|         0 |     70476 |           0 |   35238 |       0 |   10000 |
|                 ORDERLINE|         0 |         0 |           0 |       0 |       0 |   35238 |
|                    ORDERS|         0 |     45238 |           0 |   10000 |       0 |   10000 |
|                   PRODUCT|         0 |     35240 |           0 |       0 |       0 |   10000 |
|                   TMP_ADR|         0 |     10000 |           0 |       0 |       0 |       0 |
|                  TMP_CITY|         0 |      9999 |           0 |       0 |       0 |       0 |
|                    TMP_FN|         0 |      9996 |           0 |       0 |       0 |       0 |
|                   TMP_FN2|         0 |     19959 |           0 |       0 |       0 |       0 |
|                    TMP_LN|         0 |      9994 |           0 |       0 |       0 |       0 |
|                   TMP_LN2|         0 |     19962 |           0 |       0 |       0 |       0 |
|                 TMP_STATE|         0 |      9895 |           0 |       0 |       0 |       0 |
|             TMP_TITLEWORD|         0 |     29987 |           0 |       0 |       0 |       0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

oder mit anderen Worten:
Während der Ausführung der Prozedur wurden 391224 individuelle Insert/Update/Delete Statements
ausgeführt und gedauert hat das 8,6 Sekunden. Das die zigtausend Aufrufe von Zufallszahlen in
der Prozedur auch noch Zeit kosten ist klar, wenn man sich der Code in der Prozedur INITALL
ansieht.

Meßergebnis sind auf dem Wege aber über 45000 Operationen pro Sekunde, die man im Rahmen einer
Stored Procedure problemlos erreichen kann. Damit lassen sich auch richtig große Datenmengen
in vertretbaren Zeiten verarbeiten (Ich weiß von einem Kunden im Iran, der eine Firebird
DB mit 15TB betreibt, normal sind aber meistens Datenbankgrößen zwischen 100MB und 1TB).

Ich wollte nur noch mal deutlich machen, warum ich im Tutorial den vermeindlichen Umweg über
die SP gegangen bin, wenn man alles mit einer Zeile Code erledigen könnte, dann hätten die
meisten von uns keinen Job.

So sähe der gleich Befehl in Firebird und einigen anderen DBs übrigens als SQL ohne SP aus

Code:

merge into product p
using import_product ip
on p.id = ip.id
when matched then
update set CATEGORY_ID = IP.CATEGORY_ID,
           TITLE = IP.TITLE,
           ACTOR = IP.ACTOR,
           PRICE = IP.PRICE,
           SPECIAL = IP.SPECIAL
when not matched then
        insert (ID, CATEGORY_ID, TITLE, ACTOR, PRICE, SPECIAL)
        values (IP.ID, IP.CATEGORY_ID, IP.TITLE, IP.ACTOR, IP.PRICE, IP.SPECIAL)

[FredlFesl]

IBExpert, toll das es mit Firebird geht (wäre ja auch noch schöner).
Der Fragesteller hat aber MSSQL, soweit ich mich erinnere.

[IBExpert]

ja, das ist mir auch klar, aber hier und da findet man in der eigenen Plattform durchaus vergleichbare Techniken.

Und ganz ehrlich, wenn ein Import von 100000 Sätzen 22 Minuten dauert kann man auch mal über die eigene Plattform
nachdenken, wahlweise in Bezug auf die eigenen Kenntnisse oder eben die Technischen Möglichkeiten der jeweiligen
Software.

Das MSSQL das unbestritten schneller können sollte ist ja eh klar, aber Foren wie die Delphipraxis zeichnen
sich ja lobenswerterweise nicht durch uneingeschränkte Microsoftgläubigkeit aus.

[FredlFesl]

Zitat von IBExpert:

Das MSSQL das unbestritten schneller können sollte ist ja eh klar, aber Foren wie die Delphipraxis zeichnen
sich ja lobenswerterweise nicht durch uneingeschränkte Microsoftgläubigkeit aus.

Behauptet keiner, und Niemand hat bisher von Microsoftgläubigkeit geredet. Ich dachte nur, man sollte beim Thema und damit der Plattform bleiben, das ist alles.

[Piro]

Schönen guten Abend,

das ist ja mal ne Menge an Antworten.

Ich bin an MSSQL gebunden und möchte auch nicht wechseln. Ist halt strategisch vorgegeben.

Ich werde es mal mit dem Bulk Insert oder dem CSV Import versuchen.

@FredlFesl: Wie muss ich denn die einzelnen Anweisungen setzen?
Den Import kann ich mit der TADOCommand ausführen. Danach ist mir aber nicht klar, wie es weiter geht. Wenn ich jetzt erst eine Abfrage machen soll, wo der Datensatz vorhanden ist und anhand des Ergebnisses ein INSERT oder UPDATE durchführe, habe ich doch wieder Geschwindigkeitseinbußen. Deshalb habe ich halt, die Stored Procedure UPSERT verwendet.

Ich versuche mal den Bulk INSERT

[Bummi]

die Methode von FredlFesl dürfte für Dich die schnellste und einfachste sein.
Der unterschied ist daß Du nach dem Bulk insert, welcher schnell geht mit Mengen arbeitest und nicht mehr mit einzelnen Sätzen, mit den passenden Indizes würde ich darauf tippen dass Dein Import/Update so in ein paar Sekunden rein läuft.

EDIT:

Du muss die Struktur für #TEMP bereitstellen, also per Create Table oder per

Code:

Select x,y,z
into #temp
From VorlageOderZiel
Where 1=0

zudem muss sich die Importdatei auf dem Server befinden, oder vom Server aus zugreifbar sein und Du könntest Probleme mit Umlauten bekommen....

[Piro]

So, ich habe es jetzt mit dem BULK INSERT gemacht.

Da ich die Daten jeden Tag neu brauche. Mache ich erst ein Trunc auf die Table und dann ein BULK INSERT.

Ich habe eben ca. 1Mio. Datensätze importiert. Es hat knapp 1Minute gedauert.

Vielen Dank an alle.