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 DB QUERY optimization 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 "DB QUERY optimization" Watch "DB QUERY optimization" New topic

DB QUERY optimization

Steve Jiang
Ranch Hand

Joined: May 17, 2004
Posts: 124
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

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

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?

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

Joined: May 26, 2003
Posts: 33130

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.

[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
I agree. Here's the link:
subject: DB QUERY optimization
It's not a secret anymore!