Menu
Woocommerce Menu

接受导入数据的表名是 CALLS,我假定数据导入任务是将外部文件数据导入到

0 Comment


每个数据库管理员都会面临数据导入的问题,这有可能发生在数据库的新老移植过程中,或者是在数据库崩溃后的恢复重建过程中,还有可能是在创建测试数据库的模拟环境过程中,总之作为一名合格的数据库管理员,你应该做好接受各种数据导入请求的技术储备,同时还要尽量满足人本能的对导入速度的苛求。本文仅针对
Oracle
数据库所提供的加速数据导入的各种特性和技术进行探讨,其中的一些方法也可以转化应用于其他数据库。以下七种数据导入方法哪个最适用需要针对具体情况具体分析,我也附带列举了影响导入速度的各种因素供斟酌。为了比较各种数据导入方法的效果,我创建了示例表和数据集,并用各种方法导入示例数据集来计算总体导入时间和导入进程占用
CPU 时间,这里得出的时间仅供参考。需要说明的是,建议你使用 Oracle 9i
企业版数据库,当然你也可以尝试使用 Oracle 7.3
以上的标准版数据库。本文使用的机器配置为:CPU Intel P4,内存
256M,数据库 Oracle 9i 企业版。

示例表结构和数据集

命名空间:Oracle.DataAccess.Client

示例表结构和数据集

为了演示和比较各种数据导入方法,我假定数据导入任务是将外部文件数据导入到
Oracle 数据库的CALLS表中,外部数据文件包含十万条呼叫中心记录,将近 6MB
的文件大小,具体的数据示例如下:

组件:Oracle.DataAccess.dll(2.112.1.0)

为了演示和比较各种数据导入方法,我假定数据导入任务是将外部文件数据导入到
Oracle 数据库的CALLS表中,外部数据文件包含十万条呼叫中心记录,将近 6MB
的文件大小,具体的数据示例如下:

82302284384,2003-04-18:13:18:58,5001,投诉,手机三包维修质量82302284385,2003-04-18:13:18:59,3352,咨询,供水热线的号码82302284386,2003-04-18:13:19:01,3142,建议,增设公交线路

ODP.NET 版本:ODP.NET for .NET Framework 2.0 或 ODP.NET for .NET
Framework 4

82302284384,2003-04-18:13:18:58,5001,投诉,手机三包维修质量82302284385,2003-04-18:13:18:59,3352,咨询,供水热线的号码82302284386,2003-04-18:13:19:01,3142,建议,增设公交线路

接受导入数据的表名是 CALLS,表结构如下:

工具:Microsoft Visual Studio Ultimate 2013 + Oracle SQL Developer
1.5.5 + Oracle Database 11g Enterprise Edition 11.2.0.1.0(32位)
+ TNS for 32-bit Windows 11.2.0.1.0

接受导入数据的表名是 CALLS,表结构如下:

Name Null? Type Comment———— ——— ————-
—————–CALL_ID NOT NULL NUMBER Primary keyCALL_DATE NOT NULL
DATE Non-unique indexEMP_ID NOT NULL NUMBERCALL_TYPE NOT NULL
VARCHAR2(12)DETAILS VARCHAR2(25)

 

Name Null? Type Comment———— ——— ————-
—————–CALL_ID NOT NULL NUMBER Primary keyCALL_DATE NOT NULL
DATE Non-unique indexEMP_ID NOT NULL NUMBERCALL_TYPE NOT NULL
VARCHAR2(12)DETAILS VARCHAR2(25)

逐条数据插入INSERT

方式一:ArrayBind

当插入一条数据时,SQL 语句如下:

