Blog Post

SSIS 6 Million Rows in 30 Seconds using standard laptop


First of all I would like to thank Koen Verbeeck and Joost van Rossum for great suggestions that helped to get 30s on my laptop MSDN Forum Thread

Recently I posted SSIS 6 million rows in 60 seconds using standard laptop. Today I'm still using laptop and one physical disk but my 6 million rows load (heap table) is 30 seconds just by making several minor changes (and one bigger).

Below is the list and video that summarizes it:
1) Enterprise Database Engine is faster than BI Database Engine on my laptop (I really don't know why).
Time: 52.8s
Rows Per Seconds: 113k

2) Commit Size = 0
Time: 40.6s
Rows Per Second: 147k

3) Packet size = 32767
Time: 38.2s
Rows Per Second: 155k

4) Compression = Row
Time: 34.2s
Rows Per Second: 175k

5) Balance Data Distributor = 2 outputs (more than 2 is slower)
Time: 29.3s
Rows Per Second: 204k

Some of you might be thinking it is fast because it is not first run and it cached data? Well after complete machine restart with best config it took 32.1 seconds.

You can subscribe to our YouTube Channel using the button below
Subscribe to me on YouTube

To see more detailed videos see our SSIS Performance Tuning Heap Load Playlist

Take care
Katie and Emil