Hi Sergey,

using integer indices seems to get the biggest boot when using .idx. Also updating fields which are used as an index is markedly different in my tests - and this goes for all field types. The update cost can be seen in appending/inserting as well.

cdx seem to catch up as tables grow larger, and seem to be better if used in SQL queries. I do realize that the test for SQL select is bordering on insanity, since different tables are used for one and also fill dynamically the where clause - only because the results were surprisingly stable I left that one in. With larger tables the number of queries rises and should even out sampling effects.

Since the other results showed clear trends in repeated runs and across different table sizes / machine[s] configs / index target disks I am not too worried that I am comparing the "work" done on completely different tables during measurement. Law of large numbers is nice here...

The code is meant to be tweaked for "personal stiuation" - the areas to tweak should be evident. I had thought the differences were smaller, but this probably stems from tests done way back in FPW without an integer PK... Not really usable for "my" UC still in vfp8 and using char PK's, but interesting nevertheless. And I will keep it in mind if I run into slow running reports. Definitely worth considering if you are constantly hitting small tables with integer PK's or always have to update indexed fields.

Thanks to Fabio for making me look again at the numbers.

regards

thomas

SET TALK OFF
SET SAFETY OFF
CLOSE DATABASES all
CLOSE TABLES all
CLEAR
private pcPersType, pcFldType, pcDiskDx, pnSize, pnSqlMany
pnSqlMany = 0
pnSize = 50000
pcFldType = ""
pcPersType = IIF(.f., "Cursor", "Table")
CREATE DATABASE dummy
pcDiskDx = ""
? "FIELDTYPE","COMPACT" AT 30,"IDX" AT 80,"COMPACT" AT 120
FOR FLDTYPE= 1 TO 7
	? M.FLDTYPE,SPACE(3)
	=TEST(m.FLDTYPE,.t.),TEST(m.FLDTYPE,.f.),TEST(m.FLDTYPE,.T.)	&& CHECK CACHE EFFECT
	?? " Fieldtype:" + m.pcFldType
		
NEXT
? " Recs" + STR(Recmany())  + " Seeks: " + STR(10*m.pnSize) + ;
	" Queries:" + STR(m.pnSqlMany) + " With: " +  m.pcPersType

SET TALK On
SET SAFETY On

PROCEDURE TEST(TYPEFIELD,COMPACTINDEX)

local laF[1], laDummy[1], ;
	lcOrder, lcDxFile, ;
	lnTF, lnTR, lnTS, lnTU, lnTM, lnTQ, lnT1

STORE 0 TO lnTF, lnTR, lnTS, lnTU, lnTM, lnTQ
PRIVATE pcKey, ;
	pa0, pa1, pa2, pa3, pa4, pa5, pa6, pa7, pa8, pa9

Dimension ;
	pa0[m.pnSize], pa1[m.pnSize], pa2[m.pnSize], ;
	pa3[m.pnSize], pa4[m.pnSize], pa5[m.pnSize], ;
	pa6[m.pnSize], pa7[m.pnSize], pa8[m.pnSize], ;
	pa9[m.pnSize]

