In this post: Index creation performance question I asked: Is it faster to fill a table with values, then index it or create the index on an empty table, then fill it? Why?
After a record in a cursor is modified, any associated index must be updated. If there is already an index, then after each record added, the index needs to be checked.
If the index is created on the filled table, then a single, specialized optimized index creation routine can be invoked within VFP to create the index.
For example, I profiled the code for creating 10000 records. If the special routine is invoked to create the index on these records,
If the index is created beforehand, then the routine to add a node (let’s call it AddNode) to the index is called 10001 times (the extra 1 time is for the index itself: multiple indices can be stored in an index: see What is an index anyway?)
If the index is created after, then the special routine is called, which reduces the number of calls to AddNode to 312!
On my machines, the index after scenario is about 3-5 times faster than the index before! What results do you get?
Next question: what effect will there be if you use random data rather than a constant string? You may be surprised by the results!
(Another question: Why does this code not use SYS(2015) - Unique Procedure Name to get random values?)
SET EXCLUSIVE ON
SET SAFETY OFF
?"Create Index before = ",TestIt(.t.,num)
?"Create Index after = ",TestIt(.f.,num)
?"Index size = ",aa[1,2]
PROCEDURE TestIt(fIndexBefore as Boolean, num as Integer)
CREATE table test (name c(100))
INDEX on name TAG name
FOR i = 1 TO num
INSERT INTO test VALUES (MakeRandString(7))
INSERT INTO test VALUES ("testing")
PROCEDURE MakeRandString(nLen as Integer)
FOR i = 1 TO nLen