SQL SELECT vs. SEEK

[risslsu]

I am currently trying to obtain 2 records out of a table that consist of 1 million records. I want the person with the highest last name and the person with the lowest last name.

Using BETWEEN or <> operators in my where statement seems to take longer than a SEEK comand.

Which is more efficient... SEEK or SQL SELECT? Why? Should they be used at different times?



Thank You,
Marissa

 

[ramani]

I would set the index and do a seek.

assuming index on lastname is available (if not create that, since this will be required often), set the order to this index...

SELECT myTable
LOCATE
myLowestLastName record..

GO BOTT
myHIghestLastName record

***********************
SEEK is better if you have a value to be searched. Otherwise, we have to set near on and then make a seek and get to the record.

In SQL select with between values, a large junk of unwanted records will be read and picked up.





____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Olaf Doschke]

*index on lastname tag lastname assumed

set exact off
set order to lastname ascending
seek "" && finds the first lastname
set order to lastname descending
seek "" && finds the last lastname

Locate and Go Bottom can be quite slow on a large table, if an index is set (because then top and bottom don't correspond to the files begin and end), so a seek "" is preferrable. You may use locate each time (with the 'trick' of changing the index from ascending to descending), because it is rushmore optimized, but Go Bottom is not! Seek "" will still be faster than locate. And SQL should be slower even with rushmore optimization just because of the overhead of finding the appropriate index to optimize the query. If you set the index yourself and seek using it you spare that time.

Bye, Olaf.

 

[Mike Lewis]

Marissa,

The short answer to your question is this: When you are searching or selecting from a large table, the choice of which command to use does not make a substantial difference. What really matters is whether an index exists on the field being selected, and whether VFP can take advantage of that index.

You say you tried using BETWEEN in a WHERE clause, but you didn't say if it was the VFP BETWEEN function or the SQL between operator. This is important because VFP can optimise the latter but not the former.

For example, suppose this is your WHERE clause:

... WHERE Cust_ID BETWEEN 1 and 20

If there is an index on Cust_ID, that query will be fully optimised and will therfore be fast. However, this one won't be optimised:

... WHERE BETWEEN(Cust_ID, 1, 20)

This is true regardless of which command you use. It is the optimisation that it important, not the command.

That said, SELECT is usually a better choice when you need to get more than one record at a time. SEEK only finds the first record that meets the condition. LOCATE and CONTINUE can find multiple records, but require you to write more code to achieve it.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com