A new article series, by John Goodson, presents some general guidelines for improving JDBC application performance that have been compiled by examining the implementations of numerous shipping JDBC applications. Part 1 looks at how to minimize the use of database metadata methods, avoid search patterns, and how to use a dummy query to determine table characteristics.

Read Performance Tips for the Data Tier (JDBC) - Part 1: Using Database MetaData Methods Appropriately

Also check out John Goodson's Tech Talk on Database Access Technologies

In this article.. the code snippet give for getting metaData is valid for JDBC 2.0, What about JDK 1.1.8 and the corresponding JDBC? PreparedStatement.getMetaData() method is not available in previous versions.

Code snippet from the article:
// 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


I'm stuck at JDK 1.1.8 and use something similar to the above.
Here is my code.

// prepare dummy query
PreparedStatement WSps = WSc.prepareStatement
  ("SELECT * from UnknownTable WHERE 1 = 0");
// query is never executed on the server - only prepared
ResultSet rs = WSc.executeQuery(); //Needed for JDK 1.1.8
ResultSetMetaData WSsmd= rs.getMetaData(); //Needed for JDK 1.1.8
int numcols = WSrsmd.getColumnCount();
...
int ctype = WSrsmd.getColumnType(n)
...
// result column information has now been obtained

The problem:I use JDK 1.1.8 and my view builds on some huge tables, Inspite of the where clause having "1 = 0", the DataBase does full table scans on the underlying tables. Now these tables have a couple of million rows each. The work around ofcourse is to send some limiting join id, but the code is for a frame work and not all views will have ID's that can be passed around.

Any why to speed things up in the above case?
Thank You,
Chris

Java/JDBC doesn't have anything to do with the execution plan for a query - thats determined by the database. Which database are you using? Most databases will see the WHERE 1 = 0 in the where clause and return immediately without performing a full table scan. I just tried it here on Oracle 8.1.6.

If you're using a database that actually performs the full table scan, then you need to formulate your query differently. For example, you could take the column in your table which is the Primary Key, and test for a known non-existent value. Say your Primary Key values are all positive numbers, then your where clause could look like this:

WHERE PRIMARY_KEY_COLUMN

Appears to be no way to edit a reply here.

That last where clause should read:

WHERE PRIMARY_KEY_COLUMN = -1

Hi Stephen,

Thank you for your reply.

I am using Oracle 8.x

The problem is at the point where I call this method, I do not have the primaryKey. This is part of a frame work code that builds a dataObject for use by the View.

It works well for most cases, except when the view is built on a bunch of tables with some complex joins. There is no way of specifying a where clause on the primarykey as different views using this code have different keys.

Did you test this code with JDK 1.1.8?

In JDK 2.0 and above, there is no need to execute the prepared statement. But with lower versions you have to get the metaData object form the resultSet, not from the prepared statement.

Thank you,
Chris

Hi Stephen Halko,
You are right
"Java/JDBC doesn't have anything to do with the execution plan for a query - thats determined by the database. Which database are you using? Most databases will see the WHERE 1 = 0 in the where clause and return immediately without performing a full table scan. I just tried it here on Oracle 8.1.6."

I agree.. this is what I assumed when I coded the class. Its been 2 years now and we hit a performance bottleneck now with a table with 2 million rows.

The only reason I have is that since I'm not passing a join condition variable with the query, Oracle does a full table scan of the underlying Views/Tables.

Thank You,
Chris

To John Goodson,

In the Metadata article

"Although almost no JDBC application can be written without database metadata methods".

This seems like a sweeping generalization. I'm not going to take issue with it because I'm not too sure what the reasoning is - but I have rarely seen metadata used or needed to be used, except in management applications.

I presume I must have missed something?