Using ADO. Net to optimize mass data update in Oracle)

In business system applications, a large amount of data is often submitted (including querying, updating, or deleting). If the data volume of the target database is large, a large number of operations need to be processed at a time, the system execution efficiency is low. Taking Oracle9i Database as an example, this article describes several common optimization methods based on the application practices supported by the database in ADO. NET and compares the advantages and disadvantages of these methods.

In order to explain the situation, taking the business data filling function as an example, assume that 100 users need to fill in a certain statistical amount every week, and the detailed data volume is about 200 records, A dedicated report page is provided to implement one submission. In this way, the data increment for one week is about 20 thousand, and the value for one year is more than 1 million. To ensure that the system is running for more than six years, the efficiency of data storage (addition, deletion, and modification) needs to be considered. (The Optimization Configuration of the database itself, including the query efficiency of tablespaces and indexes, has been taken into consideration and is not discussed here ). This type of business features a large volume of data operations, but the complexity of executing commands is low, including simple addition, modification, and deletion.

 

 

 

Problems with traditional processing methods
For each operation to be processed, the corresponding SQL operation (or stored procedure) is directly executed on the target table. You can use ADO. Net parameterized SQL statements or indirectly process them through dataset and dataadapter. In this way, each user needs to execute about 200 SQL operations when submitting data in batches. Although the database is optimized, the efficiency of executing SQL statements at a time is not low, but because there are many commands at a time, with the increase of the target data capacity, the efficiency will gradually decrease, and it will be intolerable.

 

Optimization Method 1: temporary table processing mode
The efficiency of modifying, deleting, or updating a large-scale target database table is slow. To reduce the number of operations on the target table, you can use a temporary table solution. The specific method is to create a temporary table with a structure similar to the target table (due to the characteristics of the B/S mode, the temporary table is based on transactions rather than connections ), the operation mode field is added. Before the operation is executed, the data to be operated is the data of a user per week (about 200 pieces, no data should be found during the first processing) after one query is transferred to the temporary table, modify, update, and delete the temporary table. After the processing is completed, the data of the temporary table is submitted to the target table in three types. The process is shown in:

 

 

 

Delete
Delete from target_table where key in (select key

From temp_table where State = 'delete'

New
Insert into target_table...

Software Development Network www.mscto.com

 

Select... From temp_table where State = 'insert'

 

Modify
Update target_table set...

 

Where key = temp_table.ket and temp_table.state = 'update'

 

Experiments show that this method is about 40 times faster than the traditional method with 0.5 million data volume, and the execution efficiency is less affected by the target database capacity.

Optimization Method 2: SQL Batch Processing
SQL batch processing generally has two modes: one is the SQL statement to be executed, and the other is the SQL command to be executed, multiple sets of parameters are passed for batch execution. Both methods require the support of databases and ADO. net.

Software Development Network www.mscto.com

 

System. data. ADO. NET 2.0 supports batch processing in the first method. For example, when you submit data in batches to dataset through dataadapter, the system adds, modifies, deletes the ID of the dataset, and constructs batch processing commands, form an SQL command segment and submit it to the server for execution. In this way, multiple SQL commands are formed into a group of SQL commands to implement batch execution of multiple commands, which can improve the function execution efficiency to a certain extent. The principle is as follows:

 

Begin

Insert into taregt_table (a, B, c) values (: 1,: 2,: 3 );

Insert into taregt_table (a, B, c) values (: 4,: 5,: 6); www.mscto.com

Insert into taregt_table (a, B, c) values (: 7,: 8,: 9 );

......

Insert into taregt_table (a, B, c) values (: N,: n + 1,: n + 2); www.mscto.com

End;

 

The batch processing SQL commands and parameters formed by this method will multiply with the increase of data volume, and the data update volume and execution efficiency are limited. However, Microsoft's Oracle ADO. Net implementation does not directly disclose the batch processing method to external users. It can only be used indirectly through data batch update of dataset.

 

 

 

Another solution is to use ADO. Net of oracle. Oracle. dataaccess. client-implemented ADO. net supports the second mode of batch processing commands. It uses the batch processing function provided by the Oracle database to transmit the parameter array by setting the arraybindcount of oraclecommand. When arraybindcount is set to greater than 1, the parameter passed to an oraclecommand is no longer a parameter value but an array of parameters. In this way, a command can execute multiple processes, such: insert 100 data records. This method greatly improves data operation efficiency by using the optimization mechanism of the database itself for batch data operations. Through testing the target table with the target database capacity of 0.5 million, we found that the execution efficiency of this method is more than 50 times higher than that of the traditional method. During the testing, we found that, if the target data volume is 1 million, it takes about 1 second to insert 10 thousand data records at a time, and the operation efficiency is less affected by the target data volume. Software Development Network www.mscto.com

Conclusion
Based on the above analysis, we believe that the use of temporary tables and batch processing methods can better solve the problem of batch data submission in the large-scale data volume mode. Among them, the implementation of Oracle's ADO. NET is the most efficient and the easiest to process. The implementation of Microsoft ADO. net2.0 does not fully utilize the functions of the database itself, and its functions and efficiency are limited. The Processing Method of temporary tables is complicated and suitable for Microsoft's ADO. net. Furthermore, Oracle stored procedures support passing parameter arrays, and can also be implemented by passing parameter arrays. The premise is that Oracle ADO must be used. net implementation, because Microsoft's ADO. the net implementation does not support parameter array transmission, while the Oracle database also supports the bulk insert function. If batch data needs to be inserted, you can consider using this method, which is not discussed in detail here.