• 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

JDBC huge record set - process is very slow.

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All

I need one help from you guys regarding JDBC performance optimization. One of our pojo is using jdbc to connect to a oracle database and retrieve the records. Basically the records are email addresses basing upon which emails will be sent to the users. The problem here is the performance. This process happens every weekend and the records are very huge in number, around 100k.

The performance is very slow and it worries us a lot. Only 1000 records seem to be fetched from the database every 1 hour, which means that it will take 100 hours for this process to complete (which is very bad). Please help me on this.

The database server and the java process are in two different remote servers. We have used rs_email.setFetchSize(1000); hoping that it would make any difference but no change at all.
The same query executed on server takes 0.35 seconds to complete. Any quick suggestion would of great help to us.

Thanks,
Aamer.
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Define "remote servers". Are they just two different machines in the same network, or are they both located in different locations and connected through the Internet (with or without a VPN connection)? If the latter's the case then the bandwidth may be the limiting factor here.

But one thing you need to verify is where the actual slowness is coming from. Is it from the actual querying, or from the processing? How long does it take if you only retrieve the data and loop through it, without doing anything else?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd suggest using SQL trace to obtain plan of the query when run from server and when run from Java. Using these traces you would see whether the database processes the query in these two cases identically. This is not guaranteed, a difference in using or not using bind variables, or even different NLS settings might cause the execution plans to differ, especially if an implicit conversion takes place or the data is being sorted by column containing national characters. If the plans are equal, other factors should be investigated, firstly the network, as Rob hinted, then other possible causes, eg. whether a LOB or LONG column is being fetched.

If you can't easily use SQL trace facility, let the query to run from Java for several hours and obtain an AWR report for that time. If the database is really the source of the problem, your query probably uses lots of database resources or at least waits a lot. Such a query should show up in this report and the detail from this report might provide a hint. SQL trace would probably help much more, however.

Furthermore, how is the query execution time of 0.35 s measured? If you use a GUI tool, such as TOAD or SqlDeveloper, such a tool might report you the time to fetch just first few tens of records it displays in a table. As you scroll the table down, more records are fetched and the time to obtain these might not be included in the reported time. Chances are the query is not as fast on the server as you think it is.
 
Greenhorn
Posts: 16
MyEclipse IDE Chrome Fedora
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hey guys, let me understand
1.- ----> execute query
2.- 100...record<----db
3.- loop
each record get mail----> send mail
4.- close

It is correct?

In step 2.- is where it breaks.
or where?
My first suggestion: is consult db like when consult by page. retrieve first block, second block...etc
 
reply
    Bookmark Topic Watch Topic
  • New Topic