Performance optimization in JAVA database programming

1, prohibit automatic submission:
By default, any SQL statements that are executed by the program are automatically committed
Insert 2000 records into a table,
The time it took to automatically commit 11666 milliseconds
Prohibit Auto-commit (show commit) 3450 MS

2. Batch processing:
Multi-use batching reduces the number of database operations.
(1), prohibit automatic submission
Setautocommit (FALSE);
(2), prepare a statement object
PreparedStatement myprepstatement = myconnection.preparestatement ("INSERT into Test_tab (value)" VALUES (?) ";
(3), add the statement into the batch
Addbatch ();
(4), execute this batch of statements
ExecuteBatch ();
(5), submit the execution of the statement
Myconnection.commit ();

Oracle's new improved batch processing: (JDBC2.0 above support)
Only Oraclepreparedstatement objects can be processed, where the SQL statements are optimized in 5-30.
The improvement is that the batch size can be preset, and the SQL statement is automatically added to the batch.
(1), prohibit the submission
(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.executeupdate ();
}
Note: You can also enforce int rowsinserted = Myoracleprepstatement.sendbatch ();

3, row pre-acquisition
By default, the number of rows fetched by the result set is 10, which is appropriate for most programs, but if you want to get more rows, you can increase the fetch size to further improve program performance.
Oracle row prefetching is typically used instead of the row pre-fetch

Standard row pre-fetch
Statement mystatement = Myconnection.createstatement ();
Mystatement.setfetchsize (20);
Fetch 2000 records from the database
When set to 1 1642 MS
10 161 ms
20 91 ms

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

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


4. Define the type and length of the result set
Defining the Java type of the result set column can save you from judging the result set type round trip.
When a query is sent to a database, there is a round trip to determine which Java type the result set should use.
((oraclestatement) mystatement). Definecolumntype (1,java.sql.types.integer);

5. Statement caching
With cached statements, you can typically reduce the time to prepare a statement by half, as well as create a new cursor when you avoid using the result set.
Two types of:
Implicitly-statement caching
The statement string used before and after two times is exactly the same.
Display language Cache
((oraclestatement) myprepstatement). Closewithkey ("Mycachedstatement");
6. Data type definition
defined as the same data type as SQL.
7. Variable name definition rules
The variable is case-sensitive, and the SQL statement string is case-sensitive.

>>> equivalence correlation
Select A.id,a.title,b.columnid
From Articleinfo A,articlecolumn b
where A.id=b.articlei;

>>> External Links
Select A.id,a.title,b.columnid
From Articleinfo A,articlecolumn b
where A.id=b.articlei (+) and b.articleid not null;

>>> in Association
Select A.id,a.title,b.columnid
From Articleinfo A,articlecolumn b
where B.articlei (+) =a.id and b.articleid not null;

>>> equivalence correlation
Select A.id,a.title
From Articleinfo A,articlecolumn b
where A.id=b.articleid;

>>>in Association
Select A.id,a.title from Articleinfo a
Where a.id in (select ArticleID from Articlecolumn b);

>>> equivalent (40%)
Select A.id,a.title
From Articleinfo A
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))

>>> Show using an index
Select/*+ articleinfo (fun_trunc_entertime) */ID from Articleinfo
where Trunc (entertime) >=sysdate-30;

Conditional order in the >>>where clause
The smaller the range, the farther back.
Select A.id,b.columnid from Articleinfo a,articlecolumn b
where A.id=b.articleid (+) and B.articleid are 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

Performance optimization in JAVA database programming