幾種資料庫的大資料批量插入【轉】

標籤：

在之前只知道SqlServer支援資料批量插入，殊不知道Oracle、SQLite和MySql也是支援的，不過Oracle需要使用Orace.DataAccess驅動，今天就貼出幾種資料庫的批量插入解決方案。

首先說一下，IProvider裡有一個用於實現批量插入的外掛程式服務介面IBatcherProvider，此介面在前一篇文章中已經提到過了。

/// <summary>    /// 提供資料批量處理的方法。    /// </summary>    public interface IBatcherProvider : IProviderService    {        /// <summary>        /// 將 <see cref="DataTable"/> 的資料批量插入到資料庫中。        /// </summary>        /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>        /// <param name="batchSize">每批次寫入的資料量。</param>        void Insert(DataTable dataTable, int batchSize = 10000);    }

一、SqlServer資料批量插入

SqlServer的批量插入很簡單，使用SqlBulkCopy就可以，以下是該類的實現：

/// <summary>    /// 為 System.Data.SqlClient 提供的用於大量操作的方法。    /// </summary>    public sealed class MsSqlBatcher : IBatcherProvider    {        /// <summary>        /// 擷取或設定提供者服務的上下文。        /// </summary>        public ServiceContext ServiceContext { get; set; }        /// <summary>        /// 將 <see cref="DataTable"/> 的資料批量插入到資料庫中。        /// </summary>        /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>        /// <param name="batchSize">每批次寫入的資料量。</param>        public void Insert(DataTable dataTable, int batchSize = 10000)        {            Checker.ArgumentNull(dataTable, "dataTable");            if (dataTable.Rows.Count == 0)            {                return;            }            using (var connection = (SqlConnection)ServiceContext.Database.CreateConnection())            {                try                {                    connection.TryOpen();                    //給表名加上前後導符                    var tableName = DbUtility.FormatByQuote(ServiceContext.Database.Provider.GetService<ISyntaxProvider>(), dataTable.TableName);                    using (var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null)                        {                            DestinationTableName = tableName,                             BatchSize = batchSize                        })                    {                        //迴圈所有列，為bulk添加映射                        dataTable.EachColumn(c => bulk.ColumnMappings.Add(c.ColumnName, c.ColumnName), c => !c.AutoIncrement);                        bulk.WriteToServer(dataTable);                        bulk.Close();                    }                }                catch (Exception exp)                {                    throw new BatcherException(exp);                }                finally                {                    connection.TryClose();                }            }        }    }

以上沒有使用事務，使用事務在效能上會有一定的影響，如果要使用事務，可以設定SqlBulkCopyOptions.UseInternalTransaction。

二、Oracle資料批量插入

System.Data.OracleClient不支援批量插入，因此只能使用Oracle.DataAccess組件來作為提供者。

/// <summary>    /// Oracle.Data.Access 組件提供的用於大量操作的方法。    /// </summary>    public sealed class OracleAccessBatcher : IBatcherProvider    {        /// <summary>        /// 擷取或設定提供者服務的上下文。        /// </summary>        public ServiceContext ServiceContext { get; set; }        /// <summary>        /// 將 <see cref="DataTable"/> 的資料批量插入到資料庫中。        /// </summary>        /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>        /// <param name="batchSize">每批次寫入的資料量。</param>        public void Insert(DataTable dataTable, int batchSize = 10000)        {            Checker.ArgumentNull(dataTable, "dataTable");            if (dataTable.Rows.Count == 0)            {                return;            }            using (var connection = ServiceContext.Database.CreateConnection())            {                try                {                    connection.TryOpen();                    using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand())                    {                        if (command == null)                        {                            throw new BatcherException(new ArgumentException("command"));                        }                        command.Connection = connection;                        command.CommandText = GenerateInserSql(ServiceContext.Database, command, dataTable);                        command.ExecuteNonQuery();                    }                }                catch (Exception exp)                {                    throw new BatcherException(exp);                }                finally                {                    connection.TryClose();                }            }        }        /// <summary>        /// 產生插入資料的sql語句。        /// </summary>        /// <param name="database"></param>        /// <param name="command"></param>        /// <param name="table"></param>        /// <returns></returns>        private string GenerateInserSql(IDatabase database, DbCommand command, DataTable table)        {            var names = new StringBuilder();            var values = new StringBuilder();            //將一個DataTable的資料轉換為數組的數組            var data = table.ToArray();            //設定ArrayBindCount屬性            command.GetType().GetProperty("ArrayBindCount").SetValue(command, table.Rows.Count, null);            var syntax = database.Provider.GetService<ISyntaxProvider>();            for (var i = 0; i < table.Columns.Count; i++)            {                var column = table.Columns[i];                var parameter = database.Provider.DbProviderFactory.CreateParameter();                if (parameter == null)                {                    continue;                }                parameter.ParameterName = column.ColumnName;                parameter.Direction = ParameterDirection.Input;                parameter.DbType = column.DataType.GetDbType();                parameter.Value = data[i];                if (names.Length > 0)                {                    names.Append(",");                    values.Append(",");                }                names.AppendFormat("{0}", DbUtility.FormatByQuote(syntax, column.ColumnName));                values.AppendFormat("{0}{1}", syntax.ParameterPrefix, column.ColumnName);                command.Parameters.Add(parameter);            }            return string.Format("INSERT INTO {0}({1}) VALUES ({2})", DbUtility.FormatByQuote(syntax, table.TableName), names, values);        }    }

以上最重要的一步，就是將DataTable轉為數組的數組表示，即object[][]，前數組的上標是列的個數，後數組是行的個數，因此迴圈Columns將後數組作為Parameter的值，也就是說，參數的值是一個數組。而insert語句與一般的插入語句沒有什麼不一樣。

三、SQLite資料批量插入

SQLite的批量插入只需開啟事務就可以了，這個具體的原理不得而知。

public sealed class SQLiteBatcher : IBatcherProvider    {        /// <summary>        /// 擷取或設定提供者服務的上下文。        /// </summary>        public ServiceContext ServiceContext { get; set; }        /// <summary>        /// 將 <see cref="DataTable"/> 的資料批量插入到資料庫中。        /// </summary>        /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>        /// <param name="batchSize">每批次寫入的資料量。</param>        public void Insert(DataTable dataTable, int batchSize = 10000)        {            Checker.ArgumentNull(dataTable, "dataTable");            if (dataTable.Rows.Count == 0)            {                return;            }            using (var connection = ServiceContext.Database.CreateConnection())            {                DbTransaction transcation = null;                try                {                    connection.TryOpen();                    transcation = connection.BeginTransaction();                    using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand())                    {                        if (command == null)                        {                            throw new BatcherException(new ArgumentException("command"));                        }                        command.Connection = connection;                        command.CommandText = GenerateInserSql(ServiceContext.Database, dataTable);                        if (command.CommandText == string.Empty)                        {                            return;                        }                        var flag = new AssertFlag();                        dataTable.EachRow(row =>                            {                                var first = flag.AssertTrue();                                ProcessCommandParameters(dataTable, command, row, first);                                command.ExecuteNonQuery();                            });                    }                    transcation.Commit();                }                catch (Exception exp)                {                    if (transcation != null)                    {                        transcation.Rollback();                    }                    throw new BatcherException(exp);                }                finally                {                    connection.TryClose();                }            }        }        private void ProcessCommandParameters(DataTable dataTable, DbCommand command, DataRow row, bool first)        {            for (var c = 0; c < dataTable.Columns.Count; c++)            {                DbParameter parameter;                //首次建立參數，是為了使用緩衝                if (first)                {                    parameter = ServiceContext.Database.Provider.DbProviderFactory.CreateParameter();                    parameter.ParameterName = dataTable.Columns[c].ColumnName;                    command.Parameters.Add(parameter);                }                else                {                    parameter = command.Parameters[c];                }                parameter.Value = row[c];            }        }        /// <summary>        /// 產生插入資料的sql語句。        /// </summary>        /// <param name="database"></param>        /// <param name="table"></param>        /// <returns></returns>        private string GenerateInserSql(IDatabase database, DataTable table)        {            var syntax = database.Provider.GetService<ISyntaxProvider>();            var names = new StringBuilder();            var values = new StringBuilder();            var flag = new AssertFlag();            table.EachColumn(column =>                {                    if (!flag.AssertTrue())                    {                        names.Append(",");                        values.Append(",");                    }                    names.Append(DbUtility.FormatByQuote(syntax, column.ColumnName));                    values.AppendFormat("{0}{1}", syntax.ParameterPrefix, column.ColumnName);                });            return string.Format("INSERT INTO {0}({1}) VALUES ({2})", DbUtility.FormatByQuote(syntax, table.TableName), names, values);        }    }

 四、MySql資料批量插入

