This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Reduce oracle SELECT query Response Time

 
Mari Yappan
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Yuval Goldstein
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
- 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
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 18163
53
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Pat Farrell
Rancher
Posts: 4678
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic