Using a Dummy Query to Determine Table Characteristics
Avoid using the getColumns() method to determine characteristics about a database table. Instead, use a dummy query with getMetadata().
Consider an application that allows the user to choose the columns to be selected. Should the application use getColumns() to return information about the columns to the user or instead prepare a dummy query and call getMetadata()?
Case 1: GetColumns() Method
ResultSet WSrc = WSc.getColumns(... "UnknownTable" ...);
// 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 return N rows from the server
// N = # result columns of UnknownTable
// result column information has now been obtained
Case 2: GetMetadata() Method
// prepare dummy query
PreparedStatement WSps = WSc.prepareStatement
("SELECT * FROM UnknownTable WHERE 1 = 0");
// query is never executed on the server - only prepared
ResultSetMetaData WSsmd=WSps.getMetaData();
int numcols = WSrsmd.getColumnCount();
...
int ctype = WSrsmd.getColumnType(n)
...
// result column information has now been obtained
// Note we also know the column ordering within the
// table! This information cannot be
// assumed from the getColumns example.
In both cases, a query is sent to the server. However, in Case 1, the potentially complex query must be prepared and executed, result description information must be formulated, and a result set of rows must be sent to the client. In Case 2, we prepare a simple query where we only return result set information. Clearly, Case 2 is the better performing model.
To somewhat complicate this discussion, let us consider a DBMS server that does not natively support preparing a SQL statement. The performance of Case 1 does not change but the performance of Case 2 improves slightly because the dummy query must be evaluated in addition to being prepared. Because the Where clause of the query always evaluates to FALSE, the query generates no result rows and should execute without accessing table data. For this situation, Case 2 still outperforms Case 1.
In summary, always use result set metadata to return table column information, such as column names, column data types, and column precision and scale. Only use the getColumns() method when the requested information cannot be obtained from result set metadata (for example, using the table column default values).
