CODEWITHSUNDEEP
sql-server-2008ssissql-server-2008-r2ssmsbulkinsert
alextc
alextc

Reputation: 3515

BULK INSERT or Import and Export Data Wizard?

I have a large weekly CSV file (ranging from 500MB to 1GB with over 2.5 million rows) to load into an SQL Server 2008 R2 database.

I was able to use either BULK INSERT command or the Import and Export Data Wizard to load the data in. There was no observed difference in loading time span between them as far as my dataset is concerned.

What is your recommended approach as far as performance. efficiency and future maintenance is concerned?

Thanks in advance!

Cheers ,Alex

I ended up with using the SQL Server Import and Export Data Wizard and saving it to an SSIS package. Then I used Business Intelligence Development Studio to edit the saved package and re-imported it back to SQL Server. It works well and only takes 2 mins to load all 9 CSV files ranging from 10MB to 600 MB to the SQL Server database.

Upvotes: 3

Views: 5195

Answers (2)

Vikramsinh Shinde
Vikramsinh Shinde

Reputation: 2878

MSDN Forum:

When a SSIS developer opted for using the "Fast Load" option along with the "Table lock" on the OLEDB target, or used the SQL Server Destination, then he/she has effectively used the very BULK INSERT, so this is a moot point to debate what is faster.

Bulk insert on its own has tricks, in SQL Server contest more can be done to make it faster a row process, namely making it minimally or not logging at all. Now disabling constraints is another thing the bcp takes care of, not SSIS (unless instructed), and this what MSFT can decide to change in SSIS, but where the SSIS shines is in using an algorithm figuring out what are the best parameters for a given machine/system to use (e.g. the buffer size, etc).

So in most applications the SSIS is faster right away and even more faster with proper tweaking.

In real life many factors bring different impacts to the benchmarking, but at this stage I am inclined to state there is no real measurable difference.

Microsoft has published very informative guide about Comparing the different load strategies for achieving high performance and Choosing Between Bulk Load Methods - The Data Loading Performance Guide

Also have a look of following article as well.

Upvotes: 5

Mike Honey
Mike Honey

Reputation: 15017

I would save the SSIS package from the Import and Export Data Wizard and tweak the OLE DB Destination settings using Visual Studio (aka BIDS aka SSDT BI) - setting a Exclusive Table Lock and a large Batch Size and Commit Size e.g. 100000 rows. Typically this will boost performance by around 20%.

SSIS is the best option for future tuning e.g. filtering or transforming data, disabling and rebuilding indexes before & after your load.

Upvotes: 1

Related Questions