Das Problem
In einem SSIS-Paket musste ich eine Tabelle für Raumdaten auslesen, welche sehr viele (> 1 Million) Datensätze enthielt.
Das Auslesen der Tabelle dauerte sehr lange und sollte beschleunigt werden.
Die Lösung
Nach einiger Recherche stieß ich dann auf den Link unter [1].
Das Prinzip zur Beschleunigung der Abfrage basiert auf der Parallelisierung der Abfrage.
Als möglicher Grund für die Beschleunigung könnte die Block-Größe der IO-Pakete eine Rolle spielen, welche standardmäßig auf 4kb festgelegt ist und auf maximal 32kb erweitert werden kann.
Um das Problem nachzuvollziehen sollte zunächst eine entsprechende Tabelle angelegt und mit Testdaten gefüllt werden.
Das kann z.B. so aussehen:
CREATE TABLE ROOMS ( ID int not null IDENTITY, Col1 varchar(100) null, Col2 varchar(100) null, Col3 varchar(100) null, Col4 varchar(100) null, Col5 varchar(100) null, Col6 varchar(100) null, Col7 varchar(100) null, Col8 varchar(100) null, Col9 varchar(100) null, Col10 varchar(100) null, Col11 varchar(100) null, Col12 varchar(100) null, Col13 varchar(100) null, Col14 varchar(100) null, Col15 varchar(100) null, Col16 varchar(100) null, Col17 varchar(100) null, Col18 varchar(100) null, Col19 varchar(100) null, Col20 varchar(100) null, Col21 varchar(100) null, Col22 varchar(100) null, Col23 varchar(100) null, Co24 varchar(100) null, Col25 varchar(100) null, Col26 varchar(100) null, Col27 varchar(100) null, Col28 varchar(100) null, Col29 varchar(100) null, Col30 varchar(100) null )
Für die Befüllung mit Testdaten kann z.B. folgende View erstellt werden:
CREATE VIEW randomDS AS ( SELECT char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col1, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col2, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col3, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col4, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col5, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col6, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col7, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col8, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col9, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col10, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000) as col11, char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+char(abs(checksum(newid()))/10000000)+ char(abs(checksum(newid(
die dann über eine Schleife z.B. 1 Million mal ausgeführt wird:
DECLARE @counter INT SET @counter = 0 WHILE @counter < 1000000 BEGIN SET @counter = @counter + 1 INSERT INTO ROOMS SELECT * FROM randomDS END
Nun heißt es erstmal abwarten und einen Kaffee trinken gehen ;- )
Als nächstes muss im SSIS-Paket ein Datenfluss-Task erstellt werden, der die Tabelle ausliest und sie weiterverarbeitet. Da die Weiterverarbeitung für diesen Beitrag nicht relevant ist, besteht sie einfach aus einem simplen Sortieren der Daten.
Das Auslesen sei zunächst einmal mit einer einfachen OLE-Data-Source gezeigt:
Ein Durchlauf dauerte auf dem Entwicklungsrechner des Kunden dann durchschnittlich ca. 4 Minuten.
Nun erfolgt der zweite Versuch in dem die Abfrage auf mehrere Abfragen aufgeteilt wird.
Dies sieht dann so aus:
Die OLD-DB-Sources Modulo1, 2 und 3 bestehen dabei aus einem SELECT-Statement, welches immer nur jeweils den 1., 2. oder 3. Datensatz zurückliefert.
Zur Synchronisation der Einzel-Prozesse muss an das SELECT-Statement dann noch ein “OPTION (MAXDOP1)” angehängt werden.
Eine Abfrage sieht dann so aus:
1: SELECT *
2: FROM ROOMS
3: WHERE ID % 3 = 0
4: OPTION (MAXDOP 1)
(der Wert “0” in Zeile 3 muss dann in den anderen beiden Tasks durch 1 bzw. 2 ersetzt werden)
Wenn das Paket dann ausgeführt wird, sieht man, dass jedes Statement nur jede 1., 2. oder 3. Zeile zurückliefert, die dann in der nachfolgenden “UNION”-Komponente wieder zu einer Datenmenge zusammengeführt werden.
Fazit
Auf meinem Test-System erhöhte sich die Verarbeitungsgeschwindigkeit dann signifikant und das Auslesen der Daten brauchte nur noch ca. 14 Sekunden.
Man sollte im Einzelfall abwägen, ob sich der (relativ geringe) Mehraufwand lohnt, in meinem Fall hat es sich aber durchaus gelohnt.
Links
[1] http://henkvandervalk.com/reading-as-fast-as-possible-from-a-table-with-ssis-part-ii