>>>>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.
>>
>SQL Server is more optimized that VFP.
>But the true VFP's power is xBase engine, not the SQL engine.
>
>

>Prefix = "ABC bla bla bla "
>CREATE CURSOR testx (REFN C(100))
>FOR K=1 TO 100000
>	FOR j=1 TO 10*RAND()
>		INSERT INTO testx VALUES (replicate(CHR(RAND()* 255),100*RAND()))
>	NEXT
>	INSERT INTO testx VALUES (m.prefix+replicate(CHR(RAND()*255),70*RAND()))
>NEXT
>
>* optimization : none
>t1=SECONDS()
>SELECT TOP 1 REFN ;
>INTO CURSOR Result;
>	FROM testx ;
>	WHERE REFN LIKE (m.prefix+"%") ;
>	ORDER BY REFN DESC
>DEBUGOUT SECONDS()-m.t1,result.REFN 
>
>SELECT testx
>INDEX on REFN TAG REFN 
>
>* optimization : partial
>t1=SECONDS()
>SELECT TOP 1 REFN ;
>INTO CURSOR Result;
>	FROM testx ;
>	WHERE REFN LIKE (m.prefix+"%") ;
>	ORDER BY REFN DESC
>DEBUGOUT SECONDS()-m.t1,result.REFN 
>
>SELECT testx
>SET ORDER TO REFN DESCENDING
>
>t1=SECONDS()
>SET EXACT OFF
>SEEK m.prefix
>DEBUGOUT SECONDS()-m.t1,testx.REFN 
>

>
>
>>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.
>
>This can reduce the time to move the application on SQL Server
>but more likely to enter errors difficult to find


Man... you really make me feel I am silly in forcing myself to use the SELECT SQL command.... believe it or not, just recently someone was argueing with me that there isn't any case or in anyway the SQL way of data operation will be slower than xBase's (i.e. ordinary Fox data engine's)... I even have difficulty in convincing that stupid fool...