[Oracle] Bulk Insert Data, oraclebulk

[Oracle] Bulk Insert Data, oraclebulk

Namespace: Oracle. DataAccess. Client

Components:Oracle. DataAccess. dll (2.112.1.0)

ODP. NET version: ODP. NET for. NET Framework 2.0 or ODP. NET for. NET Framework 4

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

 

Method 1: ArrayBind

When a piece of data is inserted, the SQL statement is as follows:

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 }

In this case, each OracleParameter Value is assigned a specific Value to a single field, which is also the most traditional method for data insertion.

In Oracle V6, the OCI programming interface adds the array interface feature.

When ArrayBind is used, the Value of OraleParameter is an array assigned to a single field, that is, an array composed of multiple data fields. At this time, Oracle only needs to execute an SQL statement once to parse and import data in batches in the memory, reducing the back-and-forth operations between the program and the database. The advantage is that the overall data import time is significantly reduced, especially the CPU usage time of processes.

If the data source is of the DataTable type, first convert the DataTable data source to the object [] [] type, and then bind the Value of OracleParameter to an Object [] array of the corresponding field; the reference code is as follows:

1 /// <summary> 2 /// batch insert large data volume 3 /// </summary> 4 /// <param name = "columnData"> column name-column data dictionary </param> 5 // <param name = "dataCount"> data volume </param> 6 // <returns> insert data volume </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 sb1_text = new StringBuilder (); 16 if (columnData. count> 0) 17 {18 // concatenate the insert SQL statement 19 sb1_text. appendFormat ("insert into {0} (", m_TableName); 20 sb1_text. append (string. join (",", colHeaders); 21 sb1_text. append (") VALUES ("); 22 sb1_text. append (m_ParameterPrefix + string. join ("," + m_ParameterPrefix, colHeaders); 23 sb1_text. 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 = sb1_text. toString (); 34 command. commandTimeout = 1800; 35 OracleParameter parameter; 36 OracleDbType dbType = Ora CleDbType. 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 = connect Ion. 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 // <summary> 2 // obtain OracleDbType 3 Based on the Data Type // </summary> 4 // <param name = "value"> data </param> 5 // <returns> data Oracle type </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 Description: If the InsertBigData method is executed in batches (10 thousand data each time), the speed is slower than the one-time InsertBigData method. For details, see the following test results;

Test results:

No index, data type:4 columns NVARCHAR2, 2 columns NUMBER

30 + 7.36 (623 M): one-time import time: 15: 10000, each import time

60 + 14.6 (10000 M): for one-time import, for each import

100 + 24.9 (M): one-time import reports the following exception

At this time, we can actually know from the resource monitor that there is still available memory, but it still reports OutOfMemoryException, So we guess it should be a bug;

If is required for each 10000 import

If 58: 50000 is used for each 101 import

Example of attaching the InsertBigData method:

1 // 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; // column data row index 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 Method 2: OracleBulkCopy

Note:

1. OracleBulkCopy is imported using direct path;

2. transaction is not supported and Rollback is not supported;

3. If the table has a trigger, OracleBulkCopy (reporting exception information Oracle Error: ORA-26086) cannot be used unless all triggers for the table are disabled first;

4. the not null, UNIQUE, and primary key constraints are automatically enabled. The not null constraint is verified when the column array is bound. Any row data that violates the not null constraint is discarded; the UNIQUE constraint is verified when the index is re-built after the import is complete. However, when bulk copy is used, it allows violation of the index constraint and sets the index to disabled after completion, if the index is disabled at the beginning, OracleBulkCopy reports an error.

The reference code is as follows:

1 /// <summary> 2 /// batch insert data 3 /// this method needs to disable all triggers of the table, and if the inserted data is empty, yes. The default value is not 4 /// </summary> 5 /// <param name = "table"> data table </param> 6 /// <param name = "targetTableName"> target database table name </param> 7 // <returns> </returns> 8 public bool InsertBulkData (DataTable table, string targetTableName) 9 {10 bool result = false; 11 string connStr = GetConnectionString (); 12 using (OracleConnection connection = new OracleConnec Tion (connStr) 13 {14 using (OracleBulkCopy bulkCopy = new OracleBulkCopy (connStr, OracleBulkCopyOptions. Default) 15 {16 if (table! = Null & table. rows. count> 0) 17 {18 bulkCopy. destinationTableName = targetTableName; 19 for (int I = 0; I <table. columns. count; I ++) 20 {21 string col = table. columns [I]. columnName; 22 bulkCopy. columnMappings. add (col, col); 23} 24 connection. open (); 25 bulkCopy. writeToServer (table); 26 result = true; 27} 28 bulkCopy. close (); 29 bulkCopy. dispose (); 30} 31} 32 33 return result; 34}

Test results:

Data Type:4 columns NVARCHAR2, 2 columns NUMBER

30 + 7.36 (590 M): Time used: 14:

60 + 14.6 (M):

1048576 (24.9 M): Time 52: 971

Attach to disable all foreign key SQL statements of the table:

ALTER TABLE table_name DISABLE ALL TRIGGERS

Summary

1. the time difference between ArrayBind one-time import and OracleBulkCopy for 30 + and 60 + data is not very large. However, the ArrayBind mode generally needs to convert the data format, which takes some time, oracleBulkCopy can be imported simply by processing the DataTable data source;

2. When the data volume reaches 100 +, ArrayBind is prone to memory insufficiency exceptions. At this time, only batch import can be performed. According to the test results, the less the number of times, the faster the import speed; however, the OracleBulkCopy mode rarely causes insufficient memory. Therefore, OracleBulkCopy occupies less memory than ArrayBind;

References:

1. ArrayBind http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

2. ArrayBind http://www.soaspx.com/dotnet/csharp/csharp_20130911_10501.html

3. http://dbanotes.net/Oracle/All_About_Oracle_Data_Loading.htm of Oracle Data Import Method

4. Introduces OracleBulkCopy class https://docs.oracle.com/cd/E11882_01/win.112/e23174/OracleBulkCopyClass.htm#ODPNT7446

5. http://dba.stackexchange.com/questions/7287/what-specifically-does-oraclebulkcopy-do-and-how-can-i-optimize-its-performance