Caching for global valuelist function used for typeahead

Questions, tips and tricks and techniques for scripting in Servoy

Caching for global valuelist function used for typeahead

Postby deezzub » Fri Jun 13, 2014 1:04 pm

I have implemented a global valuelist function for showing search suggestions during typing. Thx Peter. :)

The problem is that is a performance killer if it is used in a table view.

I try to build a cache for example:

Using global variables to cache the foundsets and result datasets.
Code: Select all
/**
* @type {JSDataSet}
*
* @private
*/
var clientCache = null;

/**
* @type {JSDataSet}
*
* @private
*/
var customerCache = null;

/**
* @type {JSFoundSet}
*
* @private
*/
var clientFs = null;

/**
* @type {JSFoundSet}
*
* @private
*/
var customerFs = null;


Only load foundset if there is no cached foundset.
Code: Select all
   if ( !clientFs ) {
      /** @type JSFoundSet<db:/boss_sql/mandpara> */
      var fs = databaseManager.getFoundSet( 'db:/boss_sql/mandpara' );
      fs.addFoundSetFilterParam( 'mandnr', '<', 100000, 'Clients without foreign vessels and seamen' );
      fs.addFoundSetFilterParam( 'schiff', '=', 1, 'Clients with vessels' );
      fs.loadAllRecords( );
      clientFs = fs;
   } else {
      fs = clientFs;
   }


Only load foundset if there is no cached foundset.
Code: Select all
   if ( !customerFs ) {
      /** @type JSFoundSet<db:/boss_sql/kunden> */
      var fs = databaseManager.getFoundSet( 'db:/boss_sql/kunden' );
      fs.addFoundSetFilterParam( 'firma', '!=', '', 'nur_firmen_anzeigen' );
      fs.loadAllRecords( );
      customerFs = fs;
   } else {
      fs = customerFs;
   }


Return cached result, if the result is already cached.
Code: Select all
   if ( displayValue == null ) { //field is empty
      foundSet.sort( displayColumn + ' asc' );
      if ( displayColumn === 'name' && clientCache ) {
         return clientCache;
      } else if ( displayColumn === 'firma' && customerCache ) {
         return customerCache;
      } else {
         result = databaseManager.convertToDataSet( foundSet, [ displayColumn, realColumn ] ); //[display value, real value]
      }

      if ( displayColumn === 'name' ) {
         clientCache = result;
      } else if ( displayColumn === 'firma' ) {
         customerCache = result;
      }
   } else if ( displayValue != null ) { //field has text
      var searchText = utils.stringTrim( displayValue );
      var searchArray = searchText.split( ' ' );
      if ( foundSet.find( ) ) {
         foundSet[ displayColumn ] = '#%' + searchArray[ 0 ] + '%';
         foundSet.search( );
         for ( var i = 1; i < searchArray.length; i++ ) {
            if ( foundSet.find( ) ) {
               foundSet[ displayColumn ] = '#%' + searchArray[ i ] + '%';
               foundSet.search( false, true );
            }
         }
      }
      foundSet.sort( displayColumn + ' asc');
      result = databaseManager.convertToDataSet(foundSet, [ displayColumn, realColumn ] );
   }
   result.removeColumn( 2 );
   
   return result;


I would be great if someone could give me some ideas how to improve it.

See complete code at GitHub.
deezzub
 
Posts: 328
Joined: Tue May 28, 2013 3:02 pm
Location: Oldenburg, Germany

Re: Caching for global valuelist function used for typeahead

Postby goldcougar » Sun Jun 15, 2014 5:14 am

I would suggest trying to use the Query Builder API. You should a performance increase from that, instead of doing the find through a foundset.

Also, consider checking the length on the input. Many times I'll just return null if the typed in value is under 3 characters, since a wildcard LIKE query is usually pretty slow searching a couple characters.

Lastly, if this is for a type ahead, consider only using the wildcard at the end, instead of on both sides (so the query is a 'begins with' instead of a 'contains')
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
goldcougar
Servoy Expert
 
Posts: 688
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Caching for global valuelist function used for typeahead

Postby deezzub » Tue Jun 17, 2014 9:23 am

goldcougar wrote:I would suggest trying to use the Query Builder API. You should a performance increase from that, instead of doing the find through a foundset.

Also, consider checking the length on the input. Many times I'll just return null if the typed in value is under 3 characters, since a wildcard LIKE query is usually pretty slow searching a couple characters.


Hello Scott, I try that using the Query Builder. The second is a really good tip. Thx. :)

goldcougar wrote:Lastly, if this is for a type ahead, consider only using the wildcard at the end, instead of on both sides (so the query is a 'begins with' instead of a 'contains')


I need the "contains".

Back to my above question, if I use the Query Builder, I also need caching, I think? Is the above caching method a good approach or what can I do better?
deezzub
 
Posts: 328
Joined: Tue May 28, 2013 3:02 pm
Location: Oldenburg, Germany

Re: Caching for global valuelist function used for typeahead

Postby Harjo » Tue Jun 17, 2014 9:45 am

Scott,

why should the Query Builder, be quicker than a normal find??
Both are translated to the same query. With the Query Builder you have more options, to create complexer (cross database) queries, but I don't see in this example why the query builder would be quicker...
Harjo Kompagnie
Direct ICT / Servoy Hosting / ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
User avatar
Harjo
 
Posts: 4307
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Caching for global valuelist function used for typeahead

Postby goldcougar » Tue Jun 17, 2014 9:55 am

