Please help optimize a query:

//QUERY IS AS FOLLOWS: select --+index (s ix_sr2) s.trandt ,sub_min as min ,s.s_class ,s.status ,s.active_dt ,s.exp_dt ,s.canc_dt from status_records s where s.canc_dt >= '08/04/2008' and s.canc_dt < '09/04/2008' and s.canc_dt = (select --+index(y ix_sr2) max(canc_dt) from status_records y where canc_dt >= '08/04/2008' and canc_dt < '09/04/2008' and sub_min = s.sub_min) and s.trandt = (select --+index(y ix_sr2) max(trandt) from status_records y where canc_dt >= '08/04/2008' and canc_dt < '09/04/2008' and sub_min = s.sub_min) //INFO... I use Informix 10.00 ; Row counts = Status records table 541,791; Indexes = on status_records, there are 3 indexes - create unique index "systems".ix_sr on "systems".status_records (sub_min,canc_dt,s_class,status,active_dt,exp_dt) using btree; create index "systems".ix_sr2 on "systems".status_records (canc_dt,sub_min) using btree ; create index "systems".ix_sr3 on "systems".status_records (trandt) using btree ; Could someone please help me optimize this query? thanks.