aspose file tools*
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
Author

Reduce oracle SELECT query Response Time

Mari Yappan
Greenhorn

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
Marshal

Joined: May 26, 2003
Posts: 31067
    
232

Mari,
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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Yuval Goldstein
Greenhorn

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.

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

Thanks.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16305
    
  21

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


Customer surveys are for companies who didn't pay proper attention to begin with.
Pat Farrell
Rancher

Joined: Aug 11, 2007
Posts: 4659
    
    5

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: http://aspose.com/file-tools
 
subject: Reduce oracle SELECT query Response Time