Your code doesn't really do much caching. The only worthwhile caching is what you are storing in clientCache and customerCache. The places where you refer to a "cached foundset" really aren't cached. A foundset is just a pointer to a datasource that may or my hot hold a collection or records. Each time in your code you are eventually doing a foundset.find() and foundset.search() which means your reloading the foundset from the find criteria. So, lets walk though some places and see what your code is actually doing...


Code: Select all
      var fs = databaseManager.getFoundSet( 'db:/boss_sql/mandpara' );
      fs.addFoundSetFilterParam( 'mandnr', '<', 100000, 'Clients without foreign vessels and seamen' );
      fs.addFoundSetFilterParam( 'schiff', '=', 1, 'Clients with vessels' );
      fs.loadAllRecords( );

Here you are getting a new pointer to the foundset, telling servoy what filters you want, and telling it to load all records. There is overhead with doing the loadAllRecords. Your telling Serovoy to issue a SQL query to the DB with the foundset filter criteria, and load the first block of records (if which you never use)


Code: Select all
if ( displayValue == null ) { //field is empty
      foundSet.sort( displayColumn + ' asc' );
      if ( displayColumn === 'name' && clientCache ) {
         return clientCache;
      } else if ( displayColumn === 'firma' && customerCache ) {
         return customerCache;
      } else {
         result = databaseManager.convertToDataSet( foundSet, [ displayColumn, realColumn ] ); //[display value, real value]
      }

      if ( displayColumn === 'name' ) {
         clientCache = result;
      } else if ( displayColumn === 'firma' ) {
         customerCache = result;
      }
   }

Here, you are doing a foundset.sort (even if its in the client or customer cache), so your telling Servoy to re-issue another query and re-load the foundset.


Code: Select all
else if ( displayValue != null ) { //field has text
      var searchText = utils.stringTrim( displayValue );
      var searchArray = searchText.split( ' ' );
      if ( foundSet.find( ) ) {
         foundSet[ displayColumn ] = '#%' + searchArray[ 0 ] + '%';
         foundSet.search( );
         for ( var i = 1; i < searchArray.length; i++ ) {
            if ( foundSet.find( ) ) {
               foundSet[ displayColumn ] = '#%' + searchArray[ i ] + '%';
               foundSet.search( false, true );
            }
         }
      }
      foundSet.sort( displayColumn + ' asc');
      result = databaseManager.convertToDataSet(foundSet, [ displayColumn, realColumn ] );
   }


Next we are down to the part where the search is actually performed. So, when you get to foundSet.search( false, true ); your performing the search, and loading up the foundset with records. Then later on, you do foundSet.sort( displayColumn + ' asc');, which is going to re-issue the query again, this time with an ORDER BY statement, and reload the foundset, again. Th better option would be to try and use the defer option, like: foundSet.sort( displayColumn + ' asc', true); . That should just store the sort you want, and when the query is issues, it should hopefully use that sort option. Then you only run the query once.


So, there is a lot going on in that method. On top of all that, I wouldn't use foundsets, and would use the Query Builder instead :)
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
goldcougar
Servoy Expert
 
Posts: 688
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Caching for global valuelist function used for typeahead

Postby goldcougar » Tue Jun 17, 2014 10:14 am

Harjo wrote:Scott,

why should the Query Builder, be quicker than a normal find??
Both are translated to the same query. With the Query Builder you have more options, to create complexer (cross database) queries, but I don't see in this example why the query builder would be quicker...


In my experience, with some large datasets, using the Query Builder is always faster than doing a foundset.find/search. I assume this is for a few reasons (some assumptions here)

- Servoy skips the step of having to convert your foundset search criteria into the proper query syntax. Using the Query Builder, you are directly calling into the API to build the Query in the format Servoy wants to use it in.

- His final step is to use databaseManager.convertToDataSet(...). So ultimately for the valuelist, it needs a DataSet. Using the foundset.find/search makes it go through a few more steps. So it has to do the original query, then load the first chunk of records. Then when you do databaseManager.convertToDataSet(...), it has to go through the entire foundset, and convert it to a DataSet. If you look at whats happening in the background, you'll see Servoy issues several SQL queries to load the foundset data in chunks so it can then convert it to a dataset. However, using the Query Builder API, the direct result is the DataSet that you are looking to return.

- If you look at the Queries generated from a foundset.search/find, you'll notice it does a SELECT on all columns. With the Query Builder API, you can choose exactly what columns you want returned and skip the overhead of the extra data being returned.

- Lastly, with the "cached foundsets" that he is using, he is holding the foundsets in his top level scope. So, even though he is done with the foundset, he still has these variables sitting around that are holding the entire foundset (because the convertToDataSet woud've loaded all the records into the foundset ). So you have some extra overhead for Servoy to keep that up-to-date since it does databroadcasting and will make sure you never have stale data in your foundset. So, multiply this times 100's of users in the system, and you have lots of extra foundsets needing to be kept up-to-date


PS. An extra performance tip, with both Foundset and DataSets, would be to add some type of limit. So, if the result returns 1 Million rows, there is really no point in returning all those.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
goldcougar
Servoy Expert
 
Posts: 688
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Caching for global valuelist function used for typeahead

Postby Harjo » Tue Jun 17, 2014 10:39 am

Thanks Scott, great info!
Harjo Kompagnie
Direct ICT / Servoy Hosting / ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
User avatar
Harjo
 
Posts: 4307
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands


Return to Methods

Who is online

Users browsing this forum: No registered users and 1 guest