Further improve the performance of JDBC applications (four)
Bootcool@263.net
Four: Using the pre-compiled statement and bulk update
First we have a general understanding of how the database is how to deal with various database operation statements. When the database receives a statement, the database engine first parsing the statement and then analyzing if there is a syntax, a semantic error. If there is no error, the database will calculate how to efficiently execute the statement. Once the execution policy is obtained, the statement is executed by the database engine, and finally feeds the execution result to the user. Although the database manufacturer has made the biggest optimization for their respective databases, it is indeed a lot of expenses.
So, we consider how to make our database operation more efficient? If a statement is executed once, the database records the execution policy of the statement, then the same statement can be saved, and it can save the above troubles.
This interface is available in Java -preparedStatement .. We can easily improve statement execution efficiency by precompiling prepaaredStatement objects. At the same time, it is necessary to point out that there is another interface -Statement that implements database operations in Java, but we prefer to use PreparedStatement when the statement format is fixed, and we only consider using Statement.
The following is a test result for INSERT, UPDATE, and SELECT statements that perform the same 1000 statement structure:
Interface Type
INSERT statement
UPDATE statement
SELECT statement
First test time
Second test time
First test time
Second test time
First test time
Second test time
Statement
2360 ms
2190 ms
3790 ms
3460 ms
3570 ms
2530 ms
PreparedStatement
1270 ms
1040 ms
2600 ms
2410 ms
440 ms
380 ms
(Table 8)
Analysis: PreparedStatement is significantly higher than that of Statement. In addition, we have to go deep into how JDBC is implemented. After the JDBC executes a query, a resultset will be returned. In order to establish this result set, JDBC will access the database twice. The first time you ask the database to explain the columns in the result set, the second time tells the database how to place data when the program needs to obtain data. Thus we can calculate the number of times the JDBC needs to access the database.
Access database number = number of columns in the result set * statement execution * 2
If the same query is also executed, the number of columns in the result is also the same, and it is assumed to be 20 columns:
Use Statement: Access database number = 20 * 100 * 2 = 4000
Use prepared statement: Access database number = 20 * 1 * 2 = 400
We should also pay attention to it. If you use the preparedStatement interface, you cannot achieve the purpose of improving the performance efficiency. We use a simple test program description:
Import java.sql. *;
Public class jdbctest2 {
Private static connection con = NULL;
Private static string dburl = NULL;
Public JDBCTEST2 () {
}
Public static void main (string args []) {
Try {
DBURL = "JDBC: ODBC: TEST";
Class.Forname ("Sun.jdbc.odbc.jdbcodbcdriver");
PreparedStatementInsrtTest_1 (); system.out.println ("===========================");
PreparedStatementInsertTest_2 ();
}
Catch (Exception EX) {
EX.PrintStackTrace ();
}
}
/ / Method 1 uses a PreparedStatement interface in an inappropriate manner
Public static void preparedStatementInsertTest_1 () {
Try {
Con = DriverManager.getConnection (DBURL);
PreparedStatement PST = NULL;
Long Start = system.currenttimemillis ();
/ / Execute the same query of 1000 statements
For (int i = 0; i <1000; i ) {
PST = Con.PrepareStatement ("SELECT * FROM S where S1 =" i);
Pst.executeQuery ();
Pst.close ();
}
System.out.println ("MethOrd_1 Execute Ellapse:"
(System.currenttimemillis () - start)
"MS");
C. close ();
}
Catch (Exception EX) {
EX.PrintStackTrace ();
}
}
// Method 2 uses a preparedStatement interface in the correct way
Public Static Void PreparedStatementInsertTest_2 () {
Try {
Con = DriverManager.getConnection (DBURL);
Long Start = system.currenttimemillis ();
PreparedStatement PST = NULL;
PST = Con.PrepareStatement ("SELECT * FROM S Where S1 =?");
/ / Execute the same query of 1000 statements
For (int i = 0; i <1000; i ) {
Pst.Setint (1, i);
Pst.executeQuery ();
}
System.out.println ("MethOrd_2 Execute Ellapse:"
(System.currenttimemillis () - start)
"MS");
Pst.close ();
C. close ();
}
Catch (Exception EX) {
EX.PrintStackTrace ();
}
}
}
The following is the relevant test results:
the way
SELECT statement
Perform the same query consumption of 100 statements
Execute the same query consumption of 1000 statements
First test
Second test
First test
Second test
Way 1
1100 ms
330 ms
3510 ms
3020 ms
Way 2
110 ms
50 ms
440 ms
380 ms
(Table 9)
Analysis: Test results Description If the PREPAREDSTATEMENT interface is not used correctly, then its execution efficiency and the use of Statement have no difference, and the advantage of the PreparedStatement interface will not be fully utilized. Finally, we have to add a little while we need to be batch inserted or updated records. We consider using Java's bulk update mechanism, which allows multiple statements to submit a time to submit to database batch processing. Normally, more efficiently more efficient than separately submitted. If we use the PreparedState interface, we will further increase the performance of the program. We also give a small program to explain.
Import java.sql. *;
Public class jdbctest3 {
Public static void main (String [] args) {
Try {string [] values = {"beijing", "kunming"}
Int [] results;
Class.Forname ("Sun.jdbc.odbc.jdbcodbcdriver");
Connection Con = DriverManager.getConnection ("JDBC: ODBC: TEST");
Statement ST = con.createstatement ();
For (int i = 0; i // Add a SQL command to the command list St.Addbatch ("INSERT INTO City VALUES"); "); } / / Execute bulk update St.executebatch (); PreparedState pst = con.preparestatement ("Insert INTO City" "VALUES"); For (int i = 0; i Pst.SetString (1, Values [i]); // Add a SQL command to the command list Pst.addbatch (); } / / Execute bulk update Pst.executebatch (); St.close (); Pst.close (); C. close (); } Catch (Exception EX) { EX.PrintStackTrace (); } } }
