Flat File Bulk Import methods speed comparison in SQL Server 2005

Mon Jul 17, 2006 by Mladen Prajdić in sql-server

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:

1.	SSIS - FastParse ON	=	7322 ms
2.	SSIS - FastParse OFF	=	8387 ms
3.	Bulk Insert	=	10534 ms
4.	OpenRowset	=	10687 ms
5.	BCP	=	14922 ms

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.

Scripts, code and details here.

Edit 2006-07-22: Some updated tests here.

Legacy Comments


Jon 2006-07-17	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Neat I was just wondering if you considered network latency, network bandwidth, and amount of traffic when performing these tests? Or did you just unplug the network ?

Mladen 2006-07-17	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Tests were performed on a computer that isn't in a network. So no i didn't take that into consideration :))

rockmoose 2006-07-19	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Does specifying TABLOCK for BULK INSERT make any difference? I am devastated my favorite import method is beaten by SSIS :-\|

Mladen 2006-07-19	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Hmm... i wouldn't think that it would since it's a table that only one process is accessing. But i'll let you know during the weekend when i'm again at the machine i performed these tests on.

Adam Machanic 2006-07-19	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Did you perchance note CPU, memory, and other resource utilization differences? I'm actually working on an import process right now that has to run on a VERY bogged-down server, so every little bit counts. I wrote it using BCP but given these figures I'll probably switch it over to BULK INSERT... I'm concerned that SSIS will use up a lot more RAM/CPU. Are my concerns at all valid?

Jon 2006-07-19	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Adam's test would be a lot more tricker to test. Resource and memory allocation could vary based on the chosen method. I think Mladen was merely testing on a standard PC that was not running any more than the sql server agent in the bkgrd?

Adam Machanic 2006-07-19	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Could probably get a general idea just looking at the server's processor time %, memory utilization, etc..?

Mladen 2006-07-19	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 Well i tested it on my home PC and i did notice that CPU was a bit higher on BCP and SSIS. but not by much... As i said to rockmoose i'll be at my testing PC back on the weekend so i'll do this on saturday and post some figures. Can't do it before, sorry... But as always you can use my script and run it on your own PC and see exactly what suits you best. If you do that i'd appreciate your results.

Nabin Padhi 2007-04-08	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 what about the OpenXML option for inserting Bulk records below is the sp code. CREATE PROC sp_InsertLineItems @LineItemXML ntext AS DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc OUTPUT, @LineItemXML INSERT INTO LineItem SELECT * FROM OPENXML(@hDoc, '/LineItemRoot/LineItem',1) WITH (LineItemName varchar(32),AsAllowedAmount money,AdjustedAmount money) EXEC sp_xml_removedocument @hDoc select * from LineItem GO

Chris Eastland 2008-04-05	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 I'm migrating temporarily from the Oracle world. I'm using SQL Server 2005 and have to move a bunch of tables wholesale from one db to another on a different network (i.e. not visible in windows network of source machine). I've been using DTS and BCP and have found issues with both. What I am ideally looking for is something that will take one table of whatever structure in terms of column types and copy the entire contents to a replace empty table on the target machine without any need for hand crafted or generated format files. Would also be nice if it would compare row by row afterwards.

Mladen 2008-04-05	re: Flat File Bulk Import methods speed comparison in SQL Server 2005 if you can create linked server between the 2 servers you can simply use select * into server2.db2.schema2.table2 from server1.db1.schema1.table1 another way to do this is to simply script the create table DDL and generate insert statements for the rows on server1 and execute both on the server2 there are many tools for generating insert statements from data on the net. you can also use the add-in for SSMS 2005 http://www.ssmstoolspack.com/ that i've created that can script the entire database in the form of insert statements. hope any of that helps :)