1. Prohibited Automatic Submission: In the By default, any SQL statement executed by the program is automatically submitted into 2000 records into a table. The time for automatic submission is automatically submitted (display submission) 3450 milliseconds 2, batch Processing: Multi-use batch, reduce the number of operational databases. (1), prohibiting automatic submission of SetAutocommit (FALSE); (2) Prepare a statement object preparedStateMent myprepStatement = myconnection.prepareStatement ("INSERT INTO TEST_TAB (VALUE) VALUES (?)"; (3), add statements to batch Addbatch (); (4) Execute this statement executebatch (); (5), submit the statement mYConnection.commit (); Oracle new improvement batch: (JDBC2.0 or above) can only be used to OraclePreparedStatement Objects are processed, where SQL statements are 5-30 Optimized) Improved places where you can preset bulk size, and SQL statements will automatically add into batches. (1), prohibiting submission (2), set the batch myoracleConnection.SetDefaultExecutebatch (10); (3), batch for batch for the SQL statement for (int count = 0; count <20; count ) {MyoraclePrepStatement.setint (1, Count); int RowsInserted = myoracleprepStatement.executeUpdate ();} Note: Int rowsInsert.sendbatch () (); 3, the number of rows acquired by the result set is 10, for most programs It is suitable, but if there is a lot of rows to get, you can increase the size of the acquisition to further improve program performance. Usually use Oracle's pre-acquisition, without the standard line pre-acquisition of Statement MyStatement = myconnection.createment (); MyStatement.SetFetchSize (20); Number of 2000 records from the database to 1 1642 ms 10 161 ms 20 91 millisecond oracle line pre-acquired oraclestatement myraclestatement = (oraclestatement) myconntion.createment.setrowprefetch (20); when set to 1 1532 milliseconds 11 140 milliseconds 21 80 milliseconds 4, defined result set type and length pre-definition result set column The Java type can save judgment set type round trip. When the query is sent to the database, there will be a Java type that a round-trip judgment result set should be used. (ORACleStatement) MyStatement) .defineColumnType (1, java.sql.types.integer); 5, statement cache uses a cache, can usually reduce the time of the preparation statement, and to avoid the use of results to create new Cursor. Two types: The statement string used twice before and after the implicit statement cache is exactly the same.
Dictionary cache ((ORACleStatement) myprepStatement .closewithKey ("MyCachedStatement"); 6, data type definition is defined as the same data type as SQL. 7, the variable name definition rule variable size is one to the SQL statement string case. >>> Equivalence Association Select A.ID, A.Title, B.ColumnidFrom Article, B. ArticleColumn bwhere a.id = b.Articlei; >>> External association Select A.ID, A.TITLE, B.COLUMNIDFROM ARTICLEINFO A ArticleColumn bwhere a.id = b.Articlei ( ) and b.ArticleId not null; >>> Interconnected Select A.ID, A.TITLE, B.COLUMNIDFROM ARTICLEINFO A, ArticleColumn Bwhere B.Article ( ) = A .id and b.ArticleId not null; >>> Equivalent association Select A.ID, A.Title from Article1Info A, ArticleColumn bwhere a.id = b.ArticleId; >>> in association SELECT A.ID, A.TITLE From articleinfo a where a.id in (select articleid from articolumn b); >>> Equivalent association (40%) Select A.ID, A.Title from Article Info Awhere EXISTS (SELECT B.ArticleId from articolumn bwhere a.id = B.ArticleID; >>> Create a function index Select A.ID, a.titlefrom articleinfowhere trunc (entertime)> = sysdate-30; create index fun_trunc_entertime on articleinfo (Trunc (Entertime) >>> Display Using an index Select / * Articleinfo (fun_trunc_entertime) * / id from articleinfowhere trunc (entertime)> = sysdate-30; >>> WHERE clauses in the condition sequence range select A.ID, B.Columnid from ArticleInfo A, ArticleColumn bwhere a.id = b.Art icleid ( ) 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
