Quickest way of imported 100,000's of records

knappster

Active member
Joined
Nov 13, 2006
Messages
42
Programming Experience
Beginner
Hi,

I have created a web program that will import data into a SQL database. The data to be imported is just one field, but there will be hundreds of thousands to be imported at a time. At the moment I create a data table from the data in the file and then use the SqlBulkCopy command to insert them into the database. I was just wondering if this was the most efficient way of doing this and also if there was anyway of informing the user after every 10,000 records imported the total imported so far?

Cheers.
 

MattP

Well-known member
Joined
Feb 29, 2008
Messages
1,206
Location
WY, USA
Programming Experience
5-10
Set the NotifyAfter Property: SqlBulkCopy.NotifyAfter Property (System.Data.SqlClient)

Once you've set the NotifyAfter property it will fire the SqlRowsCopied event after that amount.

Here's an article talking about the times to upload 1 milliion records to a SQL database: .NET - Fastest Way to Load Text File To SQL - SqlBulkCopy - stevienova.com

Method 1: Insert Line By Line
Method 2: Batch Insert With DataAdapter
Method 3: SqlBulkCopy

Method 1- 14.69 minutes to insert 1 million records
Method 2 - 7.88 minutes to insert 1 million records
Method 3 - 0.28 minutes to insert 1 million records
 

knappster

Active member
Joined
Nov 13, 2006
Messages
42
Programming Experience
Beginner
Cheers, looks like the sqlbulkcopy is the fastest way then... About the SqlRowsCopied event, the app I am using is a web app, I take it that the SqlRowsCopied event is for windows forms and not web apps?
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
I wouldn't do that; I'd accept the file for upload via HTTP, but send them an email when it's complete. You could put a 10 second refresh on your page to query the status of the upload, but I'd probably farm it off to somewhere else rather than having the browser sit there either looking crashed or re-querying all the time..
 
Top Bottom