>Hi all,
>
>I have a large table in the following format:
>

>Table name: table1
>Record count: 137,866
>Fields: filenum I,field1,field2...field124
>index: filenum
>

>I've created a query form allowwing the user to select top N of records in the following manner:
>

>select top nSelectCount ;
>     * ;
>   from table1 ;
>   where filenum >= nFilenum ;
>   order by filenum ;
>   into cursor curMySelection ;
>   nofilter
>

>The user determines the values of nSelectCount and nFilenum through the form interface.
>But the above query runs so much slower compared to the following query statement:
>
>

>select top nSelectCount ;
>     * ;
>   from table1 ;
>   where filenum >= nFilenum and filenum <= nFilenum + nSelectCount;
>   order by filenum ;
>   into cursor curMySelection ;
>   nofilter
>

>
>The reason I couldn't use the second statement is that the filenum is not always in sequence due records deletion, therefore the user doesn't get top nSelectCount, worse yet sometimes he gets nothing.
>
>Is there a way to speed up query with TOP option included the first statement?
>
>TIA

No. VFP don't use index when do a ORDER BY clause.
The first command extract all the records with where condition true,
SORT the result,
extract the first nSelectCount rows.

two ways exists:

1. foud the nSelectCount fileNum and do the second select
SELECT table1
set order to filenum 
set near on
seek M.nFilenum 
set near off
COUNT NEXT m.nSelectCount
upperfilenum = IIF(EOF(),MAXFILENUMALLOWEDCOSTANT,filenum)

select * ;
   from table1 ;
   where filenum BETWEEN M.nFilenum and M.upperfilenumt;
   order by filenum ;
   into cursor curMySelection NOFILTER

2. You can use a in order SCAN with INSER
* BUILD THE RESUL CURSOR
select * from table1 ;
   where .f. into cursor curMySelection ;
   readwrite
SELECT table1
set order to filenum 
set near on
seek M.nFilenum 
set near off
SCAN NEXT m.nSelectCount
  * append table1.record into curMySelection 
ENDSCAN