Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC ResultSet Performance

 
Thomas Sutton
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been noticing some really negative performance impacts on my iteration through a result set (approx 10Min performance penalty on a normal execution time of around 2 minutes) when my query has a select count from select statement in it to get one of the fields.

Eg Select x, y, Select count (foo) from (select bar from atable) from anothertable

obviously the actual query is a little more complex with joins etc but that illustrates where I am seeing the issue (on the select count (foo)...). The result set is not massive ~45,000 rows. What really confuses me is that the performance impact does not come from executing the query but iterating the result set. I thought the result set contained the results of the query and iterating was just simply going through those results pulling out the data but that clearly ins't the case if this can cause such a performance drop! Please can somebody explain to me why it is behaving like this? I have since amended my query and it's fine but I just wanted to know what was behind this.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where is the database in relation to where your query is executing?
That data has to get across the network.

You say you know it's not the query. Have you run it on the database "directly"?
Since you say you modified the query and it's now fine, is the modified version returning the same amount of data?
 
Thomas Sutton
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The database is on my local machine. I removed that part of the query so there is one less column coming back. And yes I ran the query direct on the DB. I have also put in sys.out.nanoTime() to measure the method. The time spikes are on the resultSet.next() not on the actual extract of the data from the resultset or the query execution.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic