Performance Optimization in Java Database Programming

1. Automatic submission prohibited:
By default, any SQL statements executed by the program are automatically submitted.
Insert 2000 records into a table,
The automatic submission time is 11666 milliseconds.
Disable Automatic submission (display submission) in 3450 milliseconds

2. batch processing:
Batch Processing is used to reduce the number of database operations.
(1) automatic submission prohibited
Setautocommit (false );
(2) prepare a statement object
Preparedstatement myprepstatement = myconnection. preparestatement ("insert into test_tab (value) values (?)";
(3) Add the statement to the batch
Addbatch ();
(4) execute these statements
Executebatch ();
(5) Statement submitted for execution
Myconnection. Commit ();

New Improved batch processing in Oracle: (supported by jdbc2.0 or above)
Only oraclepreparedstatement objects can be processed. The number of SQL statements is 5-30)
The improvement is that the batch size can be preset, and the SQL statement will be automatically added to the batch.
(1) Submission prohibited
(2) set the batch value
Myoracleconnection. setdefaultexecutebatch (10 );
(3) Batch Processing of SQL statements
For (int count = 0; count <20; count ++ ){
Myoracleprepstatement. setint (1, count );
Int rowsinserted = myoracleprepstatement.exe cuteupdate ();
}
Note: You can also force int rowsinserted = myoracleprepstatement. sendbatch ();

3. Row pre-acquisition
By default, the number of rows obtained by the result set is 10, which is suitable for most programs. However, if you want to obtain a large number of rows, you can increase the size to further improve program performance.
Oracle row pre-acquisition is usually used, instead of row pre-acquisition.

Standard row pre-acquisition
Statement mystatement = myconnection. createstatement ();
Mystatement. setfetchsize (20 );
Retrieve 2000 records from the database
When set to 1 1642 Ms
10 161 Ms
20 91 Ms

Oracle row pre-acquisition
Oraclestatement myoraclestatement = (oraclestatement) myconntion. createstatement ();
Myoraclestatement. setrowprefetch (20 );

When set to 1 1532 Ms
11 140 ms
21 80 ms

4. Define the result set type and length
Pre-define the Java type of the result set column, which can save the round-trip judgment result set type.
When a query is sent to the database, a round-trip judge of the Java type that the result set should use.
(Oraclestatement) mystatement). definecolumntype (1, java. SQL. types. integer );

5. Statement Cache
When using cached statements, you can usually reduce the preparation time by half, and avoid creating a cursor when using the result set.
Two types:
Implicit statement Cache
The statement strings used both before and after are identical.
Display language Cache
(Oraclestatement) myprepstatement). closewithkey ("mycachedstatement ");
6. Data Type Definition
It is defined as the same data type as SQL.
7. variable name definition rules
The variables are case-insensitive and SQL statement strings are case-insensitive.

>>> Equijoin
Select a. ID, A. Title, B. columnid
From articleinfo A, articlecolumn B
Where a. ID = B. articlei;

>>> External Association
Select a. ID, A. Title, B. columnid
From articleinfo A, articlecolumn B
Where a. ID = B. articlei (+) and B. ArticleID not null;

>>> Internal Association
Select a. ID, A. Title, B. columnid
From articleinfo A, articlecolumn B
Where B. articlei (+) = A. ID and B. ArticleID not null;

>>> Equijoin
Select a. ID, A. Title
From articleinfo A, articlecolumn B
Where a. ID = B. ArticleID;

>>> In Association
Select a. ID, A. Title from articleinfo
Where a. ID in (select ArticleID from articlecolumn B );

>>> Equijoin (40%)
Select a. ID, A. Title
From articleinfo
Where exists (select B. ArticleID from articlecolumn B
Where a. ID = B. ArticleID );

>>> Create a function index
Select a. ID, A. Title
From articleinfo
Where trunc (entertime) >=sysdate-30;

Create index fun_trunc_entertime on articleinfo (trunc (entertime ))

>>> A certain index is displayed.
Select/* + articleinfo (fun_trunc_entertime) */ID from articleinfo
Where trunc (entertime) >=sysdate-30;

>>> Conditional order in the WHERE clause
The smaller the range, the closer it is to the back
Select a. ID, B. columnid from articleinfo A, articlecolumn B
Where a. ID = B. ArticleID (+) and B. ArticleID is not null and B. columnid> = 353454564564576 and B. columnid <234345344565676;
• Nested loops (NL) join
• Sort-merge join
• Hash join (not available with the RBO)
• Cluster join