CODEWITHSUNDEEP
sqlsql-server-2008-r2
Mohammad
Mohammad

Reputation: 589

Insert data from C# into SQL Server 2008 R2

I have nearly 7 billion rows of data in memory (list<T> and sortedlist<T,T>) in C#. I want to insert this data into tables in SQL Server. To do this, I define different SqlConnection for each collection and set connection pool to False.

First, I tried to insert data with connected mode (ExecuteNonQuery). Even I defined Parallel.Invoke and called all insert methods for different collections concurrently, it is too slow and up to now I couldn't finish it (I couldn't discriminate any differences between sequential and concurrent insert).

Also, I tried to create an object from SqlDataTable. To fill tables I read all data from collections once and add data to SqlDataTable. In this case I set SqlBatchSize=10000 and SqlTimeOut=0 for SqlBulkCopy. But this one also is very slow.

How can I insert a huge amount of data into SQL Server fast?

Upvotes: 2

Views: 520

Answers (2)

usr
usr

Reputation: 171236

You already discovered SqlBulkCopy but you say it is slow. This can be because of two reasons:

  1. You are using too small batches. Try to stream the rows in using a custom IDataReader that you pass to WriteToServer (or just use bigger DataTables)
  2. Your table has nonclustered indexes. Disable them pre-import and regenerate them

You can't go faster than with bulk-import, though.

Upvotes: 1

alzaimar
alzaimar

Reputation: 4622

Look for 'BULK INSERT'. The technique is available for various RDBMS. Basically, you create a (text)file with one line per record and tell the server to consume this text file. This is the fastest approach I could think of. I import 50 million rows in a couple of seconds that way.

Upvotes: 1

Related Questions