Schnelle Befüllung von MS-SQL-Tabellen?

[Thomas G. Liesner]

Guten Tag!

Ich arbeite in jüngerer Zeit häufiger an Konvertierungen, deren
Ergebnisse jeweils in eine MS-SQL-Server-Datenbank kommen. Bei dem
jüngsten Projekt ist mir aufgrund der Datengröße die Asymetrie zwischen
Laden und Speichern besonders aufgefallen - das Laden der 500.000
Datensätze aus Paradox-Tabellen und Füllen in ein komplexeres internes
Modell dauert nur ein paar Minuten, das Schreiben in die Zieldatenbank
dagegen ca. 50 Minuten.

Momentan verwende ich intern ein TAdoDataset mit CommandText "Select *
from Tabellenname" und Append/FieldbyName(...)/Post zum Schreiben der
Datensätze. Bei kleinen Datenmengen ist das flott, bei größeren geht der
Speicherverbrauch temporär ganz gut hoch und es scheint gegen Ende immer
langsamer zu füllen. CommitTrans machte ich vorher nach jeder gefüllten
Tabelle, ein Umstellen auf pro Datensatz änderte das Zeitverhalten nicht
erkennbar.

Welche schnelleren Alternativen gibt es? Drauflos experimentieren will
ich deshalb nicht, weil der entsprechende Umbau relativ aufwendig wäre
und ich zumindest wissen möchte, ob sich der Aufwand lohnt.

Eine (recht aufwendig zu implementierende) Alternative wäre auf jeden
Fall wohl das Schreiben mit einem Insert-Statement pro Datensatz, würde
das erkennbar was bringen oder sogar langsamer sein können?

Ein wesentliches Kriterium der Alternativlösung muss dabei sein, dass
ich die ID eines neuen Datensatzes direkt erfahren kann, wie bislang
durch simples Einlesen des ID-Feldes nach dem POST...

So long,
Thomas G. Liesner

[Joe Galinke]

Hallo Thomas,

Thomas G. Liesner schrieb:

[... zu langsames Schreiben großer Datenmengen in MySQL ...]

Eine wirkliche Lösung kann ich leider nicht anbieten, aber vielleicht
Anstöße.

Der erste ist die Verwendung von Bulk-Inserts, also einem INSERT-Statement
mit vielen Datensätzen, mehreren VALUES-Listen.

Leider kollidiert das mit Deinem Kriterium bzgl. der Kenntnis über die ID
des Datensatzes.

Post by Thomas G. Liesner
Ein wesentliches Kriterium der Alternativlösung muss dabei sein, dass
ich die ID eines neuen Datensatzes direkt erfahren kann, wie bislang
durch simples Einlesen des ID-Feldes nach dem POST...

Demnach verwendest Du AUTO_INCREMENT, MySQL kennt keine Generatoren für
Sequenzen?

Handelt es sich um eine überschaubare Anzahl von Zieltabellen _und_ ist das
Einfügen von Datensätzen von anderen Prozessen während Deines
Einlesevorgangs ausgeschlossen?

Wenn ja, könnte zu Beginn die aktuell höchste ID mit LAST_INSERT_ID
ausgelesen werden und von dort an wird der Wert selbst verwaltet.
Zumindest die InnoDB-Engine soll es unterstützen, dass man den Wert des
Feldes selbst festlegt. So dieser 0 oder NULL ist, wird er von der Engine
nicht mit einem neuen Wert versehen.

Welche MySQL-Version kommt überhaupt zum Einsatz? Wurde InnoDB nicht
irgendwann sogar zur Standardengine?

Auf jeden Fall sollte es sich einfach testen lassen, wie das System mit
programmseitig gesetzten AUTO_INCREMENT-Spalten umgeht und ob bei späteren
"normalen" INSERTs wirklich der nächste neue Wert auf dem aktuellen und
nicht auf dem alten ID-Wert basiert.

Gruß, Joe
--

[Joe Galinke]

Hallo,

Joe Galinke hatte wohl noch nicht genügend Kaffee:


Ja, MS-SQL <> MySQL.

Sorry. Mann, ist das peinlich.


Aber MS-SQL kann Bulk-Insert, AFAIK. Generatoren stehen auch zur Verfügung.
Also holt man sich genügend IDs im Vorfeld und präpariert seinen
Bulk-Insert entsprechend.

Nun halt ich mal die Klappe.


Gruß, Joe
--

[Thomas G. Liesner]

