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

resultset.next() takes too much time

 
Rahul Mishra
Ranch Hand
Posts: 211
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

While analyzing the performance for my application

i found that the calls to resultset.next() and sometimes resultset.getxxx() take too much time.

i am aware of the following possible issues

1) fetch size - tried increasing it, no gain
2) use correct getters , ex for varchar2 use getString(), tried - no gain
3)its a normal iteration and all objects are varchars/integers

any pointers to improve the performance will be highly appreciated

Regards,
Rahul
 
vu lee
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
resultset.next() ... take too much time


how long did it take?
how long did it take to execute and get your resultset?
Did it take a long time on every single iteration? or just once for a while?
What was your sql statement? was it a nested or complex sql statement?
How large is your table(s)? Did you data scatter on different partitions?
[ March 30, 2007: Message edited by: vu lee ]
 
Rahul Mishra
Ranch Hand
Posts: 211
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by vu lee:


how long did it take? Around 12 Seconds
how long did it take to execute and get your resultset?
Prepared statement.execute() took around 3-4 seconds
Did it take a long time on every single iteration? or just once for a while?
will have to dig into that

What was your sql statement? was it a nested or complex sql statement?
complex sql statement
How large is your table(s)?
some are considerably large
Did you data scatter on different partitions?
no

[ March 30, 2007: Message edited by: vu lee ]


but what i am thinking is this - is it possible that doing a complex select from across a lot of tables can slow it down?..basically when i try to find why did the resultset.next() take so much time..i see that a call to T4c*.unmarshall() is taking too much time

the above class is inside the driver jar

now, this leaves me with too possiblities

1) every iteration is taking it across the network - the unmarshall is for network data
2) internally the jdbc driver will try to create objects before returning the result - the unmarshall call is basically deserializing the byte data into objects, since select is complex its slowing down

any concepts on these?

thanks for the help
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Create indexes in the database. Database performance is non-trivial and larger systems requires database experts to optimize the queryies.

Oh, and try increasing the prefech size in the results query so it doesnt make as many trips to the database.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic