Hi Hilmar
>>
>>It is often faster to LOCATE instead of GO TOP after SET FILTER.
>
>I have seen LOCATE being used instead, but do you have any idea why it would it be faster?
It all depends on having Rushmore optimizable indexes and how far away the first filtered record is from the top of the file in the current SET ORDER. Without Rushmore optimization, there is no difference. With optimization there can be a big difference. I have a table with a million records. Each record has a "primary key" (SPD_ID) that corresponds to the record number.
CREATE TABLE SPEED.DBF FREE ;
(Spd_ID I, ;
Spd_Char C(10), ;
Spd_Date D, ;
Spd_DT T)
LOCAL ;
lnI, ;
lnX, ;
Spd_ID, ;
Spd_Char, ;
Spd_Date, ;
Spd_DateTime
FOR m.lnI = 1 TO 1000000
m.SPD_ID = m.lnI
m.SPD_CHAR = STR(m.lnI)
m.lnX = m.lnI/100
m.SPD_DATE = {^2000-01-01} + m.lnX
m.SPD_DT = {^2000-01-01 00:00:00} + m.lnX
INSERT INTO SPEED FROM MEMVAR
IF MOD(m.lnI,1000) = 0
WAIT WINDOW STR(m.lnI) NOWAIT
ENDIF
ENDFOR
INDEX ON SPD_ID TAG SPD_ID
INDEX ON SPD_CHAR TAG SPD_CHAR
INDEX ON SPD_DATE TAG SPD_DATE
INDEX ON SPD_DT TAG SPD_DT
In the following program, change the filter statement and SET ORDER to see the various effects.
SET ORDER TO
SET FILTER TO SPD_ID = 10000
A=SECONDS()
FOR I = 1 TO 1000
GO TOP
ENDFOR
?SECONDS()-M.A
A=SECONDS()
FOR I = 1 TO 1000
LOCATE
ENDFOR
?SECONDS()-M.A
When the number is very small, GO TOP beats LOCATE. You'll see that LOCATE takes about the same amount of time each time. As the number gets larger, the LOCATE starts to be faster than GO TOP.
It has been suggested that GO TOP finds the record does not match the filter and then begins looking for the first record that matches by checking each record.
When the filter is SPD_ID=650, my machine shows both times as roughly the same, about 1 second. When the filter is SPD_ID=10000, GO TOP takes 17 seconds, but LOCATE still takes 1 second. I can't imagine how bad it would be if the filter was SPD_ID=1000000
Try changing to SET ORDER TO SPD_CHAR and set the filter to SPD_ID = 10000. My machine shows 24 seconds for GO TOP versus 7.6 for the LOCATE.
Conversely, if you are not accessing the tables directly and using views, the resulting cursors won't have indexes, should have few records and GO TOP is faster.