Walter Meester
HoogkarspelNetherlands
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Hi Mike,
Why do you query for Guest_no while you want to find the next pri_key value ?
Is guest_no indexed ?
Walter,
>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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only