[MSSQL] multiple inserts

RED-BARON

Hallo Gemeinde,

ich habe ein Problem Laut diesem Blog funktioniert alles wie gewünscht

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

jedoch scheint es eine "natürliche" Grenze zu geben, wie lang der String
sein darf den ich an den Server sende. Bei meinem Versuch ~2000 Datensätze
einzufügen. Ich verwende den OLEDB-Treiber. Es wird kein Fehler in dem Sinn
gemeldet sondern eben ein TimeOut.

Nutz von Euch jemand solche Multiplen Inserts ? Wenn ja, wie geht ihr damit um ?
Aufteilen in kleiner "Pakete" ? Wenn ja, wie viele Inserts maximal, bzw. wie
lang maximal der String an den Server ? Irgendwie hab ich dazu keine definitiven
Aussagen gefunden

Vielen Dank. RB

hustbaer

UNION ALL ist mit MSSQL leider scheisse, weil langsam.

Ein INSERT mit z.B. 5x UNION ALL ist zwar noch schneller als 5 einzelne INSERT , ein INSERT mit 1000x UNION ALL allerdings u.u. sogar langsamer als 1000 einzelne INSERT .
Und zwar weil der Query-Optimizer nicht versteht was da gemacht werden soll, und wie ein Irrer anfängt alle möglichen Varianten der Ausführung der einzelnen UNION ALL Statements durchzuprobieren, um die schnellste zu finden.

Die einzige mir bekannte Möglichkeit (über ADO-ohne-.Net) die schnell funktioniert ist der INSERT EXEC SELECT SELECT Trick:

CREATE TABLE #temp (a varchar(100), b int);

INSERT #temp (a, b)
	EXEC('
		SELECT ''foo'', 1;
		SELECT ''bar'', 2;
		SELECT ''bar'', 3;
		SELECT ''bar'', 4;
		SELECT ''bar'', 5;
		SELECT ''bar'', 6;
		SELECT ''bar'', 7;
		SELECT ''bar'', 8;
		SELECT ''bar'', 9;
		SELECT ''bar'', 10;
		SELECT ''bar'', 11;
		SELECT ''bar'', 12;
		SELECT ''bar'', 13;
	');

SELECT * FROM #temp;
DROP TABLE #temp;

Sieht bekloppt aus, aber funktioniert.

Damit kannst du locker 1000 Zeilen auf einmal einfügen, je mehr desto schneller wird es.
Aufteilen in "Pakete" zu je max. 1000 oder 10000 Zeilen solltest du es trotzdem -- irgendwann wird der Speicher knapp, dann wird's wieder langsamer als schneller.
Ich würde sagen guck zu dass der gesamte Batch < 1MB bleibt und alles ist in Butter.

Alternativ kann man sowas über eine advanced Sp machen , ein C dll . Schau mal auf der Sybase Seite nach zu dem Thema.

hustbaer

Achja, ab SQL Server 2008 geht es endlich so wie es gehen sollte:

CREATE TABLE #temp (a varchar(100), b int);

INSERT #temp (a, b)
    VALUES
    ('foo', 1),
    ('bar', 2),
    ('bar', 3),
    ('bar', 4),
    ('bar', 5),
    ('bar', 6),
    ('bar', 7),
    ('bar', 8),
    ('bar', 9),
    ('bar', 10),
    ('bar', 11),
    ('bar', 12),
    ('bar', 13);

SELECT * FROM #temp;
DROP TABLE #temp;

Vergesse ich immer, da unser grösster SQL Server immer noch den 2005er drauf hat...

RED-BARON

Danke Hustbear,

leider antworte ich erst jetzt, die Mai-Feiertage .... *g*

Ich probier das mal aus, wie UNION ALL arbeitet wusste ich noch garnicht^^

Danke für die Erklärung.

hustbaer

Was meinst du, "wie UNION ALL arbeitet"?

Der Grund ist nicht in irgendeiner wesentlichen Eigenschaft von UNION ALL zu finden. Es liegt daran dass der Query Optimizer versucht eine sehr komplexe Query zu optimieren, und dafür schweinemässig lange braucht.
Mit "komplex" meine ich dass sie aus vielen "Teilen" besteht, nämlich den einzelnen Statements die über UNION ALL zusammengehängt werden.