Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Which one would you think is faster?
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement