数据库批量插入速度实验

数据库插入速度，哪样最快，特做了一下实验，结果如下：

可见，不同方式速度相差明显，其中使用SqlBulkCopy最快，在我本机能达到5w条/秒。

使用SqlBulkCopy的代码如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace BigDataInsertToSqlserver
{
    /// <summary>
    /// 批量插入数据库
    /// 使用示例：
    /// SqlConnection conn = new SqlConnection(sqlconnstr);
    /// SqlBulkCopyHelper.BulkCopy(conn,usersToInsert.ToList(), 50000, "t_users",300000);
    /// </summary>
    public class SqlBulkCopyHelper
    {
        /// <summary>
        /// 使用 SqlBulkCopy 向 destinationTableName 表插入数据
        /// </summary>
        /// <typeparam name="TModel">必须拥有与目标表所有字段对应属性</typeparam>
        /// <param name="conn"></param>
        /// <param name="modelList">要插入的数据</param>
        /// <param name="batchSize">SqlBulkCopy.BatchSize</param>
        /// <param name="destinationTableName">如果为 null，则使用 TModel 名称作为 destinationTableName</param>
        /// <param name="bulkCopyTimeout">SqlBulkCopy.BulkCopyTimeout</param>
        /// <param name="externalTransaction">要使用的事务</param>
        public static void BulkCopy<TModel>(SqlConnection conn, List<TModel> modelList, int batchSize, string destinationTableName = null, int? bulkCopyTimeout = null, SqlTransaction externalTransaction = null)
        {
            bool shouldCloseConnection = false;

            if (string.IsNullOrEmpty(destinationTableName))
                destinationTableName = typeof(TModel).Name;

            DataTable dtToWrite = ToSqlBulkCopyDataTable(modelList, conn, destinationTableName);

            SqlBulkCopy sbc = null;

            try
            {
                if (externalTransaction != null)
                    sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, externalTransaction);
                else
                    sbc = new SqlBulkCopy(conn);

                using (sbc)
                {
                    sbc.BatchSize = batchSize;
                    sbc.DestinationTableName = destinationTableName;

                    if (bulkCopyTimeout != null)
                        sbc.BulkCopyTimeout = bulkCopyTimeout.Value;

                    if (conn.State != ConnectionState.Open)
                    {
                        shouldCloseConnection = true;
                        conn.Open();
                    }

                    sbc.WriteToServer(dtToWrite);
                }
            }
            finally
            {
                if (shouldCloseConnection && conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        public static DataTable ToSqlBulkCopyDataTable<TModel>(List<TModel> modelList, SqlConnection conn, string tableName)
        {
            DataTable dt = new DataTable();

            Type modelType = typeof(TModel);

            List<SysColumn> columns = GetTableColumns(conn, tableName);
            List<PropertyInfo> mappingProps = new List<PropertyInfo>();

            var props = modelType.GetProperties();
            for (int i = 0; i < columns.Count; i++)
            {
                var column = columns[i];
                PropertyInfo mappingProp = props.Where(a => a.Name == column.Name).FirstOrDefault();
                if (mappingProp == null)
                    throw new Exception(string.Format("model 类型 '{0}'未定义与表 '{1}' 列名为 '{2}' 映射的属性", modelType.FullName, tableName, column.Name));

                mappingProps.Add(mappingProp);
                Type dataType = GetUnderlyingType(mappingProp.PropertyType);
                if (dataType.IsEnum)
                    dataType = typeof(int);
                dt.Columns.Add(new DataColumn(column.Name, dataType));
            }

            foreach (var model in modelList)
            {
                DataRow dr = dt.NewRow();
                for (int i = 0; i < mappingProps.Count; i++)
                {
                    PropertyInfo prop = mappingProps[i];
                    object value = prop.GetValue(model);

                    if (GetUnderlyingType(prop.PropertyType).IsEnum)
                    {
                        if (value != null)
                            value = (int)value;
                    }

                    dr[i] = value ?? DBNull.Value;
                }

                dt.Rows.Add(dr);
            }

            return dt;
        }
        static List<SysColumn> GetTableColumns(SqlConnection sourceConn, string tableName)
        {
            string sql = string.Format("select syscolumns.name,colorder from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);

            List<SysColumn> columns = new List<SysColumn>();
            using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())
            {
                conn.Open();
                SqlCommand com = conn.CreateCommand();
                com.CommandText = sql;
                using (var reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        SysColumn column = new SysColumn();
                        column.Name = reader.GetString(0);
                        column.ColOrder = reader.GetInt16(1);

                        columns.Add(column);
                    }
                }
                conn.Close();
            }

            return columns;
        }

        static Type GetUnderlyingType(Type type)
        {
            Type unType = Nullable.GetUnderlyingType(type); ;
            if (unType == null)
                unType = type;

            return unType;
        }

        class SysColumn
        {
            public string Name { get; set; }
            public short ColOrder { get; set; }
        }

    }
}

 例子中用到的表：

CREATE TABLE [dbo].[T_Users](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](100) NULL,
    [Gender] [int] NULL,
    [Age] [int] NULL,
    [CityId] [int] NULL,
    [OpTime] [datetime] NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

准备要插入的数据，先放到并发集合中ConcurrentBag

 string sqlconnstr = "Data Source=127.0.0.1;Initial Catalog=DBTest;User Id=sa;Password=1";
        ConcurrentBag<User> usersToInsert = new ConcurrentBag<User>();
        int totalCount = 0;

        private void InitData()
        {
            usersToInsert = new ConcurrentBag<User>();
            totalCount = int.Parse(txtTotalCount.Text) * 10000;
            for (int i = 0; i < totalCount; i++)
            {
                usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "user"+i, Gender = Gender.Man, Age = 18, CityId = 1, OpTime = DateTime.Now });
            }
        }

然后调用上方的SqlBulkCopyHelper类进行批量插入数据库

  private void btn2_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            sw.Restart();
            InitData();
            sw.Stop();
            Showlog(string.Format("使用方式三，初始化{0}条数据完成，总耗时{1}毫秒", totalCount, sw.ElapsedMilliseconds));
            sw.Start();
            using (SqlConnection conn = new SqlConnection(sqlconnstr))
            {
                SqlBulkCopyHelper.BulkCopy(conn,usersToInsert.ToList(), 50000, "t_users",300000);
            }
            sw.Stop();

            Showlog(string.Format("使用方式三，插入{0}条数据完成，总耗时{1}毫秒,速度为{2}条/秒", totalCount,sw.ElapsedMilliseconds, Math.Ceiling((double)totalCount*1000/sw.ElapsedMilliseconds)));
        }