INSERT INTO table_name VALUES (:col1, :col2, :col3, :col4, :col5)

  1 public void InsertDataRow(Dictionary<string, object> dataRow)
  2 {
  3     StringBuilder sbCmdText = new StringBuilder();
  4     sbCmdText.AppendFormat("INSERT INTO {0}(", m_TableName);
  5     sbCmdText.Append(string.Join(",", dataRow.Keys.ToArray()));
  6     sbCmdText.Append(") VALUES (");
  7     sbCmdText.Append(":" + string.Join(",:", dataRow.Keys.ToArray()));
  8     sbCmdText.Append(")");
  9 
 10     using (OracleConnection conn = new OracleConnection())
 11     {
 12         using (OracleCommand cmd = conn.CreateCommand())
 13         {
 14             cmd.CommandType = CommandType.Text;
 15             cmd.CommandText = sbCmdText.ToString();
 16             OracleParameter parameter = null;
 17             OracleDbType dbType = OracleDbType.Object;
 18             foreach (string colName in dataRow.Keys)
 19             {
 20                 dbType = GetOracleDbType(dataRow[colName]);
 21                 parameter = new OracleParameter(colName, dbType);
 22                 parameter.Direction = ParameterDirection.Input;
 23                 parameter.OracleDbTypeEx = dbType;
 24                 parameter.Value = dataRow[colName];
 25                 cmd.Parameters.Add(parameter);
 26             }
 27             conn.Open();
 28             int result = cmd.ExecuteNonQuery();
 29         }
 30     }
 31 }

此时,每一个 OracleParameter 的 Value 值都赋予单个字段的
一个具体值,这种也是最为传统的插入数据的方法。

Oracle V6 中 OCI 编程接口加入了数组接口特性。

当采用 ArrayBind 时,OraleParameter 的 Value 值则是赋予单个字段的
一个数组,即多条数据的该字段组合成的一个数组。此时 Oracle 仅需要执行一次
SQL
语句,即可在内存中批量解析并导入数据,减少程序与数据库之间来回的操作,其优点就是数据导入的总体时间明显减少,尤其是进程占用
CPU 的时间。

如果数据源是 DataTable 类型,首先把 DataTable 数据源,转换成
object[][] 类型,然后绑定 OracleParameter 的 Value
值为对应字段的一个 Object[] 数组即可;参考代码如下:

  1 /// <summary>
  2 /// 批量插入大数据量
  3 /// </summary>
  4 /// <param name="columnData">列名-列数据字典</param>
  5 /// <param name="dataCount">数据量</param>
  6 /// <returns>插入数据量</returns>
  7 public int InsertBigData(Dictionary<string, object> columnData, int dataCount)
  8 {
  9     int result = 0;
 10     if (columnData == null || columnData.Count < 1)
 11     {
 12         return result;
 13     }
 14     string[] colHeaders = columnData.Keys.ToArray();
 15     StringBuilder sbCmdText = new StringBuilder();
 16     if (columnData.Count > 0)
 17     {
 18         // 拼接INSERT的SQL语句
 19         sbCmdText.AppendFormat("INSERT INTO {0}(", m_TableName);
 20         sbCmdText.Append(string.Join(",", colHeaders));
 21         sbCmdText.Append(") VALUES (");
 22         sbCmdText.Append(m_ParameterPrefix + string.Join("," + m_ParameterPrefix, colHeaders));
 23         sbCmdText.Append(")");
 24         OracleConnection connection = null;
 25         try
 26         {
 27             connection = new OracleConnection(GetConnectionString());
 28             using (OracleCommand command = connection.CreateCommand())
 29             {
 30                 command.ArrayBindCount = dataCount;
 31                 command.BindByName = true;
 32                 command.CommandType = CommandType.Text;
 33                 command.CommandText = sbCmdText.ToString();
 34                 command.CommandTimeout = 1800;
 35                 OracleParameter parameter;
 36                 OracleDbType dbType = OracleDbType.Object;
 37                 foreach (string colName in colHeaders)
 38                 {
 39                     dbType = GetOracleDbType(columnData[colName]);
 40                     parameter = new OracleParameter(colName, dbType);
 41                     parameter.Direction = ParameterDirection.Input;
 42                     parameter.OracleDbTypeEx = dbType;
 43                     parameter.Value = columnData[colName];
 44                     command.Parameters.Add(parameter);
 45                 }
 46                 connection.Open();
 47                 OracleTransaction trans = connection.BeginTransaction();
 48                 try
 49                 {
 50                     command.Transaction = trans;
 51                     result = command.ExecuteNonQuery();
 52                     trans.Commit();
 53                 }
 54                 catch (Exception ex)
 55                 {
 56                     trans.Rollback();
 57                     throw ex;
 58                 }
 59             }
 60         }
 61         finally
 62         {
 63             if (connection != null)
 64             {
 65                 connection.Close();
 66                 connection.Dispose();
 67             }
 68             GC.Collect();
 69             GC.WaitForFullGCComplete();
 70         }
 71     }
 72     return result;
 73 }

