File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle data fetch delay Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle data fetch delay" Watch "Oracle data fetch delay" New topic

Oracle data fetch delay

Makesh Kumar Ramakrishnan
Ranch Hand

Joined: Jan 07, 2006
Posts: 88
We are facing significant delay in loading the screen. Most of the delay time is due to data fetching(JDBC) time.

It's a swing application with Oracle 10i database. It's used by our 3 plants in different locations.Database is located at Arizona.
Three plant locations are Arizona,New Mexico and Ohio.

There is no delay in loading the screen for the users accessing from Arizona and New Mexico plants.It takes 4 secs to load the screen. This is expected as there are more data and lot of screen components to load.

But the users in Ohio plant are having problem. It takes 30 secs to load the same screen. The time break up shows that most of the delay is in fetching the data.

What are my options to fix this problem?

Note: We had similar problem in New Mexico plant. But it was solved. I used setFetchSize() function to fix this.

I appreciate your help.


Database: Oracle 10i
Java : Java 5
JDBC and Oracle thin driver


Makesh<br /> <br />SCJP | SCWCD | SCBCD | SCEA
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1141

JDBC code that runs on the same machine as the database server runs considerably quicker than if the JDBC code executes on a remote machine.
So does your JDBC code run on the same machine as the database server?
If not, then I suggest shifting the JDBC code to the database server host machine, storing query results in some serializable data structure and transferring to the remote client.

Good Luck,
Makesh Kumar Ramakrishnan
Ranch Hand

Joined: Jan 07, 2006
Posts: 88
This is a swing application. So it's definitely not executing on the machine database resides.

Then I have only the second option as you mentioned. If I understood correctly,these are the things need to be done.

Separate the database code from the GUI code. Deploy the database code as a web application. And make a call to web application whenever the swing application needs to get the data from database.

Is that correct?

Or anything else we can do.
lavar kaflen

Joined: Dec 09, 2008
Posts: 1
I liked the logic of using jdbc codes in DB Server itself instead of runnning from Application Server.
So, we need to separate the user interface JSF codes in application server with that of jdbc driver loading calls in database server. Never experimented this. So, we need to write codes in new styles and serialize those driver outputs to user fronts to get perforrmance

Lava Kafle<br />Software Systems Technical Analyst
I agree. Here's the link:
subject: Oracle data fetch delay
It's not a secret anymore!