FOR JJ=0 TO 3
	IF LEFT(m.pcPersType, 1)="T" 
		IF INDBC("TEstIndex","TABLE")
			DROP TABLE TestIndex
		endif
		DELETE FILE "TestINdex.*"
	endif
	DO CASE
		CASE TYPEFIELD=1
			IF m.pnSize>5000 AND !m.COMPACTINDEX
				?? PADC("Skipped", 62) + SPACE(1)
				return
			endif
			CREATE &pcPersType TestIndex (F1 L DEFAULT RAND()>0.5)

		CASE TYPEFIELD=2
			CREATE &pcPersType TestIndex (F1 I DEFAULT RAND()*100000)

		CASE TYPEFIELD=3
			CREATE &pcPersType TestIndex (F1 D DEFAULT DATE()+RAND()*10000)

		CASE TYPEFIELD=4
			CREATE &pcPersType TestIndex (F1 T DEFAULT DATETIME()+RAND()*10000)

		CASE TYPEFIELD=5
			CREATE &pcPersType TestIndex (F1 N(15,8) DEFAULT RAND()*100000)

		CASE TYPEFIELD=6
			CREATE &pcPersType TestIndex (F1 C(20) DEFAULT REPLICATE(CHR(RAND()*256),RAND()*021))

		CASE TYPEFIELD=7 AND VERSION(5)>=900
			CREATE CURSOR TestIndex (F1 V(99) DEFAULT REPLICATE(CHR(RAND()*256),RAND()*100))
		OTHERWISE
			pcFldType = ""
			return
	ENDCASE
	= AFIELDS(laF)
	pcFldType = laF[1,2]
	
	IF m.COMPACTINDEX
		lcDxFile = IIF(EMPTY(m.pcDiskDx), "", ADDBS(m.pcDiskDx)) + "ONECDX.CDX"
	*	INDEX ON F1 TAG SCDX
		INDEX ON F1 TAG NCDX OF (m.lcDxFile)
	*	INDEX ON F1 TO TAGIDX.IDX COMPACT && THIS IS USELESS
	ELSE
		lcDxFile = IIF(EMPTY(m.pcDiskDx), "", ADDBS(m.pcDiskDx)) + "TAGIDX.IDX"
		INDEX ON F1 TO (m.lcDxFile)
	ENDIF
	pcKey = KEY()
	lcOrder = ORDER()

	SET ORDER TO

	lnT1=SECONDS()
	FOR K=1 TO RecMany()
		APPEND BLANK
	NEXT
	lntf=m.lntf+SECONDS()-m.lnt1

	lnT1=SECONDS()
	REINDEX
	lntr=m.lntr+SECONDS()-m.lnt1
	
	SET ORDER to &lcOrder
	= SeekInit()
	lnT1=SECONDS()
	= SeekTest()
	lnts=m.lnts+SECONDS()-m.lnt1
	


	SET ORDER TO
	SELECT f1 FROM TestIndex INTO CURSOR dup nofilter
	GO BOTTOM in DUP
	SELECT TestIndex
	lnT1=SECONDS()
	Scan
		replace f1 WITH dup.f1
		SKIP -1 IN DUP
	ENDSCAN
	lntU=m.lntU+SECONDS()-m.lnt1
	
	
	SET ORDER TO &lcOrder
	SELECT Dup
	SET RELATION TO F1 INTO TestIndex
	lnT1=SECONDS()
	SCAN
		lux = TestIndex.F1
	ENDSCAN
	lntM=m.lntM+SECONDS()-m.lnt1
	
	SET RELATION TO 
	lnT1=SECONDS()
	pnSqlMany = 0
	SCAN FOR RECNO()< max(3, RECCOUNT()/1000-10)
		pnSqlMany = m.pnSqlMany + 1
		lu1 = f1
		SKIP 10
		lu2 = f1
		SKIP -10
		luMin = MIN(m.lu1, m.lu2)
		luMax = MAX(m.lu1, m.lu2)
		SELECT * from TestIndex WHERE BETWEEN(&pcKey, m.luMin, m.luMax) INTO Cursor laDummy
	EndScan
	lntQ=m.lntQ+SECONDS()-m.lnt1
	
	USE
	= CleanBetWeen()
NEXT
?? STR(m.lnTF/m.jj,6,3), ;
	STR(m.lntr/m.jj,6,3), ;
	STR(m.lnts/m.jj,6,3), ;
	STR(m.lntu/m.jj,6,3), ;
	STR(m.lntm/m.jj,6,3), ;
	STR(m.lntq/m.jj,6,3), ;
	SPACE(5)

FUNCTION RecMany
RETURN 7*m.pnSize

FUNCTION CleanBetween()
	= SYS(1104)
	private lnWait
	lnWait = MIN(MAX(0.1, RecMany()/20000),1)
	WAIT WINDOW "CoolIt" + STR(m.lnWait, 6,2) TimeOut m.lnWait

FUNCTION SeekInit()
private lcKey
lcKey = KEY()
LOCAL lnRun
FOR lnRun = 1 TO m.pnSize
	pa0[m.lnRun] = SeekFill()
	pa1[m.lnRun] = SeekFill()
	pa2[m.lnRun] = SeekFill()
	pa3[m.lnRun] = SeekFill()
	pa4[m.lnRun] = SeekFill()
	pa5[m.lnRun] = SeekFill()
	pa6[m.lnRun] = SeekFill()
	pa7[m.lnRun] = SeekFill()
	pa8[m.lnRun] = SeekFill()
	pa9[m.lnRun] = SeekFill()
NEXT

FUNCTION SeekFill()
	GO INT(MAX(1, MIN(RECCOUNT(), RAND()*RECCOUNT())))
	RETURN EVALUATE(m.pcKey)
	
FUNCTION SeekTest
private lnRun 
FOR lnRun = 1 TO m.pnSize
	= SEEK(pa0[m.lnRun])
	= SEEK(pa1[m.lnRun])
	= SEEK(pa2[m.lnRun])
	= SEEK(pa3[m.lnRun])
	= SEEK(pa4[m.lnRun])
	= SEEK(pa5[m.lnRun])
	= SEEK(pa6[m.lnRun])
	= SEEK(pa7[m.lnRun])
	= SEEK(pa8[m.lnRun])
	= SEEK(pa9[m.lnRun])
NEXT