Hi everybody.
Here you have the results for the test I did.
Client PC
Pentium 4
1 gb Ram
Xp Professional SP2
Ethernet DLink 100mb
Switch
TENDA 24 + 2 Web Administrable
Mysql Server
Athlon Xp
2 Gb Ram
Linux Fedora Core 8
Mysql 5.0.01
Ethernet Realtek 100MB
File Server
Pentium 4
4 Gb Ram
Windows 2003 Server
Ethernet Dlink 1000 Gb ( but connected to a 100MB Switch port )
Methodology.
I Started Network Probe and reset counters on my Switch.
On each test, I reset all counters on NP and the switch, did the test, reboot, and reset counters again.
I repeated each test 3 times, so every value of bytes and seconds is the average of 3 tests, which were not very different one from each other.
I use 2 diferent DBFS, which I uploaded to my Mysql Server with NAVICAT MYSQL, I did an IMPORT of data, both structures are similar, and content is identical.
The test.
3 Different cathegories of test, Times, bytes recieved and bytes sent.
First the elapsed times since I open the dbf or Recordset, and how much does xBrowse take to show the data. The second one, is identical, but when you do it the second time. It means, open the dbf, show the xBrowse, close it, open the dbf and show the xbrowse again... and take these times ( here dbf's in cache are almost intantaneous ).
Bytes Recieved. Here i did 4 routines.
1.- Open the recordset or dbf, and fill the xbrowse, took the traffic for the ports involved in the process, 3306 for Mysql and 137, 138, 139, 445 for DBF. ( which by the way were the only ones with actual traffic as seen on the network tool , and on the switch )
2.- The same, but this time I write something to make the incremental search work and have some extra traffic.
3.- Idem as N°2, but this time I wrote 3 letters, backspace 3 times, and 3 new keystrokes, so I "move" the record pointer to the midle, then to the end.
4.- Very simple, just 3 times PgDown, then 3 times pageup to end were I started
The loading of a complete record to show it, is instantaneous in both cases ( less than 0,0001 second) so I discarded it.
I used 2 dbf's, one with 400.000 records, and the other with only 27.000, and repeat the test for both in the same conditions
You can donwload the results from here
http://200.72.140.34/privado/adobase/TEST%20ADO.xlsConclusions
ADO's is slower when getting data, and also consumes more bandwith when you first call the xbrowse,but... thats all, after the job is done, no more net traffic is present. If you work with a dbf browse and navigate thru it , after some little searches or movements, you would have more bytes recieved than ADO. A 400.000 ado Recordset will take up to 4 seconds to retrieve, but a 27.000 recordset, less than a second. Well theres plenty of asumptioms to do with the results. Have a look at them and share your opinions.
Any questions on the test... just do them
Here's the main part of the code for 1 of the groups of test ( Clientes with 27000 records )
- Code: Select all Expand view
#include "FiveWin.ch"
#include "Ado.ch"
#include "inkey.ch"
#include "xBrowse.ch"
Static oWnd
//-------------------------------------------------------------------------------------
Function Main()
Public lMysql:=.F.
Request DBFFPT, DBFCDX
RDDSETDEFAULT("DBFCDX")
SET OPTIMIZE ON
SET EPOCH TO 1920
SET DATE FORMAT "dd/mm/yyyy"
SET WRAP ON
SET DELETED ON
DEFINE WINDOW oWnd FROM 1, 1 TO 25, 79 MDI ;
TITLE "Test Traffic" Menu BuildMenu()
ACTIVATE WINDOW oWnd MAXIMIZED
Return Nil
//--------------------------------------------------------------------
//--------------------------------------------------------------------
Function BuildMenu()
local oMenu
MENU oMenu 2007
MENUITEM "ADO"
MENU
MENUITEM "Ado Test" ACTION AdoTest()
ENDMENU
MENUITEM "DBF"
MENU
MENUITEM "DBF Test" ACTION DBFTest()
ENDMENU
MENUITEM "Exit"
MENU
MENUITEM "End" ACTION oWnd:End()
ENDMENU
ENDMENU
Return oMenu
//--------------------------------------------------------------------
//--------------------------------------------------------------------
Function Conectar()
Local Retorno:=.F.
Local rs, oErr,oCs
Local odbcli
Local cSelect
Public oCn
TRY
oCn:= CreateObject( "ADODB.Connection" )
oCs:= "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=192.168.0.1; DATABASE=test; UID=adolfo; PWD=300966"
oCn:Open(oCs)
Retorno:=.T.
CATCH oErr
MSGAlert("Error : No se tiene acceso al servidor de MYSQL " + CRLF + "por favor verifique conexion a red o que el servidor este funcionando", "Error: " + oErr:Operation + " -> " + oErr:Description )
Retorno:=.F.
END
Return Retorno
//-------------------------------------------------------------------------------------------------------------------------------------
//---------- Mysql Browse ------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
Function AdoTest()
Local oWndMain:=WndMain()
Local lExit:=.F.
Local oRcs
Local cSelect:="select RUT,RAZON,DIRECCION from CLIENTES order by RAZON"
Local oErrorAdo
Local TBO:=0
Local TAO:=0
Local TDO:=0
Local TBX:=0
Local TAX:=0
Local TDX:=0
If !Conectar()
Return Nil
Endif
TBO:=Seconds()
oRcs:=TOleAuto():New("ADODB.recordset")
oRcs:CursorLocation:= adUseClient
oRcs:LockType := adLockOptimistic
oRcs:CursorType := adOpenDynamic
oRcs:CacheSize := 30
oRcs:PageSize := 30
TRY
oRcs:Open(cSelect,oCn)
CATCH
FOR EACH oErrorAdo IN oCn:Errors
ShowError(oErrorAdo)
NEXT
END
oRcs:MoveFirst()
lMysql:=.T.
TAO:=Seconds()
TDO:=TAO - TBO
TBX:=Seconds()
Define Font oFontBold NAME "Arial" SIZE 9, 15 BOLD
DEFINE WINDOW oWnd02 FROM 1, 1 TO 25, 79 TITLE "MYSQL XBrowse Incremental Search" STYLE nOr( WS_CAPTION, WS_VISIBLE, WS_SYSMENU )
oBrw:=TxBrowse():New(oWnd02)
oBrw:nTop:=0
oBrw:nLeft:=0
oBrw:nMarqueeStyle := MARQSTYLE_HIGHLROW
oBrw:nColDividerStyle := LINESTYLE_BLACK
oBrw:nRowDividerStyle := LINESTYLE_BLACK
oBrw:lColDividerComplete := .T.
oBrw:l2007 :=.T.
oBrw:lAutoSort :=.T.
oBrw:bClrSelFocus := { | | { 16777215, 9539840 } }
aClrRow := { { CLR_BLACK, 14737602 }, { CLR_BLACK, 16777215 } }
oBrw:bClrStd := {|| aClrRow[ oBrw:KeyNo % 2 + 1 ] }
oBrw:nHeaderLines := 1
oBrw:nDataLines := 1
oBrw:lHScroll := .F.
oBrw:lVScroll := .T.
oBrw:lFooter := .T.
oBrw:bClrFooter := { || {CLR_HBLUE,CLR_WHITE} }
oBrw:bKeyDown :={| nKey, nFlags | MyKeyDown(nKey, nFlags, oBrw) }
oBrw:bKeyChar :={| nKey, nFlags | MyKeyChar(nKey, nFlags, oBrw) }
oBrw:SetAdo(oRcs)
oBrw:bSeek := { | c | SeekIncremental(UPPER(c),oRcs,"RAZON") }
oBrw:CreateFromCode()
oWnd02:oClient := oBrw
oBrw:SetFoCus()
SET MESSAGE OF oWnd02 TO " " 2007
DEFINE MSGITEM oBrw:oSeek OF oWnd02:oMsgBar ;
PROMPT "BUSQUEDA DE DATOS" ;
SIZE 350 COLOR CLR_RED FONT oFontBold
ACTIVATE WINDOW oWnd02 MAXIMIZED VALID ( oWnd02 := nil, lExit := .T. , .T.) ;
ON INIT (TAX:=Seconds() , TDX:=TAX-TBX , .T. )
StopUntil( {|| lExit} )
If oWndMain != nil
oWndMain:Show()
SysRefresh()
Endif
oRcs:Close()
SysRefresh()
MsgAlert( Str(TDO,10,5) + CRLF + STR(TDX,10,5) )
Return Nil
//-----------------------------------------------------------------------------------------------------------
Function MyKeyDown( nKey, nFlags, oBrw)
Return 0
//-----------------------------------------------------------------------------------------------------------
Function MyKeyChar( nKey, nFlags, oBrw )
Do Case
Case nKey == K_ENTER
ShowRecord(lMysql)
Endcase
Return 0
//-----------------------------------------------------------------------------------------------------------
Function ShowError(oError)
MsgInfo( "Descripción : " + oError:Description + CRLF + ;
"Error Nativo : " + Str(oError:NativeError) + CRLF + ;
"Número Error : " + Str(oError:Number) + CRLF + ;
"Origen : " + oError:Source + CRLF + ;
"Estado SQL : " + oError:SQLState )
Return nil
//-------------------------------------------------------------------------------------------------------------------------------------
//---------- DBF Browse ------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
Function DbfTest()
Local oWndMain:=WndMain()
Local lExit:=.F.
Local TBO:=0
Local TAO:=0
Local TDO:=0
Local TBX:=0
Local TAX:=0
Local TDX:=0
TBO=Seconds()
Use P:\TEST\clientes Alias Cli
Set Order To 1
DbGoTop()
lMysql:=.F.
TAO:=Seconds()
TDO:=TAO - TBO
TBX:=Seconds()
Define Font oFontBold NAME "Arial" SIZE 9, 15 BOLD
DEFINE WINDOW oWnd02 FROM 1, 1 TO 25, 79 TITLE "DBF XBrowse Incremental Search" STYLE nOr( WS_CAPTION, WS_VISIBLE, WS_SYSMENU )
oBrw:=TxBrowse():New(oWnd02)
oBrw:nTop:=0
oBrw:nLeft:=0
oBrw:nMarqueeStyle := MARQSTYLE_HIGHLROW
oBrw:nColDividerStyle := LINESTYLE_BLACK
oBrw:nRowDividerStyle := LINESTYLE_BLACK
oBrw:lColDividerComplete := .T.
oBrw:l2007 :=.T.
oBrw:lAutoSort :=.T.
oBrw:bClrSelFocus := { | | { 16777215, 9539840 } }
aClrRow := { { CLR_BLACK, 14737602 }, { CLR_BLACK, 16777215 } }
oBrw:bClrStd := {|| aClrRow[ oBrw:KeyNo % 2 + 1 ] }
oBrw:nHeaderLines := 1
oBrw:nDataLines := 1
oBrw:lHScroll := .F.
oBrw:lVScroll := .T.
oBrw:lFooter := .T.
oBrw:bClrFooter := { || {CLR_HBLUE,CLR_WHITE} }
oBrw:bKeyDown :={| nKey, nFlags | MyKeyDown(nKey, nFlags, oBrw) }
oBrw:bKeyChar :={| nKey, nFlags | MyKeyChar(nKey, nFlags, oBrw) }
// oBrw:SetRdd()
Add Column To oBrw DATA Cli->Rut
Add Column To oBrw DATA Cli->Razon
Add Column To oBrw DATA Cli->Direccion
oBrw:bSeek := {|c| DbSeek( Upper( c ) ,.T.) }
oBrw:CreateFromCode()
oWnd02:oClient := oBrw
oBrw:SetFoCus()
SET MESSAGE OF oWnd02 TO " " 2007
DEFINE MSGITEM oBrw:oSeek OF oWnd02:oMsgBar ;
PROMPT "BUSQUEDA DE DATOS" ;
SIZE 350 COLOR CLR_RED FONT oFontBold
ACTIVATE WINDOW oWnd02 MAXIMIZED VALID ( oWnd02 := nil, lExit := .T. , .T.) ;
ON INIT (TAX:=Seconds(),TDX:=TAX-TBX , .T. )
StopUntil( {|| lExit} )
If oWndMain != nil
oWndMain:Show()
SysRefresh()
Endif
MsgAlert( Str(TDO,10,5) + CRLF + STR(TDX,10,5) )
Close All
SysRefresh()
Return Nil
From Chile
Adolfo