I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 216, comments - 2277, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book
My Blog Feed via Email
Follow MladenPrajdic on Twitter
Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff
<< Copy Source As Html (CSAH) | Home | More on Flat File Bulk Import methods speed comparison in SQL Server 2005 >>

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:

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.

Print | posted on Monday, July 17, 2006 5:32 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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 ?
7/17/2006 8:56 PM | Jon
Gravatar

# 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 :))
7/17/2006 10:01 PM | Mladen
Gravatar

# 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 :-|
7/19/2006 4:36 PM | rockmoose
Gravatar

# 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.
7/19/2006 4:47 PM | Mladen
Gravatar

# 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?
7/19/2006 7:58 PM | Adam Machanic
Gravatar

# 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?
7/19/2006 8:48 PM | Jon
Gravatar

# 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..?

7/19/2006 9:56 PM | Adam Machanic
Gravatar

# 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.
7/19/2006 10:29 PM | Mladen
Gravatar

# 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
4/8/2007 8:09 PM | Nabin Padhi
Gravatar

# 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.
4/5/2008 5:38 PM | Chris Eastland
Gravatar

# 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 :)
4/5/2008 5:46 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Mladen Prajdić