I want some Moore

Flat File Bulk Import methods speed comparison in SQL Server 2005

In a previous post I explained where an SSIS FastParse option can be found
when importing data from Flat files.
I finally had some time to explore all four of SQL Server 2005 Bulk import utilities.
Those are:

1. BCP
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS

I ran each bulk import option 12 times, disregarded best and worst time and averaged the remaining ten times.
Results are:

So speed gain is quite large when using FastParse.
I was also surprised that SSIS - FastParse OFF method was faster by 20% to Bulk Insert and OpenRowset
and around 40% faster than BCP.

Since my desire was to test how much faster is importing flat files when FastParse option is used
I created a text file containing 4 bigint columns with 1,000,000 rows.

The script i used to create a sample test file in C#:

string str;
StreamWriter sw = new StreamWriter(@"d:\work\test.txt");
for (int i = 1; i <= 1000000; i++)
{
    str = i.ToString() + "|" + Convert.ToString(i * 2) + "|" + Convert.ToString(i * 3) + "|" + Convert.ToString(i / 2);
    sw.WriteLine(str);
}
sw.Close();

9.0
4
1       SQLBIGINT        0       8       "|"   1     c1       ""
2       SQLBIGINT        0       8       "|"   2     c2       ""
3       SQLBIGINT        0       8       "|"   3     c3       ""
4       SQLBIGINT        0       8       "\r\n"   4     c4       ""

SSIS Package was a very simple one with a Flat File source and SQL server destination objects.

The sql script i used is:

create database test
go
use test
go
-- ran for each SSIS test run
-- SSIS data type for each column was "eight-byte signed integer [DT_I8]"
drop table testFastParse
create table testFastParse(c1 bigint, c2 bigint, c3 bigint, c4 bigint)
go
-- insert data using OPENROWSET 
create table testOpenRowset(c1 bigint, c2 bigint, c3 bigint, c4 bigint)
go
DBCC DROPCLEANBUFFERS
declare @start datetime
set @start = getdate()
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);
select  getdate() - @start as ElapsedTime
drop table testOpenRowset
-- insert data using Bulk Insert
create table testBulkInsert(c1 bigint, c2 bigint, c3 bigint, c4 bigint)
go
DBCC DROPCLEANBUFFERS
declare @start datetime
set @start = getdate()
BULK INSERT testBulkInsert
   FROM 'd:\work\test.txt'
   WITH (FORMATFILE='d:\work\testImport-f-n.Fmt')
select  getdate() - @start as ElapsedTime
drop table testBulkInsert
go
-- insert data using BCP
create table testBCP(c1 bigint, c2 bigint, c3 bigint, c4 bigint)
go
DBCC DROPCLEANBUFFERS
exec master..xp_cmdshell 'bcp test.dbo.testBCP in d:\work\test.txt -T -b1000000 -fd:\work\testImport-f-n.Fmt'
drop table testBCP
go
drop database test

Print | posted on Monday, July 17, 2006 5:28 PM |