Limit challenge-C#+ODP 1 million data to import Oracle database in less than 1 seconds

. 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:

Limit Challenge-c#100 data import SQL Server database only in 4 seconds (with source code)

http://kb.cnblogs.com/page/73491/

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 to insert multiple rows of records: 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 ODP characteristics, 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: code

Set up 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, it's all our familiar code, and we're going to start here.

This parameter needs to specify the number of records to insert per batch

Command. Arraybindcount = RECC;

In this command line, parameters are used, and parameters are familiar to us, but when the parameter is passed the value

Uses an array, not a single value, and that's where it's unique.

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

The meaning of each parameter can be seen directly from the name, 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, you first define the array rather than generating the SQL directly as above

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 at the same time

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	20
3000	3749	113
4000	5737	40
5000	6820	52
6000	9469	72
7000	10226	69
8000	15280	123
9000	11475	83
10000	14536	121
11000	15705	130
12000	16548	145
13000	18765	125
14000	20393	116
15000	22181	159

Because of space reasons, no longer paste all the data, but we can look at this data generated scatter plot:

Some of these data are jumping, probably related to the database itself, but most of the data is already in the description. After reading the data, is not a bit of a heartbeat.

The source program put over a period of time direct copy posted over, may need to debug to run through, but not the essence of the problem, right if you want to test do not forget to install Oracle Access components.