>I'd like to use SQL Syntax to make the code more compatible when using SQL Server for the back end. Can anyone see a flaw in these statements?
>
>The problem is to find the primary key of the record with the next highest primary key after #1700001.
>
>In old-style fox syntax, that's:
> set order to pri_key
> seek 1700001
> skip
> ?pri_key
>
>The table has 112,000 records, over 100 columns and some large memos. The primary key is a double (8 bytes). SYS(3054,11) tells me the query is fully optimized.
>
>
>
>This one takes just over 30 seconds (fresh start of VFP - no cached data)
>select min(guest_no);
> from mytable;
> where pri_key>1700001 nofilter
>
>
>This one takes just over 30 seconds (fresh start of VFP - no cached data)
>select top 1 guest_no;
> from mytable;
> where pri_key>1700001;
> order by guest_no nofilter
>
>
>This one takes just over 1 second (fresh start of VFP - no cached data)
>select guest_no;
> from mytable;
> where pri_key>1700001;
> order by guest_no
>locate
>
>
>I am awestruck, but the clear winner, at far less than 1 second is:
> set order to pri_key
> seek 1700001
> skip
>
>Thanks,
>Mike

Mike,
I expect seek be fastest here (I would do 'set near on' and seek 1700002, 1700001 might not be there).
OTOH removing 'nofilter' on SQLs I expect them to be much faster than 30 secs too.
VFP SQL unlike SQL server's is not kind enough to break when it finds top n :( If this wasn't a PK issue it would count too.
Cetin