• 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

Performance Issue

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
am making call Oracle Function on remote database .
The values are taken in result set.The numbers of records in result set are aroung 6000.
when I do
while(rs.next())
{
//My logic
}
This takes aroung 5 min(300 sec) to get completed
Now I commented all my logic inside while loop of rs.next()
then also its taking around 4 min (250 sec) to just loop through records.
Is there any way I can minize my time to just loop through records.
I am using oracle thin driver to make connection.
 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I just ran a test using DbVisualizer (JDBC based) on an Oracle database using the Oracle thin drivers and it took just under a minute to fetch 6000 rows from a database that in another state.
How big are your rows? Mine are about 350 bytes each. If your's are larger, and you don't need all the columns, the maybe you can change the query to not return as much.
You can also try setting fetchSize on the results set to 100 or 1000 to see if that helps.
 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Make sure the sql you run can take advantage of table's indexes and make sure the right indexes have been defined.
Another thing to try if possible is to move the java program as close (network-wise) as possible to the database, preferably on the same machine.
A good test to see if the sql that's the problem or the java program logic is to run the command via a different tool like powerbuilder or dbartisan and take note of the time.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic