>I think I've discovered an optimization problem with TOP and wondered if I'm missing something obvious here.
>
>I'm developing some standard routines that I can use in an app that will use either fox tables or upsized SQL data. Native Fox access returns data at the normal 2-3 times faster than SQL except when I try to use TOP in the SELECT. Then, Fox *crawls*!
>
>For example, I am running the same query against a 64,000 record, 120 MB Fox table and the same table after upsizing it to SQL. An index tag for "ship_no" exists in both data sets. I execute the following line:
>
>select top 21 * from tship where ship_no >= ?m.ship_no order by ship_no
>
>The performance I get is:
>SQLEXEC() thru SQL Server: 2.956 secs
>SELECT thru native Fox: 83.727
>SQLEXEC() thru Fox ODBC: 166.490
>
>Has anyone had any experience with this? Any suggestions? Thanks.

We ran into the same problem. What we did was to just do a SQL in the order we wanted things in, and then COPY the next n records into an array, and then append the array into a cursor. For a million record table it ran about a million times faster. :) Just make sure you're initial select is a "filtered view" like SELECT * FROM origtable INTO temp ORDER BY OptimizedFieldInTag INTO CURSOR temp.