http://www.sqlservercentral.com/blogs/katie-and-emil/2013/01/28/ssis-6-million-rows-in-60-seconds-using-standard-laptop/

Printed 2014/10/23 08:38AM

SSIS 6 Million Rows in 60 Seconds using standard laptop

By KatieAndEmil, 2013/01/28

SSIS is a very powerful tool and can be very fast however I frequently come across situations where performance could be improved by making several simple changes or minor hardware upgrades.

There are many factors that need to be taken into consideration and it would be nice to do a simple health test that would show instantly if there is a problem. I generally work on several projects every year so I thought about building a SSIS Performance Framework that will help me identify problems quickly when I take new contract (below v0.001) but also help me to justify upgrades.

I thought the best way to do that is to generate data and load it using the same package on different environments and additionally compare it to typical laptop that uses one physical disk; in other words if I tell the client your server is slower than my laptop than I have quite good chances to persuade the client to look into that or upgrade the server.

Below is video that shows simple load of 6 millions (almost 1GB) in 60 seconds on a laptop which gives us 100k rows per second.



p.s. I got 45 seconds after reading data from pen drive that I bought for 10£ (USB 2.0) so it was 133k rows per second

If you would like to generate the data than you can use DBGEN tool and below is video with instructions.



You can subscribe to our YouTube Channel (150 videos so far with almost 100 videos for SSIS 2012) using the button below

Subscribe to me on YouTube

Take care
Katie and Emil

Comments

To add comments please use this link

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.