• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC Retrieval of rows from an index

 
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic