CLEAR = idxtstRun() = idxtstRun(0200, 10, 050, "C:") = idxtstRun(1000, 02, 500, "F:", "D:") FUNCTION idxtstrun PARAMETERS tnSeekCountDiv10, tnRecCntFactor, tnNoBoolIdx, tcOptDefDir, tcOptIdxDir, tlOptUseCursor * tnSeekCountDiv10 : the number of seeks to be executed div 10 (using an array for older versions). * tnRecCntFactor : the mutiplier for Parameter 1 to get the number of records to be used. * tnNoBoolIdx : Logical fields can take some time when measuring idx, * therefore a numerical border to skip this measurement can be set in the third parameter. * Optional paths * tcOptDefDir : switching the directory the test is run in * (I've disks with different characteristics here from Ramdisk over SCSI, UDMA, downto laptop disks). * tcOptIdxDir : Also you can set the path for the non-structural idx/cdx to another (faster) disk. * tlOptUseCursor : You can measure cursor perfomance optionally, default is table. private pcPersType, pcFldType, pnSqlMany SET TALK OFF SET SAFETY OFF CLOSE DATABASES all CLOSE TABLES all _Screen.FontSize = 10 _Screen.FontName = "Arial" pnSqlMany = 0 tnSeekCountDiv10 = IIF(EMPTY(m.tnSeekCountDiv10), 100, m.tnSeekCountDiv10) tnRecCntFactor = IIF(EMPTY(m.tnRecCntFactor), 5, m.tnRecCntFactor) tnNoBoolIdx = IIF(EMPTY(m.tnNoBoolIdx), 5000, m.tnNoBoolIdx) LOCAL lcOldDir lcOldDir = SYS(5) + CURDIR() IF VARTYPE(m.tcDefDir)="C" SET DEFAULT TO &tcDefDir ENDIF pcPersType = IIF(m.tlOptUseCursor, "Cursor", "Table") pcFldType = "" CREATE DATABASE TestIndex ? "FIELDTYPE","COMPACT" AT 30,"IDX" AT 80,"COMPACT" AT 120 lcTitle = "Fill, ReInd, Seek, Updt, Scan, Query" ? lcTitle at 15, lcTitle at 60, lcTitle at 105 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 ? "Done with Fill, ReInd, Update, Scan with Records:" + STR(Recmany()) ; + ", No. of Seeks: " + STR(10*m.tnSeekCountDiv10) ; + ", No. of Queries:" + STR(m.pnSqlMany) ; + " With: " + m.pcPersType ; + " in " + SYS(5) + CURDIR() ; + ", indices in " + Indexfilename("*.?dx") ; + ", vfp:" + VERSION(4) SET DATABASE TO CLOSE DATABASES all DELETE FILE TestIndex.dbc DELETE FILE TestIndex.dct DELETE FILE TestIndex.dcx IF LEFT(m.pcPersType, 1)="T" DELETE FILE "TestINdex.dbf" DELETE FILE "TestINdex.cdx" endif DELETE FILE (indexfilename("ONECDX.CDX")) DELETE FILE (indexfilename("TAGIDX.IDX")) SET TALK On SET SAFETY On SET DEFAULT TO &lcOldDir ? 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.tnSeekCountDiv10], pa1[m.tnSeekCountDiv10], pa2[m.tnSeekCountDiv10], ; pa3[m.tnSeekCountDiv10], pa4[m.tnSeekCountDiv10], pa5[m.tnSeekCountDiv10], ; pa6[m.tnSeekCountDiv10], pa7[m.tnSeekCountDiv10], pa8[m.tnSeekCountDiv10], ; pa9[m.tnSeekCountDiv10] FOR JJ=0 TO 3 IF LEFT(m.pcPersType, 1)="T" IF INDBC("TEstIndex","TABLE") DROP TABLE TestIndex endif DELETE FILE "TestINdex.dbf" DELETE FILE "TestINdex.cdx" endif DO CASE CASE TYPEFIELD=1 IF m.tnSeekCountDiv10>tnNoBoolIdx 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 = indexfilename("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 = indexfilename("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 indexfilename(tcFilename) RETURN IIF(EMPTY(m.tcOptIdxDir), "", ADDBS(m.tcOptIdxDir)) + m.tcFilename FUNCTION RecMany RETURN m.tnRecCntFactor*m.tnSeekCountDiv10 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.tnSeekCountDiv10 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.tnSeekCountDiv10 = 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
