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

Link Address: http://www.cnblogs.com/armyfai/p/4646213.html

to: Here we will see the use of the ODP in C # to import millions data instantaneously in an Oracle database, which is important for fast batch import implementations. . NET program through the ODP call features, the Oracle database operation, today to talk about the data bulk insertion function, the use of technology is not high, I believe many friends have contacted, the younger brother swim, hehe. This article is a sequel to the previous article, because the last trial of the results did not break the 4-second approach, so this time continue to challenge and mining new methods, although Oracle, but still have a certain collection significance.

The experiment mentioned in the last article:

Extreme challenges-c#100 Data import SQL Server database in only 4 seconds (with source)

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

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

In addition, some friends have some objections to the previous test environment, that the database and server should be optimized or set to reflect the final test time results. This will affect the time of the test results, but in the context of the test environment, the standard and optimization of the database optimization is not uniform and quantitative, the test results are not easy to explain its impact source, so the test is still the standard database after the configuration of the base, the test server hardware environment and the previous test to maintain the same. The purpose of the experiment is to excavate and compare the data batch operation methods in the host program.

A new way to improve performance time indicators of friends, welcome to each other, improve each other, the mouth of Kung fu is free ...

Okay, the text begins.

Ordinary meat Cushion type

What is called BULK INSERT, is one-time insert a batch of data, we can interpret this batch of data as a large array, and these all only through a SQL to implement, and in the traditional way, need to call many times of SQL to complete, this is the famous "array binding" function. Let's take a look at how to insert multiple rows of records in the traditional way:

CodeSet 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;

Conn. Open ();

Stopwatch SW = new Stopwatch ();

Sw. Start ();

By looping through a large amount of data, this method is obviously a meat pad

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 the time measurement, because in the back 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 features, and then we are going to introduce a magical program, we look at the code, in order to be more intuitive, I put all the comments and instructions directly in the code:

Code

The above code is a bit verbose, but after adding the comment, the basic expression is clear.

Well, so far, the two-way insert procedure has been completed, leaving the comparison. I wrote a small function at the main function, looping through two methods, and 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 levels, the time spent is still satisfactory, the fastest time to reach 890 milliseconds, generally 1 seconds or so.

After testing, a set of data can be seen in two ways in terms of the efficiency of the staggering 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 the length of the data, we will not paste all of it, but we can look at the scatter plot generated by this data:

Some of these data are somewhat jumping and may be related to the database itself, but most of the data can already explain the problem. After looking at the data, is it a bit of a thrill?

Source program put a period of time direct copy pasted over, may need to debug to run through, but not the essence of the problem, if you want to test do not forget to install the Oracle Access Components

Category: ASP.

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