Index creation performance question

  • Article

Is it faster to fill a table with values, then index it or create the index on an empty table, then fill it? Why?

Guess which one is faster before running the code below.

(I attended a presentation by the MSN Search team a while ago where the architecture of the web search engine was described. Not surprisingly, the indexing structures were quite similar to those of VFP!)

See also What is an index anyway?

num=100000

?"Create Index before = ",TestIt(.t.,num)

?"Create Index after = ",TestIt(.f.,num)

PROCEDURE TestIt(fIndexBefore as Boolean, num as Integer)

      ns=SECONDS()

      CREATE table test (name c(100))

      IF fIndexBefore

            INDEX on name TAG name

      ENDIF

      FOR i = 1 TO num

            INSERT INTO test VALUES ("testing")

      ENDFOR

      IF !fIndexBefore

            INDEX on name TAG name

      ENDIF

      RETURN SECONDS()-ns