File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Performance and the fly likes Reduce oracle SELECT query Response Time Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Reduce oracle SELECT query Response Time" Watch "Reduce oracle SELECT query Response Time" New topic

Reduce oracle SELECT query Response Time

Mari Yappan

Joined: Aug 04, 2008
Posts: 2
When my page gets opened It needs to execute 50-60 select queries get executed so page gets delayed.I need to reduce response time . I am using rowset.jar(SQLCachedRowSet) for executing a query.

Driver- Type 4
DB - Oracle 9g
Server - WebSphere 6.0/Jboss
EJB 2.0- StateLess Session Bean
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

Welcome to JavaRanch!

The first step is to figure out where all the time is going. Which of your queries take the longest? Can any of them be merged or return less data?

Also, do all of these queries need to be done for each user or can some be cached across calls?

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Yuval Goldstein

Joined: Dec 27, 2006
Posts: 18
- If you are receiving a lot of result try setting the result fetch size to a large number.

- Consider consolidating queries
- Consider executing queries from a stored procedure
- Consider pre-fetching some of the information before the time you actually use it
- Consider using application level caching.

Ragav Baskaran
Ranch Hand

Joined: Nov 04, 2007
Posts: 36
May be you can try adding an unique index to the table from which you are selecting.

I hope it will improve the performance. Give a try.

Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 17410

Alternatively, can you fold these queries down using database joins? The overhead for setting up a query can be substantial, especially if you're not using prepared statements (although CMP EJBs normally would).

An IDE is no substitute for an Intelligent Developer.
Pat Farrell

Joined: Aug 11, 2007
Posts: 4659

Originally posted by Mari Yappan:
When my page gets opened It needs to execute 50-60 select queries get executed so page gets delayed.

Its simple. don't do 50 selects. Do one or two.
Once you have it debugged, convert it all to a stored procedure.

You simply can't do 50 selects quickly, and if you try to scale, you will find it gets much worse.

So the answer is a Doctor Doctor: don't do that
I agree. Here's the link:
subject: Reduce oracle SELECT query Response Time
It's not a secret anymore!