18
while( result_set.next() )
{
  ...
}

I have use System.nanoTime() and calculated the time, for each iteration the time taken is in milliseconds but the overall loop takes about 16s. I am considering a possible reason that the condition test is taking a lot of time, the next() function.

FYI I am connecting to a remote database server and the select query that I make is completed in milliseconds again calculated using the above mentioned method. Any reasons about why it's happening and how I can bring the time to iterate the resultset down to at max a second?

EDIT:

I am dealing with about 4000 records and each record contians about 10 columns each having a size of about 10 chars

EDIT2 Thanks setFetchsize() did the magic, awesome, awesome

hershey92
  • 783
  • 2
  • 6
  • 17
  • 1
    How many records do you think you have? – Duncan Jones Jul 19 '13 at 10:40
  • 1
    How many records does the result set contains? Also, it will be helpful to understand if the whole code of while loop is provided. – Mubin Jul 19 '13 at 10:42
  • Either you have a lot of records or you have a slow network. Don't blame JDBC here. – Kayaman Jul 19 '13 at 10:43
  • Profile the ... too. Check for logging on database level. – Joop Eggen Jul 19 '13 at 10:49
  • 1
    What is your `fetchSize` set to? If you haven't explicitly called `preparedStatement.setFetchSize()` you may have a small default; the difference between 4000 fetches of one row at a time, and a single fetch of 4000 rows in one go, could be considerable. Something in between is probably more appropriate though, to avoid using too many resources - maybe 100? That doesn't affect how you handle the result set, just what it's doing in the background. – Alex Poole Jul 19 '13 at 10:49
  • 2
    4000 iterations can take any amount of time from near zero to eternity, depending on what you do in the loop. – Jannis Alexakis Jul 19 '13 at 10:50

2 Answers2

36

I have set up a table with 4000 rows and 10 columns with 10 characters each and made a simple performance test using the following approach (RealTimeCounter is a class which measures the real time between start() and stop()):

List<String> myResult = new ArrayList<>();
ResultSet rs = s.executeQuery("SELECT * FROM Performance");

RealTimeCounter rtc = new RealTimeCounter();
rtc.start();
while(rs.next()) {
    myResult.add(rs.getString(1));
}
rtc.stop();
System.out.println(rtc);

Results:

  • Default fetch size: execution time is approx. 20 sec
  • fetch size = 100: execution time is approx 2.2 sec
  • fetch size = 500: execution time is approx 450 msec
  • fetch size = 2000: execution time is approx 120 msec
  • fetch size = 4000: execution time is approx 50 msec
  • fetch size = 4001: execution time is approx 10 msec (!!)

So, the fetch size does have a significant impact on the execution speed.

Note that, on the other hand, the fetch size has some impact on memory consumption. Interestingly enough, a quick analysis using Runtime.getRuntime().freeMemory(); before and after the above code showed that the impact is much less than I would expect, though. The numbers I got are:

  • Default fetch size: 665k
  • fetch size = 100: 665k
  • fetch size = 500: 665k
  • fetch size = 2000: 743k
  • fetch size = 4000: 821k
  • fetch size = 4001: 861k
Andreas Fester
  • 36,091
  • 7
  • 95
  • 123
  • It's worth mentioning that while increasing fetch size often improves execution speed, it also causes your program to use more memory - you are ultimately making a tradeoff. No free lunches here. – John Roberts Nov 06 '15 at 21:15
  • @JohnRoberts good point - interestingly enough, a quick and dirty analysis of memory consumption showed that the impact is much less than I would expect, though ... – Andreas Fester Nov 09 '15 at 06:48
27

Try to change ResultSet fetch size. By default, Oracle JDBC driver receives the result set only 10 rows at a time from the database cursor. It may not significantly improve performance but there arent many other options to make driver work faster.

Evgeniy Dorofeev
  • 133,369
  • 30
  • 199
  • 275
  • 1
    `I dont think this will significantly improve performance` - well, it does :-) see my answer below. Anyway +1 for correct and good answer – Andreas Fester Jul 19 '13 at 11:15