Post by Joe Galinke
Aber MS-SQL kann Bulk-Insert, AFAIK. Generatoren stehen auch zur Verfügung.
Also holt man sich genügend IDs im Vorfeld und präpariert seinen
Bulk-Insert entsprechend.

Wie sieht das praktisch aus?

So long,
Thomas G. Liesner

[Joe Galinke]

Hallo Thomas,

Post by Thomas G. Liesner

Post by Joe Galinke
Aber MS-SQL kann Bulk-Insert, AFAIK. Generatoren stehen auch zur Verfügung.
Also holt man sich genügend IDs im Vorfeld und präpariert seinen
Bulk-Insert entsprechend.

Wie sieht das praktisch aus?

Vorab nochmal zu Erinnerung:

| Eine wirkliche Lösung kann ich leider nicht anbieten, aber vielleicht
| Anstöße.
:-)

BULK-Insert wie MS SQL es versteht ist wohl nur über Textdateien möglich,
die Du halt erst füllen müsstest.

Ich hatte etwas wie

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

im Sinn. Das sollte zumindest merkbar schneller sein als einzelne INSERTs.

Aber entgegen meiner Vermutung unterstützt MS SQL diese Variante wohl
nicht, stattdessen aber

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

Es bleibt das Problem mit Deinen ID-Werten. Wie werden die denn bisher
gefüllt? Per Trigger und Generator?

Egal ob Du nun eine Textdatei oder ein das multiple INSERT verwendest, so
musst Du die Zeilen/Anweisungen im Vorfeld aufbauen. Unter der Annahme,
dass Du eine Generator verwendest, holst Du für jede Zeile einen neuen Wert
vom Generator und verwendest diesen als ID, bzw. holst den Wert daher,
woher er auch beim normalen Posten kommen würde.

Wenn Du vorher bereits die Anzahl der neuen Datensätze kennst, so kannst Du
entsprechend viele IDs vorher abfragen und in einer geeigneten Liste
speichern. Das wäre vielleicht schneller.

Kannst Du sicher sein, dass zwischendurch nicht von "irgendwoher anders"
ein Datensatz eingefügt wird, dann kannst Du auch den gerade aktuellen
ID-Wert (Generatorwert) holen, je nach Bedarf im Code hochzählen und den
Wert als ID verwenden. Anschließend darfst Du natürlich nicht vergessen,
den Generatorwert auf den Wert Deiner höchsten ID+1 zu setzen.

Ich habe jetzt einige Vermutungen bzgl. der Art der ID-Generierung
angestellt, die natürlich vollkommen falsch sein mögen. Kläre uns auf,
vielleicht hat dann jemand anderes eine bessere Idee.


Gruß, Joe
--

[Thomas G. Liesner]

Post by Joe Galinke
Aber entgegen meiner Vermutung unterstützt MS SQL diese Variante wohl
nicht, stattdessen aber
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

Wirkt extrem umständlich und unperformant, aber Bauchgefühl ist kein
verlässlicher Massstab.

Post by Joe Galinke
Es bleibt das Problem mit Deinen ID-Werten. Wie werden die denn bisher
gefüllt? Per Trigger und Generator?

Siehe anderes Posting - simple Identity-Felder. Und daran kann ich auch
nichts ändern, Vorabfragen von IDs o.ä. dürfte somit als Lösung
ausfallen.

So long,
Thomas G. Liesner

[Joe Galinke]

Hallo Thomas,

Post by Thomas G. Liesner

Post by Joe Galinke
Aber entgegen meiner Vermutung unterstützt MS SQL diese Variante wohl
nicht, stattdessen aber
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

Wirkt extrem umständlich und unperformant, aber Bauchgefühl ist kein
verlässlicher Massstab.

Ich gehe davon aus, dass es zumindest deutlich performanter ist als Deine
aktuelle Lösung. Außerdem soll ja der Code das Konstrukt aufbauen. :-)

Nach Deiner ausführlicheren Beschreibung in Deiner Antwort an Michael ist
das Gesamtsystem schon deutlich komplexer als erwartet und somit eine
Versorgung Deiner INSERT-Statements mit selbst verwalteten ID-Werten
zumindest merklich komplizierter.

Post by Thomas G. Liesner
Siehe anderes Posting - simple Identity-Felder. Und daran kann ich auch
nichts ändern, Vorabfragen von IDs o.ä. dürfte somit als Lösung
ausfallen.

