A quick comparison between loading a SQL Server Ole DB destination with “Table and View” vs the “Table and View Fast Load”

I’ve set up a simple data flow that loads data from a csv and pushes it into SQL Server.




If you open up the Ole DB Destination Edition the default option is to use “Table or view” when your selecting where to load the data to.



If we run the package and go to Progress we can see the logs (Note it’s taken 45.848 seconds to run)



Now let’s open the destination again and change it to Table View Fast Load



And re run the package the progress results are:


Down from 45 seconds to sub 1 second.. That’s quiet the speed up, so whats happening? Well if you run profiler against the Destination SQL server we can re-run the packages and take a look.

Table or View – Profiler Results



Looks like we have a cursor on the go.. (bad news for performance in most cases!)

And Table or View – Fast Load results:



A bulk insert..


The real question is to why not use Fast load. I can’t think of many, The Fast Loads will cause a table lock because of doing a BULK Load, so if you’re running this during the day you may find that it’s not suitable, but it’s definitely worth testing!.