Result Sets | SQL Performance Tuning

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.

Table 13-6. Recommended Matches: Java Methods and SQL/JDBC Data Types

Java Method	SQL/JDBC Data Type
getByte	TINYINT
getShort	SMALLINT
getInt	INTEGER
getLong	BIGINT
getFloat	REAL
getDouble	FLOAT, DOUBLE
getBigDecimal	DECIMAL, NUMERIC
getBoolean	BIT
getString	CHAR, VARCHAR (presumably Unicode)
getCharacterStream	LONGVARCHAR
getBytes	BINARY, VARBINARY
getDate	DATE
getTime	TIME
getTimestamp	TIMESTAMP
none	INTERVAL
getAsciiStream	LONGVARCHAR
getBinaryStream	LONGVARBINARY
getClob	CLOB
getBlob	BLOB
getArray	ARRAY
getRef	REF
getObject	UDT, STRUCT, JAVA_OBJECT

 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 Statement s, 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(*) .

JDBC versus ODBC

There are many similarities between JDBC and ODBC, partly because both follow X/Open standards, and partly because the makers of JDBC were able to learn from the ODBC experience when they started designing. We have observed a few times in this chapter that such-and-such a JDBC method "does the same thing as" such-and-such an ODBC functionright down to use of the same field names or constant values. The differences can usually be attributable to exigencies of the languagefor example, ODBC uses pointers a lot whereas JDBC has provisions for objects.

We do see a tendency in JDBC to have several functions where ODBC has only one. For example, one ODBC function for "how NULLs sort" returns four mutually exclusive values. Meanwhile, JDBC requires four functions for "how NULLs sort "; all return true / false .

We also see that JDBC is lacking in some functionality that ODBC possesses. There are two noticeable areas:

In ODBC, there is a thoroughly defined hierarchy:
```
 env> dbc> stmt> desc 
```
(see Figure 12-1 in Chapter 12, "ODBC").

In JDBC, there is only:
```
 dbc> stmt 
```
(or Connection and Statement to use the JDBC terms). This was the situation in ODBC 1.0, but the more elaborate structure had to be adopted to accommodate users' needs.
There are tiny but baffling lacunae in the JDBC metadata functions. For example, we've already mentioned the lack of a row count option for queries. For another example, we note the curious absence of a method named getSchemaSeparator to correspond to getCatalog Separator . (ODBC has separate functions for returning the separator characters that one puts between identifiers in fully qualified identifiers such as Catalog1.Schema1.Table1 .)

Even if you plan to program with JDBC alone, you'll still benefit from a perusal of the ODBC documentation or the standard SQL/CLI specification. It's not necessarily better, but it is put differently, and what's said about ODBC can often be applied to JDBC.

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 Statement s 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.