There is a different ways to load the data from Flat File to SQL Server. whenever we have more options, we would like to know which performs better. Following may help you take a decision to choose a way.
Among may options, I am taking following ways to do analysis.
1. SSIS Bulk Load Task
2. OLE DB Fast Load
3. SQL Bulk Insert command
I used following environment settings with each technique for this experiment.
1.Input Flat file size : 2.3 GB
2. Total number of rows : 35470898
3. Number of Columns : 12
3. Destination : SQL Server 2014 database
4. Flat file and database are in same machine.
Time taken by each data load is listed in following table.
This data clearly shows that there is no significant difference between OLE DB fast load and Bulk load.
Among may options, I am taking following ways to do analysis.
1. SSIS Bulk Load Task
2. OLE DB Fast Load
3. SQL Bulk Insert command
I used following environment settings with each technique for this experiment.
1.Input Flat file size : 2.3 GB
2. Total number of rows : 35470898
3. Number of Columns : 12
3. Destination : SQL Server 2014 database
4. Flat file and database are in same machine.
Time taken by each data load is listed in following table.
|
Data Load Type
|
Time taken to load(minute)
|
|
SSIS Bulk Load Task
|
7.51
|
|
Fast Load in OLE DB
|
8.42
|
|
SQL Server Bulk Insert Command
|
10.41
|
This data clearly shows that there is no significant difference between OLE DB fast load and Bulk load.
How do you make this visual? I am trying to do similar analysis for one of my process, can you help me how to implement it?
ReplyDelete