Hi Glenn
The locate would be slower than a GO TOP since the result of the query should be an unindexed cursor. Although you'd never be really able to determine the difference, I think it important to realize locate is only good when working with set filters or SQLs that result in filtered tables with Rushmore optimizable indexes.
>what if you try:
>
>Select top 2 guest_no;
> from mytable;
> where pri_key>1700001;
> order by guest_no
>locate
>
>
>Glenn
>
>
>>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