1

I have a program that is reading through a text file to update/insert rows. I've tried the following pseudocode methods below. These are all extremely slow. I'm running this code directly on the SQL Server itself and these processes all take many hours to complete...

The amount of update/insert statements is somewhere in the millions. What is the most efficient way to run that many SQL statements in .net c#?

// Insert/Update while reading text file
While (reader.read)
{
   sql.ExecuteNonQuery();
}

Or...

// build a list to loop through later and insert/update
While (reader.read)
{
   List.Add(sql);
}

foreach(string s in sql)
{
   sql.ExecuteNonQuery();
}

Or... 

// Build a list and run 1000 statements at one time
While (reader.read)
{
   List.Add(sql);
   if(List.Count == 1000)
   {
      sql.ExecuteNonQuery();
   }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shaolinmonk
  • 31
  • 4
  • If Bulk Inserts are not an option then you can create Temp Tables and do all your bulk Inserting via XML it's pretty simple, fast and efficient as well – MethodMan Dec 02 '14 at 15:44

1 Answers1

5

Use SqlBulkCopy Class.

Lets you efficiently bulk load a SQL Server table with data from another source.

Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SqlBulkCopy class lets you write managed code solutions that provide similar functionality.

There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them. The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

Habib
  • 219,104
  • 29
  • 407
  • 436
  • @shaolinmonk, you can't use it for updates, as far as I know, You have to insert the data in a Temp table and then update. See: http://stackoverflow.com/questions/4889123/any-way-to-sqlbulkcopy-insert-or-update-if-exists and http://stackoverflow.com/questions/12521692/c-sharp-bulk-insert-sqlbulkcopy-update-if-exists/12535726#12535726 – Habib Dec 02 '14 at 16:06