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

Perhaps the "Full Optimization" is part of the problem. Do you have an index on DELETED()? If yes, get rid of it. This will give you "Partial Optimization", which is better in this case.

Hilmar.

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