SSIS: Auslesen von SQL-Tabellen beschleunigen
avatar

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:

image

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:

image

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:

image

   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

Schreibe einen Kommentar