Hello
As any body come across this problem or is it the fact that I need to use USE-INDEX
Here's a test proc
DEF QUERY q FOR charges ,treat_stage ,operative ,furnaces .
DEF VAR a AS INT.
a = ETIME(YES).
OPEN QUERY q FOR EACH charges
WHERE charges.charge_no > 0 NO-LOCK /* USE-INDEX idx2 */
,
FIRST treat_stage WHERE treat_stage.stage_code = charges.stage_code OUTER-JOIN NO-LOCK,
FIRST operative WHERE operative.op_init = charges.op_init OUTER-JOIN NO-LOCK ,
FIRST furnaces WHERE furnaces.furnace_no = charges.furnace_no OUTER-JOIN NO-LOCK
BY charges.charge_no DESCENDING
BY charges.chrg_stg_no.
DISPLAY ETIME.
Xref file says it's using charge_idx
I have two indexes on the charges table
charge_idx Primary Unique charge_no Ascending
chrg_stg_no Ascending
Idx2 Primary Unique charge_no Descending
chrg_stg_no Ascending
This open query takes 34 seconds if I change the BY charges.chrg_stg_no to BY charges.chrg_stg_no DESCENDING it takes .1 of a second.
The XREF file says its using charge_idx
If i force the query to use idx2 it takes .1 of a second.
Even without all the joins there is a difference without the second descending statement or use-index statement.
Do I always need to create indexes for descending and ascending possabilities or is this a BUG.
Regards
Dale
As any body come across this problem or is it the fact that I need to use USE-INDEX
Here's a test proc
DEF QUERY q FOR charges ,treat_stage ,operative ,furnaces .
DEF VAR a AS INT.
a = ETIME(YES).
OPEN QUERY q FOR EACH charges
WHERE charges.charge_no > 0 NO-LOCK /* USE-INDEX idx2 */
,
FIRST treat_stage WHERE treat_stage.stage_code = charges.stage_code OUTER-JOIN NO-LOCK,
FIRST operative WHERE operative.op_init = charges.op_init OUTER-JOIN NO-LOCK ,
FIRST furnaces WHERE furnaces.furnace_no = charges.furnace_no OUTER-JOIN NO-LOCK
BY charges.charge_no DESCENDING
BY charges.chrg_stg_no.
DISPLAY ETIME.
Xref file says it's using charge_idx
I have two indexes on the charges table
charge_idx Primary Unique charge_no Ascending
chrg_stg_no Ascending
Idx2 Primary Unique charge_no Descending
chrg_stg_no Ascending
This open query takes 34 seconds if I change the BY charges.chrg_stg_no to BY charges.chrg_stg_no DESCENDING it takes .1 of a second.
The XREF file says its using charge_idx
If i force the query to use idx2 it takes .1 of a second.
Even without all the joins there is a difference without the second descending statement or use-index statement.
Do I always need to create indexes for descending and ascending possabilities or is this a BUG.
Regards
Dale