SqlBulkCopy测试

àì夳堔傛蜴生んèń 2022-08-22 06:25 60阅读 0赞

先上码：

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

![SouthEast][]

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

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

![SouthEast 3][]

另附相关阅读：

[http://stackoverflow.com/questions/4535536/timeout-expired-with-sqlbulkcopy][http_stackoverflow.com_questions_4535536_timeout-expired-with-sqlbulkcopy]

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

[SouthEast]: /images/20220722/38f79184c7df4b159410b5679ec3c604.png
[SouthEast 1]: /images/20220722/9319cb23b77c4053935ea29014557e1f.png
[SouthEast 2]: /images/20220722/a5936a27194e4d8d894da2f2c276f25c.png
[https_msdn.microsoft.com_zh-cn_library_system.data.sqlclient.sqlbulkcopy.batchsize.aspx]: https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy.batchsize.aspx
[SouthEast 3]: /images/20220722/034801f258c84a768609bcbde61eb7cc.png
[http_stackoverflow.com_questions_4535536_timeout-expired-with-sqlbulkcopy]: http://stackoverflow.com/questions/4535536/timeout-expired-with-sqlbulkcopy
[http_stackoverflow.com_questions_779690_what-is-the-recommended-batch-size-for-sqlbulkcopy]: http://stackoverflow.com/questions/779690/what-is-the-recommended-batch-size-for-sqlbulkcopy