posts - 262,  comments - 221,  trackbacks - 0
【一】关于ResultSet metadata的使用

Minimizing the Use of Database Metadata Methods
Compared to other JDBC methods, database metadata methods that generate ResultSet objects are relatively slow. Applications should cache information returned from result sets that generate database metadata methods so that multiple executions are not needed.

Avoiding Search Patterns
Using null arguments or search patterns in database metadata methods results in generating time-consuming queries. In addition, network traffic potentially increases due to unwanted results. Always supply as many non-null arguments to result sets that generate database metadata methods as possible.

Using a Dummy(虚拟) Query to Determine Table Characteristics
Avoid using getColumns() to determine characteristics about a table. Instead, use a dummy query with getMetadata().

ResultSet WSrc = WSc.getColumns (... "UnknownTable" ...);

PreparedStatement WSps = WSc.prepareStatement("SELECT * from UnknownTable WHERE 1 = 0");

In summary, always use result set metadata to retrieve table column information such as column names, column data types, and column precision and scale. Only use getColumns() when the requested information cannot be obtained from result set metadata (i.e. table column default values).


①Retrieving Long Data

Unless it is necessary, applications should not request long data because retrieving long data across a network is slow and resource-intensive.

②Reducing the Size of Data Retrieved
To reduce network traffic and improve performance, you can reduce the size of any data being retrieved to some manageable limit by calling setMaxRows(), setMaxFieldSize(), and the driver-specific SetFetchSize(). Another method of reducing the size of the data being retrieved is to decrease the column size. If the driver allows you to define the packet size, use the smallest packet size that will meet your needs.

③Choosing the Right Data Type
Retrieving and sending certain data types can be expensive. When you design a schema, select the data type that can be processed most efficiently.

④Retrieving Result Sets
Most JDBC drivers cannot implement scrollable cursors because of limited support for scrollable cursors in the database system. Unless you are certain that the database supports using a scrollable result set, (for example, rs), do not call rs.last() and rs.getRow() methods to find out how many rows the result set has. For JDBC drivers that emulate scrollable cursors, calling rs.last() results in the driver retrieving all results across the network to reach the last row. Instead, you can either count the rows by iterating through the result set or get the number of rows by submitting a query with a COUNT column in the SELECT clause.


①Using Parameter Markers as Arguments to Stored Procedures
The database server would parse the SQL query, consult database metadata to determine the parameter contract of the procedure, isolate the single argument value 12345, then convert the string ‘12345’ into an integer value before finally executing the procedure as a SQL language event.

By invoking an RPC inside the database server, the overhead of using a SQL character string is avoided. Instead, a JDBC driver will construct a network packet that contains the parameters in their native data type formats and execute the procedure remotely.

②Using the Statement Object Instead of the PreparedStatement Object
The Statement object is optimized for a single execution of a SQL statement. In contrast, the PreparedStatement object is optimized for SQL statements that will be executed two or more times.

The overhead for the initial execution of a PreparedStatement object is high. A JDBC driver will most likely process the prepare request by making a network request to the database server to parse and optimize the query. The execute then results in another network request.

If my application will only make a request like this once during the life of the application, then use the Statement object. With a Statement object, the same query execution will result in only a single network roundtrip to the database server.

if a query is ad-hoc and will likely never be executed again, then use the Statement object. If a query will be executed infrequently, but may be executed again during the life of a statement pool inside a connection pool, then use a PreparedStatement. Under the same circumstances without statement pooling, use the Statement object.

③Using Batches Instead of Prepared Statements
Updating large amounts of data typically is done by preparing an INSERT statement and executing that statement multiple times, resulting in numerous network roundtrips. To reduce the number of JDBC calls and improve performance, you can send multiple queries to the database at a time using the addBatch() method of the PreparedStatement object.

PreparedStatement ps = conn.prepareStatement(
   "INSERT into employees values (?, ?, ?)");

for (n = 0; n < 100; n++) {


Remember that the biggest gains in performance with JDBC drivers is found by reducing network communication between the JDBC driver and the database server.

④Choosing the Right Cursor
A forward-only cursor provides excellent performance for sequential reads of all of the rows in a table.

Insensitive cursors used by JDBC drivers are ideal for applications that require high levels of concurrency on the database server and require the ability to scroll forwards and backwards through result sets. The first request to an insensitive cursor fetches all (or many but not all rows when a JDBC driver using “lazy” fetching) of the rows and stores them on the client. Thus, the first request is very slow, especially when long data is retrieved. Subsequent requests do not require any network traffic (or limited network traffic when a driver uses lazy fetching) and are processed quickly.

Because the first request is processed slowly, insensitive cursors should not be used for a single request of one row. Developers should also avoid using insensitive cursors when long data is returned, because memory can be exhausted.

Sensitive cursors, sometimes called keyset-driven cursors, use identifiers, such as a ROWID. At execute time or when the first row is requested, a JDBC driver would not execute the SELECT statement that was provided by the application. Instead, the JDBC driver would replace the SELECT list of the query with the key identifier, for example, ROWID.

Each request from the application for a result row directs the JDBC driver to look up the key value for the appropriate row in it’s local cache, construct an optimized query that contains a WHERE clause similar to ‘WHERE ROWID = ?’, execute the modified query, and then retrieve the single result row from the server.


此 游标策略就是Hibernate中的1+N次查询。即先不执行Select *,而是内部偷偷替换成Select id, rowid,得到一批ID后缓存到本地。如果程序不是连续访问,而是跳跃式访问、随机式访问的话。可以在游标指向需要的记录时才根据该ID/Rowid发 出一个Select *.... where rowid = ?这样的SQL语句。


⑤Using get Methods Effectively
The getObject() method is the most generic and provides the worst performance when the non-default mappings are specified. This is because the JDBC driver must do extra processing to determine the type of the value being retrieved and generate the appropriate mapping. Always use the specific method for the data type.

To further improve performance, provide the column number of the column being retrieved, for example, getString(1), getLong(2), and getInt(3), instead of the column name. If column numbers are not specified, network traffic is unaffected, but costly conversions and lookups increase.

To further improve performance, provide the column number of the column being retrieved, for example, getString(1), getLong(2), and getInt(3), instead of the column name. If column numbers are not specified, network traffic is unaffected, but costly conversions and lookups increase.

⑥Retrieving Auto-Generated Keys
An optional feature of the JDBC 3.0 specification is the ability to retrieve auto-generated key information for a row when the row is inserted into a table.

For example:
int rowcount = stmt.executeUpdate ("insert into LocalGeniusList (name) values ('Karen')",
ResultSet rs = stmt.getGeneratedKeys (); // key is automatically available

posted on 2010-03-19 23:11 Paul Lin 阅读(553) 评论(0)  编辑  收藏 所属分类: J2SE