Well i've done some more testing of bulk import methods i described in my previous post. The difference between previsous tests and these tests are these:
1. Database recovery model was set to Bulk logged. (This improved performance by 2-3 seconds for each method)
2. I tried BULK INSERT and BCP with and without TABLOCK option.
3. I also measured general processor utilization and RAM. IO reads and writes were similar in all methods.
everything else was the same.
| Method |
Time (seconds) |
CPU utilization (%) |
RAM change (MB) |
| Bulk insert with Tablock |
4648 |
75 |
+5MB |
| SSIS FastParse |
5812 |
75 |
+30Mb |
| BCP with Tablock |
6300 |
100 |
+5MB |
| Bulk insert |
7812 |
75 |
+5MB |
| OpenRowset |
8750 |
95 |
+5MB |
| BCP |
11250 |
100 |
+5MB |
SSIS also has a bulk insert tablock option set to true in the SQL Server destination. So my guess is that approx 1 second overhead comes from package startup time. However SSIS uses more memory than Bulk insert.
So if you can use TABLOCK option Bulk insert is the way to go. If not SSIS is.
It must be taken into account that the the test flat file i used had only 4 integer columns and you could set the FastParse option to all of them. In real world there are no such ideal conditions. And since i had only integer type i don't have to specify collations.
Modified scripts are:
BULK INSERT testBulkInsert
FROM 'd:\work\test.txt'
WITH (
FORMATFILE='d:\work\testImport-f-n.Fmt',
TABLOCK
)
insert into testOpenRowset(c1, c2, c3, c4)
SELECT t1.c1, t1.c2, t1.c3, t1.c4
FROM OPENROWSET
(
BULK 'd:\work\test.txt',
FORMATFILE = 'd:\work\testImport-f-n.Fmt'
) AS t1(c1, c2, c3, c4);
exec master..xp_cmdshell 'bcp test.dbo.testBCP in d:\work\test.txt -T -b1000000 -fd:\work\testImport-f-n.Fmt -h"tablock"'
