Hey Mike

I thought this topic had been beaten to death already <g>. Seek maybe hits the index 13 times to find your record. SQL will examine all 100K plus index entries.

However, if you had a seperate table that had 2 columns, the first being the table name and the second being the last key generated, you'd be able to determine the next record to generate using either the SQL or the SEEK and there'd be less of a significant difference.

>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