> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Caching remote tables
This 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 store.
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.
## How it works
We will create an in-memory store for the remote table and populate it with the contents of the remote table. We will then use this in-memory store for all
queries. The remote table will only be used when the application is [refreshed](./directquery-rest-service#application-refresh).
This process of caching tables can be applied to any store that is not part of the star-schema.
Only isolated tables can be cached in-memory.
We cannot cache referenced stores as the reference would be between in-memory and remote tables, which is not supported.
### Cached Table Example
Let's say we want to cache the remote `FXRATES` table in-memory.
Under the hood, we will create an in-memory store named `FXRates` and populate it with the contents of the remote `FXRATES` table. We will give the remote name
an alias of `FXRATES_CACHE_SOURCE` to allow us to use the `FXRates` store for all queries. The remote `FXRATES_CACHE_SOURCE` table will only be used when the
application is refreshed.
We can use the AdminUI to see the two tables side by side. The `FXRates` store is the in-memory store and the `FXRATES_CACHE_SOURCE` is the remote table.
By caching the `FXRates` table in-memory, we can perform GetByKey queries on the `FXRates` store without any performance penalty of having to query the remote
database.
### Application Refresh
When the Application is [Refreshed](./directquery-rest-service#application-refresh) through the
[DirectQuery REST Service](./directquery-rest-service), the in-memory Stores will be completely
re-populated with the content of the remote Tables.
## Defining Cached Table
To define a cached table we simply register it as a table to cache in-memory and expose the table as a `bean` with a `qualifier` of `SideStoresToCache`.
### Registering the Table to Cache
First we must define our table in our datastore description.
See [Create Datastore Definition](../../dev-extensions/etl/creating-and-loading-a-custom-store#step-2---datastore-modifications)
on how to add new Tables/Stores to the application.
Once our store is defined in our datastore configuration, we can register it as a table we want to cache locally. This is done by creating a bean that is
annotated with `DirectQueryCachedTablesConfig.TABLES_TO_CACHE_LOCALLY` and returns a list of the table names we want to cache.
We can override the default tables to cache by creating a new bean with the same method name and returning a list of the tables we want to cache.
We will end up creating a configuration class along the lines of the following:
```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
@Configuration
public class CustomCachedTablesConfig {
/**
* Add additional tables to cache
*/
@Bean
@Qualifier(DirectQueryCachedTablesConfig.TABLES_TO_CACHE_LOCALLY)
public List addAdditionalTableToCache() {
return List.of(
"MyTableToCache",
"MyOtherTableToCache"
);
}
/**
* Overrides default cached tables defined in the
* DirectQueryCachedTablesConfig.commonTablesToCacheLocally() bean.
*/
@Bean
@Qualifier(DirectQueryCachedTablesConfig.TABLES_TO_CACHE_LOCALLY)
public List commonTablesToCacheLocally() {
return List.of(
"FXRates",
"MyTableToCache",
"MyOtherTableToCache"
);
}
}
```