Choosing SQL database ...

[Adolfo]

Marcelo...

That's the idea...

Measure the traffic in both situations, DBF's and ADO with similar Tables, size, n° of records and fields.
I Will put times, and number of bytes transmited and received in both cases. I made early tests with NETWORK PROBE 3.0 a very reliable network tool, freeware. and I have an administrated switch also. in order to check both results.

Any hint or idea will be welcome.

From chile
Adolfo

[James Bott]

Adolfo,

Marcelo is right about the index loading. If the index is complex it could be similar in size to a recordset containing only a few fields. However, if the index contains only one field and the recordset contains lots of fields, (which is more typical) the index would be much smaller.

We will all be anxiously awaiting your test results.

Regards,
James

[Adolfo]

Hi everybody..

I did the test this weekend. Got the results ( with some surprises...)

I'm going to tabulate them in an excel sheet, and upload it in an hour.

From Chile
Adolfo

[Adolfo]

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.xls


Conclusions
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

[reinaldocrespo]

Hi.

I'd like to see the SeekIncremental() function code as well, please?

Reinaldo.

[Enrico Maria Giordano]

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.

This is expected. The slowness in getting data is exactly the problem. But it's not an ADO problem. It's the SQL way.

EMG

[Adolfo]

Enrico...

Paging does a good trick when retrieving data is involved... both on the server ( because the DB and the ADO client supports PAGES ) or by prg code, for instance, I get a 30 items browse and I takes 0,00001 second to get filled, instead of the 4 seconds for the 400.000 items in the test table.

Now after a 10 minutes browsing the same DBF, I got 35 mb traffic, while with ADO... nothing.

Reinaldo..

This 15 lines do the trick....

Code: Select all  Expand view

FUNCTION SeekIncremental(cBuscar,oRcs,cOrden)
LOCAL nLen:=Len(cBuscar)
   STATIC nLenAnt
   DEFAULT nLenAnt:=0

   IF Len(cBuscar)>1
      IF(nLenAnt>nLen,oRcs:MoveFirst(),)
      oRcs:Find( cOrden + " like '"+cBuscar+"*'",,1)
   ELSE
      oRcs:MoveFirst()
      IF !Empty(cBuscar)
         oRcs:Find( cOrden + " like '"+cBuscar+"*'",,1)
      ENDIF
   ENDIF
   nLenAnt:=nLen

RETURN !(oRcs:Eof .OR. oRcs:Bof)

Now you have tons of info in the web related to ADO, which helps us a lot to do "things" we can't do or are too difficult with plain SQL

From Chile
Adolfo

[reinaldocrespo]

I'm not sure if you already did, but would you please point again to that thread where you publish your tado class?

Thank you,



Reinaldo.

[James Bott]

OK, if I am reading the data properly, in all tests it took more network traffic to get to the end of the search result using a recordset than using a DBF (although not much of a difference). A further conclusion is that if the database was 10 times larger there would be ten times the network traffic using a recordset and NO more network traffic using a DBF.

I will also point out that in this case, if the database was smaller that a recordset might require less traffic than the DBF.

This test does confirm that only the records needed for the browse display are loaded when a DBF is used--not the entire database.

I suppose we can assume that the time to achieve the search result is relative to the size of the network traffic, but we don't know what that relationship is. As Adolfo stated, a 400000 record recordset loads in only 4 times the time of a 27000 record recordset. One would expect it to take 15 times as long (400000/27000 = 14.8 ), so there are other network issues that affect load time in addition to recordset size.

It would have been nice to have the times for the tests also.

Also, keep in mind that the record size and the total size of the fields in the recordset will affect the results (and the comparison).

So, in general, the larger the database the slower it will be to find a record using a recordset than a DBF. However, if the user needs to lookup lots of records a recordset may be faster. One also needs to decide if the user will even notice the time differences. They will notice 4 seconds vs 1 second but probably will not notice 1 sec vs 1/4 sec.

Another point to consider is that applications usually have a number of different databases (tables) of different record sizes and numbers of records, so choosing which database type (SQL or DBF) is not a simple matter size one may be faster in some situations and the other faster in other situations.

On a slightly different topic, I'm not sure about this, but isn't a recordset static--if another user updates a record then the recordset does not reflect that change? If true, this could be an issue to be concerned about with frequently changing databases and if a user keeps the browse running for long periods of time. DBF browsing always retrieves the record from disk when the record is displayed.

Thanks for doing this test Adolfo.

Regards,
James

[Adolfo]

I'm not sure if you already did, but would you please point again to that thread where you publish your tado class?

Thank you,



Reinaldo.

Reinaldo

Here you have the post.
Download it, try it, comment it

http://forums.fivetechsupport.com/viewtopic.php?f=6&t=17178


From Chile
Adolfo

[Enrico Maria Giordano]

Paging does a good trick when retrieving data is involved...

Sorry, but paging can't be achieved using standard SQL, that was my earlier objection.

EMG

[Adolfo]

Paging does a good trick when retrieving data is involved...

Sorry, but paging can't be achieved using standard SQL, that was my earlier objection.

EMG

Enrico.. download my class.

See the example...
See methods PgUp,PageDown,FirstPage,LastPage... a minimal modification on Skip Method, and you have PAGING, using standard SQL thru ADO ( although ADO does nothing but create the Recordset ), you can see how I build the select that does the "magic"

See the example, Menu Item "Mantenciones", Option "PAGINADO 1*1"

That's how I Emplemented IT

From Chile
Adolfo

[Enrico Maria Giordano]

You are using LIMIT clause that is NOT standard SQL.

EMG

[Adolfo]

You are using LIMIT clause that is NOT standard SQL.

EMG

mmm
yep.. you are rigth.. but you have the equivalent in.

Mysql= Select * from clientes limit 10
Sql Server= select top 10 from clientes
Oracle=select * from cliente where rownum <= 10
Postgress=select * from clientes limit 10
Sybase= set rowcount 10; select * from clientes
DB2=select * from clientes fetch 10 first 20 rows only
Select * from clientes where RN between 10 and 20

so.. although is not "STANDARD" sql you have the equivalent in ALL main dbs'.. now you are done... tou can do paging with them... well not in STANDARD sql, but paging.

From Chile
Adolfo

[Enrico Maria Giordano]

Ok. But this was exactly the point. We can't do fast incremental searching using SQL in an engine-independent way. SQL was been designed to be a "query-language" not for the modern interactive applications.

EMG