In J2EE applications, we often access enterprise resources through JDBC. However, if JDBC is not used properly, the system performance will be affected. This article is written by referring to performance tips for the data tier (JDBC) by John Goodson. It is helpful for our development.
This article describes the following four parts:
L use the metadata method of the database as appropriate
L retrieve the required data
L select the performance optimization function
L manage connections and data updates
1. Use the metadata method of the database as appropriate
1.1. use as little metadata as possible
The execution speed of the metadata method is slow, so try to use the metadata method as little as possible. Because calling the metadata method to generate a result set requires a lot of overhead, the result set generated by the metadata method should be cached instead of executing the query multiple times, which can provide JDBC performance. For example, if you call gettypeinfo once in an application, you should cache the result set and use it again.
1.2. Avoid query mode
A null parameter or query mode is provided for metadata, which can produce time-consuming queries. At the same time, because some unnecessary data is transmitted over the network, the network traffic increases and the performance of the entire system is reduced. The execution of the metadata method is slow, so try to provide it with a non-null parameter and efficiently call it. Our applications often see the following phenomenon:
Resultset wsrs = WSC. gettables (null, null, "wstable", null );
It should be changed:
Resultset wsrs = WSC. gettables ("cat1", "johng", "wstable", "table ");
Obviously, in the first gettables () call, the application may need to know whether the wstable table exists. Of course, the JDBC driver's literal call is different from the resolution request. JDBC parses the request as follows: returns all tables named "wstable", views, system tables, synonyms, zero-time tables, or an alias that exists in the Database mode in any database directory.
The second call to gettables () more accurately reflects what the application needs to know. JDBC parses this request as follows: returns all tables named "wstable" that exist in the same directory as those in the "johng" mode. Obviously, the JDBC driver is more effective in processing the second request than in processing the first request.
The more information you provide to the metadata method, the higher the accuracy and performance of the information you obtain.
1.3. Use dummy meta queries to determine table features
Avoid using getcolumns () to determine the features of a table. Replace it with getmedata () dummy meta query. Consider an application that allows users to select columns. Should the application use getcolumns () to return the user column information or prepare a dummy meta query and call getmetadata?
Case 1: getcolumns Method
Resultset wsrc = WSC. getcolumns (... "unknownable "...); // This call to getcolumns () will generate a query to // The system catalogs... possibly a join // which must be prepared, executed, and produce // a result set... wsrc. next (); string cname = getstring (4 );... // user must retrieve n rows from the server // n = # result columns of unknownable // result column information has now been obtained
Case 2: getmetadata Method
// Prepare dummy querypreparedstatement WSPs = WSC. preparestatement ("select * From unknownable where 1 = 0"); // query is never executed on the server-only preparedresultsetmetadata wssmd = WSPs. getmetadata (); int numcols = wsrsmd. getcolumncount (); ...int ctype = wsrsmd. getcolumntype (n )... // result column information has now been obtained
In both cases, the query is sent to the server. However, in Case 1, the query must be prepared and executed, the result description must be concise, and the result set must be sent to the client. In Case 2, a simple query must be prepared and only the result description information is briefly described. Obviously, scenario 2 is a better performance model.
This discussion is somewhat complicated. Let's consider a database that does not support local preparation of SQL statements. The performance of scenario 1 has not changed, but because the dummy query must be evaluated rather than just prepared, the performance of Scenario 2 has increased slightly. Because the WHERE clause of a query statement always calculates false results, the query does not generate result rows and does not access the execution of table data. In this case, method 2 is better than method 1.
In short, table column information, such as column names, column data types, column precision, and Numerical range, is always retrieved using result set metadata. When the requested information cannot be obtained from the result record set (for example, the default value of a table column), only the getcolumns () method is used.
2. Retrieve the required data
2.1. Retrieve long data
Unless necessary, retrieval of long data results in tight network resources and reduces performance. Generally, most users do not need to view long data. If you need to view the data, the application then retrieves it.
The following code appears in our code: Select * from <Table Name>... If the selected table contains long data columns, the query performance will be very poor. Besides, do you need all the data items in the table? If you don't need them, why do you need them to be transmitted over the network, wasting network resources?
For example, check the following JDBC code:
Resultset rs = stmt.exe cutequery ("select * from employees where SSID = '2017-99-2222 '"); RS. Next (); string name = Rs. getstring (4 );
JDBC is not smart. When you write code like this, it doesn't even know the columns you actually need. It makes sense to return all the columns, therefore, during development, you need to specify the required columns in the SELECT statement. If the Employees table contains long data fields such as photos, the system performance would be quite low.
Although the methods getclob () and getblod () support long data field retrieval, not every database supports it. So remember: Read it when long data is needed.
2.2. Reduce the size of Retrieved Data
Sometimes, long data must be retrieved. In this case, most users may not need to view the 100 K (or more) body on the screen. To reduce network traffic and improve performance, you can call setmaxrows (), setmaxfieldsize (), and the driver-related setfetchsize () to reduce the retrieved data size to a manageable range. Another way to reduce the retrieved data size is to reduce the number of columns. If the driver allows you to define the package size, the minimum package size will meet your needs.
Remember: only the required rows and columns are returned. If you return five columns and you only need two columns, the performance will be reduced ?? In particular, unnecessary results contain long data.
2.3. Select the correct data type
The overhead of retrieving and sending a certain data type is very expensive. When designing the database mode, select the data type that can be most effectively processed. For example, integer data is faster than floating point data or decimal data. Floating Point Numbers are defined according to the special format of the database, usually the compression format. In order to be processed by the database communication protocol, the data must be decompressed and then converted to a different format.
2.4. Retrieve Record Sets
Due to the limited support of the database system for scroll cursors, most JDBC drivers cannot implement scroll cursors. Do not call Rs. Last () and Rs. getrow () to obtain the number of rows in the record set unless you are sure that the database supports a rolling record set (for example, RS. For the JDBC driver that imitates the scroll cursor, calling Rs. Last () will cause the driver to retrieve all the data through the network for the last row. You can use the record set to enumerate the number of record rows, or submit a query with a count column in the SELECT statement to obtain the number of rows.
Generally, do not write code that depends on the number of rows in the record set. To obtain the number of rows, the driver must read all rows in the record set.
