Why Some Browses with ADS are slow?

[nageswaragunupudi]

Many times our friends keep expressing that some of the advanced browses are extremely slow while browsing *LARGE* ADS tables. This phenomenon is observed with tsbrowse, txbrowse and even hernan's browse. In fact the problem is not with the browses. These advanced browses go a step forward in setting up the codeblocks appropriate for the RDD being used. For ADS, by default, these browses use AdsKeyCount() (or similar functions) for the bLogicLen / bKeyCount codeblocks.

The real problem is in the AdsKeyCount ( also AdsGetRecordCount ) function of rdd_ads. The problem is not even the making of the author of the library. The function transparently passes on the efficiency or inefficiency of the original functions in the ACE32.DLL ( AdsGetRecordCount or AdsGetKeyCount. Both are twins with same functionality ).

We do not perceive the problem with small tables but we do notice great sluggishness with large tables, To find a solution we need to understand the behaviour of these functions.

We normally use SET DELETED ON. We are not interested in seeing the deleted records but interested in dealing with the live records only. The functions AdsGetRecordCount or AdsGetKeyCount return the count instataneously ( even with very complex AOF filters and scopes) IF AND ONLY IF SET DELETED IS OFF. So if we use the browses with SET DELETED OFF they are blazing fast again. But we want them to work with SET DELETED ON.

AdsGetRecordCount / AdsGetKeyCount functions of ACE32.DLL literally skip and count records if SET DELETED ON, even if there is no single deleted record. This is the doumented behaviour. Browses call this function several times during a single refresh. Now imagine the ADS Server literally traversing the entire table each time the function is called particulary if the table is large say with hundreds of thousands of records.

SOLUTION:

Let us continue with SET DELETED ON as usual.

For every large table, create an index on deleted(). Example INDEX ON DELETED() TAG DELETED. ( Name of the tag is not important)
Then whenever we open a table the first thing to do is to set filter to .not. deleted(). Example: AdsSetAOF( '!DELETED()', ADS_RESOLVE_IMMEDIATE ). Whenever we set any other filter, AdsSetAOF( 'DELETED() .AND. <OUR FILTER CONDITION>', ADS_RESOLVE_IMMEDATE). Now traversal of the DBF by the ADS Server is optimised. Without this the traversal is still not optimized. ( I use my custom function for setting filters which does this automatically if the table is DBF. )

Still the AdsRecordCount() is not optimized. We should create a wrapper function. Let us do this now:

Code: Select all  Expand view

function AdsRecCount()

local nRecs

SET DELETED OFF
AdsSetDeleted(.f.)
nRecs := AdsGetRecordCount()      // rdd_ads by default uses respect filters
                                        // because our AOF filter is already set to !deleted()
                                        // anyway we get nondeleted record count only
AdsSetDeleted(.t.)
SET DELETED ON
RETURN nRecs


Now use this function for blogilen

oBrw:bLogiclen ( for xbrowse oBrw:bKeyCount ) := {||MyAlias->(AdsRecCount())}

Now you find the browses even on large ADS table to be as fast as other tables.

BETTER SOLUTION:

Though the above solution is fast, we know the browse calls the reccount function manytimes during each refresh, with resulting increase in network traffic and unnecessary load on the server. Imagine hundreds of users browsing and thousands of calls to the server. We know this is not necessary and also is not a good prgramming behaviour.

Better would be to ascertain the total records with the above function at the outset and store in a module wide static variable ( let us call it nRecCount. ). Assgin the bLogicLen codeblock with {||nRecCount}. Also choose the right value for AdsCacheRecords(nCatche), to reduce the round trips to the server. Now the browse will go with blazing speed. We refresh the value of the variable nRecCount whenever our program perceives the need to refresh.


SPECIAL CASE WITH SBROWSE

I now stopped using tsbrowse, but earlier i was using it extensively. The problem was that tsbrowse not only assigns the codeblocks but also uses them even before returning control to my program, i.e., even before I could replace the assignment with my functions. So the initial display was taking ages. Only way I could solve the issue was the alter the source code of tsbrowse.prg.

Authors of browses might have tested their product on small tables on local server versions.

I thought my experiences might be useful for those who deal with large tables on ADS server.

Note: All this is not necessary for ADT tables. Defaults work well because ADT tables do not have any deleted records.

[kokookao2007]

hi nageswaragunupudi:

#define ADS_RESPECTFILTERS 1
#define ADS_IGNOREFILTERS 2
#define ADS_RESPECTSCOPES 3

