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.

 

ssis_bulkLoad1

 

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.

ssis_bulkLoad2

 

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

ssis_bulkLoad3

 

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

ssis_bulkLoad4

 

And re run the package the progress results are:

ssis_bulkLoad5

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

ssis_bulkLoad6

 

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

And Table or View – Fast Load results:

ssis_bulkLoad7

 

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!.

 

 

Advertisements