Ich fürchte, dann sieht es damit sehr schlecht aus.

Unter weiteren Vorbedingungen ginge es vielleicht doch.

Wenn für den Zeitraum des Imports andere Einfügeoperation auszuschließen
sind, bliebe die Alternative der Abfrage des aktuell letzten
Identity-Wertes, des Hochzählens und Einfügens in Deine Statements.

Man kann zumindest den Wert der Identity-Spalte setzen, wenn vorher
IDENTITY_INSERT eingeschaltet wurde.

SET IDENTITY_INSERT <TABLE> ON
INSERT INTO dbo.<TABLE> (ID, Name) Values(1, 'A name')
SET IDENTITY_INSERT <TABLE> OFF

Nun muss man wahrscheinlich einen neuen Startwert für die Spalte setzen.

Das tödliche Gegenargument wird wohl sein, dass man IDENTITY_INSERT nur
jeweils für eine Tabelle pro Session setzen kann. In Deinem Szenario dürfte
das, wenn überhaupt, nur schwer realisierbar sein.

Aber ich wollte die Möglichkeit immerhin erwähnen.

Solche Möglichkeiten wie AUTOINC und IDENTITY sind zwar süß, nett und
bequem. Aber wenn sie einen beißen, dann gleich feste. Ich mag sie nicht.
SCNR

Gruß, Joe
--

[Thomas G. Liesner]

Post by Joe Galinke
Man kann zumindest den Wert der Identity-Spalte setzen, wenn vorher
IDENTITY_INSERT eingeschaltet wurde.
SET IDENTITY_INSERT <TABLE> ON
INSERT INTO dbo.<TABLE> (ID, Name) Values(1, 'A name')
SET IDENTITY_INSERT <TABLE> OFF
Nun muss man wahrscheinlich einen neuen Startwert für die Spalte setzen.
Das tödliche Gegenargument wird wohl sein, dass man IDENTITY_INSERT nur
jeweils für eine Tabelle pro Session setzen kann. In Deinem Szenario dürfte
das, wenn überhaupt, nur schwer realisierbar sein.

Session = Transaktion? Oder was ist gemeint? Eine neue Transaktion pro
Tabelle ist kein Problem und es wird immer nur eine Tabelle gleichzeitig
weggeschrieben...

Post by Joe Galinke
Solche Möglichkeiten wie AUTOINC und IDENTITY sind zwar süß, nett und
bequem. Aber wenn sie einen beißen, dann gleich feste. Ich mag sie nicht.
SCNR

Kommt auf's Szenario an...

So long,
Thomas G. Liesner

[Michael Broede]

Hi,

Post by Joe Galinke
...
Ich hatte etwas wie
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');
im Sinn. Das sollte zumindest merkbar schneller sein als einzelne INSERTs.
Aber entgegen meiner Vermutung unterstützt MS SQL diese Variante wohl
nicht, stattdessen aber
...

Doch, das geht ab SQL-Server 2008.

Viele Grüße

Michael

[Thomas G. Liesner]

Post by Michael Broede

Post by Joe Galinke
...
Ich hatte etwas wie
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');
im Sinn. Das sollte zumindest merkbar schneller sein als einzelne INSERTs.
Aber entgegen meiner Vermutung unterstützt MS SQL diese Variante wohl
nicht, stattdessen aber
...

Doch, das geht ab SQL-Server 2008.

Ach ja, den darf ich nicht voraussetzen, einige Kunden fahren noch
frühere Versionen...

So long,
Thomas G. Liesner

[Michael Broede]

Hi,

... Bei dem
jüngsten Projekt ist mir aufgrund der Datengröße die Asymetrie zwischen
Laden und Speichern besonders aufgefallen - das Laden der 500.000
Datensätze aus Paradox-Tabellen und Füllen in ein komplexeres internes
Modell dauert nur ein paar Minuten, das Schreiben in die Zieldatenbank
dagegen ca. 50 Minuten.
Momentan verwende ich intern ein TAdoDataset mit CommandText "Select *
from Tabellenname" und Append/FieldbyName(...)/Post zum Schreiben der
Datensätze. Bei kleinen Datenmengen ist das flott, bei größeren geht der
Speicherverbrauch temporär ganz gut hoch und es scheint gegen Ende immer
langsamer zu füllen. CommitTrans machte ich vorher nach jeder gefüllten
Tabelle, ein Umstellen auf pro Datensatz änderte das Zeitverhalten nicht
erkennbar.
Welche schnelleren Alternativen gibt es?
...

