XBrowse very slow in network with big databases (solved!)

[Antonio Linares]

Gilbert,

CDXs are much faster as they greatly reduce the network traffic.

i.e. if you have an application that uses 15 NTXs and have 10 users, then there are 150 indexes files managed over the network.

if the same application uses CDXs, then there are only 10 indexes files managed. What a big difference! The network traffic is reduced very much

[nageswaragunupudi]

After going through the above discussions, I tested browsing one of my large tables (600,000 records with record length of 733 bytes with 30 indexes ) over Local Area Network ( 2003 server and xp clients). Upto 5 users the performance is quite good and over 6 users the performance keeps falling rapidly with the number of users. This 6 users limit is something known to me for years even from the earlier DOS days.

If we are not getting this performance, its a matter of the kind of network we are using or the way we are managing the tables.

If we need the software to work for more users, better we either client-server environment or redesign the database ( splitting into smaller tables ).

Definitely WBrowse is the fastest. XBrowse and TCBrowse provide more features but a very small cost on performance. In most cases the differnce in performance is not perceptible. If we are in a doubt, we may frist try with WBrowse. There is no browse faster than wbrowse. Once we achieve acceptable performance on WBrowse, then we can convert to any other column browse incorporating other features, also employing a few tricks to make them work a little faster.

If we are not getting acceptable performance upto 5 users on a local area network even on wbrowse, we should look into our network and the way we use the RDD and browse parameters and definitely not into the capacities of the Browses.

[gkuhnert]

Antonio,

We tested our application with .cdx instead of .ntx and there is an improvement in time. But still xbrowse in network with .cdx is much slower than wbrowse with .ntx. The issue still is about access from at least two computers to the same database at the same time.

As wBrowse is fast, even on 5 computers at the same time using the same table, using .ntx-files, but xBrowse-speed is only acceptable, when limited to one computer, the main issue isn't likely to be with the type of indexfiles.
A single dbseek and refresh() of the browse now takes up to 3 seconds with a database of 190.000 records, while the same seek in wbrowse with .ntx takes less than a second (and in wbrowse with .cdx even much less) How can these differences been explained, if it is true, like James says, that the browser should not make diffence when a seek is done?

Is it possible, that there is a connection between the performance of xbrowse and the codeblocks, that are largely more available in xbrowse than wbrowse? And if there is, would it be possible, to construct a „light“ version of xbrowse, that would only handle needed codeblocks?

[James Bott]

Gilbert,

I suggest making a copy of xbrowse and putting in some code to write the time to a file. Put this code in several places to try to find where the delay is happening. The first place I would look is the Refresh() method. Also record the time immediately after the dbseek().

Here is a function you can use to get times in milliseconds.

Code: Select all  Expand view

DLL32 STATIC FUNCTION GETTICKCOUNT() AS DWORD;
      PASCAL FROM "GetTickCount" LIB "kernel32.dll"

James

[Antonio Linares]

Gilbert,

FiveWin Class TWBrowse was originally designed for 286 microprocessors computers and by that time we needed to get the fastest execution speed.

Anyhow, the speed of the browse should not be related to the amount of records that the database has.

How many columns do you show on the browse ?

[James Bott]

Gilbert,

Anyhow, the speed of the browse should not be related to the amount of records that the database has.

I agree with Antiono's statement above. You are doing a seek then displaying a fixed number of records. The seek might be slighlty slower with a larger database, but the browse redisplay should be the same regardless of the number of records.

This is not to say that TWBrowse and TXBrowse should redisplay at the same speed. TXBrowse is much more complicated and therefore there are many more lines of code to process with each redisplay. So, TXBrowse's redisplay is probably slower than TWBrowse's, but the number of records in the database should not have much of an effect on the redisplay, if any. After the seek you are just reading the next few records in the index and displaying them (with either browse).

James

[Rick Lipkin]

Gilbert

I am looking at this entire thread and if I am correct this setup is a peer to peer network .. meaning this is a 'work group' network ??

If this is the case .. I would seriously set aside a 'beefy' pc and use it as a deficated box just for you central share.

By centralizing your share, you take the local pc that is hosting the database out of the equasion and any 'other' processes that pc may be performing concurrently.

Again .. if I have read into your thread that this setup is truly a peer to peer .. I would HIGHLY recommend to your customer if they want to do simple networking .. dedicate a strong ( stand alone ) pc and use that as your 'share' for your application .. that will make a big difference in your performance.

Rick Lipkin
SC Dept of Health, USA

[Antonio Linares]

The number of shown columns makes a big difference when a TWBrowse or a TXBrowse is used:

