Christof,

>The optimized version is constantly between 10% and 20% faster than the non-optimized LOCATE. Since this is a cursor, all the possible conflicts that force VFP to reread the index nodes can't occur.

I've ran your test and rushmore was the winner by about 5% (so close to the break even point). Only when using about significant less than 100 records will result into a peformance advantage for the LOCATE NOOP. I must admit that my memory didn't serve me well, as I could not reproduce the performance advantage of 300% in any case where only 1 index was used in a 100 record table.

However there is much to say about this example. First off all in your example you use a primarykey to search on, so only one indexnode is downloaded into memory for rushmore optimization.

If you use a foreign key to search on the situation is that rushmore has to download more indexnodes (as a particular foreign key value might appear once in a table). Also in this cases running LOCATE with rushmore would likely to be faster (only if the NOOP LOCATE searches 50 records on the avarage, see below).

However, when trying to achieve maximum performance, you'd normally use SEEK instead of locate because this would be faster in almost every case.

It is getting interesting when you're trying to take advantage of rushmore with the use of more than one index, by contructing a FOR clause with more than one expression combined with OR or AND.

Clear All
Close All
Release All

Create Cursor Bench (nField N(2), Text C(10))
For t=0 to 99
	Insert into Bench Value (m.t, "Bla"+ALLTRIM(STR(INT(m.t / 10))))
Endfor
Index On nField Tag nField
Index On text Tag nField2
SET ORDER TO 0

Set Talk off
Set Notify off

Set Optimize On
DoTest()
Set Optimize Off
DoTest()

Procedure DoTest
	Local lnStart, lnCount
	lnStart = Seconds()
	For lnCount=1 to 10000
		Locate for nfield=50 AND text="Bla5"
	Endfor
	? Seconds() - m.lnStart
EndProc 

The non optimized LOCATE is faster (in my case) than the rushmore optimizable one. From our discussion about a year ago we concluded that rushmore downloads the matching indexnodes of each optimizable expression and then use an indexquery to determine the first matching record. The more indexnodes there are to be involved in the indexquery the slower rushmore gets (hence the theory about the DELETED() tag).

Yet another thing about the test: Rushmore doesn't care where the record is located in the table (Recordnumber, OTOH it cares about the location of the indexnodes in the B-tree), the non-optimizable locate is searching sequentially trough the table, so if the record to seach is located at the end, it will take longer than if the record is located at the beginning of the table. In your test you did carefully search for a record in the exact middle of the table, so this would be the avarage time needed for a sequential LOCATE.

This is correct when your searching for a unique record (there is only one matching record). But when you're searching for an expression which matches more than one record, it will become more likely that the first candidate will be more to the front of the file, which means that the avarage time of a non-optimizable LOCATE will drop significantly.

OTOH if you are using the LOCATE to check if a record does exist, rushmore is likely to be the winner as it can detemine quite quickly if a matching record does exist.

I think there are lot's of factors involved which determins which is faster; to name a few which probably are:
- Multiuser access
- Local workstation or from a LAN or WAN
- The selectivity of the optimizable part of the FOR clause
- The existance of NON - optimizable parts of the FOR clause
- The datatype of the indexes
- If the indexes are balanced
- The number of records in the involved table(s)
- The width of a table
- ....

As a result I can only conclude, you've got to test if rushmore is helpfull or not in your case. I do step off the idea that there is a general rule of thumb; it simply depends on too many factor IMO.

Walter,