Which do you reckon would be faster? 1

[spuppett]

I need to search a table for 1 record. I know it will be unique. Would it be faster to do a 'locate for', or a select into a cursor?

Any thoughts?

 

[Stella740pl]

On an indexed table, SEEK or SEEK() would be faster than LOCATE.
SELECT would be faster, too, if the table is indexed on that field.

Between the two, I am not sure which is faster, SEEK or SELECT.

In any case, my first suggestion would be to index on that field if it's not already, then set up a test with timing using SECONDS().

 

[DSummZZZ]

Actually, VFP will utilize Rushmore for either/both if you have an index on the field.
The time difference will be minimal if it is there at all.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[spuppett]

Thanks both of ya

 

[craigsboyd]

Whenever you're looking to compare two snippets of code to see which one is faster you can use the methods outlined on my blog entry, Execution Time for VFP

http://www.sweetpotatosoftware.com

 

[Stella740pl]

I've just ran a simple test looking for 1 unique numeric value (generally, this field is not unique).
Table contains nearly 2.5 mln records, has regular index on all fields.

GO TOP
a=seconds()
seek 20933
b=seconds()
?b-a  [COLOR=maroon]&& RESULTS: 1st run - 0.000, 2nd run - 0.000[/color]

GO TOP
a=seconds()
locate for count=20933
b=seconds()
?b-a  [COLOR=maroon]&& RESULTS: 1st run - 0.771, 2nd run - 0.544[/color]

a=seconds()
select * from myTable where count=20933 into cursor found_it
b=seconds()
?b-a  [COLOR=maroon]&& RESULTS: 1st run - 0.011, 2nd run - 0.001[/color]

Thus, SEEK is the fastest, SELECT comes close second, and LOCATE is noticeably slower, even though this difference under 1 second can be negligible. With more complex index/search expression, result may differ.

 

[craigsboyd]

Stella740pl,

Nice example to prove your point. Given that the Seek and the Select both ran so fast on my laptop that the actual time couldn't be recorded (resolution of SECONDS is 1 or 10 miliseconds), I decided to rework your example a little bit. I agree that the differences could quickly add up.

LOCAL lcBuffer1, lcBuffer2, lnTicksPerSecond, lnCurrentTickCount
DECLARE INTEGER QueryPerformanceFrequency IN kernel32 STRING @lpFrequency
DECLARE INTEGER QueryPerformanceCounter IN kernel32 STRING @lpPerformanceCount
lcBuffer1 = SPACE(8)
lcBuffer2 = SPACE(8)
=QueryPerformanceFrequency(@lcBuffer1)
lnTicksPerSecond = buf2num(SUBSTR(lcBuffer1, 1,4)) + buf2num(SUBSTR(lcBuffer1, 5,4)) * 2^32

*!* Create the dummy data and Index it
CREATE CURSOR crsTest (Field1 I)
FOR lnCounter = 1 TO 100000
	INSERT INTO crsTest (Field1) VALUES (lnCounter)
ENDFOR
INDEX ON Field1 TO FIELD1
SET ORDER TO Field1

CLEAR

GO TOP
=QueryPerformanceCounter(@lcBuffer1) && Click stop watch on
locate for Field1 = 20933
=QueryPerformanceCounter(@lcBuffer2) && Click stop watch off
lnCurrentTickCount = buf2num(SUBSTR(lcBuffer1, 1,4)) + buf2num(SUBSTR(lcBuffer1, 5,4)) * 2^32
lnCurrentTickCount = (buf2num(SUBSTR(lcBuffer2, 1,4)) + buf2num(SUBSTR(lcBuffer2, 5,4)) * 2^32) - lnCurrentTickCount
? "LOCATE:" + CHR(9) +  TRANSFORM(lnCurrentTickCount/lnTicksPerSecond) + " secs"

GO TOP
=QueryPerformanceCounter(@lcBuffer1) && Click stop watch on
seek 20933
=QueryPerformanceCounter(@lcBuffer2) && Click stop watch off
lnCurrentTickCount = buf2num(SUBSTR(lcBuffer1, 1,4)) + buf2num(SUBSTR(lcBuffer1, 5,4)) * 2^32
lnCurrentTickCount = (buf2num(SUBSTR(lcBuffer2, 1,4)) + buf2num(SUBSTR(lcBuffer2, 5,4)) * 2^32) - lnCurrentTickCount
? "SEEK:" + CHR(9) +  TRANSFORM(lnCurrentTickCount/lnTicksPerSecond) + " secs"

GO TOP
=QueryPerformanceCounter(@lcBuffer1) && Click stop watch on
select * from crsTest where Field1 = 20933 into cursor found_it
=QueryPerformanceCounter(@lcBuffer2) && Click stop watch off
lnCurrentTickCount = buf2num(SUBSTR(lcBuffer1, 1,4)) + buf2num(SUBSTR(lcBuffer1, 5,4)) * 2^32
lnCurrentTickCount = (buf2num(SUBSTR(lcBuffer2, 1,4)) + buf2num(SUBSTR(lcBuffer2, 5,4)) * 2^32) - lnCurrentTickCount
? "SELECT:" + CHR(9) + TRANSFORM(lnCurrentTickCount/lnTicksPerSecond) + " secs"

USE IN SELECT("crsTest")
USE IN SELECT("Found_It")

************************
FUNCTION buf2num(tcBuffer)
************************
    RETURN ASC(SUBSTR(tcBuffer, 1,1)) + ;
        ASC(SUBSTR(tcBuffer, 2,1)) * 2^8 + ;
        ASC(SUBSTR(tcBuffer, 3,1)) * 2^16 + ;
        ASC(SUBSTR(tcBuffer, 4,1)) * 2^24

SweetPotato Software Website
My Blog

 

[Stella740pl]

That's a nice piece of code with an impressive precision.
Saved it for future use. Thanks.

 

[craigsboyd]

Thanks for the star. The code is modified from the example I posted on my blog (there's mroe information there and it will show you how to find out what the precision is for your machine's counter).

The precision that the High-Resolution Performance Counter gives is pretty darn good. I remember being really impressed when I first started using it, and truth be told I'm still pretty impressed with it.

SweetPotato Software Website
My Blog

 

[TamarGranor]

I did a Q&A piece about this question in FPA in the February issue. You may find it helpful.

Tamar