i.e. a browse with 20 rows and 10 columns requires 20 codeblocks evaluations when a TWBrowse is used. But if a TXBrowse is used, then 200 codeblocks evaluations are required.

Besides those evaluations, if the colors for each column are based on codeblocks, then 200 more are done.

[gkuhnert]

Antonio and all others,

as of now, it looks like we tracked down the problem. After every dbseek() we called the Method olb:refresh(). After stopping the time (@James, thanks for your hint, but I used the Function Seconds() that is already available in Fivewin), I recognized, that not the dbseek() took so long, but the refresh-method took (and still takes)lots of time. Up to several seconds in a database with about 200.000 records. But simply removing refresh wasn't an option, because then the list wouldn't get refreshed. Instead of that I now use olb:select(0) and olb:select(1), then the list is also being repainted. But maybe there is a slightly more elegant solution for this repaint of data.

And maybe there also is a possibility to increase the speed of the refresh(), because xbrowse-refresh is much slower than wbrowse-refresh().

At all, thanks for sharing your thoughts.

[Enrico Maria Giordano]

I don't know if this makes any differences (it does using TWBrowse) but try

Code: Select all  Expand view

oBrw:Refresh( .F. )

EMG

[nageswaragunupudi]

I may be permitted to reiterate some points mentioned by me and other friends already.

It is well known that WBrowse is by far the fastest browse. XBrowse is not designed for speeds but to be feature rich. Defintiely xbrowse refreshes are slower than wbrowse. But in most cases, the speed diffferences are not perceptible.

If for any reason, the speed difference is preceptible in a specific case, obviously we need to choose wbrowse and thats where the matter should rest. XBrowse will never match WBrowse.

But I still maintain that even with huge tables the xbrowse speeds should not really be unsatisfactory. It is the data access that takes time. Once the data is fetched into the client PC's catche, whether it is xbrowse or wbrowse the screen painting times should not matter much.

Though we use ADS, for this topic I tested by browsing a large table with more than 600,000 records with about .75 kb record length with xbrowse and the speeds were quite acceptable upto 5 simultaneous users. ( With ADS we have more than 150 simultaneous users at any time Xbrowsing ). There were times when we handled nearly 3.5 GB tables ( now moved to Oracle).

If there are still problems, I insist the solutions should be sought elsewhere, and not in the browses. ( Hints: Removal of vert scroll bars reduces lot of avoidable data acess. Changing bKeyCount block helps. But all these are for finetuning ).

[James Bott]

Gilbert,

I just tested the speed of the xbrowse refresh with a database of 200,000 records on a very slow (11 Mbps) peer-to-peer network using a very slow Win98 computer as the server, and the time for the refresh is 0.01 seconds.

Granted my xbrowse was very simple with two columns, but it doesn't seem that the refresh is affected by the size of the database.

I think something else is going on either in your code or with your network that is causing the slowdown you are experiencing.

James

[nageswaragunupudi]

I agree with Mr James

When we are experiencing sluggishness in browsing, I suggest we first look into data access speeds. When we do seek and refresh, what any browse essentially does is (1) skip the table backwards till the top of browse and (2) skip forward number of rows to fit the browse display reading the data and then refresh the browse. We better keep all browses aside and test the time taken for the above operations with normal code. Any browse will not add more than a small fraction of a second to display in the grid.

I sugest a small function to test the data access speeds

Code: Select all  Expand view

func AccessTime( cSeek, nDataLines )

   local nSecs   := Seconds()
   local n, cText := ''
   local j, nFlds := fCount()
   
   dbSeek( cSeek, .t. )
   if Eof()
      dbGoBottom()
   endif
   for n := 1 to nDataLines - 1
      dbSkip( -1 )
      if Bof()
         dbGoTop()
         exit
      endif
   enddo
   for n := 1 to nDataLines
      dbSkip( 1 )
      if Eof()
         dbGoBottom()
         exit
      endif
      cText += CRLF
      for j := 1 to nFlds
         cText += cValToChar( FieldGet( j ) ) + ','
      next j
   next n
   
   nSecs := Seconds() - nSecs
   MemoEdit( cText )  // optional
   
return nSecs


We may call this function (cAlias)->( AccessTime( cSeek, nBrowseLines ) ) for random values of cSeek and asertain the average time taken. The same test program needs to be run from around 5 to 6 client pcs simultaneously. if this time is not acceptable, we need to look into our network and database issues.

Any browse will anyway take this much time and adds only a small fraction of a second to display the grid. Then we can finetune the browse selected (WBrowse, tCBrowse, TSBrowse or TXBrowse).

Whenever I had similar problems, I used to solve the speed issues with the help of similar code, without using the browses first.

[gkuhnert]

Antonio, James, NageswaraRao and all others,

