Open Query with BY option slowdown

[Dhubbuck]

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

 

[toby.Harman]

Ok - So how did you get 2 Primary indices?

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 suggests that they are not the same database....

Which in turn suggests a whole other bunch of potential issues.... such as number of records in tables, tuning, environment (client-server/web/host based) etc

Sorry to be picky but only one index can be primary!

 

[Dhubbuck]

Ok - So how did you get 2 Primary indices?

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 suggests that they are not the same database....

Which in turn suggests a whole other bunch of potential issues.... such as number of records in tables, tuning, environment (client-server/web/host based) etc

Sorry to be picky but only one index can be primary!

Sorry my mistake only the fcharge_idx is the Primary.

charge_idx Primary Unique Charge_no Ascending
Chrg_stg_no Ascending
Idx2 Unique charge_no Descending
chrg_stg_no Ascending

 

[toby.Harman]

Ah - Ok!

So Idx2 will NEVER be selected by the compiler since it is the same fields and charge_idx is the Primary. I would delete the Idx2 and see if this helps!

My gut reaction to this is "tell Progress there's a problem" and this should be nice and simple to show them (you can probably send them some sample data and code that reproduces it!)

No mention of O/S and Version (not that it makes a difference here I suspect!