xBrowse + ADS = Slow Motion!

[sambomb]

I'm using ADS, I acquired the FW 10.5 and as part of the version migration in my search screen I upgraded from wBrowse to the xBrowse but with ADS it was too slow...

I have an version of my program without the ADS and in this there is no speed difference from wBrowse to xBrowse, but I use this version only for tests.

With 75.000 registers I took about 15 seconds just to open the dialog with the xBrowse, and below 1 second with wBrowse.

I use xBrowse in many places in my system, but the in my search screen i can't use it because of the speed issue.

[nageswaragunupudi]

I used XBrowse for browsing 800,000 rows table, with user selectable filters and all features, with around 200 simultaneous users on client server environment over slow speed wide area network ( those days our WAN speeds were as low as 64kbps/128 kbps).

There are a few tricks to use XBrowse with ADS. If followed we gain speeds similar to WBrowse.

[Jaque Morique]

Thanks u for sharing u tricks

Jaque

[nageswaragunupudi]

viewtopic.php?f=3&t=9343&p=79429&hilit=adsrecordcount#p79429

[frose]

Hi,

yes, xBrowse + ADS + tricks is fast

But there is another annoying speed issue regarding ADS and the WildMatch function.

Animated by this topic viewtopic.php?f=3&t=15469 and specially Rao's statement:

All my earlier programs contained similar code since clipper days. Open the dbf file with FOpen in shared mode scan the entire text and work back the record numbers and then proceed. ( I also read the dbf files in this raw mode to check integrity and for repairs. )

But those were the days when we did not have these advanced functions.

Now these functions are available. I did not compare the speeds but please consider what happens in these two different cases. Assume the dbf resides on a remote server. Our program is executed on the client PC. Our old logic reads the entire DBF file. May be fast, but it still reads the entire DBF data. OrdWildSeek reads only the index contents. In effect the quantity of data ordwildseek has to read is less than the data we read in our raw dbf read method.

Assume a DBF file with record length of 2048, containing 100,000 records. We read 205 MB of data in our old method.
Assume an index on field city with a field length of 20 bytes. Size of the index is only 2 MB. Isn't it faster to search in 2 MB of data than in 205 MB of data ?

Long time back, I read somewhere that indexes are fully read over the client in the beginning. I am not sure it it is so even in the present RDDs. In such a case, reading data in indexes from local memory ( or memory cache ) is faster than reading from a remote computer. I say I remember to have read, but I am not sure.

Further more, our coding effort is reduced a lot.

I can’t imagine that skipping through the file record by record is the solution.


I agree. For this, setting filter with WildMatch( ... ), as proposed by Mr Armando, is the best solution. It is working for me well. But this function is available in xharbour only. I am sure some function with similar functionality may be available in Harbour too. But I am not aware of it. Wish some Harbour pundits throw some light on this.

I've built a filter function with Wildmatch and it is working quit well and fast for small tables, e. g. a customer address table with 25000 recnos.

For larger tables, have tested one with 250000 recnos, the WildMatch function is extremely slow, means that the time for setting the filter needs typically 30 minutes, yes minutes not seconds

Rao, is there a chance to improve this situation using furthermore WildMatch, or do you recommend another approach for ADS users?

[nageswaragunupudi]

If we are using ADS, let us not think in terms of wildmatch.
Let us approach from Fast Text Search feature. This feature on ADS is similar to Oracle, MSSql and other major RDMSs functionally. If we create appropriate FTS indexes, the results are spectacular even on tables with million rows.

Filters containing UDFs are not optimized by ADS and the entire filtering is done at the client side, reading record by record. We should never think of using any expression that can not be optimized by ADS server.

But FTS is different from wildmatch but we get similar (but not same) functionality. We need to give the user the best within the constraints of the tools we have.

But let me say this. ADS client server software's server side filters are very fast. I could get acceptable results even with very inefficient filters like "'ADAM' $ FIRSTNAME" on tables with half a million rows.

Whatever I said in my earlier post was with reference to DBFCDX not ADS. That does not apply to ADS client server.

[frose]

Rao,

thank you very much for clarification.

[frose]

Rao,

tested FTS in our customer address table:

Code: Select all  Expand view  RUN

           ( cAlias )->( AdsCreateFTSIndex( , "name1", "name1" ) )
            ( cAlias )->( AdsCreateFTSIndex( , "name2", "name2" ) )
            ( cAlias )->( AdsCreateFTSIndex( , "name3", "name3" ) )
            ( cAlias )->( AdsCreateFTSIndex( , "pst_str", "pst_str" ) )
            ( cAlias )->( AdsCreateFTSIndex( , "ort", "ort" ) )
            ( cAlias )->( AdsCreateFTSIndex( , "bmrkng", "bmrkng" ) )

            cFilter := 'contains( dbtrn->Name1,   "' + AllTrim( Lower( cPattern ) ) + '" ) OR ' + ;
                       'contains( dbtrn->Name2,   "' + AllTrim( Lower( cPattern ) ) + '" ) OR ' + ;
                       'contains( dbtrn->Name3,   "' + AllTrim( Lower( cPattern ) ) + '" ) OR ' + ;
                       'contains( dbtrn->Pst_str, "' + AllTrim( Lower( cPattern ) ) + '" ) OR ' + ;
                       'contains( dbtrn->Ort,     "' + AllTrim( Lower( cPattern ) ) + '" ) OR ' + ;
                       'contains( dbtrn->Bmrkng,  "' + AllTrim( Lower( cPattern ) ) + '" )'
         MsgRun( "Der Filter wird gesetzt, bitte warten!", "Filter setzen", { || ( cAlias )->( AdsSetAOF( cFilter ) ) } )
 

