Developing a JDBC app that focuses on performance is not an easy task. When your code is running very slow, the JDBC driver does not throw an exception tells you.
The performance tips of this series will introduce some basic guiding principles to improve the performance of JDBC applications, which have been compiled and validated by many existing JDBC applications. These guidelines include:
Correct use of database metadata methods only get required data to select the best performance management connection and update
These general principles can help you solve the performance of some public JDBC systems.
Select JDBC objects and methods
The guidelines for this section will help you have the best performance when you choose JDBC objects and methods.
Use parameter tags as parameters during stored procedures
When the stored procedure is called, the parameter tag should always be used instead of the parameters on the literal. The JDBC driver can call the database stored procedure, and can be executed by other SQL queries, or directly call (RPC) Mode execution. When you execute the stored procedure as an SQL query, the database is to resolve this query statement, verify the parameters, and convert the parameters to the correct data type.
To remember, the SQL statement is always sent to the database in the form of a string, for example, "{Call getcustname (12345)}. Here, even if the parameters are assigned to getSustName as an integer, the upper parameters are implemented The string is transmitted to the server. The database will resolve this SQL query, and according to Metadata to determine the parameter type of the stored procedure, then decompose the parameter "12345", and then the string is finally in the final storage process as a SQL query. '' 12345 'is converted to an integer.
When calling by RPC, the way the SQL string should be avoided. Reveared, the JDBC driver constructs a network packet, which contains the parameters of the local data type, and then performs remote calls.
Case 1
In this example, the stored procedure cannot be optimally using the RPC. The database must parse this as a normal language, check the parameter type and convert the parameters to the correct data type, and finally perform this stored procedure.
Callablestatement cstmt = conn.prepareCall ("{Call getCustName (12345)}); ResultSet RS = cstmt.executeQuery ();
Case 2
In this example, the stored procedure can perform RPC. Because the program avoids the literal parameters, use special parameters to call the stored procedure, the JDBC driver is best to perform the stored procedure directly in the RPC mode. SQL language Treatment is avoided here and is also improved.
Callablestatement cstmt - conn.preparecall ("{call getcustname (?); Cstmt.setlong (1,12345); resultset = cstmt.executeQuery ();
Use Statement instead of preparedStatement object
The optimization of JDBC is based on how the use is based. Select PreparedStatement or Statement depends on how you want to use them. For the SQL statement that only executes, select Statement is the best. On the contrary, if the SQL statement is used multiple times PreparedStatement is the best.
The first execution consumption of PreparedStatement is very high. Its performance is reflected in the rearuption. For example, it is assumed that I use the Employee ID to perform a query for the Employee table in a way. JDBC driver sends a network request To data parsing and optimizing this query. Another network request is executed when executed. In JDBC drivers, reduce network communication is the ultimate purpose. If my program only needs a request during operation, then use statement. For statement. For Statement. For statement. The same query will only generate communication to the database.
In the case of using the PreparedState pond, this guideline is a bit complicated. When using a PREPAREDSTATEMENT pool, if a query is very special and it is not performed again, then STATEMENT can be used. If a query is rarely executed, but connected The Statement pool in the pool may be executed again, then use preparedStatement. In the same case of a Statement pool, use Statement. Use preparedStatement's BATCH function
When Update is a large amount of data, the first prepare is executed multiple times, causing many network connections. To reduce the number of JDBC's calls to improve performance, you can use preparedStatement's addbatch () method to send multiple queries at once a one-time query Database. For example, let us compare the following example.
Example 1: Multiple execution prepared statement
PreparedState ps = conn.preparestatement ("Insert INTO Employees VALUES (?,?,?)"); For (n = 0; n <100; n ) {ps.setstring (name [n]); ps.setlong (ID [n]); ps.setint (Salary [n]); ps.executeUpdate ();
Example 2: Using BATCH
PreparedState ps = conn.preparestatement ("Insert INTO Employees VALUES (?,?,?)"); For (n = 0; n <100; n ) {ps.setstring (name [n]); ps.setlong (ID [n]); ps.setint (Salary [n]); ps.addbatch ();} ps.executebatch ();
In Example 1, PreparedStatement was used to execute the Insert statement multiple times. Here, 100 Insert operations were performed, a total of 101 network round trips. Among them, a round trip is pre-stored Statement, and each 30 rounds will perform each iteration. In Example 2, only two network round trips were used in 100 INSERT operations. One round trip is pre-stored Statement, and the other is to execute the BATCH command. Although the Batch command will use more databases CPU cycle, but by reducing network round trip, performance is improved. Remember, the maximum performance of JDBC is to reduce network communication between JDBC drivers and databases.
Select the appropriate cursor type
Choose the applicable cursor type to maximize your application. This section focuses on the performance issues of three cursor types. For the case of reading all records in a table, the Forward-Only type cursor is provided. The best performance. When you get the data in the table, no method is faster than using the Forward-Only type cursor. But no matter what, this cursor cannot be processed in the program without sequence, this cursor cannot be used.
For the program, the data synchronization with the database is to be able to move the cursor before and after the result set, using the JDBC's scroll-INSENSITIVE type cursor is ideal. This type of cursor acquires all the data in the first request ( When the JDBC driver acquires data, it may be a lot of instead of full data) and store it on the client. Therefore, the first request will be very slow, especially when requesting long data. The next request does not cause any network round trip (when using the '' Lazy 'method), it is only limited network traffic) and processes very fast. Because the first request is very slow, the scroll-insensitive type cursor should not Used in a single line of data. When there is a returning long data, developers should also avoid using the scroll-insensitive cursor, as this may cause internal deposits. Some scroll-INSENSITIVE type cursors are implemented in the database The cache data in the temporary table avoids performance issues, but most of the data can be cached in the application. Scroll-sensitive type cursor, sometimes known as the KeySet-Driven cursor, use the identifier, the root like the database. When the result set moves the cursor, the identifier will be retrieved. Because each request will have a network round trip, performance may be very slow. No matter how, the improvement of the results of the rebound of unordered mode is not helpful. .
Now explain this, come see this situation. A program returns to 1000 row data normally. When executed or the first line is requested, the JDBC driver does not execute the SELECT statement provided by the program. On the contrary, it You will replace SELECT queries with a key identifier, for example, RowID. Then modified queries will be executed by the driver, follow the database to get all 1000 key values from the database. Every time the request for a row, the JDBC driver will be directly from the local The corresponding key value is found in the cache, and then constructs a '' WHERE ROWID =? The optimization query of '' clauses, then perform this modified query, and finally obtain the data line from the server.
The scroll-sensitive cursor can be replaced with a dynamic scrollabable cursor as the Scroll-Insensitive type cursor is available.
Effective getter method
JDBC provides a variety of ways to get data from the ResultSet, like getint (), getString (), and getObject (), and so on. The getObject () method is the most generalized, providing the worst performance. This is because the JDBC driver must make additional processing to map to specific objects to a specific object. So use the appropriate method for a particular data type.
To improve performance, the index number of fields should be provided when data is obtained, for example, getString (1), getlong (2), and getint (3), etc., replace the field name. If you do not specify the specified field index number, network traffic Not affected, but will increase the cost of conversion and lookups. For example, suppose you use getString ("foo") ... JDBC driver may turn the field name to uppercase (if needed), and list in the field name list By comparison to find the "foo" field. If you can, use the field index directly, save you a lot of processing time.
For example, suppose you have a 100 row of 15 columns of ResultSet, the field name is not included. You are interested in Employeename (string), Employeenumber (long integer), and Salary. If You specify GetString ("Employeename"), Getlong ("Employeenumber"), and getInt ("Salary"), check each field name must be converted to Metadata in the corresponding case, and then look for. If you use GetString (1), getlong (2), and getint (15). Performance will have significant improvement.
Get automatically generated key values
There are many databases that provide a hidden list of records in the table allocated a unique key value. Very typical, using these field types in the query is the fastest way to get the record value, because these hidden columns usually react data Physical location on the disk. Before JDBC3.0, the application can only get the value of the implicit column after being executed immediately after inserting the data. For example: // Insert INT ROWCOUNT = Stmt.executeUpdate ("INSERT INTO LocalgeniusList (Name) VALUES ('Karen') "); // Today is a disk location for newly inserted rows - RowidResultSet RS = Stmt.executeQuery (" SELECT ROWID from localgeniuslist where name = '' karen '');
This way to get hidden columns has two main disadvantages. First, the acquisition of the hidden column is in a separate query, it is necessary to send it to the server through the network. Second, because it is not a primary key, query condition May not be the uniqueness ID in the table. In the later example, multiple hidden columns may be returned, and the program cannot know which is the value of the last inserted row.
(Translator: Due to the different degrees of different database support, the way to return ROWID has different differences. In SQL Server, return the last inserted record that can be used in such query statement: select @IDENTITY)
An optional feature in the JDBC3.0 specification provides a capability that allows you to get an automatically generated key value just inserted into the record.
For example: int rowcount = stmt.executeUpdate ( "insert into LocalGeniusList (name) values ( '' Karen '')", // return the key and insert rows Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys (); // Get the generated key value
Now, a program contains a uniqueness ID, which can be used as a query condition to quickly access data rows, or even there is no primary key in the table.
This method of acquiring automatically generated key provides flexibility to the developers of JDBC and enhances the performance of access data.
Select the appropriate data type
Receiving and sending some data may cost expensive. When you design a schema, you should select the data type that can be processed most efficiently. For example, the integer is faster than floating point numbers or real numbers. Floating point It is usually a compressed format according to the format specified in the database. The data must be decompressed and converted to another format so that it can be processed by the protocol of the data.
Get a Resultset
Since the database system supports the rolling cursor, many JDBC drivers do not implement a scrollable cursor. Unless you are confident that the database supports the result set of scrolling cursors, do not call rs.last () and RS.Getrow () methods. Find the maximum number of data sets. Because the JDBC driver simulates the scrollable cursor, call rs.last () caused the driver to move to the last line of the data set through the network. Reveared, you can use the resultset to traverse a count or The number of data lines is obtained with the COUNT function query by the SELECT query.
Typically, please do not write that code that relies on the results set row, because the driver must get all the datasets to know how much data will be returned.
