Embedded sql without index faster than with index

Hi all,

I am having a strange performance issue while loading a subfile from an sql cursor.
It seems that when the sql select gets run and no index can be used, it is MUCH faster than when an index can be used.

I have 2 transaction files , currentTransactions and historyTransactions. Both these files have logicals with the client number as the first key field.

I am using sql to search these files with any range of client numbers and a transaction amount. I am doing something like :
select fields from files where clientNumber between c1 and c2 and amount between a1 and a2

No index has yet been created for the transaction amount. I am now testing to see what the performance is like.

If I enter an amount 20.25 and no client number, my statement will be something like :
select fields from files where clientNumber between 0 and 999999 and amount between 20.25 and 20.25

If I enter client number 12345 and no amount, my statement will be something like :
select fields from files where clientNumber between 12345 and 12345 and amount between 0 and 9999999999.99

The first statement is running twice as fast than the second, which I don't understand. They are both taking several seconds to run.

I've tried debugging and copying and pasting the result into an iNav sql script window and it runs in a split second.

I thought maybe it was other logic between reading the cursor and writing to the subfile, but if I enter no client and no amount, the subfile displays immediately.

any ideas would be most welcome.