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.
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?
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.