Problem with mass INSERTs

I have a set of objects (stored in a linkedlist) that need to be inserted into a SQL Server 2005 table.

This solution here is deadly slow. I have about 10K records to insert. Every once in a while I pause and only see a few more execution done.

Can anyone help to improve this?

using (SqlConnecti开发者_如何学Goon dbConnection = new SqlConnection(connectionString))
{
    dbConnection.Open();
    SqlTransaction dbTrans = dbConnection.BeginTransaction();       
    SqlCommand cmd = dbConnection.CreateCommand();  
    cmd.Transaction = dbTrans;    
    foreach (MyRecord myr in Records)
    {
        cmd.CommandText = buildInsertionString(MyRecord)
        cmd.ExecuteNonQuery();
    }                   
    dbTrans.Commit();
    dbConnection.Close();
}

public string buildinsertionString(Myrecod myr){
    string sqlCommandString = "insert into Table1 values";

    string values = "'" + myr.field1 + "',"
                            + myr.field2 + ","
                            + "'" + myr.field3 + "',"
                            + "'" + myr.field4 + "',"
                            + "'" + myr.field5 + "',"
                            + "'" + myr.field6 + "'";
    return sqlCommandString + "(" + values + ");

}

---

Use SQLBulkCopy (System.Data.SqlClient):

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
    {
        bulkCopy.DestinationTableName = "table";

        bulkCopy.WriteToServer(Records);
    }
}

The SQL generated uses the BULK INSERT command and functionality present in SQL Server 2005 and newer.

^{Note: Records must be of type DataRow, DataTable, or use IDataReader.}

---

The only improvement you can make to your code is to parametrize the insert statement built by the buildInsertionString method so that the insert statement can be compiled once and reused by all subsequent calls for every record in the linked list. For example:

String insert = "insert into table (field) values (@value)";

cmd.Parameters.AddWithValue("@value",element);

---

A simple approach is to make use of passing a user defined table type into a stored procedure. This is better than BulkInsert if you need the transactions logged for replication and if you have identity columns or are not supplying all the columns in the table. Using your above code as an example, it would look like this:

On Sql Server:

Create Type dbo.MyRecord As Table
(
   field1 Varchar(50), -- Or whatever your types and names are
   field2 Varchar(50),
   field3 Varchar(50),
   field4 Varchar(50),
   field5 Varchar(50),
   field6 Varchar(50)
)
Go
Create Procedure dbo.spInsertTable1
(
   @vals dbo.MyRecord ReadOnly
)
As
Begin
   Insert Into dbo.Table1(field1, field2, field3, field4, field5, field6)
   Select field1, field2, field3, field4, field5, field6
   From @vals
End
Go

On c# side:

using System.Linq;
....

using (SqlConnection dbConnection = new SqlConnection(connectionString))
{
   dbConnection.Open();
   SqlTransaction dbTrans = dbConnection.BeginTransaction();       
   using(SqlCommand cmd = new SqlCommand("dbo.spInsertTable1", dbConnection))
   {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Transaction = dbTrans;  
      var records = Records.Select(it =>
          new {
             field1 = it.field1,
             field2 = it.field2,
             field3 = it.field3,
             field4 = it.field4,
             field5 = it.field5,
             field6 = it.field6
          }).ToDataTable();
      var param  = cmd.Parameters.AddWithValue("@vals", records);
      param.TypeName = "dbo.MyRecord";
      cmd.ExecuteNonQuery();
   }

   dbTrans.Commit();
}

public DataTable ToDataTable<T>(IEnumerable<T> data)
{
    PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));

    if (props == null) throw new ArgumentNullException("Table properties.");
    if (data == null) throw new ArgumentNullException("data");

    DataTable table = new DataTable();
    for (int i = 0; i < props.Count; i++)
    {
       PropertyDescriptor prop = props[i];
      table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
      for (int i = 0; i < values.Length; i++)
      {
          values[i] = props[i].GetValue(item) ?? DBNull.Value;
      }

      table.Rows.Add(values);
    }

    return table;
}

---

You can build up the SQL Statements into a single string separate by the GO command and send them over in one shot. This should be a lot quicker.

StringBuilder sb = new StringBuilder();


foreach(Thing thing in Something)
{
    string query = BuildMyQuery(thing);
    sb.Append(query);
    sb.Append("GO");
}

string SQLText = sb.ToString();

// Execute SQL Command here using SQL Text