Deine Anfrage ist bzgl. des Datenflusses bzw. -handlings leicht unklar.

Natürlich gibt es schnelle Verfahren um Daten in eine
MSSQL-Servertabelle zu laden. Ein Bespiel:

INSERT INTO <EineTabelle>
(
<Feldliste>
)
SELECT
<Feldliste>
FROM <EineAndereTabelle>

Mit sowas können Millionen von Zeilen in ein paar Sekunden eingefügt
werden. Ähnlich schnell geht es auch mit einem Bulk-Insert aus einem
Flat-File, wie Joe oben schon andeutete:

BULK INSERT <EineTabelle>
FROM '\\...\...\<textfile>'
WITH (DATAFILETYPE='char',ROWTERMINATOR=...)

Man kann auf dem SQL-Server auch den Import-Manager bemühen und ein
DTS-Paket schnüren, in dem Datenquelle, -ziel, Tabellen- bzw.
Fieldmapping usw. festgelegt sind, das als SQL-Server-Auftrag speichern
und dann aus eine beliebigen Anwendung heraus via SQL-Command ausführen.

Es gibt weitere Möglichkeiten. Die Frage ist u.a., nützt Dir das was,
wenn Du von jedem eingefügten Datensatz die ID brauchst, um damit weiter
zu arbeiten. Dann müßtest Du nämlich eigentlich einzeln pro Datensatz
vorgehen, und da gilt pauschal: Das dauert! Aber eigentlich kann ich mir
das nicht vorstellen, denn die IDs könnte man sich auch später besorgen.
Eine weitere Frage wäre, hast Du viele einzelne kleinere Tabellen mit
insgesamt 500.000 DS oder nur eine große Tabelle? Davon abhängig gibts
völlig verschiedene Vorgehensweisen fürs Tuning.

Viele Grüße

Michael

[Thomas G. Liesner]

Post by Michael Broede
Deine Anfrage ist bzgl. des Datenflusses bzw. -handlings leicht unklar.
Natürlich gibt es schnelle Verfahren um Daten in eine
INSERT INTO <EineTabelle>
(
<Feldliste>
)
SELECT
<Feldliste>
FROM <EineAndereTabelle>

Nicht verwendbar, siehe unten.

Post by Michael Broede
Mit sowas können Millionen von Zeilen in ein paar Sekunden eingefügt
werden. Ähnlich schnell geht es auch mit einem Bulk-Insert aus einem
BULK INSERT <EineTabelle>
FROM '\\...\...\<textfile>'
WITH (DATAFILETYPE='char',ROWTERMINATOR=...)

Das sieht schon deutlich brauchbarer aus, wobei man dann weitere Infos
über die Umgebung braucht, um einen passenden Netzwerkpfad verwenden zu
können...

Post by Michael Broede
Man kann auf dem SQL-Server auch den Import-Manager bemühen und ein
DTS-Paket schnüren, in dem Datenquelle, -ziel, Tabellen- bzw.
Fieldmapping usw. festgelegt sind, das als SQL-Server-Auftrag speichern
und dann aus eine beliebigen Anwendung heraus via SQL-Command ausführen.

Nicht verwendbar, siehe unten.

Post by Michael Broede
Es gibt weitere Möglichkeiten. Die Frage ist u.a., nützt Dir das was,
wenn Du von jedem eingefügten Datensatz die ID brauchst, um damit weiter
zu arbeiten. Dann müßtest Du nämlich eigentlich einzeln pro Datensatz
vorgehen, und da gilt pauschal: Das dauert! Aber eigentlich kann ich mir
das nicht vorstellen, denn die IDs könnte man sich auch später besorgen.
Eine weitere Frage wäre, hast Du viele einzelne kleinere Tabellen mit
insgesamt 500.000 DS oder nur eine große Tabelle? Davon abhängig gibts
völlig verschiedene Vorgehensweisen fürs Tuning.

Ich skizziere meine aktuelle Situation:
- Die Zieldatenbank ist strukturell nicht änderbar und gehört zur
Branchenlösung meines Kunden. Die Importe sorgen für die Übernahme von
Datenbeständen verschiedenster Quellen in diese Branchenlösung.
- Somit dient der jeweilige Spezialimport als Datenquelle, die
Datensätze liegen nur temporär im Arbeitsspeicher vor, eine direkte
Übernahme der Originalquelle mittels Mapping ist nie möglich, dazu
muss zuviel konvertiert, interpretiert und sonstwie umgearbeitet
werden.
- Die Zieldatenbank wird Tabelle für Tabelle in der Reihenfolge der
internen Abhängigkeiten gefüllt, wobei es zahlreiche Fremdschlüssel
gibt, für die ich die erzeugten IDs unbedingt brauche.

Die Tabellen sind bzgl. ID-Feld alle nach folgendem Schema aufgebaut:

CREATE TABLE AUSWAHLKRITERIEN (
ID int IDENTITY(1,1) NOT NULL,
...
CONSTRAINT PK__AUSWAHLKRITERIEN__49C3F6B7
PRIMARY KEY CLUSTERED (ID ASC) WITH (...) ON PRIMARY
)

Tabellenanzahl? Je nach Quellenlage zwischen 20 und 60 zu füllende
Tabellen, pro Tabelle zwischen 1 und 200.000 Datensätzen...
Wobei der bisherige Rekord irgendwas über 700.000 Datensätze in allen
Zieltabellen war plus der Abbildungstabelle, wo für jeden Quelldatensatz
verzeichnet wird, in welche Zieldatensätze er abgebildet wird iSv:

Laufende ID, Quelltabelle, QuellID(s), Zieltabelle, Ziel-ID

Und bei solchen größeren Geschichten wäre eine Beschleunigung ganz
angenehm...

So long,
Thomas G. Liesner

[Michael Broede]

Hi,

jetzt ist noch die Frage, ob das ein synchron laufender Job aus einer
Anwendung heraus werden soll - also per Knopfdruck gestartet, mit
Progress-Balken usw., der Benutzer sitzt vorm Bildschirm und wartet auf
das Ende - oder ob das asynchron laufen kann. Im letzten Fall würde ich
eine Stored Procedure (SP) für den SQL-Server schreiben, die die Daten
in die Tabellen einfügt. Das setzt natürlich voraus, dass der SQL-Server
auf die Quelldaten zugreifen kann. Diese müssen also in irgendeiner Form
in den SQL-Server rein. Hierzu gibt es verschiedene Möglichkeiten:
Flat-Textfiles, access.mdb, Oledb/Odbc-Quelle, anderer SQL-Server, gerne
auch die Express-Version usw.. Microsoft nennt das SSIS = SQL-Server
Integration Services. Dazu kann man auch so ein DTS-Paket benutzen, wie
ich schon schrieb. (DTS hieß es früher, heute eben SSIS-Paket). Ich
würde auch keinen Aufwand dafür betreiben, die Quelldaten vorher noch
irgendwie inhaltlich aufzubereiten. Hauptsache sie kommen als Rohdaten
fehlerfrei im SQL-Server an. Die ganze Verarbeitungslogik wie
Konvertieren, Herrichten der Schlüsselbeziehungen usw. würde ich dann
mittels SQL in der SP realisieren. Wie die SP dann aufgerufen wird, ist
Dir freigestellt. Ich lasse so was gerne automatisch über Nacht laufen.

Viele Grüße

Michael

[Thomas G. Liesner]

Post by Michael Broede
jetzt ist noch die Frage, ob das ein synchron laufender Job aus einer
Anwendung heraus werden soll - also per Knopfdruck gestartet, mit
Progress-Balken usw., der Benutzer sitzt vorm Bildschirm und wartet auf
das Ende -

Ja. Im Endeffekt läuft jeder Import nur einmal "richig", mir geht es
darum, ob ich die Turn-Around-Zeiten bei der Entwicklung kürzen kann.
Der aufwendigste Import bislang war nach einer Stunde durch,
Nachtaktionen bringen da nichts. Nebenbei bezweifele ich stark, dass die
Ausdrucksfähigkeit der SP mit Delphi mithalten kann. Die Änderungen sind
doch teilweise recht "tricky" und alleine das Verknüpfen der Daten
erfordert einen Gesamtüberblick über die bisherigen Daten durch den Code
incl. Schlüsseln, die in der Zieldatenbank gar nicht mehr vorkommen.

So long,
Thomas G. Liesner

[Michael Broede]

Hi,

... Nebenbei bezweifele ich stark, dass die
Ausdrucksfähigkeit der SP mit Delphi mithalten kann...

das bezweifle ich nun wieder ganz stark.
(Natürlich bezogen auf das gefragte Datenhandling.)

Viele Grüße

Michael