Little speed test app dbf and adordd
Posted: Mon Mar 21, 2016 8:59 am
Dear member,
I am trying to compare the speed of dbf(cdx/ntx/ads) with adordd. I want to improve the speed of adordd, but before that I should know what the current situation is exactly. Therefor I made a little testapp, especially to compare the DbSeek function for dbf and adordd.
Is there a way to count the duration more precisely? Now sometimes it gives 0.00 (with dbf).
I am using something like this:
I have also tested MsgInfo( 5 / 0 ) which gives 0. I think this should give an error.
Pieter
I am trying to compare the speed of dbf(cdx/ntx/ads) with adordd. I want to improve the speed of adordd, but before that I should know what the current situation is exactly. Therefor I made a little testapp, especially to compare the DbSeek function for dbf and adordd.
Is there a way to count the duration more precisely? Now sometimes it gives 0.00 (with dbf).
I am using something like this:
Code: Select all
nsecs := Seconds()
USE LargeDbf
duration1 := Seconds() - nsec
memowrit("log.txt", Memoread("log.txt") + "|" + "USE LargeDbf: " + str(duration1)
Code: Select all
#include "Adordd.ch"
#include "adodef.ch"
static oCn
PROCEDURE Main()
//createLargeDbfFile()
//ConvertDbf()
array1Dbf := RunDbSeekTest(.F.,10000)
array2Ado := RunDbSeekTest(.T.,10000)
DbfTimesFasterThanAdorddUse := array2Ado[1] / array1Dbf[1]
DbfTimesFasterThanAdorddIndex := array2Ado[2] / array1Dbf[2]
DbfTimesFasterThanAdorddDBSEEK := array2Ado[3] / array1Dbf[3]
DbfTimesFasterThanAdorddAVDBSEEK := array2Ado[4] / array1Dbf[4]
memowrit("log.txt",MemoRead("log.txt") + " " + "DbfTimesFasterThanAdorddUse: " + str(DbfTimesFasterThanAdorddUse) + " " + "DbfTimesFasterThanAdorddIndex: " + str(DbfTimesFasterThanAdorddIndex) + " " + "DbfTimesFasterThanAdorddDBSEEK: " + str(DbfTimesFasterThanAdorddDBSEEK) + " " + "DbfTimesFasterThanAdorddAVDBSEEK: " + str(DbfTimesFasterThanAdorddAVDBSEEK) )
RETURN
FUNCTION RunDbSeekTest(bSQL, numberOfLoop)
memowrit("log.txt",MemoRead("log.txt") + "Begin DbSeek=" + "SQL: " + IF(bSQL, "TRUE", "FALSE") + "|numberOfLoop: " + str(numberOfLoop) + " ")
IF bSQL
//MsgInfo( ADOVERSION() )
******
REQUEST ADORDD
RddRegister("ADORDD",1)
RddSetDefault("ADORDD")
SET ADO FORCE LOCK OFF // Required!
SET ADO DEFAULT DATABASE TO "dbseekdb" SERVER TO "localhost" ENGINE TO "MYSQL" USER TO "pieter" PASSWORD TO "password"
SET ADO TEMPORAY NAMES INDEX LIST TO {"TMP","TEMP"}
SET ADO DEFAULT RECNO FIELD TO "HBRECNO"
SET ADO DEFAULT DELETED FIELD TO "HBDELETED"
SET ADODBF TABLES INDEX LIST TO { {"LARGEDBF", {"CO1", "COLUMN1"}, {"CO2", "COLUMN2"}}}
SET AUTOPEN ON
ELSE
REQUEST DBFCDX
RddRegister("DBFCDX",1)
RddSetDefault("DBFCDX")
ENDIF
nsecs := Seconds()
USE LargeDbf
duration1 := Seconds() - nsecs
memowrit("log.txt", Memoread("log.txt") + "|" + "USE LargeDbf: " + str(duration1))
nsecs := Seconds()
INDEX ON COLUMN1 TO LargeDbf
duration2 := Seconds() - nsecs
memowrit("log.txt", Memoread("log.txt") + "|" + "INDEX ON COLUMN1 TO LargeDbf: " + str(duration2))
nsecs := Seconds()
FOR i:= 1 TO numberOfLoop
DBSEEK(numberOfLoop / 2)
NEXT
duration3 := Seconds() - nsecs
memowrit("log.txt", Memoread("log.txt") + "|" + "FOR DBSEEK Duration: " + str(duration3))
AverageDuration := duration3 / numberOfLoop
memowrit("log.txt", Memoread("log.txt") + "|" + "Average DBSEEK Duration: " + str(AverageDuration))
RETURN {duration1, duration2, duration3, AverageDuration }
FUNCTION createLargeDbfFile()
Create StructureLargeDbf
APPEND BLANK
FIELD->FIELD_NAME := "Column1"
FIELD->FIELD_TYPE := "N"
FIELD->FIELD_LEN := 8
FIELD->FIELD_DEC := 0
FOR i:=2 TO 20
APPEND BLANK
ts := ALLTRIM(STR(i,8))
FIELD->FIELD_NAME := "Column" + ts
FIELD->FIELD_TYPE := "C"
FIELD->FIELD_LEN := 20
FIELD->FIELD_DEC := 0
NEXT
CLOSE
CREATE LargeDbf FROM StructureLargeDbf
FOR j:=1 TO 20000
APPEND BLANK
FOR x := 1 TO 20
IF x = 1
REPLACE Column1 WITH j
ELSE
ts := ALLTRIM(STR(x,3))
ts2 := ALLTRIM(STR(HB_RandomInt(1000),5))
ts3 := "Column" + ts
ts4 := "td" + ts2
REPLACE &ts3 WITH ts4
ENDIF
NEXT
NEXT
CLOSE
RETURN NIL
function ConvertDbf()
CreateDatabaseIfNotYetExist( "dbseekdb" )
ConnectWithDatabase( "dbseekdb" )
FW_AdoImportFromDBF( oCn, "C:\Pieter\LeerOmgevingHarbour\DbSeek\largedbf.dbf", "largedbf")
RETURN
//----------------------------------------------------------------------------//
FUNCTION CloseDatabase()
oCn:Close()
RETURN NIL
FUNCTION ConnectWithDatabase( vardb )
ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE &vardb USER pieter PASSWORD "password" // PASSWORD ...
if oCn == nil .or. oCn:State < 1
MsgInfo( "Connect failed" )
return nil
endif
//MsgInfo( "Connection Open" )
RETURN NIL
FUNCTION CreateDatabaseIfNotYetExist( vardb )
local oError
ADOCONNECT oCn TO MYSQL SERVER localhost USER pieter PASSWORD password
if oCn == nil
MsgInfo( "Not connected" )
else
if oCn:State > 0
//MsgInfo( "open" )
TRY
oCn:Execute( "CREATE DATABASE " + vardb )
//MsgInfo( "created" )
CATCH oError
MsgInfo( "The database already exists" )
END
else
MsgInfo( "not open" )
endif
endif
oCn:Close()
Return nil