we decided to publish the source code of this puffer-search, that was causing us the performance problems. Maybe it can be of help to someone of you for your own programs.
First a screenshot, it's the blue get-field in the bottom-right corner and responds to the active order that is chosen.

Code: Select all  Expand view

   DEFINE MESSAGE OF oWin
   DEFINE MSGITEM oPuffertext OF oWin:oMsgBar PROMPT "Puffersuche: " SIZE 120
   DEFINE MSGITEM oPuffer OF oWin:oMsgBar ;
       SIZE 120  COLOR "R*/BG"              ;
       ACTION (IIF(MsgYesNo("Puffer löschen?",oPuffer:cMsg),oPuffer:SetText(""),))

  oWin:bLostFocus := {||oPuffer:SetText("")}

And this is our buffer-search function:

Code: Select all  Expand view

// Puffersuche mit TGET Feld: der Typ des Anzeigefeldes wird vorher abgefragt
FUNCTION Puffer(nKey,KeyFlags,oPuffer,oLB)    // Puffer Suche in Browser Listen
LOCAL cAlias
LOCAL nOldSelect
LOCAL nOldRec
LOCAL cSuchPuffer
LOCAL cPuffer
LOCAL L_DATUM,L_PUFFER
LOCAL cField,nPos
LOCAL cIndexKey, cIndexTyp
LOCAL nYear
LOCAL dTemp, cTemp, nTemp
LOCAL lGet := .f.     // bei oPuffer handelt es sich um ein TGET-Object
LOCAL nVor
LOCAL cVorField

  cAlias := oLB:cAlias
  nOldRec := (cAlias)->(RecNo())
  if oPuffer:ClassName()=="TGET"
    lGet := .t.
  endif
  cSuchPuffer := IIF(lGet,oPuffer:VarGet(),oPuffer:cMsg)
   DO CASE
  CASE nKEY==VK_BACK  // letztes Zeichen löschen
    if !EMPTY(cSuchPuffer)
        cSuchPuffer := SUBSTR(cSuchPuffer,1,LEN(cSuchPuffer)-1)
      IIF(lGet,oPuffer:cText(cSuchPuffer),oPuffer:SetText(cSuchPuffer))
      return .t.
    endif
   CASE nKey < VK_SPACE // bei allen anderen Steuerzeichen zurück
    return .t.
  OTHERWISE
    cSuchPuffer += CHR(nKey)
   ENDCASE
  // bei leerem Suchpuffer zurück
  if EMPTY(cSuchPuffer)
    return .t.
  endif
  // max. Pufferlänge berücksichtigen
  if LEN(cSuchPuffer) > 25
      cSuchPuffer := SUBSTR(cSuchPuffer,LEN(cSuchPuffer),1)
  endif
  // Tabelle wird ohne Index angezeigt
  if (cAlias)->(IndexOrd())==0
    (cAlias)->( dbGoto(MAX(MIN(Lastrec(),VAL(cSuchpuffer)),1)) )
    DO WHILE (cAlias)->(Deleted()) .and. !(cAlias)->(EOF())
      (cAlias)->(dbSkip())
    ENDDO
  else
    // Typ eines Indexes läßt sich nur bestimmen, wenn die Datenbank selectiert ist !!
    nOldSelect := Select()
    Select(cAlias)
    cIndexKey := IndexKey(IndexOrd())
    cIndexTyp := TYPE(cIndexKey)
    Select(nOldSelect)
    //** werte den Puffer aus

    // numerisch
     IF cIndexTyp=="N"
        (cAlias)->(dbSeek(VAL(cSuchPuffer),.t.))    // mit Softseek

    // reine Datumswerte
    ELSEIF cIndexTyp=="D"

      dTemp := &(cAlias+"->"+cIndexKey)
      if Empty(dTemp)
        dTemp := Date()
      endif
        IF LEN(cSuchPuffer)==3 .AND. !SUBSTR(cSuchPuffer,3,1)="."
           cSuchPuffer := STUFF(cSuchPuffer,3,0,".")
        ENDIF
        IF LEN(cSuchPuffer)==6 .AND. !SUBSTR(cSuchPuffer,6,1)="."
           cSuchPuffer := STUFF(cSuchPuffer,6,0,".")

        ENDIF
      // abhängig von 4 oder 2-stelligen Jahreszahlen entsprechend auffüllen
      cPuffer := DTOC(dTemp)
      nTemp := MIN(LEN(cSuchPuffer),6)
      cTemp := Substr(cSuchPuffer,1,nTemp)
      cPuffer := STUFF(cPuffer,1,nTemp,cTemp)
      nYear := IIF(__SetCentury(),4,2)
      cTemp := Substr(cSuchPuffer,7,nYear)
      nTemp := LEN(cTemp)
      cPuffer := STUFF(cPuffer,7+nYear-nTemp,nTemp,cTemp)

      // Pufferinhalt suchen (als Datum)
      (cAlias)->(dbSeek(CTOD(cPuffer),.t.))

     ELSEIF "DTOS"$UPPER(cIndexKey)
      nPos := At("DTOS",UPPER(cIndexKey))
      cField := Substr(cIndexKey,nPos+4)
      cField := Sparse(cField)
      // Basisdatum herausfinden
      dTemp := &(cAlias+"->"+cField)
      if Empty(dTemp)
        dTemp := Date()
      endif
      // Herausfinden, wie lang der Vorspann ist
      cVorField := Substr(cIndexKey,1,nPos-1)
      nVor := LEN(&(cAlias+"->"+cVorField+"'')"))

        IF LEN(cSuchPuffer)==(nVor+3) .AND. !SUBSTR(cSuchPuffer,(nVor+3),1)="."
           cSuchPuffer := STUFF(cSuchPuffer,(nVor+3),0,".")
        ENDIF
        IF LEN(cSuchPuffer)==(nVor+6) .AND. !SUBSTR(cSuchPuffer,(nVor+6),1)="."
           cSuchPuffer := STUFF(cSuchPuffer,(nVor+6),0,".")
        ENDIF
      cPuffer := DTOC(dTemp)
      nTemp := MIN(LEN(cSuchPuffer)-nVor,6)
      cTemp := Substr(cSuchPuffer,1+nVor,nTemp)
      cPuffer := STUFF(cPuffer,1,nTemp,cTemp)
      nYear := IIF(__SetCentury(),4,2)
      cTemp := Substr(cSuchPuffer,(7+nVor),nYear)

      nTemp := LEN(cTemp)

      cPuffer := STUFF(cPuffer,7+nYear-nTemp,nTemp,cTemp)


      // Inhalt des Vorspanns bestimmen
      cTemp := SUBSTR(cSuchPuffer,1,nVor)


      if "UPPER"$UPPER(cVorField)
        cTemp := UPPER(cTemp)
      endif
      // Text as Buffer-Search
      if LEN(cSuchPuffer)>nVor
        (cAlias)->(dbSeek(cTemp+DTOS(CTOD(cPuffer)),.t.))
      else
        (cAlias)->(dbSeek(cTemp,.t.))
      endif
     ELSEIF "UPPER"$UPPER(cIndexKey)
        (cAlias)->(dbSeek(UPPER(cSuchPuffer)))
    ELSE
        (cAlias)->(dbSeek(cSuchPuffer))
     ENDIF
  endif

  IIF(lGet,oPuffer:cText(cSuchPuffer),oPuffer:SetText(cSuchPuffer))
   IF !(cAlias)->(EOF())
    (cAlias)->(dbSkip(1))
    if (cAlias)->(EOF())
      oLB:GoBottom()
    else
      (cAlias)->(dbSkip(-1))
      //oLB:Refresh() // This made our search slow, because after every
      oLB:select(0)
      oLB:select(1) // This also does the trick for a refresh
      oLB:lHitTop := .f.
    endif
  ELSE
      (cAlias)->(dbGoto(nOldRec))
   ENDIF
