Why Some Browses with ADS are slow?

[nageswaragunupudi]

We are aware that OrdKeyNo() ( which in turn calls AdsKeyNo(...) ) consumes more time in case of large tables on ADS Server and each call involves roundtrip to the server and thus increasing network traffic too.

The objective is to achieve pajama effect without calling OrdKeyNo() at all or if that is not possible to greatly minimize the number of calls to this function.

Here is an attempt, which greatly reduces the calls but does not totally avoid. Though I have used DBFCDX, the same logic applies to ADS.

Code: Select all  Expand view  RUN

#include "FiveWin.Ch"
#include "ord.ch"
#include "xbrowse.ch"

//----------------------------------------------------------------------------//

REQUEST DBFCDX

static nRowNo

//----------------------------------------------------------------------------//

function Main()

   local oDlg, oBrw

   USE CUSTOMER
   SET ORDER TO TAG FIRST
   SET FILTER TO !DELETED()
   GO TOP
   nRowno      := 1

   DEFINE DIALOG oDlg SIZE 600,600 PIXEL
   @ 10,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
      AUTOCOLS ALIAS 'CUSTOMER' AUTOSORT CELL LINES NOBORDER

   WITH OBJECT oBrw
      :bClrStd    := { || If( nRowNo % 2 == 1, { CLR_BLACK, CLR_WHITE }, { CLR_BLACK, CLR_YELLOW } ) }
      :bGoTop     := { || ( oBrw:cAlias )->( DbGoTop() ), nRowNo := 1  }
      :bGoBottom  := { || ( oBrw:cAlias )->( DbGoBottom(), nRowNo := OrdKeyCount() ) }
      :bBookMark  := { |x| If( x == nil, ( oBrw:cAlias )->( RecNo() ), ( oBrw:cAlias )->( DbGoTo( x ), nRowNo := OrdKeyNo(), x ) ) }
      :bSkip      := { |n| ( oBrw:cAlias )->( MySkipper( n ) ) }
      :bRClicked  := { || oBrw:Refresh() }
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED

return (0)

//----------------------------------------------------------------------------//

init procedure PrgInit

   SET DATE ITALIAN
   SET CENTURY ON

   SET DELETED ON
   SET EXCLUSIVE OFF

   RDDSETDEFAULT( "DBFCDX" )

   XbrNumFormat( 'E', .t. )

return

//----------------------------------------------------------------------------//

static function MySkipper( nToSkip )

   local nSkipped    := DbSkipper( IfNil( nToSkip, 1 ) )
   nRowNo            += nSkipped

return nSkipped

//----------------------------------------------------------------------------//
 

Still, I am of the opinion to avoid any calls to these slow functions while browsing large tables on ADS server, even it means we can not show pajama effect.

[Gale FORd]

Are you sure oGrid:nAt does not work. I use it all the time to show a column with the line numbers. It correctly displays each line number.

Code: Select all  Expand view  RUN

   ADD COLUMN TO oBrw ;
      DATA { || oBrw:nAt } ;
      HEAD ""      ;
      SIZE 30 PIXELS
 

[nageswaragunupudi]

oBrw:nAt shows the row number while browing arrays with WBrowse and TCBrowse, but not with XBrowse.

In XBrowse, oBrw:KeyNo shows the row number.

[Carles]

R,

I think that's a mistake to work of this way. In environment Client/server, the best solution is:,

- Load registers in table array
- Browse table
- if u need, then save some record, don't problem.

When u work in local mode or in server with a few records, maybe u don't have problem, but when u have a lot of registers, this method for me is wrong, big error. Of course, u can do it, but it's correct for a big tables. But it's only my vision. Browse with dbf it's a technical for local mode, not for server solutions...

[nageswaragunupudi]

Mr Carles

Do you mean it is not a good idea to browse large tables directly in a client-server enviroment (ADS) ?

Let me assure you, it can be done on large tables.
In one of my earlier applications the customer table with 700,000 customers with large number of columns was being browsed very comfortably with xbrowse over wide area network. ( Those days the speeds were much less over WAN ). This particular browse was very feature rich providing the required information on a click of button with different kinds of analysis. The most used feature of that software. We had more than 200 simultaneous users at peak times with a total of around 350 clients connected.

Though the main application was on Oracle, with around 40,000 transactions a day, I maintained a parallel copy of customer table on ADS, because it was only on ADS, I could provide such a browse.

Its possible with fairly satisfactory speeds on slower connections and good speeds on faster connections.

The only point is we should master the nuts and bolts of ADS and FWH and take pains to optimize on every aspect.

This is my humble opinion out of experience. I am saying this with full confidence, because I have done it.

[Carles]

Mr. N,

Not at all. You're right. I also took 10 years working with ADS and for me is the best solution for dbf, without any discussion.

I just think that sometimes the way you work with dbf is not the right way. Ok, I've worked with tables of more than 2 million records and connections between different countries. You can browser it? Yes, of course, but the way sometimes as you work (pijama effect, bad filters, ...) the system runs slowly.

I have my own routines you (find, load, process, recorded). Yes, you can do it directly but I assure you that this methodology is also very fast and makes your browses fly. It would be a philosophy similar to Sql (You create query and retrieve records.) Ok, ok, you can do it also on the flight, I can't say to you no and it works, is simply another way of working.

And if you remember, in this post I put some test data access via ADS. When large tables with many records and many fields, blobs, memo ... need fine tune the indexes and how to access the ADS, the system flies

But yeah, you too can make a table and USE + Browse () and the system works well.

It's also my humble opinion of an RDD that also have worked many years and I love. It is always advised but even to work locally. Finally, remember in this post my final question 2 years ago ?

viewtopic.php?f=3&t=9343#p85394

Thanks for your feedback N, are very valuable to me, friend ...

[reinaldocrespo]

Mr. R.

Thank you for your replies. I sort of have a similar solution, but like yours it is not perfect. Some times, when nothing is really happening, the browse will repaint and toggle colors. Not a terrible thing, but just not perfect (as in other environment such as .net or delphi).

The way I see it, the pijama effect should not depend on having to execute an external code block regardless of data source (array, dbf....). The pijama effect should be something the xbrowse should be able to do internally. If you take a look at other grid classes (outside of our small fw world), the pijama effect is internal to the grid. I do love the fact that you can execute a code block and change anything about the grid, including a row fgrnd/bckgrnd colors, but other than having to handle some exception, the alternating colors should be handled by the xbrowse class itself.

As for ADS and large tables... don't you just love what you can do with ADS and still maintain ISAM while adding SQL? ... And another thing, I no longer have a definition for large tables. No table size is large with ADS.

Again, thank you for your responses.

Reinaldo.

[nageswaragunupudi]

the alternating colors should be handled by the xbrowse class itself.

FWH endeavors to provide what many programmers want. May be this will be provided sooner or later. Before that we need to achieve best speeds with ADS.

I have later improved the above sample further to almost avoid calling OrdKeyNo(). In such a case it should not matter if it is ADS or any other source.

No table size is large with ADS.

Well said.