图片 1图片 2

  1 /// <summary>
  2 /// 根据数据类型获取OracleDbType
  3 /// </summary>
  4 /// <param name="value">数据</param>
  5 /// <returns>数据的Oracle类型</returns>
  6 private static OracleDbType GetOracleDbType(object value)
  7 {
  8     OracleDbType dataType = OracleDbType.Object;
  9     if (value is string[])
 10     {
 11         dataType = OracleDbType.Varchar2;
 12     }
 13     else if (value is DateTime[])
 14     {
 15         dataType = OracleDbType.TimeStamp;
 16     }
 17     else if (value is int[] || value is short[])
 18     {
 19         dataType = OracleDbType.Int32;
 20     }
 21     else if (value is long[])
 22     {
 23         dataType = OracleDbType.Int64;
 24     }
 25     else if (value is decimal[] || value is double[] || value is float[])
 26     {
 27         dataType = OracleDbType.Decimal;
 28     }
 29     else if (value is Guid[])
 30     {
 31         dataType = OracleDbType.Varchar2;
 32     }
 33     else if (value is bool[] || value is Boolean[])
 34     {
 35         dataType = OracleDbType.Byte;
 36     }
 37     else if (value is byte[])
 38     {
 39         dataType = OracleDbType.Blob;
 40     }
 41     else if (value is char[])
 42     {
 43         dataType = OracleDbType.Char;
 44     }
 45     return dataType;
 46 }

GetOracleDbType

说明:如果采用分次(每次1万数据)执行 InsertBigData
方法,速度反而比一次性执行 InsertBigData 方法慢,详见下面测试结果;

测试结果:

无索引,数据类型:4列NVARCHAR2,2列NUMBER

30+万(7.36M):一次性导入用时 15:623,每次10000导入用时

60+万(14.6M):一次性导入用时 28:207,每次10000导入用时 1:2:300

100+万(24.9M):一次性导入报如下异常

图片 3

此时实际上从资源监视器上可以得知仍有可用内存,但是仍旧报
OutOfMemoryException,所以猜测应该是一个 bug;

如果每次10000导入用时 2:9:252

如果每次50000导入用时 58:101

附加 InsertBigData 方法使用示例:

图片 4图片 5

  1 // 每10000数据导入一次
  2 Dictionary<string, object> columnsData = new Dictionary<string, object>();
  3 int dataCount = m_SourceDataTable.Rows.Count;
  4 int times = dataCount / 10000 + (dataCount % 10000 == 0 ? 0 : 1);
  5 for (int i = 0; i < times; i++)
  6 {
  7     int startIndex = i * 10000;
  8     int endIndex = (i + 1) * 10000;
  9     endIndex = endIndex > dataCount ? dataCount : endIndex;
 10     int currDataCount = endIndex - startIndex;
 11     columnsData.Add("COL1", new string[currDataCount]);
 12     columnsData.Add("COL2", new string[currDataCount]);
 13     columnsData.Add("COL3", new decimal[currDataCount]);
 14     columnsData.Add("COL4", new string[currDataCount]);
 15     columnsData.Add("COL5", new decimal[currDataCount]);
 16     columnsData.Add("COL6", new string[currDataCount]);
 17     for (int rowIndex = startIndex; rowIndex < endIndex; rowIndex++)
 18     {
 19         int dicRowIndex = rowIndex - startIndex;// 列数据行索引
 20         foreach (string colName in columnsData.Keys)
 21         {
 22             object cell = m_SourceDataTable.Rows[rowIndex][colName];
 23             string cellStr = (cell + "").TrimEnd(new char[] { '\0', ' ' });
 24             if (colName == "COL3" || colName == "COL5")
 25             {
 26                 decimal value = 0;
 27                 decimal.TryParse(cellStr, out value);
 28                 ((decimal[])columnsData[colName])[dicRowIndex] = value;
 29             }
 30             else
 31             {
 32                 ((string[])columnsData[colName])[dicRowIndex] = cellStr;
 33             }
 34         }
 35     }
 36     m_DAL.InsertBigData(columnsData, currDataCount);
 37 
 38     columnsData.Clear();
 39     GC.Collect();
 40     GC.WaitForFullGCComplete();
 41 }

View Code

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图