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.
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?
Joined: Jan 20, 2002
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.