Schnelle Befüllung von MS-SQL-Tabellen?
Thomas G. Liesner
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
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.
> 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
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
> 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?
Michael Broede
Am 27.04.2011 02:06, schrieb Thomas G. Liesner:
> ... 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
Joe Galinke
Thomas G. Liesner schrieb:
>> 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
> 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>
Nicht verwendbar, siehe unten.
> 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=...)
Das sieht schon deutlich brauchbarer aus, wobei man dann weitere Infos
über die Umgebung braucht, um einen passenden Netzwerkpfad verwenden zu
können...
> 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.
> 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...
Thomas G. Liesner
> 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.
> 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.
Joe Galinke
Thomas G. Liesner schrieb:
>> 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.
> 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
--
Michael Broede
nur mal eben zwischendurch:
Am 27.04.2011 18:16, schrieb 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
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 - 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
> 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...
> 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...
Thomas G. Liesner
> 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.
Thomas G. Liesner
Ach ja, den darf ich nicht voraussetzen, einige Kunden fahren noch
frühere Versionen...
Michael Broede
Am 28.04.2011 15:45, schrieb Thomas G. Liesner:
> ... 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