wood burning stoves 2.0*
The moose likes JDBC and the fly likes time taken in rs.next() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "time taken in rs.next()" Watch "time taken in rs.next()" New topic

time taken in rs.next()

anil bisht
Ranch Hand

Joined: Nov 04, 2000
Posts: 81
does anyone have idea how much time it takes for rs.next()
I have a code in which i am getting the resultset from the database and then putting the resultset into the arraylist.
To my wonder it took > 15 sec for converting the resultset into the arraylist. but database retrieval is fast.
then i tried using just
while (rs.next())
here also the time taken was same
The total number of records were 800 and each row had 4 columns.
and if i add the number of columns then it takes more time ... Is it maintaining the connection with the database after getting then result set? I created the statement with default parameters.
I am bit confused . can anyone help me ?
Joe Gilvary
Ranch Hand

Joined: May 11, 2001
Posts: 152
Most databases are ignorant of Java and store
data in characters or some binary form. You have to deal with object creation, string encoding,
etc. when you call the ResultSet.getXXX methods.
If you have the source for your JDBC classes, and
your license will allow it, you may be able to do
some performance tuning in there. But you would tie
your application to those particualar non standard libraries
as a result.
anil bisht
Ranch Hand

Joined: Nov 04, 2000
Posts: 81
hi Joe
but the problem is that i am not calling rs.getXXX()
i am just doing
while (rs.next())
thats it ..so should it take so much of time..
and i am using sybase driver..
Craig Demyanovich
Ranch Hand

Joined: Sep 25, 2000
Posts: 173
Implementation of ResultSet.next() is driver-dependent, even across different drivers for the same database. For example, one driver may hit the database for each call, while another may return data in larger chunks for fewer hits to the database. Information about the driver and the database may allow us to offer more insight.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

This is a perfect situation for a profiling tool. But if you do not have one handy or do not know how to use one, try this:

I selected 4 columns and 728 rows from a table in Oracle and my results were(sort of a benchmark to compare to):
95% of my rs.next() were 0. Approximately every tenth read (which is probably a fetch to the DB) took about 50 milliseconds. My total processing time is 1920 milliseconds.
try inserting this code into your problem after the executeQuery method to see how you compare on each read. Let us know your results.
anil bisht
Ranch Hand

Joined: Nov 04, 2000
Posts: 81
Thanks Jamie for that idea ..
here is a sample output of my program.. everything is unrelated
27. (201)
28. (0)
29. (0)
30. (0)
31. (0)
32. (190)
33. (0)
34. (0)
35. (220)
36. (0)
37. (0)
38. (0)
39. (0)
40. (140)
41. (0)
42. (0)
43. (30)
44. (271)
45. (0)
46. (0)
47. (0)
48. (0)
49. (190)
50. (0)
51. (0)
52. (70)
53. (270)
54. (0)
55. (0)
56. (0)
57. (0)
58. (41)
59. (0)
60. (280)
BTW i am using odbc driver and sybase as my database.
So does it means that the resultset dosent fetch all the rows in one go ??

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

It seems like your fetchSize is rather small for the amount of data you are processing. It appears that it is set around 5 or so. FetchSize is the number of records that the resultset returns from the database at one time when you are processing them. You may want to increase this number to reduce the number of calls to your database.
You can find out the fetch size by calling Statement.getFetchSize and set it by using Statement.setFetchSize(). The same methods can be used on the ResultSet. **NOTE- setFetchSize is only a hint, so the driver can ignore your request to change it.
Another performance tip would be to look into a type 4 driver(pure java) for Sybase. I believe they have a free one available for download. This should help you as well.
So does it means that the resultset dosent fetch all the rows in one go ??
Correct, it only fetches the amount of rows that is specified in the fetchSize.

jQuery in Action, 2nd edition
subject: time taken in rs.next()