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

---

I'm probably wrong in this, but the TOP is issued after order by. So you are sorting all of them and then taking the top 21. This is 2 steps non optimized.

Why not create a view without the order by, open it nodata, index on ship_no and then requery() as test 1.

Test 2 create a view without the top, or order by. Same as above, but count the first 21 rows.

__Stephen