Nice research, Peter. FYI, I think it was Mac Rubel who had a series of articles in FPA about a year or two ago experimenting with optimization that had some conclusions that may help you explore this further.

I remember doing the same sort of tests when FPD 2.0 came out and getting more or less the same results.


>Wanting to learn more about Rushmore behavior, I ran some experiments the other night. This is probably old news for the experienced but may be useful to some.
>
>I have a VFP 5.0 table with about 86,000 records divided into four associations (WM,SV,SM,ET). There is a CDX index on the CASSOC field (among others).
>
>I ran some counts:
>
>COUNT FOR CASSOC="WM"
>COUNT FOR CASSOC="SV"
>etc.
>
>and printed the counts and elapsed times. Then I would re-execute the COUNT commands.
>
>First three runs after boot-up:
>
>WM 02222 0.243 seconds
>SV 17337 1.486
>SM 19924 2.603
>ET 46947 4.715
>
>WM 02222 0.158 seconds
>SV 17337 1.764
>SM 19924 1.494
>ET 46947 3.419
>
>WM 02222 0.011 seconds
>SV 17337 0.004
>SM 19924 0.005
>ET 46947 0.009
>
>
>A later run:
>
>WM 02222 0.002 seconds
>SV 17337 0.004
>SM 19924 0.005
>ET 46947 0.009
>
>
>Based on these and other runs I deduce:
>
>1) On a Novell-Ethernet network using a 10-BaseT connection on a medium-fast pentium, Rushmore loads about 20000 records per second into its index maps. (I assume this means about 20000 bits per second).
>
>2) The load time is proportional to the number of records matching the FOR clause.
>
>2a) Each separate FOR clause requires a separate load.
>
>3) The slow times on the second set of counts only occured immediately after boot-up.
>
>4) After the maps are loaded, the times are approximately 500 times faster.
>
>5) If I close the table and reopen it, Rushmore loads its index maps again.
>
>6) If I change the CASSOC field in one record on another workstation, Rushmore reloads its tables for each value of the FOR (not just the value I changed).
>
>7) If I update a different field in this table on another workstation Rushmore does NOT reload its index maps for the CASSOC field. (COUNT times remain in the millisecond range.)
>
>
>Peter Robinson
>
>
>BTW, I'm keeping this index even though the number of discrete values is only 4 because the system does batch updates within one association at a time. The index on CASSOC avoids reading 40,000 to 86,000 records during these updates.