*
The moose likes JDBC and the fly likes DB QUERY optimization Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DB QUERY optimization" Watch "DB QUERY optimization" New topic
Author

DB QUERY optimization

Steve Jiang
Ranch Hand

Joined: May 17, 2004
Posts: 107
The existing query have 2 sub query

1, select id, max(sched_time) from schedule where sched_time < sysdate
then get the max_sched_time lower than current time for each fid
2, select * from schedule where sched_time > max_sched_time and sched_time < max_sched_time + ? and id = fid


I try to combine it to one query to avoid running hundreads times of query for hundreds id
as

select * from schedule s, (select id, max(sched_time) max_sched_time from scheudle where sched_time < sysdate group by id ) ST where s.id = st.id and s.sched_time > max_sched_time and s.sched_time < max_sched_time + ? order by s.id

but it seems the new query create much more db hit and used up the temp space. Imet teh db problem of java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP.

How could I optimize the DB query to decrease the DB load?

Thanks,
[ August 02, 2005: Message edited by: Steve Jiang ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30377
    
150

Steve,
As you noticed, combining the queries makes one really slow query. Sometimes this works out better an sometimes it doesn't. The first place to start is to make sure you have the proper indexes. Having one on sched_time is critical. You can also use your database's explain feature to see what the execution plan is for optimization ideas.


[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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DB QUERY optimization