Caching Remote Tables

This section explains what a cached remote table is and how to configure a remote database table to be cached locally.

Overview

Caching a table locally takes a copy of a remote database table and stores it in an in-memory datastore.

This is useful for tables where you want to perform GetByKey queries or datastore lookups. Essentially, if you need to retrieve specific facts (that is, rows) from the table, then it will be more performant to cache the table in-memory.

Out of the box, the FXRates table is cached locally. This allows us to perform GetByKey queries in PostProcessors without any performance penalty of having to query the remote database and send the results for a single value back to Atoti Server.

How it works

You can define the list of tables to be cached locally through the ISideStoresToClone interface. Beans returning that interface are defined like this example:

    @Bean
    public ISideStoresToClone commonStoresToClone() {
        return collection -> {
            collection.add(FX_RATE_STORE_NAME);
            collection.add(ROUNDING_METHODS_STORE_NAME);
            collection.add(QUANTILES_STORE_NAME);
            collection.add(MARKET_SHIFT_STORE_NAME);
        };
    }

The FXRates table is one of these by default.

When the application starts, we create an in-memory store in the datastore, with the same name as the remote table. We then rename the remote table (the name Atoti Server uses to reference the remote table) to contain a suffix of “CACHE_SOURCE”. So our remote FXRates table will be renamed to “FXRates_CACHE_SOURCE” and we will have an in-memory datastore store “FXRates”. We then run a complete list query on the remote table “FXRates_CACHE_SOURCE” and write all the results into the in-memory “FXRates” store.

All existing references to the “FXRates” store (for datastore lookups or use in measures) within the application will query the in-memory store rather than the remote one.

This same process can be applied to any side store.

Defining Cached Table

To define a cached table, we must first register it as a table to cache in memory. Then we need to create a channel config to inject the contents of the remote table into the in-memory store in the datastore.

Registering the Table to Cache

First, we must define our table in our datastore description. See Create Datastore Definition on how to add new tables/stores to the application.

A bean returning ISideStoresToClone then needs to be defined:

    @Bean
    public ISideStoresToClone commonStoresToClone() {
        return collection -> {
            collection.add("MyCustomTable");
        };
    }

Conclusion

Now you can query your cached table in measures just as you would with any other Atoti Server in-memory datastore store. We can validate that our table was cached by taking a look in the AdminUI. We should see that we have one store with the “_CACHE_SOURCE” suffix - this is the remote table. The in-memory table is the one without the suffix.

Here we can see the Table “FXRates” as both an in-memory store (named “FXRates”) and as a remote DirectQuery database Table (named “FXRates_CACHE_SOURCE”). This can also be seen for the FXRates store. Both of these stores contain the same data.