>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!

1) Top 1 wouldn't contribute to speed, you are getting 1 but VFP reads all of them anyway, sorts and returns.
2) You should of course index on that column if you want any optimization. That is unrelated to Order by ... desc. If your back end is VFP then, VFP is case sensitive, create your index with upper() or lower. ie:

index on upper(Refn) tag refn

and match your search to that signature (otherwise index wouldn't be used):

cTextBeforeCounter = upper('ABC bla bla bla %')

SELECT TOP 1 REFN ;
	FROM table1 ;
	WHERE upper(REFN) LIKE (?m.cTextBeforeCounter)) ;
	ORDER BY REFN DESC ;
    INTO CURSOR table1_

If set ansi is off (default) you can also use = operator and remove % (I don't suggest to do that since it would be VFP specific).

Cetin