SQL Performance Tuning [Electronic resources] نسخه متنی

Result Sets

That's enough about query prepping. Let's move on. Assume that you have executed a query, and it worked. So now you have a result set. That is, the query returned a bunch of rows and now you can fetch them.

But how many rows do you have, and how should you fetch them, and what will you do when it's over?

How Many Rows?

A common question isHow many rows are in the result set? It's a reasonable question: users care, it affects the scroll bar of a display, and it determines whether you have enough buffer space for an insensitive cursor. Unfortunately, there is no easy way to ask the question. That's a shame, because it's possible the DBMS might have the answer (for proof check out the ODBC function SQLGetDiagField, which has an option for returning the query's row count).

Here are three ways to determine the size of the result set:

If you are going to update the result set, try:

int i = executeUpdate();

The count returned by executeUpdate should be the number of rows that were updated, although (unfortunately) that might also include the number of rows affected by triggers or by foreign keys with ON UPDATE CASCADE clauses.

If you have a scroll cursor, try scrolling from back to front. The advantage of this method is that you can get the number of rows after your first fetch, like this:

ResultSet rs = stmt.executeQuery(
"SELECT column1 FROM Table1
ORDER BY column1 DESC");
if (rs.last()) {               // get last row
int RowCount = rs.getRow(); // get number of last row
System.out.println(
"RowCount=" RowCount);
System.out.println(
rs.getString(1));
while (rs.previous()) {
System.out.println(
rs.getString(1));  // print in ASC order
}
}

When all else fails, resort to SELECT COUNT(*). You can do the count inside a subquery in the main search (which guarantees that the count will be correct), or you can execute a separate query to do the count before you process the main query.

getXXX methods

The getXXX methods (getFloat, getDate, etc.) provide the primary way to retrieve data from the database. (The other way is to pass OUT variables to stored procedures but that's more trouble.) For example, after you have executed a query and thus acquired a result set, you could do something like this:

boolean b = rs.next();      // fetch the first row
if (b) {
int c1 = rs.getInt(
"column1"); }    // get contents for column1

Ideally, column1 should be an SQL INTEGER column. (You can check the column's defined data type by looking at the rsmd.) There is a close correspondence between an SQL INTEGER and a Java int so there is low impedance in such a case. (The impedance analogy is to an electrical circuit that contains two joining wires. If both wires are copper, there is no impedance. When a copper wire meets an aluminum wire, there is impedance.) There doesn't have to be impedance with Java and SQL, because almost all the data types do correspond. You just have to make sure to use the right getXXX method for the SQL data type. Table 13-6 shows the best matches.

The fastest conversion (per byte of data retrieved) is CHAR, then comes INTEGER, then FLOAT and TIMESTAMP … with STRUCT and JAVA_OBJECT bringing up the rear. The worst-performing method is getObject, which returns a Java Object instead of a specific type.

Impedance

Let's see how impedance can arise with specific methods.

getString

The magic word in Table 13-6 is "Unicode"Java strings have it, SQL CHAR values probably don't (but NCHAR values often do). This means there will be conversions from some 8-bit character set to the Unicode 16-bit representation. This conversion is automatic, and it can be slow. It is, in any case, a potential source of error if a string is represented two different ways depending on whether it's found in an application or in the server code. So everyone will cheer on the day when all DBMS data is in Unicode. But today there are still two major impediments: (a) the size of all CHAR and VARCHAR columns doubles and (b) the DBMS is also accessed by other, non-Java, clients. At the moment, because the conversion work only slows the driver and not the server, the conversion to Unicode is a noble but non-urgent goal.

getShort

Because everybody except Oracle stores a SMALLINT as a 16-bit (short) signed integer, impedance should be nil. But there is a technical hitch: The number -32768 is legal according to Java but not according to ANSI. Similar hitches apply for all the integer data types. Not to worry: Most DBMSs ignore this detail. Only Informix forbids you to store -32768 in a SMALLINT.

getByte

The TINYINT data type is not standard SQL, and there could be a difficulty if it's an unsigned (range 0 to +255) byte rather than a signed (range -128 to +127) byte. Avoid negative TINYINTs.

getDate

Drivers using the JDBC-ODBC bridge are usually slow to convert DATE/TIME/TIMESTAMP columns because of a change in the rather confusing manner with which temporal data types are numbered. The matter is trivial as long as these data types are relatively little used.

getBoolean

Table 13-6 says this is appropriate for the BIT data type, but it's important to know that this means the ODBC BIT type, which is utterly different from the SQL Standard BIT. It has been found that the SQL Standard BOOLEAN data type is troublesome, particularly with PL/SQL stored procedures.

You can save a little bit of time with getXXX methods by following these suggestions.

Close

When you're done with a result set, it must be closedelse other transactions will be blocked. The explicit method call is:

rs.close();

You can skip rs.close() if the result set is closed automatically. A result set is closed automatically in these cases:

When COMMIT or ROLLBACK or DISCONNECT happens

When a new "execute" method call happens on the same Statement object, or a retrieval occurs on the next result set for the same Statement[3]

[3] Note that you cannot rely on this "auto close" in analogous ODBC situations.

In rare cases, with some DBMSs only, when UPDATE ... WHERE CURRENT OF <cursor> or DELETE ... WHERE CURRENT OF <cursor> are executed

Note: The definition of "automatic COMMIT" depends on the definition of "automatic close" so add these rules to what you need to memorize.

Why would you want to close early?

Because there are other Statements, and they either need resources or they use the rows in the result set (wasting time with sensitive cursors, etc.).

Because there are other users, and you're unwilling to block them any longer.

Why would you want not to explicitly close?

Because message passing is reduced if you let closing happen automatically.

We did a few tests on skipping rs.close() when we knew the result set should be closed automatically. Skipping made no significant difference. But we were using a fairly intelligent driver, and we know that other drivers would not be so intelligent.

The Bottom Line: Result Sets

To find out how big a result set is, try:

int i = executeUpdate();

Or, if you have a scroll cursor, try scrolling from back to front. When all else fails, resort to SELECT COUNT(*).

env> dbc> stmt> desc

dbc> stmt

There doesn't have to be impedance with Java and SQL, because almost all the data types do correspond. You just have to make sure to use the right getXXX method for the SQL data type.

The TINYINT data type is not standard SQL, and there could be a difficulty if it's an unsigned byte rather than a signed byte. Avoid negative TINYINTs.

Do getXXX methods in the order that they appear in the result set, and do them only once.

Use getXXX(<integer>) rather than getXXX("<column name>").

Use the wasNull method (which checks for indicators) ifand only if (a) the getXXX method returns a zero or blank value and (b) the column is nullable.

When you're done with a result set, it must be closedelse other transactions will be blocked. The explicit method call is rs.close().

You can skip rs.close() if the result set is closed automatically.

Close earlythat is, close explicitlywhen other Statements need resources or the rows in a result set, or when other users shouldn't be blocked any longer.

Close automaticallynot explicitlymost of the time, because message passing is reduced if you let closing happen automatically.