>>>Well,
>>>
>>>The initial query is:
>>>
>>>Select a.field1 As Code, a.Field2 As Desc From TableA a Into Cursor tmpCursor
>>>
>>>The table has an index on field1 so I added:
>>>
>>>where a.Field1 = ""
>>>
>>>It's a cheat I know but the query is optimised and works as long as set exact off.
>>>
>>>The time jumps from 5 secs to under 2.
>>>
>>
>>Hi Mathias,
>>
>>That is too cool! I just tested it on my setup and got similar results.
>>
>>Thanks,
>
>I'm not getting what is happening here. What is the 'where a.Field1 = ""' helping do? What is the test and the comparison?

Jay,

*Test1.prg
LOCAL nStart
nStart = SECONDS()
SELECT ItemNo, InvoNum FROM ARSTrans
?SECONDS() - nStart
*Result = 33.469 seconds

*Test2.prg
LOCAL nStart
nStart = SECONDS()
SELECT ItemNo, InvoNum FROM ARSTrans WHERE ItemNo = ''
?SECONDS() - nStart
*Result = 23.494 seconds

*Test3.prg
LOCAL nStart
nStart = SECONDS()
SELECT ItemNo, InvoNum FROM ARSTrans ORDER BY 1
?SECONDS() - nStart
*Result = 37.203 seconds

*Test4.prg
LOCAL nStart
nStart = SECONDS()
SELECT ItemNo, InvoNum FROM ARSTrans WHERE ItemNo = '' ORDER BY 1
?SECONDS() - nStart
*Result = 26.532 seconds

All 4 programs were run separately from separate instances of VFP to avoid memory cache affecting the results.

The key is to use a character field with an index because VFP only compares the expression on the left up to the length of expression on the right. Thus VFP fully optimizes the query but doesn't actually do any string comparison and returns all the records, or so it seems.

Regards,