【一】關(guān)于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).
【二】僅僅獲取所需的數(shù)據(jù)
①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.
【三】檢索JDBC對象的方法
①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++) {
ps.setString(name[n]);
ps.setLong(id[n]);
ps.setInt(salary[n]);
ps.addBatch();
}
ps.executeBatch();
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.
備注：
此
游標(biāo)策略就是Hibernate中的1+N次查詢。即先不執(zhí)行Select *，而是內(nèi)部偷偷替換成Select id,
rowid，得到一批ID后緩存到本地。如果程序不是連續(xù)訪問，而是跳躍式訪問、隨機式訪問的話。可以在游標(biāo)指向需要的記錄時才根據(jù)該ID/Rowid發(fā)
出一個Select *.... where rowid = ?這樣的SQL語句。
這樣在客戶端的緩存就將只存儲一批Id而已，可以大大的減輕程序的負擔(dān)。但是在最糟糕的情況下他會發(fā)出N條子查詢，反而可能加重系統(tǒng)負擔(dān)
⑤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')",
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys (); // key is automatically available
-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。
posted on 2010-03-19 23:11
Paul Lin 閱讀(573)
評論(0) 編輯 收藏 所屬分類:
J2SE