Guest Post: Application-wide Caching with Slots and IPrefetchable

Mark Franks | September 26, 2018

Written by Gabriel Michaud | Founder/Creator | Velixo | @GabrielMichaud

Today I would like to go over a technique that I covered during the last Virtual Developer Conference and is not widely used by developers outside the Acumatica development team –  slots and the IPrefetchable interface.

When monitoring and profiling any database-driven application, you often see the same queries and the same data accessed over and over again by different modules and users of your application. The performance hit of these queries may not be evident at first, especially if they’re simple. However, as the number of users and the complexity of your application increases, the effects compound and ultimately result in noticeable overhead.

What if you could load this data once, cache it, and make it available to every session? The Acumatica framework provides a mechanism to do this called “slots”. Acumatica provides a special interface, IPrefetchable, that enables lazy-loading of your data the first time that it’s needed and takes take care of invalidating your cache, if and when the cached data gets modified elsewhere in the application.

Internally, slots are used for many scenarios. Examples of structures that are cached in slots include:

  • Access Rights
  • Site Maps
  • Segmented Key Configurations
  • AR/AP Discount Rules

I would definitely NOT recommend that you start caching everything that you use in your application. You definitely don’t want to turn a latency or responsiveness problem that affects a single user, or in the rare use-case, into a memory pressure issue that affects every user. You should consider using slots to cache data in the following scenarios:

  • The data is frequently accessed (by every user/in every screen of your application)
  • It’s expensive to load (complex SQL query or set of queries; external API calls)
  • It’s reasonable in size (up to a few megabytes)
  • You don’t update the data frequently – unless you implement manual management of your data, the WHOLE cache will be invalidated and everything will have to be reloaded.

Manual updating of an existing cache will not be covered in this article, and you should be careful about trying to roll out such solutions. I will point you to a famous quote by distinguished engineer Phil Karlton – “There are only two hard things in Computer Science: cache invalidation and naming things.

Beside caching frequently-used data, there are other scenarios that can benefit from this caching mechanism; any serializable data structure can be stored in slots. I recently had to optimize a rule-based item categorization engine that relied on hundreds of dynamic expression trees. By loading and pre-parsing all the rules we were able to reduce the processing time for a single item from 1-2 seconds down to just a few milliseconds. In a database with 500 000 SKUs, you can see how much a difference that makes.

In the example below, we’re going to add a new unbound field to the inventory item selector that shows a comma-delimited list of categories the item belongs to. Note that this examples breaks at least one of the rules I outlined below – the list of inventory items can be extremely long. I’m using that example because it’s simple to understand.

We’re going to define a new aggregate attribute named SalesCategoriesAttribute that we’re going to apply to our field. This attribute will take care of loading the category list in the FieldSelecting event:

 

   [PXString]

public class SalesCategoriesAttribute : PXAggregateAttribute, IPXFieldSelectingSubscriber

   {

       private readonly Type _inventoryIDField;

       public SalesCategoriesAttribute(Type inventoryIDField)

       {

           _inventoryIDField = inventoryIDField;

       }

       public void FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)

       {

          e.ReturnValue “TODO”;

       }

}

 

A quick & dirty way of implementing this in the FieldSelecting event would be to run a PXSelect query and concatenate the list of categories:

 

public void FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)

       {

           e.ReturnValue “TODO”;

           int? inventoryID = (int?)sender.GetValue(e.Row, _inventoryIDField.Name);

           if (inventoryID != null)

           {

            //Bad!!!

            var categoryList = PXSelectJoin<INCategory,

                   InnerJoin<INItemCategory, On<INCategory.categoryID, Equal<INItemCategory.categoryID>>>,

                   Where<INItemCategory.inventoryID, Equal<Required<INItemCategory.inventoryID>>>>.Select(sender.Graph, inventoryID);

               string itemCategories = String.Empty;

            foreach(INCategory cat in categoryList)

            {

                if(String.IsNullOrEmpty(itemCategories))

                {

                    itemCategories = cat.Description;

                }

                else

                {

                    itemCategories = itemCategories + “, “ + cat.Description;

                }

            }

            e.ReturnValue = itemCategories;

        }

 

As you can guess, this would result in one query getting executed for every item in your selector. If you turn on the request profiler, this is what you’d see:

 

 

The query is relatively simple and fast, but it’s executed hundreds of times!

Let’s change our approach and create a new class that will implement the IPrefetchable interface. We’ll add a single public static function to it:

 

public class ItemCategoriesDefinition : IPrefetchable

   {

       public void Prefetch()

       {

           //TODO

       }

             

       public static string GetItemCategories(int? inventoryID)

       {

           return “TODO!”;

       }

}

 

Our SalesCategoriesAttribute class can be modified to call our new static function:

 

public void FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)

       {

           e.ReturnValue = ItemCategoriesDefinition.GetItemCategories(inventoryID);

    }

 