and it's fast or even faster than the WildMatch dependant on a local machine, thank you very much for giving me this broad hint

[nageswaragunupudi]

it's fast or even faster than the WildMatch

Please study the syntax in detail.
You do not have to 'OR' different 'CONTAINS'. One CONTAINS is enough for many AND and OR conditions. Syntax is similar on almost all RDMSs.


It is faster even on remote server (using client server ADS) because it is server side filter.

You will feel really happy with the instantaneous response when applying this filter on very large tables on very slow connections.

But we can not do what all we can do with WildMatch. Still this feature is enough for most applications. Also this approach is consistent with all other major RDMSs.

Finally, when you know how to use ADS with XBrowse, I am sure you will stick to XBrowse + ADS and will not consider other options.

[frose]

Rao,

you wrote:

Please study the syntax in detail.
You do not have to 'OR' different 'CONTAINS'. One CONTAINS is enough for many AND and OR conditions. Syntax is similar on almost all RDMSs.

Studying syntax from here http://devzone.advantagedatabase.com/dz ... ntains.htm:

Syntax

CONTAINS(<field> | *, <cSearchCondition> ) -> lSearchResult

Parameters

<field> | *
   

The field to apply the search condition to. The asterisk (*) can be used to specify that all fields with full text search (FTS) indexes are to be searched. This parameter can be a character value or the result of some expression that returns a character value, but the search will not be optimized. For best performance, this parameter should be the asterisk or a field that has a full text search index built on it.

and for example here: http://www.techtalkz.com/microsoft-sql- ... lumns.html.

So my code has to be something like:

Code: Select all  Expand view  RUN

cFilter := 'contains( ( <field1>, <field2>, ..., <fieldn> ), <cPattern> )'

But all tries in this direction failed, respectively no recnos were filtered and LastRec() is equal OrdKeyCount().

Also don't understand where to place the logical statement, here 'OR' and there are NO indications of a fieldlist in the ADS documents.

Please can you help once more?

[Marcelo Via Giglio]

Hi,

I think you can add logical condition inside query CONTAINS( <field>... ' text search [OR|AND|NEAR] textsearch [OR|AND|NEAR] .....' )

regards

and save paul

[nageswaragunupudi]

Examples:

Search conditions that use only search words combined with the logical operators AND and OR are fully optimized (assuming an FTS index exists on the field being searched). For example, the following query is fully optimized:
SELECT * FROM apdd
where contains( definition, ‘science and (history or proof)’ )


Please see
http://devzone.advantagedatabase.com/dz ... ntains.htm

You may use * for searching in all FTS indexed fields

[frose]

Marcelo, Rao,

yes, if you have more then one search word, but what about a fieldlist and how are these fields combined per default, 'OR' or 'AND' and how to change?

Remember, my logic is:

Code: Select all  Expand view  RUN

'contains( field1,   "'  + cPattern + '" ) OR ' + ;
'contains( field2,   "' + cPattern + '" ) OR ' + ;
'contains(field3,   "' + cPattern + '" ) OR ' + ;
...
'contains( fieldn,  "' + cPattern + '" )'

Where cPattern can be a list of search words:

Code: Select all  Expand view  RUN

' text search [OR|AND|NEAR] textsearch [OR|AND|NEAR] .....'

Tried also '*' but it's not functional (for me), same result as a fieldlist.

Paul is NOT my friend, so I can't promise anything

[Marcelo Via Giglio]

Frank,

you are right, there is not other way because the logical operators are for the fields and not for the text search

regards

Marcelo

[Giovany Vecchi]

Tambem tive este problema, resolvi mais não é o correto a fazer.
Isto não acontece se usar sistema remoto do ads.
Altere no Method SetRDD()

Code: Select all  Expand view  RUN

      cAdsKeyNo    := "{| n, Self | iif( n == nil, " +;
                         "Round( " + cAlias + "->( ADSGetRelKeyPos() ) * Self:nLen, 0 ), "+;
                         cAlias + "->( ADSSetRelKeyPos( n / Self:nLen ) ) ) }"
 
      cAdsKeyCount := "{|| " + cAlias + "->( ADSKeyCount(,,1) )}"

      //// GIOVANY VECCHI
      IF (cAlias)->(LASTREC()) > 5000 .and. (cAlias)->(AdsGetTableConType())==1
          cAdsKeyCount := "{|| " + cAlias + "->( ADSKeyCount(,,2) )}"
      ELSE
          cAdsKeyCount := "{|| " + cAlias + "->( ADSKeyCount(,,1) )}"
      ENDIF