RE: Embedded sql without index faster than with index

Thanks all,

I'm now running 2 programs side by side in 2 sessiions and comparing results :

The original dynamic version :
wSql =
'SELECT * FROM MYFILE +
WHERE +
DATE BETWEEN ''' + wDate1 + ''' AND +
''' + wDate2 + ''' AND +
CLIENT BETWEEN ' + wClientN1 + ' AND +
' + wClientN2 + ' AND +
AMOUNT BETWEEN ' + wAmount1 + ' AND +
' + wAmount2 + ';


and the new static version :
DECLARE C1 CURSOR FOR
SELECT * FROM MYFILE
WHERE
date BETWEEN : wDate1 AND : wDate2 AND
client BETWEEN : wClientN1 AND : wClientN2 AND
amount BETWEEN : wAmount1 AND : wAmount2 ;

At least I did my first static cursor so thanks for that.

But..... when I enter only a client number and no amount, eg client between 123 and 123 and amount between 0 and 9999999999.99 the results are fast..as I would expect because the client is in the index.

Here's the thing : at first I thought that the dynamic version was running faster than the static one. I have continued testing and I cannot say which is the fastest. I am entering data in both screens then rapidly hitting enter on one screen and switching to the other. I have no explanation why it was running so slowly yesterday. Or why the same request executed in inav ran so much faster.

Anyhow, I shall probably now create an index on the amount. If I'd done that in the first place I'd probably never have noticed anything.