Wiki Home

Append Fast


Namespace: VB
This program shows that creating an index after appending data to an empty table is about 3 times as fast as creating the index first. That means that in some few cases, deleting the indexes from a table may be faster even if the table isn't empty.

* compare building the index before and after adding records
Clear
Set Talk Off

nMax = 3200
Dimension la( nMax, 2 )
mkSources()

Set Exclusive On
tests()
Set Exclusive Off
tests()
Return

Function tests()
? "exclusive", Set("Exclusive")
test(1)
test(2)
test(3)
test(4)
Return

Function test( tnType )

? "Type", tnType
lCDXfirst( tnType )
lCDXlast( tnType )
?
Return

Function lCDXfirst( tnType )
lMkDbf()
ltStart = Datetime()
lMkCdx()
lAppend( tnType )
lEndTimer( ltStart, Datetime() )
Return

Function lCDXlast( tnType )
lMkDbf()
ltStart = Datetime()
lAppend( tnType )
lMkCdx()
lEndTimer( ltStart, Datetime() )
Return

Function lMkDbf()
Create Table foo (;
	pkFoo i, ;
	nFid1 i )
Use foo
Return

Function lMkCdx()
Select foo
Index On pkFoo tag pkFoo
Index On nFid1 Tag nFid1
Index On pkFoo + nFid1 Tag test1
Index On Str( nFid1 ) Tag test2
Return

Function lAppend( tnType )

Select foo
Do Case
	Case tnType = 1
		Rand(1)
		For lnI = 1 To nMax
			Insert Into foo Values ( lnI, Rand()*nMax )
		Endfor

	Case tnType = 2
		Append From Array la

	Case tnType = 3
		Append From foo2

	Case tnType = 4
		Append From foo.txt type DELIMITED 
		
Endcase
Return

Function mkSources()

? "making array..."
For lnI = 1 To nMax
	la(lnI,1) = lnI
	la(lnI,2) = Rand()*nMax
Endfor

? "making foo2.dbf..."
Create Table foo2 Free( ;
	pkFoo i, ;
	nFid1 i )
For lnI = 1 To nMax*15
	Insert Into foo2 Values ( lnI, Rand()*nMax )
Endfor

? "making foo.txt..."
Erase foo.txt
Copy To foo.txt Type Delimited 

?
Return

Function lEndTimer( ttStart, ttEnd )
lcDesc = Program( Program( -1 )-1 )
lnDelta = ttEnd - ttStart
? lcDesc, "Total time: ", lnDelta
Return

Contributors Carl Karsten
Category Code Samples Category VFP Commands Category Performance
( Topic last updated: 2002.07.29 05:41:35 PM )