• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

DB QUERY optimization

 
Ranch Hand
Posts: 127
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic