aspose file tools*
The moose likes JDBC and the fly likes JDBC Retrieval of rows from an index Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Retrieval of rows from an index" Watch "JDBC Retrieval of rows from an index" New topic
Author

JDBC Retrieval of rows from an index

Avianu Sud
Ranch Hand

Joined: Jan 20, 2002
Posts: 55
I have a query returning 20,000 rows.
When I loop through the result set it take 13 seconds to store the results in a hash (See code snippet below)

while(resultSet.next()) {
int countResult = resultSet.getInt("COUNT");
Object groupFieldResult = resultSet.getObject(1);
if(groupFieldResult!=null) {
if(groupFieldResult instanceof Date) {
Date date = (Date)groupFieldResult;
results.put(date.toString(), new Integer(countResult));

Q1. Is there a way to reduce the time in traversing through these number of records?

In the meantime, As I only need 500 Rows at a time, so I set the MaxRows to Fetch (see below)

stmt.setMaxRows(500);
stmt.setFetchSize(500);

Now this works perfect for the 1st 500 rows.
Q2. The issue is that how do we get the next 500 rows quickly using JDBC?

I can implement some custom cashing strategy, but a JDBC solution will be more cleaner and simpler.

Please share your thoughts.
Regards, Avi
[ November 22, 2005: Message edited by: Bear Bibeault ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

Avianu,
A few things that may improve your performance slightly:
1) Use resultSet.getInt(columnNumber) instead of resultSet.getInt("COUNT"). Some drivers perform faster when you use the index.
2) Call resultSet.getDate(1) instead of resultSet.getObject(1). You know what data type is in column one from the query. So the instanceof check and cast are redundant. They also reduce the clarity of the code.
3) Try getting column one (the date) before column 2 (the count). There are still a few drivers that perform better if you get the columns in order.

Can you post the SQL query? That may lead to additional optimizations that have a greater effect.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

Q2. The issue is that how do we get the next 500 rows quickly using JDBC?

If you have a key you can sort by, you can store where you were up to. Then on the next query, start from there.

Some databases (like Oracle) offer a way to only retrive certain row numbers. What database are you using?
Avianu Sud
Ranch Hand

Joined: Jan 20, 2002
Posts: 55
The query itself runs very fast (< 1sec). Most of the time is in the while loop traversal of the result set, and storing in HashMap. Is it typical for 20,000 rows to take as much as 13 sec (with light load on the system).

Retrieving chunks of rows will be more effective I think. I am using oracle. Do share your ideas on retrieving rows in chunks from the database or from the result set.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

Avianu,
It's good that the query runs fast. That's the most important thing! The speed depends on your hardware. 20,000 rows in 13 seconds sounds high unless you are returning a lot of columns.

Take a look at this Ask Tom column to see how to limit your query to the X through Y rows.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Retrieval of rows from an index