Oracle Bulk Import 1 million data less than 1 seconds ____oracle

 . NET program can invoke the characteristics of the ODP, Oracle database operations, today to say about the function of data bulk insertion, the use of technology is not high, I believe many friends have contacted, little brother to swim, hehe. This article is the sequel to the previous article, because the last trial of the collection results did not break the 4-second method, so this continues to challenge and explore new methods, although it is Oracle, but still has a certain collection significance.

The experiment mentioned in the previous article: SQL Server batch import c#100 data only 4 seconds source http://blog.csdn.net/mrobama/article/details/53813084

This test is for the SQL Server database, and the hosting environment is also. Net, and interested friends can compare the two experiments to support future work batch derivatives.

In addition, some friends have some objections to the last test environment, that the database and server should be optimized or set up to reflect the final results of the test. This will, of course, affect the results of the experiment, however, considering that in the test environment, the standard and optimization of database optimization is not uniform and quantitative, the test results are not easy to explain the impact of the source, so the test is still based on the standard database after the configuration, the test server hardware environment and the last Test to maintain consistent. The aim of the experiment is to excavate and compare the data batch operation method in the host program.

There are new ways to improve performance time indicators friends, welcome to learn from each other, improve each other, mouth Kung fu on the free ...

Okay, the text begins.

Ordinary meat Cushion type

What do you mean by mass insertion? is to insert a batch of data one at a time, we can interpret the data as a large array, which is only implemented in one SQL, and in the traditional way, you need to call a lot of SQL to complete, this is the famous "array binding" function. Let's take a look at the traditional way in which you insert multiple lines of records in the following ways:

Code

Sets the connection string for a database, string connectstr = "User Id=scott;" Password=tiger;data source= "; OracleConnection conn = new OracleConnection (CONNECTSTR); OracleCommand command = new OracleCommand (); Command. Connection = conn; Conn. Open (); stopwatch SW = new Stopwatch (); Sw. Start (); By looping through a lot of data, this method is obviously a meat mat for (int i = 0; i < RECC i + +) {String sql = "INSERT INTO dept values (" + i.tostring () + "," + i.tostring () + "," + i.tostring () + ")"; Command.commandtext = SQL; Command. ExecuteNonQuery (); } SW. Stop (); System.Diagnostics.Debug.WriteLine ("Normal insert:" + RECC.) ToString () + "occupied time:" + SW. Elapsedmilliseconds.tostring ());

We first prepare the program, but do not do time measurement, because in the later we will use multiple loops to calculate the time taken.

using the ODP feature

Look at the above program, everyone is very familiar with, because it does not use any of the characteristics of the ODP, and then we have to introduce a magic program, we look at the code, in order to more intuitive, I put all the comments and instructions directly in the code:

Set a connection string for a database string connectstr = "User Id=scott;" Password=tiger;data source= "; OracleConnection conn = new OracleConnection (CONNECTSTR); OracleCommand command = new OracleCommand (); Command. Connection = conn; So far, we are all familiar with the code, the next start//This parameter needs to specify the number of records per batch insert command. Arraybindcount = RECC; In this command line, parameters are used, and parameters are familiar to us, but this parameter is used to transmit the value of an array instead of a single value, which is the unique local command. CommandText = "INSERT INTO dept values (:d Eptno,:d eptname,: Loc)"; Conn. Open (); The following defines several arrays, representing three fields, with the length of the array given directly by the argument int [] deptNo = new int [RECC]; string [] dname = new string [RECC]; string [] loc = new string [RECC]; In order to pass parameters, the inevitable use of parameters, the following will be defined consecutively three//from the name can directly see the meaning of each parameter, not in each explanation of the oracleparameter Deptnoparam = new OracleParameter ("Deptno", ORACLEDBTYPE.INT32); Deptnoparam.direction = ParameterDirection.Input; Deptnoparam.value = DeptNo; Command. Parameters.Add (Deptnoparam); OracleParameter Deptnameparam = new OracleParameter ("Deptname", oracledbtype.varchar2); Deptnameparam.direction = ParameterDirection.Input; DeptnamepAram. Value = dname; Command. Parameters.Add (Deptnameparam); OracleParameter Deptlocparam = new OracleParameter ("Loc", ORACLEDBTYPE.VARCHAR2); Deptlocparam.direction = ParameterDirection.Input; Deptlocparam.value = loc; Command. Parameters.Add (Deptlocparam); stopwatch SW = new Stopwatch (); Sw. Start (); In the following loop, the array is defined first, rather than directly generating SQL for (int i = 0; i < RECC i + +) {Deptno[i] = i; dname[i] = i.ToString (); Loc[i] = i.ToString (); }//This call will pass the parameter array into SQL and write to the database command. ExecuteNonQuery (); Sw. Stop (); System.Diagnostics.Debug.WriteLine ("Bulk Insert:" + RECC.) ToString () + "occupied time:" + SW. Elapsedmilliseconds.tostring ());

The above code is a bit verbose, but with comments it's basically clear.

OK, so far, the two ways of inserting procedures have been completed, and there is only comparison. I wrote a small function at the main function, looping through two methods several times, and simultaneously recording the time, the contrast function is as follows:

for (int i = 1; I <= i + +) {Truncate (); Ordinaryinsert (i * 1000); Truncate (); Batchinsert (i * 1000); }

When the amount of data reached 1 million, the time is still satisfactory, the fastest one to 890 milliseconds, generally 1 seconds or so. After testing, a set of data can be seen in the two ways in the efficiency of the astonishing gap (in milliseconds), some of the data are as follows:

Number of records	Standard	Batch Processing
1000	1545	29
2000	3514