return .t.

[James Bott]

Gilbert,

Are you saying that the posted code solved your problem?

If so, then referring to this code:

Code: Select all  Expand view

     //oLB:Refresh() // This made our search slow, because after every
      oLB:select(0)
      oLB:select(1) // This also does the trick for a refresh

Are you saying that oLB:select(0) followed by oLB:select(1) is faster than oLB:refresh()? If so, did you time it and what was the speed difference?

You might also consider checking the time between keystrokes (using a static) and only refreshing if a certain time period has elapsed since the last keystroke. This way the user could quickly type in a string and only get one refresh when they either stop typing or pause.

Also, I would think that when the key is a value like a name, it is more useful for a refresh after each keystroke, since the user might be looking for the right one from the list. But when typing it a number code, usually the user knows exactly which code they need and they don't need to make a judgement call based on similar entries, so a dynamic search is less useful and speed might be a more important consideration.

This might also be interesting. I just was reading about a high-rise building that was built and the occupants were complaining that the wait for an elevator was too long. The architects reevaluated the building design but could not figure a way to add more elevators or make them move faster. So they called in a interior designer. The interior designer put several full-length mirrors near each elevator entrance and the complaints stopped. It seems that the people waiting for the elevator now had something to do while waiting so the wait didn't seem as long. They were busy either looking at themselves or others while waiting.

It points out how we might be able to make a wait seem shorter by some means such as distraction.

Regards,
James