I was a little skeptical at first about how effective the fast load would be compared to running a stored procedure. I thought it was doing the same exact thing and the only difference was what program you initiated it from. I was very wrong. A stored procedure that I was using to transfer 29,731,499 rows took around 45 minutes. After putting this stored procedure call into a data flow task with fast load, the transfer only took 30 minutes. Just placing the stored procedure call into a data flow task with fast load increased the speed of the transfer about 30%. Great results with little effort. SSIS takes care of all the data pulling and inserting for you while also optimizing the process. It manages the transaction logs automatically and does the loading in batches as opposed to all at once, which makes the load slower.
Here is a screenshot of what the dropdown looks like:
To improve the efficiency of the fast load even more, you could set the options for “Rows per batch” and “Maximum insert commit size”. Choosing what numbers to input for these boxes depends on a couple of factors. For my example, I used 50000 for “Rows per batch” and 100000 for “Maximum insert commit size”. My data set has about 75 columns and 50 million rows. The system we have handles it easily and with good speed. The more memory and CPU you have on the system you’re using, the higher the number you can set for these options. By loading data in batches instead of loading it in one large lump, the system will more efficiently use its CPU/memory to do its task. Setting these options could take some trial and error, but it can increase the speed of a load greatly.
The four checkboxes above the "Rows per batch" are there for you to set according to how your table is configured. You will want "Keep identity" checked if you want to keep the identity values from the imported data file. Otherwise, it will use unique values assigned by SQL Server. If "Keep nulls" is checked, it will retain null values during the bulk load operation. Checking "Table lock" will acquire table-level lock during the load. The "Check constraints" option will validate the data against the destination table's constraints. Unchecking this could speed up the load since it won't have to do the extra work of verifying.
Other benefits of using the fast load and data flow task as a whole is that you can do multiple pulls at the same time. This lets you break up the data you’re loading into several pieces (such as specific months) so you can split the load time even more. For one of my loads, this dropped the time from 90 minutes to 30 minutes. Also, the tables are not locked up (if you uncheck “Table lock”), so they can be accessed throughout the loading process. Another very important benefit of using this method of loading is the transactional log management. The transaction logs are handled for you with efficiency and minimal effort if “Rows per batch” and “Maximum insert commit size” are set correctly. The loads are quicker because the tempdb and transaction logs won’t be done in one batch and one transaction. This helps performance with the system memory and disk storage. Using fast load along with configuring “Rows per batch” and “Maximum insert commit size” is a very easy way to speed up your data loads with minimal effort.