Little speed test app dbf and adordd

Post Reply
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Little speed test app dbf and adordd

Post by pieter »

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:

Code: Select all | Expand

nsecs := Seconds()USE LargeDbfduration1 := Seconds() - nsecmemowrit("log.txt", Memoread("log.txt") + "|" + "USE LargeDbf: " + str(duration1) 


I have also tested MsgInfo( 5 / 0 ) which gives 0. I think this should give an error.

Code: Select all | Expand

#include "Adordd.ch"#include "adodef.ch"static oCnPROCEDURE 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) )RETURNFUNCTION RunDbSeekTest(bSQL, numberOfLoop)memowrit("log.txt",MemoRead("log.txt") + "Begin DbSeek=" + "SQL: " + IF(bSQL, "TRUE", "FALSE") + "|numberOfLoop: " + str(numberOfLoop) + " ")IF bSQL//MsgInfo( ADOVERSION() )******REQUEST ADORDDRddRegister("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 ONELSEREQUEST DBFCDXRddRegister("DBFCDX",1)RddSetDefault("DBFCDX")ENDIFnsecs := Seconds()USE LargeDbfduration1 := Seconds() - nsecsmemowrit("log.txt", Memoread("log.txt") + "|" + "USE LargeDbf: " + str(duration1))nsecs := Seconds()INDEX ON COLUMN1 TO LargeDbfduration2 := Seconds() - nsecsmemowrit("log.txt", Memoread("log.txt") + "|" + "INDEX ON COLUMN1 TO LargeDbf: " +  str(duration2))nsecs := Seconds()FOR i:= 1 TO numberOfLoop  DBSEEK(numberOfLoop / 2) NEXTduration3 := Seconds() - nsecsmemowrit("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 StructureLargeDbfAPPEND BLANKFIELD->FIELD_NAME := "Column1"FIELD->FIELD_TYPE := "N"FIELD->FIELD_LEN  := 8FIELD->FIELD_DEC  := 0FOR 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  := 0NEXTCLOSECREATE LargeDbf FROM StructureLargeDbfFOR 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    NEXTNEXTCLOSERETURN NILfunction 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 


Pieter
User avatar
Antonio Linares
Site Admin
Posts: 42564
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain
Has thanked: 36 times
Been thanked: 82 times
Contact:

Re: Little speed test app dbf and adordd

Post by Antonio Linares »

Pieter,

Use GetTickCount() instead of Seconds()
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Re: Little speed test app dbf and adordd

Post by pieter »

Antonio,

Thanks, I will use GetTickCount().

Pieter
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Re: Little speed test app dbf and adordd

Post by pieter »

Hi,

Results:
Number of loops 10000:
DbSeek(5000):
according to my test Dbf is more than 3000 times faster than adordd for DbSeek(5000)

Code: Select all | Expand

nCount := GetTickCount()FOR i:= 1 TO numberOfLoop  //10000DBSEEK(numberOfLoop / 2) //5000NEXTduration3 := GetTickCount() - nCountmemowrit("log.txt", Memoread("log.txt") + "|" + "FOR DBSEEK Duration: " + str(duration3) 


Code: Select all | Expand

DbfTimesFasterThanAdorddDBSEEK := array2Ado[3] / array1Dbf[3] //for three times testing it gives: 3388.69, 3462.88 ,3337.88 


I want to make my results overview a bit better. I have now everthing on one line in log.txt

Question:
How can I add a new line in memowrit("log.txt", Memoread("log.txt") + "|" + "FOR DBSEEK Duration: " + str(duration3)? (I thought something with \n)

Pieter
User avatar
Antonio Linares
Site Admin
Posts: 42564
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain
Has thanked: 36 times
Been thanked: 82 times
Contact:

Re: Little speed test app dbf and adordd

Post by Antonio Linares »

Pieter,

#include "FiveWin.ch"

... + CRLF + ...
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Re: Little speed test app dbf and adordd

Post by pieter »

Antonio, Great!, I got a better results overview now.
User avatar
Enrico Maria Giordano
Posts: 8759
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 4 times
Contact:

Re: Little speed test app dbf and adordd

Post by Enrico Maria Giordano »

pieter wrote:according to my test Dbf is more than 3000 times faster than adordd for DbSeek(5000)


Is your DBF test over a LAN?

EMG
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Re: Little speed test app dbf and adordd

Post by pieter »

Hi Enrico,

"Is your DBF test over a LAN?"

No, I use everthing locally on my operating system, Dbf and mysql server.

I am curious whether other people have better speed results for adordd. I think that dbf is very fast on local operating system compared with adordd. (without LAN etc.)

Pieter
User avatar
Enrico Maria Giordano
Posts: 8759
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 4 times
Contact:

Re: Little speed test app dbf and adordd

Post by Enrico Maria Giordano »

pieter wrote:No, I use everthing locally on my operating system, Dbf and mysql server.


So your test is not meaningful. You have to make both test over a network to get significant results.

EMG
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Re: Little speed test app dbf and adordd

Post by pieter »

Enrico,

My companies main app is actually a kind of desktop app. With a remote desktop tool one can acces the app from a distance. So I think the main app does not use something like sending data via LAN.

"So your test is not meaningful. You have to make both test over a network to get significant results."

Can you explain why both tests have to be over a network to get significant results?

Pieter
User avatar
Enrico Maria Giordano
Posts: 8759
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 4 times
Contact:

Re: Little speed test app dbf and adordd

Post by Enrico Maria Giordano »

pieter wrote:Can you explain why both tests have to be over a network to get significant results?


Because SQL has the client/server overhead even if installed locally.

EMG
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Re: Little speed test app dbf and adordd

Post by pieter »

Enrico, Thanks.

"Because SQL has the client/server overhead even if installed locally".

Do you mean with "SQL" a sql server such mysql or ado or adordd?

Pieter
User avatar
Enrico Maria Giordano
Posts: 8759
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 4 times
Contact:

Re: Little speed test app dbf and adordd

Post by Enrico Maria Giordano »

pieter wrote:Do you mean with "SQL" a sql server such mysql or ado or adordd?


Yes, a SQL server. ADO and ADORDD are just interface to a SQL server.

EMG
User avatar
pieter
Posts: 117
Joined: Thu Jan 08, 2015 9:27 am

Re: Little speed test app dbf and adordd

Post by pieter »

Oké!, I understand.

How can I do something with a network so that I can have a good comparison between dbf and sql/adordd?

Results:
Begin DbSeek=SQL: FALSE numberOfLoop: 10000
USE LargeDbf: 0
INDEX ON COLUMN1 TO LargeDbf: 15
FOR DBSEEK Duration: 16
Average DBSEEK Duration: 0.00

Begin DbSeek=SQL: TRUE numberOfLoop: 10000
USE LargeDbf: 8344
INDEX ON COLUMN1 TO LargeDbf: 3422
FOR DBSEEK Duration: 62469
Average DBSEEK Duration: 6.25

DbfTimesFasterThanAdorddUse: 0
DbfTimesFasterThanAdorddIndex: 228.13
DbfTimesFasterThanAdorddDBSEEK: 3904.31
DbfTimesFasterThanAdorddAVDBSEEK: 3904.31


Code: Select all | Expand

#include "Adordd.ch"#include "adodef.ch"#include "fivewin.ch"static oCnPROCEDURE 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) +  CRLF  + "DbfTimesFasterThanAdorddIndex: " + str(DbfTimesFasterThanAdorddIndex) +  CRLF  + "DbfTimesFasterThanAdorddDBSEEK: " + str(DbfTimesFasterThanAdorddDBSEEK) + CRLF + "DbfTimesFasterThanAdorddAVDBSEEK: " + str(DbfTimesFasterThanAdorddAVDBSEEK) + CRLF + CRLF + CRLF)RETURNFUNCTION RunDbSeekTest(bSQL, numberOfLoop)memowrit("log.txt",MemoRead("log.txt") + "Begin DbSeek=" + "SQL: " + IF(bSQL, "TRUE", "FALSE") + " " + "numberOfLoop: " + str(numberOfLoop) + CRLF )IF bSQL//MsgInfo( ADOVERSION() )******REQUEST ADORDDRddRegister("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 ONELSEREQUEST DBFCDXRddRegister("DBFCDX",1)RddSetDefault("DBFCDX")ENDIFnCount := GetTickCount()USE LargeDbfduration1 := GetTickCount() - nCountmemowrit("log.txt", Memoread("log.txt") + "USE LargeDbf: " + str(duration1) + CRLF )nCount := GetTickCount()INDEX ON COLUMN1 TO LargeDbfduration2 := GetTickCount() - nCountmemowrit("log.txt", Memoread("log.txt")  + "INDEX ON COLUMN1 TO LargeDbf: " +  str(duration2) + CRLF)nCount := GetTickCount()FOR i:= 1 TO numberOfLoop  DBSEEK(numberOfLoop / 2) NEXTduration3 := GetTickCount() - nCountmemowrit("log.txt", Memoread("log.txt") + "FOR DBSEEK Duration: " + str(duration3) + CRLF )AverageDuration := duration3 / numberOfLoop memowrit("log.txt", Memoread("log.txt") + "Average DBSEEK Duration: " + str(AverageDuration) + CRLF + CRLF)RETURN {duration1, duration2, duration3, AverageDuration }FUNCTION createLargeDbfFile()Create StructureLargeDbfAPPEND BLANKFIELD->FIELD_NAME := "Column1"FIELD->FIELD_TYPE := "N"FIELD->FIELD_LEN  := 8FIELD->FIELD_DEC  := 0FOR 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  := 0NEXTCLOSECREATE LargeDbf FROM StructureLargeDbfFOR 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    NEXTNEXTCLOSERETURN NILfunction ConvertDbf()     CreateDatabaseIfNotYetExist( "dbseekdb2" )    ConnectWithDatabase( "dbseekdb2" )      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


Pieter
Post Reply