Now, let’s finish implementing the new ItemCategoriesDefinition class. We first need a proper data structure to store our cached data. We’re going to use a dictionary. Dictionaries provide fast, key-based access to data:          

 

       private readonly Dictionary<int?, string> _itemCategories = new Dictionary<int?, string>();    

 

Next, we’re going to add the code that takes care of loading the categories into the dictionary. This is done from the Prefetch() function. We will never directly call Prefetch() – Acumatica will do it the first time we access the slot, or when the slot data gets invalidated.

 

public void Prefetch()

       {

           //The system will automatically call Prefetch() the first time we retrieve this class from our slot (via the GetSlot() function below)

           //In this example, we load some data off the DB and cache it in the dictionary, but any sort of loading/pre-computation/caching could be performed inside this function

           _itemCategories.Clear();

           //Load category names and store into a dictionary for quick retrieval

           var categoryNames = new Dictionary<int?, string>();

           foreach (PXDataRecord record in PXDatabase.SelectMulti<INCategory>(

            new PXDataField<INCategory.categoryID>(),

            new PXDataField<INCategory.description>()))

           {

            int? categoryID = record.GetInt32(0);

            string description = record.GetString(1);

            categoryNames.Add(categoryID, description);

           }

           //Build comma-delemited list of categories for each item

           foreach (PXDataRecord record in PXDatabase.SelectMulti<INItemCategory>(

            new PXDataField<INItemCategory.inventoryID>(),

            new PXDataField<INItemCategory.categoryID>()))

           {

            int? inventoryID = record.GetInt32(0);

            int? categoryID = record.GetInt32(1);

           

            string categoryName = String.Empty;

            if (categoryNames.TryGetValue(categoryID, out categoryName))

            {

                string categoryList = String.Empty;

                if (!_itemCategories.TryGetValue(inventoryID, out categoryList))

                {

                       _itemCategories.Add(inventoryID, categoryName);

                }

                else

                {

                       _itemCategories[inventoryID] = categoryList + “, “ + categoryName;

                }

            }

           }

    }

 

Next we’re going to expose the dictionary in a public property – we’ll need it after we retrieve the class from our slot:

 

public Dictionary<int?, string> ItemCategories

       {

           get

           {

            return _itemCategories;

           }

    }

 

Finally, we’ll finish implementing the GetItemCategories function. This is the function that is called by our attribute.

 

public static string GetItemCategories(int? inventoryID)

       {

           var def = GetSlot();

           string itemCategories = String.Empty;

           if (def.ItemCategories.TryGetValue(inventoryID, out itemCategories))

           {

            return itemCategories;

           }

           else

           {

            return String.Empty;

           }

       }

       private static ItemCategoriesDefinition GetSlot()

       {

           //Returns or initializes a new instance of ItemCategoriesDefinition. System will automatically invalidate and Prefetch again if INCategory or INItemCategory tables are updated.

           return PXDatabase.GetSlot<ItemCategoriesDefinition>(“ItemCategories”, typeof(INCategory), typeof(INItemCategory));

    }

 

The most important part of the logic is inside the GetSlot function. I could have placed this inside GetItemCategories, but I decided to put it in its own function and replicate the same pattern used inside Acumatica. Your prefetchable class could expose different data/fields/values through multiple static functions, and centralizing the code that retrieves the data from the slot will make maintenance easier. PXDatabase.GetSlot<T> expects a few important parameters. It’s a generic function, so we’re specifying the type of our IPrefetchable class (ItemCategoriesDefinition). The first parameter is the key name of our slot – each slot needs to have a unique name. The other parameters enable automatic invalidation of the cached data whenever a table is modified. You can list as many IbqlTables as you want (or none at all, if you’re managing the cache manually). For this example, we want the system to clear our slot whenever the list of categories or the item categories are modified. We could manually update the existing slot from the relevant screens instead of invalidating it. However, this is outside the scope of this article.

We’re done!

To use the attribute, just decorate any DAC field with it. In my case, I’m using it on a PXCacheExtension:

 

 public class InventoryItemExt : PXCacheExtension<InventoryItem>

   {

       public abstract class salesCategories : IBqlField { }

       [PXUIField(DisplayName = “Sales Categories”, Enabled = false, Visibility = PXUIVisibility.SelectorVisible)]

       [SalesCategories(typeof(InventoryItem.inventoryID))]

       public decimal? SalesCategories { get; set; }

}

 

When you open the inventory item selector in the application, you’ll now see the sales categories of this item. The data gets loaded only once from the database, and the final solution incurs very little overhead on the database or application server.

You can see a finished implementation of that on my GitHub repo here. A recording of the full Black-Belt Techniques presentation is available here.

Happy optimizing!

Mark Franks

As a Platform Evangelist, Mark is responsible for showing people the specifics about what makes Acumatica’s Cloud Development Plaform wonderfully attractive to ISV & Partners. He's also passionate about Running, Latin, and his family. | E-mail: mfranks@acumatica.com | Skype: mfranks |

Subscribe to our bi-weekly newsletter

Subscribe