 /// <summary>    /// 為 MySql.Data 組件提供的用於大量操作的方法。    /// </summary>    public sealed class MySqlBatcher : IBatcherProvider    {        /// <summary>        /// 擷取或設定提供者服務的上下文。        /// </summary>        public ServiceContext ServiceContext { get; set; }        /// <summary>        /// 將 <see cref="DataTable"/> 的資料批量插入到資料庫中。        /// </summary>        /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>        /// <param name="batchSize">每批次寫入的資料量。</param>        public void Insert(DataTable dataTable, int batchSize = 10000)        {            Checker.ArgumentNull(dataTable, "dataTable");            if (dataTable.Rows.Count == 0)            {                return;            }            using (var connection = ServiceContext.Database.CreateConnection())            {                try                {                    connection.TryOpen();                    using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand())                    {                        if (command == null)                        {                            throw new BatcherException(new ArgumentException("command"));                        }                        command.Connection = connection;                        command.CommandText = GenerateInserSql(ServiceContext.Database, command, dataTable);                        if (command.CommandText == string.Empty)                        {                            return;                        }                        command.ExecuteNonQuery();                    }                }                catch (Exception exp)                {                    throw new BatcherException(exp);                }                finally                {                    connection.TryClose();                }            }        }        /// <summary>        /// 產生插入資料的sql語句。        /// </summary>        /// <param name="database"></param>        /// <param name="command"></param>        /// <param name="table"></param>        /// <returns></returns>        private string GenerateInserSql(IDatabase database, DbCommand command, DataTable table)        {            var names = new StringBuilder();            var values = new StringBuilder();            var types = new List<DbType>();            var count = table.Columns.Count;            var syntax = database.Provider.GetService<ISyntaxProvider>();            table.EachColumn(c =>                {                    if (names.Length > 0)                    {                        names.Append(",");                    }                    names.AppendFormat("{0}", DbUtility.FormatByQuote(syntax, c.ColumnName));                    types.Add(c.DataType.GetDbType());                });            var i = 0;            foreach (DataRow row in table.Rows)            {                if (i > 0)                {                    values.Append(",");                }                values.Append("(");                for (var j = 0; j < count; j++)                {                    if (j > 0)                    {                        values.Append(", ");                    }                    var isStrType = IsStringType(types[j]);                    var parameter = CreateParameter(database.Provider, isStrType, types[j], row[j], syntax.ParameterPrefix, i, j);                    if (parameter != null)                    {                        values.Append(parameter.ParameterName);                        command.Parameters.Add(parameter);                    }                    else if (isStrType)                    {                        values.AppendFormat("‘{0}‘", row[j]);                    }                    else                    {                        values.Append(row[j]);                    }                }                values.Append(")");                i++;            }            return string.Format("INSERT INTO {0}({1}) VALUES {2}", DbUtility.FormatByQuote(syntax, table.TableName), names, values);        }        /// <summary>        /// 判斷是否為字串類別。        /// </summary>        /// <param name="dbType"></param>        /// <returns></returns>        private bool IsStringType(DbType dbType)        {            return dbType == DbType.AnsiString || dbType == DbType.AnsiStringFixedLength || dbType == DbType.String || dbType == DbType.StringFixedLength;        }        /// <summary>        /// 建立參數。        /// </summary>        /// <param name="provider"></param>        /// <param name="isStrType"></param>        /// <param name="dbType"></param>        /// <param name="value"></param>        /// <param name="parPrefix"></param>        /// <param name="row"></param>        /// <param name="col"></param>        /// <returns></returns>        private DbParameter CreateParameter(IProvider provider, bool isStrType, DbType dbType, object value, char parPrefix, int row, int col)        {            //如果產生全部的參數，則速度會很慢，因此，只有資料類型為字串(包含‘號)和日期型時才添加參數            if ((isStrType && value.ToString().IndexOf(‘\‘‘) != -1) || dbType == DbType.DateTime)            {                var name = string.Format("{0}p_{1}_{2}", parPrefix, row, col);                var parameter = provider.DbProviderFactory.CreateParameter();                parameter.ParameterName = name;                parameter.Direction = ParameterDirection.Input;                parameter.DbType = dbType;                parameter.Value = value;                return parameter;            }            return null;        }    }

MySql的批量插入，是將值全部寫在語句的values裡，例如，insert batcher(id, name) values(1, ‘1‘, 2, ‘2‘, 3, ‘3‘, ........ 10, ‘10‘)。

五、測試

接下來寫一個測試案例來看一下使用批量插入的效果。

[Test]        public void TestBatchInsert()        {            Console.WriteLine(TimeWatcher.Watch(() =>                InvokeTest(database =>                    {                        var table = new DataTable("Batcher");                        table.Columns.Add("Id", typeof(int));                        table.Columns.Add("Name1", typeof(string));                        table.Columns.Add("Name2", typeof(string));                        table.Columns.Add("Name3", typeof(string));                        table.Columns.Add("Name4", typeof(string));                        //構造100000條資料                        for (var i = 0; i < 100000; i++)                        {                            table.Rows.Add(i, i.ToString(), i.ToString(), i.ToString(), i.ToString());                        }                        //擷取 IBatcherProvider                        var batcher = database.Provider.GetService<IBatcherProvider>();                        if (batcher == null)                        {                            Console.WriteLine("不支援批量插入。");                        }                        else                        {                            batcher.Insert(table);                        }                        //輸出batcher表的資料量                        var sql = new SqlCommand("SELECT COUNT(1) FROM Batcher");                        Console.WriteLine("當前共有 {0} 條資料", database.ExecuteScalar(sql));                    })));        }

以下表中列出了四種資料庫產生10萬條資料各耗用的時間

資料庫	耗用時間
MsSql	00:00:02.9376300
Oracle	00:00:01.5155959
SQLite	00:00:01.6275634
MySql	00:00:05.4166891

幾種資料庫的大資料批量插入【轉】