>>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!
>
>VFP can optimize the LIKE filter, but is not able to use the index for the TOP selection.
>
>YOU can do the optimization manually with xBASE.
>
>

>* create a table1's cdx index
>INDEX ON REFN TAG REFN
>
>SELECT table1
>SET ORDER TO REFN DESC
>* or USE table1 ALIAS selectA ORDER REFN DESC
>SET EXACT OFF
>SEEK (m.cTextBeforeCounter)
>

I know SEEK... even before I know VFP.... :)
I try not to use SEEK in few of my applications (still under development), particularly in tables that are placed at file server, knowing it stands a high chance that I will need to move to SQL server.

I suppose by using SELECT now should reduce my effort in making my applications SQL server compatible later... though I am not quite sure if what I am doing now worth the effort.