Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Katie and Emil

We are Katie & Emil and together we have decided to learn more & more on how to build data warehouses using Microsoft products. The best option to learn is to teach so we write tutorials and share it with others.

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

Comments

Leave a comment on the original post

Loading comments...