#xcommand SET FILTER TO <xpr> ;
; AdsSetAof( <xpr> )

#xcommand SET FILTER TO <x:&> ;
=> if ( Empty( <(x)>) ) ;
; dbClearFilter() ;
; else ;
; AdsSetAof( <(x)> ) ;
; endif

AdsGetRecordCount() ==> TRY AdsKeyCount(,, ADS_RESPECTSCOPES)

AdsKeyNo()==>AdsKeyNo(,, ADS_RESPECTSCOPES)
AdsKeyCount()==>AdsKeyCount(,, ADS_RESPECTSCOPES)


MY BROWSE() work as fast as rdd "DBFCDX".

Best Regards
kokoo

[TimStone]

Where do you include this code ?

TIm

[kokookao2007]

my own ads.ch

[Wanderson]

my own ads.ch

Hi Kokookao2007 can you send me your ads.ch?
My personal address is w_peixoto@hotmail.com

Thanks in advance.
Wanderson Artiaga

[nageswaragunupudi]

ADS Documentation:

Using ADS_RESPECTSCOPES respects scopes only.
Using ADS_RESPECTFILTERS respects filters and scopes
.....
If usFilterOption contains ADS_RESPECTFILTERS, the Advantage Client Engine must skip through all records referenced by keys in the index that pass the filter and scope and count them. Thus, with large indexes where many records pass the filter and/or keys pass the scope, this function can be very slow.

So if we set filter and use ADS_RESPECTSCOPES, the results are not correct.

When SET DELETED IS ON, the situation is really worse. We start noticing the sluggishness with large tables on server with large number of deleted records. We can test this with a table of say around 600,000 records of which around 100,000 records are deleted.

Speed of AdskeyCount or AdsRecordCount varies significantly when SET DELETED IS ON or SET DELETED IS OFF. When SET DELETED IS OFF, the results are near instantaneous, When SET DELETED IS ON, the speed is very slow ( applies to very large tables with many deleted records ).

One can create a table with such number of records and see the speed differences clearly. The difference is clearly perceptible.

My suggestion ( not really my own. suggested since the days or origin of Comix/Clipmore) to create an index on DELETED() and to use a function like the one I suggested above is the fastest way.

[Carles]

Hi,

Interestingly your explanations and trying to add my two cents...

As Rao says, many of us have to work with large tables of hundreds of thousands of records, includes sometimes millions. The use of ADS requires some concepts, to make our application to work really fast. By default, when we use small tables, practically do not apreciate these differences, but if they are noticed and very much, in large tables. There are many functions that are not operated using ADS and many ways to work that maybe should change to get the maximum performance of ADS.

By default, people always use the SET DELETED ON, this option should know the deal correctly with ADS. I will expose how we work and TXBrowse using ADS, and the main problem is that the functions that have have record one by one unless we have AdsSetDeleted(.t.).

My proposal (Environments Browses)

1 .- Always use CDX index with the condition (no expression) in the tag ! Deleted(). If we need some conditions we can always add more, f.e. ! Deleted (). AND. other condition

2 .- We will use AdsKeyCount (,, ADS_RESPECTSCOPES) in the browse for the computation of records. The access is virtually instantaneous.

3 .- We use AdsKeyNo (,, ADS_RESPECTSCOPES) the index

4 .- As rightly said Rao, the use of AdsCacheRecords() is very good, but not always the most accurate. We know that a configuration is not always the most optimal and that fits all. The use of the cache access increase dramatically, but for example, keep in mind that if you edit a record, this what we have in the cache and will force the writing. We recommend the edition after the use of AdsRefreshRecord()

Then there are other functions for the management of data, which can increase a little more access, but what we will see.

Other interesting perfomance and had also experienced Rao is the idea of using a static variable for the number of records and update them to the process, but according to that case, gave us some problems.

And the last one, Rao please can you experiment with filters, using this way ?

Here I leave a small sample for testing with large tables.

Code: Select all  Expand view

// Autor     : Carles
// Descripcio: Test de velocitat de acces a ADS amb taules grans
// Data      : 09/04/2008

#include "FiveWin.ch"
#include "xbrowse.ch"
#include 'ads.ch'

#define NAME_DBF     "\\BSD001\TEMP\Test.dbf"
#define NAME_CDX     "\\BSD001\TEMP\Test.cdx"


REQUEST ADS
REQUEST ADSKeyNo
REQUEST AdsKeyCount
REQUEST AdsGetRelKeyPos
REQUEST AdsSetRelKeyPos

REQUEST HB_Lang_ES
REQUEST HB_CODEPAGE_ESWIN

STATIC oWnd

*--------------
FUNCTION MAIN()
*--------------
    LOCAL oBar
    LOCAL cAlias

    Config()

//  For testing in ADS, we created a table with 10.000.000 of registers,
//    using Dbcre8.exe tool.

    USE (NAME_DBF) SHARED NEW
    SET INDEX TO NAME_CDX

    cAlias := Alias()

//  For Tessting is import to create a index cdx with:
//    Name tag 'FIRST'
//    Expresion 'FIRST'
//    Condition !Deleted()

    (cAlias)->( OrdSetFocus( 'FIRST' ) )


    DEFINE WINDOW oWnd MDI TITLE "Test speed ADS with big tables..."

       DEFINE BUTTONBAR  oBar 3D SIZE 24, 26 OF oWnd
       DEFINE BUTTON  OF oBar ACTION TestScope( cAlias, .f. )
       DEFINE BUTTON  OF oBar ACTION TestScope( cAlias, .t. )
       DEFINE BUTTON  OF oBar ACTION TestFilter( cAlias )

    ACTIVATE WINDOW oWnd MAXIMIZED

RETU NIL

*------------------------------------------
STATIC FUNCTION TestScope( cAlias, lScope )
*------------------------------------------


    IF lScope
       (cAlias)->( OrdScope( 0, 'B' ) )
       (cAlias)->( OrdScope( 1, Replicate('B',21) ) )
      ELSE
       (cAlias)->( OrdScope( 0, NIL ) )
       (cAlias)->( OrdScope( 1, NIL ) )
    ENDIF

    (cAlias)->( DbGoTop() )

    Browse( cAlias, IF( lScope, 'Test with Scope', 'Test without scope' ) )

RETU NIL

*---------------------------------------
STATIC FUNCTION Browse( cAlias, cTitle )
*---------------------------------------
    LOCAL oChild, oBrw, oFont, oBar

    DEFINE FONT oFont NAME 'TAHOMA' SIZE 0,-11

    DEFINE WINDOW oChild MDICHILD OF oWnd TITLE cTitle

      DEFINE BUTTONBAR  oBar 3D SIZE 24, 26 OF oChild
      DEFINE BUTTON  OF oBar ACTION TestSpeed( cAlias )

      @ 10,10 XBROWSE oBrw ;
         COLUMNS "First", "Last", "Salary" ;
         SIZE 1,1 PIXEL OF oChild FONT oFont;
         ALIAS cAlias ;
         FASTEDIT FOOTERS LINES CELL

         // IMPORTANT !!! Always we need config parameters of browse
         // before call method ::CreateFormCode()

         SetRddAds( oBrw )

         oBrw:CreateFromCode()

         oChild:SetControl( oBrw )

    ACTIVATE WINDOW oChild

    RELEASE FONT oFont

RETU NIL


*-----------------------
STATIC FUNCTION Config()
*-----------------------

    SET DATE TO ITALIAN
    SET DELETE ON
    SET EXCLUSIVE OFF
    SET CENTURY ON
    SET EPOCH TO YEAR(DATE())-50

    HB_LANGSELECT( 'ES' )
    HB_SetCodePage("ESWIN")

    RddRegister("ADS",1)
    RddSetDefault("ADS")
    AdsSetServerType ( 2 )   // SERVER
    AdsLocking( .t. )
    AdsRightsCheck(.F.)

    SET FILETYPE TO CDX
    SET AUTOPEN ON

RETU NIL

/*
#define ADS_RESPECTFILTERS 1
#define ADS_IGNOREFILTERS  2
#define ADS_RESPECTSCOPES  3
*/

*--------------------------------
STATIC FUNCTION SetRddAds( oBrw )
*--------------------------------

    LOCAL cAlias  := oBrw:cAlias
    LOCAL cError  := ''
    LOCAL lUpdate := .F.

    WITH OBJECT oBrw
         :bGoTop    := {|| ( cAlias )->( DbGoTop() ) }
         :bGoBottom := {|| ( cAlias )->( DbGoBottom() ) }
         :bSkip     := {| n | iif( n == nil, n := 1, ), ( cAlias )->( DbSkipper( n ) ) }
         :bBof      := {|| ( cAlias )->( Bof() ) }
         :bEof      := {|| ( cAlias )->( Eof() ) }
         :bBookMark := {| n | if( n == nil,;
                                  ( cAlias )->( RecNo() )    ,;
                                  ( cAlias )->( DbGoto( n ) ) ;
                                )}
         :bKeyCount := {|| ( cAlias )->( AdsKeyCount( ,, ADS_RESPECTSCOPES ) ) }
         :bKeyNo    := {| n | if( n == nil,;
                                  ( cAlias )->( AdsKeyNo   ( ,, ADS_RESPECTSCOPES ) ),;
                                  ( cAlias )->( OrdKeyGoto(n) ) ) }
    END

    AdsCacheRecords(50)


RETU NIL

*-----------------------------------
STATIC FUNCTION TestFilter( cAlias )
*-----------------------------------
    STATIC cFilter := ''

    cFilter := cFilter + Space( 200 - Len( cFilter ) )

    MsgGet( 'Filtro', 'Condicion', @cFilter )

    cFilter := Alltrim( cFilter )

    IF Empty( cFilter )
      (cAlias)->( dbClearFilter() )
     ELSE
      (cAlias)->( AdsSetAOF( cFilter ) )
    ENDIF

    (cAlias)->( DbGoTop() )

    Browse( cAlias, cFilter )

RETU NIL

*----------------------------------
STATIC FUNCTION TestSpeed( cAlias )
*----------------------------------
    LOCAL nStart, nTimeIgnore, nTimeScopes, nTimeCount
    LOCAL nIgnore, nScopes, nCount
    LOCAL cInfo := ''

    nStart      := Seconds()
    nIgnore     := (cAlias)->( AdsKeyCount( ,, ADS_IGNOREFILTERS ) )
    nTimeIgnore := Seconds() - nStart

    nStart      := Seconds()
    nScopes     := (cAlias)->( AdsKeyCount( ,, ADS_RESPECTSCOPES ) )
    nTimeScopes := Seconds() - nStart

    nStart      := Seconds()
    nCount      := (cAlias)->( RecCount() )
    nTimeCount  := Seconds() - nStart

    cInfo += 'Tag  '       + Chr(VK_TAB) + Chr(VK_TAB) + (cAlias)->( OrdName() )     + CRLF
    cInfo += 'Key  '       + Chr(VK_TAB) + Chr(VK_TAB) + (cAlias)->( OrdKey()  )     + CRLF
    cInfo += 'Condition  ' + Chr(VK_TAB)               + (cAlias)->( OrdFor()  )     + CRLF
    cInfo += 'Scope Ini  ' + Chr(VK_TAB)               + cValToChar((cAlias)->( OrdScope( 0 ) )) + CRLF
    cInfo += 'Scope End  ' + Chr(VK_TAB)               + cValToChar((cAlias)->( OrdScope( 1 ) )) + CRLF
    cInfo += 'Filter     ' + Chr(VK_TAB) + Chr(VK_TAB) + (cAlias)->( AdsGetAof() )   + CRLF + CRLF
    cInfo += 'Total Reg ( ! Deleted() ) '+ Chr(VK_TAB) + ltrim(Str(nIgnore)) + ' reg., ' + ltrim(str(nTimeIgnore)) + ' sec.' + CRLF
    cInfo += 'Total Reg ( with Scope  ) '+ Chr(VK_TAB) + ltrim(Str(nScopes)) + ' reg., ' + ltrim(str(nTimeScopes)) + ' sec.' + CRLF
    cInfo += 'Total Reg ( Table )       '+ Chr(VK_TAB) + ltrim(Str(nCount )) + ' reg., ' + ltrim(str(nTimeCount )) + ' sec.'

    MsgInfo( cInfo, 'Check time...'  )

RETU NIL
 

Any modification or clarification will be welcome.

[nageswaragunupudi]

Mr Carles

When we use ADS_RESPECTSCOPES, AdsKeyCount and AdsKeyNo do not respect FILTERS. Therefore we do not get the correct count if we use any filters. This code works well if we do NOT use any filters at all in this module.

If you propose to filter for !DELETED(), this code does not give the correct count. The count includes deleted records also. If you insist on using this code and do not use use any other filters except DELETED(), one way is to set order on deleted TAG and set top and bottom scope to .F.

But we do use filters in normal course including filter to !DELETED(). So we have no other go but to use ADS_RESPECTFILTERS. And this brings us back to my first proposed solution.

Another important issue.
AdsKeyNo() is the slowest function. Please read the Advantage Documentation:

If usFilterOption contains ADS_IGNOREFILTERS or ADS_RESPECTSCOPES, this function should return fairly quickly and provide good performance if the index is not large. If the index is large, this function could take some time to complete because index keys are literally counted until the current key is reached.
If usFilterOption contains ADS_RESPECTFILTERS, the Advantage Client Engine must skip through all records referenced by keys in the index that pass the filter and/or scope and count them until the current key is reached. Thus, with large indexes where many records pass the filter and/or keys pass the scope, this function can be very slow.

Therefore AVOID AdsKeyNo() ( ACE32.DLL AdsGetKeyNum() ) if the table is large. Instead use AdsGetRelKeyPos() function. This is included in the default codeblock of xbrowse. ( I think TSBrowse also, but I am not sure )

I suggest we better go by the default codeblocks of XBrowse except bKeyCount. For bKeyCount() better we use the methods I described above.

For smaller tables anything goes well.

Another suggestion: If we have decided to use Advantage Server only, why should we still cling to CDX? It is better to convert the tables to ADT. Particularly the deleted records do not pose any problem. KeyNumber issues still remains ofcourse.

[Carles]

Mr. G.N. Rao,

Well, this is true, maybe he was having shown that the techniques described above, not intended for use filters, and it is a code that is optimized for normal use with or without scopes

Us the use of filters, through the execution of SQL simply because we felt that the system was faster. It's just another kind of technique and situation. Remember that versatiliti of ADS and its power lies in the joint use of its functionality. And another reason was to get the Browse as perfect, which is fast with scopes, no scopes, filters, filters most scopes, nothing ...

On the use of AdsKeyNo() i know the documentation, but I insist you check the result using the technique described above. Access is immediate.

Finally, ADT. If you are right, the use of ADT avoids many problems with the deleted registers, but the situation described is if you need to continue using Dbf for some reason.

If not appreciated in the picture, but I did a test and give curious results. It is for this reason that we work well. If you want that let us add new cases, i will be happy for test.



Finally, say to you that any suggestion of yours is WELCOME and always add more knowledge about the forum and get power to all the best. I will try browse + ads with all kinds of situations.

Thanks for your comments.

[nageswaragunupudi]

Wow

Excellent research done by you.
Your results are very valuable to our community and these results will be of great standard reference.

Thanks for your efforts

[Carles]

N,

The ethernal question => What's the best configuration for browser ?
I think => It depends on what you do , i donn't believe exist the perfect browse for all situations (is my humbly reply)
next week to play around with filters, which conclusions can be inferred ...

[nageswaragunupudi]

N,

The ethernal question => What's the best configuration for browser ?
I think => It depends on what you do , i donn't believe exist the perfect browse for all situations (is my humbly reply)
next week to play around with filters, which conclusions can be inferred ...

I fully agree with you

[reinaldocrespo]

Hi, everyone.

I'm trying to draw alternate colors when browsing tables. All my tables are .adt/adi, i.e. (NO DBFs). My code to achieve alternate colors for each line is this:

Code: Select all  Expand view

  ::oGrid:bClrStd := {|| If( (::cAlias)->( AdsKeyNo(,,ADS_RESPECTSCOPES) ) % 2 == 0, { CLR_BLUE, CLR_WHITE }, { CLR_BLUE, RGB(242,242,255) } ) }
 

But this turns out to be WAY to slow. If I remove this line from the xbrowse code, then my xbrowses are really fast. I have tried using AdsGetRelKeyPos() as well as OrdKeyNo() at no avail.

Any advise?


Reinaldo.

[Gale FORd]

Can you use the position of the browse object instead?
Something like

Code: Select all  Expand view

::oGrid:bClrStd :=  {|| if( ::oGrid:nAt % 2 == 0, { CLR_BLUE, CLR_WHITE }, { CLR_BLUE, RGB(242,242,255) } ) }
 

[reinaldocrespo]

Gale;

Hi. The problem here is that ::oGrid:nAt returns the cursor position in the grid and not as it is painting. Thus not achieving the pijama effect. I tried it. Thank you, but it doesn't work.

I realize that not being able to use OrdKeyNo() (as well as ::oGrid:KeyNo()) is an exclusive problem with ADSRDD which in turn is inherited from ACE. But it is also a GUI problem. Notice how the delphi folks don't have this problem while browsing the very same .adt tables.

There must be a way inside the control (xbrowse) to alter coloring that does not depend on record positioning or having to execute an external code block. The idea of having a code block to determine line colors is good when you want to allow for exceptions.


Reinaldo.