• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

resultset.next() takes too much time

 
Ranch Hand
Posts: 211
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic