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