SqlBulkCopy测试

先上码：

public static void WriteSQL()
{
    //http://www.codeweblog.com/sqlbulkcopy-%E7%9A%84-timeout-%E5%92%8C-batchsize/
    const int times = 20;
    const int precount = 100000;
    const string constr = "Data Source=.,1433;Initial Catalog=SuperSite;Integrated Security=True";

    DataRow dr;
    var dt = new DataTable();
    var tbarr = new List<DataTable>();
    var locker = new Object();

    dt.Columns.Add("name", typeof(string));
    dt.Columns.Add("age", typeof(int));
    dt.Columns.Add("address", typeof(string));
    dt.Columns.Add("remarks", typeof(string));

    for (int j = 0; j < times; j++)
    {
        dt.Rows.Clear();

        for (int i = precount * (j) + 1; i <= precount * (j + 1); i++)
        {
            dr = dt.NewRow();
            dr["name"] = "张三" + i;
            dr["age"] = i;
            dr["address"] = "杭州" + i;
            dr["remarks"] = "备注" + i;
            dt.Rows.Add(dr);
        }

        tbarr.Add(dt);
    }

    using (var con = new SqlConnection(constr))
    {
        con.Open();

        try
        {
            using (var bulkCopy = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkCopy.BatchSize = precount;
                bulkCopy.DestinationTableName = "TB_Test";
                bulkCopy.ColumnMappings.Add("name", "name");
                bulkCopy.ColumnMappings.Add("age", "age");
                bulkCopy.ColumnMappings.Add("address", "address");
                bulkCopy.ColumnMappings.Add("remarks", "remarks");

                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();

                Parallel.ForEach(tbarr, x =>
                {
                    lock (locker) { bulkCopy.WriteToServer(x); }
                });

                stopwatch.Stop();
                Console.WriteLine("{0}条数据插入，耗时:{1}", precount * times, stopwatch.Elapsed);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("错误：{0}", ex.Message);
        }
        finally
        {
            dr = null;
            dt = null;
            tbarr = null;

            con.Close();
        }
    }
}

感觉BatchSize的大小不是能随便设置的，经过测试，与总插入量有关。20w条数据，而且还是单挑数据量比较下的情况下几十次测试下来，发现最好的情况下也只能接近9。

摘录一段MSDN上的说明：https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy.batchsize.aspx

另附相关阅读：

http://stackoverflow.com/questions/4535536/timeout-expired-with-sqlbulkcopy

http://stackoverflow.com/questions/779690/what-is-the-recommended-batch-size-for-sqlbulkcopy