Named Query Caching

Overview

Named queries can opt-in to caching the results on the Gateway. This means if another request to the same Named Query comes in, the Gateway can return the cached result instead of having the database executing the query again. This will use more memory on the Gateway (to maintain the results), but could result in less queries running against the database.

Named Query caching is disabled by default, but can be enabled on the Settings section of each Named Query. If caching is enabled, the spinner and dropdown fields set the lifespan of the cache. Once the lifespan expires, the cache is invalidated. After the cache is invalidated, the next request for the Named Query will force the Gateway to re-execute the query and build a new cache.

Caching is especially useful for tables that are not updated often like recipe or inventory tables. Tables that update often like historical storage tables are bad candidates.

When Caching is Disabled

Update queries are not allowed to cache their results. With UPDATE or DELETE statements, the work will already have been performed after the first execution, and INSERT statements typically utilize different parameters each execution. Thus if the Caching setting is disabled, then this typically means that the Query Type in the Authoring tab is set to an Update Query.

Scenario

Consider the following:

• A Named Query is created. This runs a query to fetch data from an arbitrary table.
• A component in a project is configured to call the Named Query on a window.
• Client_A navigates to the window, which triggers a request for the Named Query be executed.
• Several seconds later, Client_B opens the same window, and needs the same results.

In this scenario, if caching was enabled on the Named Query, then Client_B would not cause another query execution (assuming both A and B passed the same values to the parameters). This would result in less network traffic between the Gateway and the Database, and less work for the Database. An example with two clients isn't exciting, but the same scenario with ~50 clients would mean a huge potential performance boost, especially if those requests were polling at a 5 second rate, and the cache period was configured to 5 seconds.

Considerations

The following are considerations that should determine whether or not caching the results of a Named Query is helpful.

Gateway Memory

Each time the Named Query is called, if the arguments passed differ from those used to create the current cache, a new cache is created. This means Named Queries that are frequently called with varying parameter values will create multiple caches. If the results are large datasets, this can result in a large amount of the Gateway Memory being tied up maintaining these caches. In this scenario, you will want to monitor the memory usage of the Gateway.

This is especially important to consider when dealing with queries that accept a timestamp parameter that uses an expression like now() that will return time to the current second. If 5 clients are opened with each one 1 second apart, they will all have different timestamps and create separate cache entries.

How Often the Database Values are Updated

Once a cache is created, the Named Query will not look for any changes made to the database table until the query next executes. This means changes, such as adding a new row to the table, will not appear in the Named Query's results until the cache is invalidated. Data that changes often may not be a good candidate for caching. In this case, the lifespan of the cache should be set to a short amount of time, depending on how often the data may get updated.

How Often the Named Query is Executed

If a Named Query is called frequently and there are not many updates to the table data, then there can be a huge performance benefit to caching the results. Fewer calls to the database result in less network traffic and better overall performance.

Cached Query Updates and Designer Values

Cached query results have some notable interactions in the Designer. Specifically:

• Named Queries results executed in the Designer never cache: This is because the Designer uses the live version of the query in the Designer instead of the saved Gateway version. This also prevents bad results being stored in the cache, and then appearing in the Clients.
• Making changes to a Named Query and saving will invalidate all caches for that Named Query: When a change is made to a Named Query and saved, the new query is pushed to the Gateway, which means all current caches are immediately outdated.

Scripting Functions to Clear Cache from a Named Query

You can also clear the Named Query cache using the following scripting functions.

• system.db.clearNamedQueryCache - Clears the cache of a single Named Query.
• system.db.clearAllNamedQueryCaches - Clears the caches of all Named Queries.