大量插入的问题

本文关键字：问题 插入 | 更新日期: 2023-09-27 18:08:09

我有一组对象(存储在一个链表)，需要插入到SQL Server 2005表

这个解决方案非常慢。我有大约10K条记录要插入。每隔一段时间，我暂停一下，只看到一些执行完成。

有谁能帮助改善这一点吗?

using (SqlConnection 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);
    }
}

生成的SQL使用BULK INSERT命令和SQL Server 2005及更新版本中的功能。

注:Records必须为DataRow、DataTable类型，或者使用IDataReader

您可以对代码进行的唯一改进是将buildInsertionString方法构建的插入语句参数化，以便插入语句可以编译一次，并在所有后续调用链表中的每个记录时重用。例如:

String insert = "insert into table (field) values (@value)";
cmd.Parameters.AddWithValue("@value",element);

一种简单的方法是将用户定义的表类型传递给存储过程。如果您需要记录用于复制的事务，并且您有标识列或不提供表中的所有列，那么这比BulkInsert更好。以上面的代码为例，它看起来像这样:

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

c#端:

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;
}

您可以通过GO命令将SQL语句构建成单个字符串，并一次性发送它们。这应该快得多。

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
相关文章：