SQL Server import faster than bulk import

bulkinsert sqlbulkcopy sql-server

Question

I have a large (~50Gb, ~300 mln rows) tab separated file, which I want to import into a SQL Server table with columns: char(10), varchar(512), nvarchar(512), nvarchar(512).

It takes about a day to bulk import it using T-SQL, SSIS or C# SqlBulkCopy class.

Is there any faster way to load this data?

Or could there be some condition slowing it down, which I can remove or change?

1
1
10/16/2012 8:51:22 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

If you are inserting to an existing table, drop all indexes prior to import and re-create them after the import.

If you are using SSIS, you can tweak the batch and commit sizes.

Verify there is adequate memory on the server for such a large data load.

Perform the loading operation on the local server (copy file locally, don't load over the network).

Configure your destination database and transaction log auto-growth options to a reasonable value, such as a few hundred MB chunks at a time (default is typically growth by 1MB for the master data file .mdf). Growth operations are slow/expensive so you want to minimize these.

Make sure your data and log files are on fast disks, preferably on separate LUNs. Ideally you want your log file on a mirrored separate LUN from your log file (you may need to talk to your storage admin or hosting provider for options).

2
10/16/2012 7:39:06 PM

Popular Answer

I have just spent the last few weeks fighting with the optimizing a very large load myself. BULK INSERT is the fastest way, I found with BCP, as opposed to SSIS or TSQL Bulk Insert, but there are things you can do to tune this.

  • Try raising/lowering the rows per batch setting, to move resource pressure between CPU and memory (higher will reduce CPU, lower will decrease memory).
  • If there is a Clustered Index, or non-clustered indexes drop them and re-create them after the insert
  • Split the TSV into smaller files using a .NET tool and then parallel load them into the table. This requires the table is a Heap (Clustered index dropped)
  • Make sure it is minimally logged. For a Heap, this requires TABLOCK, for a Clustered Index it requires Trace flag 610 and requires the data robe ordered the same as the clustered index key. Either will require either SIMPLE or BULK LOGGED recovery models
Pete Carter

Related Questions

With the SqlBulkCopy class, you may import data from SQLite to SQL Server.

I'm trying to transfer the data from SQLite to SQL Server. The schema of target and destination table are just the same:...SQL Server:...CREATE TABLE [dbo].[Shop] ( [ShopID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [Url] [nvarchar](128) NOT NULL, PRIMARY KEY CLUSTERED ( [ShopID] ASC )) ...and SQLite:...CREATE ...
sqlbulkcopy sqlite
asked by Jeffrey Zhao

SqlBulkCopy import causes primary key violations in SQL Server

I am trying to design a window based application in C#.NET. I am reading csv file in data grid view and then insert those data into database. I am using ...SqlBulkCopy... to insert data from csv file into database. My concern is, when I am trying to insert data into database (which already consist data) I am getting error of primary key constraint....
c# sqlbulkcopy sql-server
asked by Rushabh Shah

MS Access bulk import and insert into SQL Server

First of all read data from Excel sheet...connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/temp/") + "FileName.xlsx; Extended Properties=Excel 12.0;"; DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); DbDataAdapter adapter = factory.CreateDataAdapter(); DbCommand...
asp.net c# ms-access sqlbulkcopy sql-server
asked by Muhammad Akhtar

import text file(500 MB) data into SQL Server

If the file will be processed entirely you can process it backwards and truncate it after each record has been processed, that way you can always process the entire file and free the disk space of the rows as you process them. And you can "safely" read the entire file when resuming from an error....first you'd have to declare a FileStream to keep t...
asp.net c# sqlbulkcopy sql-server
asked by Rony Batista

Best method for importing data from text file into MS SQL Server

In need of your opinions. Currently developing an application in VB.NET. ...I have a text file which contains more than one thousand rows. Each rows contains the data needed to be inserted into the database. A sample of a row is as follows:...0001--------SCOMNET--------0.100---0.105 ...At first glance, one might figured that each column was separat...
performance sqlbulkcopy sql-server vb.net
asked by Khairul

Importing user-friendly Excel sheet into a SQL Server table that has many foreign keys

I have an Excel sheet that is populated by HR employee with thousands of client records it looks like this one:...User Friendly Excel Sheet Example Screenshot...My client's SQL Server table schema looks like this...CREATE TABLE [dbo].[Clients] ( [ID] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (100) NOT NULL, [Photo...
c# excel sqlbulkcopy sqldataadapter sql-server
asked by Ahmed TalǮat

Fast and simple way to import csv to SQL Server

We are importing a csv file with ...CSVReader... then using ...SqlBulkCopy... to insert that data into SQL Server. This code works for us and is very simple, but wondering if there is a faster method (some of our files have 100000 rows) that would also not get too complex?... SqlConnection conn = new SqlConnection(connectionString); ...
.net c# sqlbulkcopy sql-server
asked by Austin Harris

C# - Importing a CSV where empty strings do not get sent through bulk import to database

Build up individual update statement... You can chain updates ... update [test].[dbo].[Table_1] set value1 = 'newOne' where iden = 1; update [test].[dbo].[Table_1] set value1 = 'newTwo' where iden = 2; ...But that would be a open to SQL injection so you should use parameters... I am not sure if you can chain multiple parameter based updates in ...
.net c# csv mysql sqlbulkcopy
asked by paparazzo

SQL Server - can't import value 0 or 1 to bit column

It's because Boolean values get replaced by bit values on insert, to make our lives easier to fix this you just need to create a parsing method for converting bit to bool, simple enough. Should then work
c# csv sqlbulkcopy sql-server sql-server-2014
asked by TechnicalTophat

Error Import Database mdb into Sql Server

First try setting your command object to nothing. ...cmdTemp = Nothing; ...If that doesn't fix it, then try investigating why your update query for query 3 is timing out. I would first convert your UPDATE statement into a SELECT statement. Verify that you are getting results, and that you aren't taxing the server with your query....SELECT a.Stato, ...
c# sqlbulkcopy sql-server
asked by HKImpact

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow