When % is at the end, VFP9 can optimize the expression.
>The "%" sign may be at the beginning, or at the end; therefore, Visual FoxPro can't optimize the query with an index. That is, an index on field REFN can't be used.
>
>>Hi,
>>
>>
>>m.cTextBeforeCounter = "ABC bla bla bla %"
>>
>>SELECT TOP 1 REFN ;
>> FROM table1 ;
>> WHERE (REFN LIKE (?cTextBeforeCounter)) ;
>> ORDER BY REFN DESC ;
>> INTO CURSOR table1_
>>
>>
>>The purpose of the above select command is to find out the REFN field value of the last record found (in order of REFN) in table1 whose REFN field value starts with text as specified by the filter variable ?cTextBeforeCounter
>>
>>i.e. for table1 records like below:
>>
>>REFN field
>>==========
>>...
>>ABC bla bla bla a23836
>>ABC bla bla bla b47973
>>ABC bla bla bla c34858
>>ABC bla bla bla d38046 ---- this is the record I am looking for, whose REFN value is what I want.
>>BCD bee be ee 999954
>>...
>>
>>
>>My concern is the performance of the above SELECT command. What if there is a huge set of records which start with "ABC bla bla bla"
>>
>>1. I have no idea how internally this SELECT command works. I mean when I use the "TOP 1" clause will it actually speed up the command? (since only 1 record is retrieved, if any).
>>
>>2. If I create a permenent index on REFN and use it with table1 (instead of using the ORDER BY ... DESC clause), will it help in speeding things up, if so, how then should I code my SELECT coomand to make use of this index to gain performance?
>>
>>Please, any suggestion on the above will be very much appreciated!
If it's not broken, fix it until it is.
My Blog