Partager via

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

Comments

  • Anonymous
    January 27, 2006
    It must be faster to Index a table AFTER you fill it with Data, because in each append or insert index must be regenerated.
  • Anonymous
    September 27, 2007
    In this post: Index creation performance question I asked: Is it faster to fill a table with values,