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.
The following are considerations that should determine whether or not caching the results of a Named Query is helpful.
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.