# Forcing one result of a named query cache to update **URL:** \ **Category:** Ignition\ **Created:** [June 19, 2025, 6:35pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591 "2025-06-19T18:35:22Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [June 19, 2025, 6:35pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/1 "2025-06-19T18:35:22Z")
We have a MariaDB table that is moderately sized (thousands of rows, MBs of data), rarely written, read almost continuously, and the reads often have bursts of repeated parameters. It's a perfect candidate for named query caching and does pretty good with it. However, on the rare occurrence that we write a new value to a row, we need the reads to start getting the new value immediately. We are currently using system.db.clearNamedQueryCache after each write, and while it gets the job done, I can actually see spikes in the SQL server load as it is suddenly being queried for all the data again. I only really need to invalidate ONE set of read query parameters. I would really like if system.db.runNamedQuery had an optional parameter that could force a read-through and update the cache. Then, after a write that updates the DB, instead of clearing the WHOLE cache, I could just do a targeted runNamedQuery with that enabled and the parameters I know were affected. This would get the one new row into the cache while not instantly invalidating the whole cache. I tried searching over on [ideas.inductiveautomation.com](http://ideas.inductiveautomation.com) and couldn't find anything that looked like this, but forcing a cache read-through is a common idea and I really thought someone would have suggested it before. Or, maybe I'm missing some other technique that already exists. Does anyone know a way to achieve the result I want with what already exists in Ignition? Is there a feature suggestion that would apply to this that I missed? If no one knows another way or of an existing feature suggestion, I'll go make a feature suggestion tomorrow. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 19, 2025, 6:42pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/2 "2025-06-19T18:42:49Z")
> [@justin.brzozoski](#): > > Does anyone know a way to achieve the result I want with what already exists in Ignition? You could script all of your queries and maintain your own cache in a top-level script object. ---
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [June 19, 2025, 6:45pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/3 "2025-06-19T18:45:55Z")
I considered it, but many of the consumers are Perspective views. Having the ability to avoid script blocks while still caching is nice. Converting to scripting is not off the table yet, but I'm not sure if it's a net win with how many places use this query. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [June 19, 2025, 6:51pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/4 "2025-06-19T18:51:29Z")
Could you create two named queries, one that caches and one that doesn't. Call the one that doesn't cache after writes, otherwise use the cached one? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 19, 2025, 6:53pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/5 "2025-06-19T18:53:09Z")
After you do your clearNamedQueryCache, presuming you know what parameters are your most used, then call a system.db.runNamedQuery for those params right after to set the cache? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 19, 2025, 7:01pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/6 "2025-06-19T19:01:57Z")
> [@justin.brzozoski](#): > > avoid script blocks while still caching is nice Sounds like a job for my `globalVarMap()` expression function. How many parameters does the NQ use? If just one, an expression like this might suit you: ```python transform( {path.to.some.query.param}, coalesce( globalVarMap('someCacheName')[value()], // Try to get the NQ result for the parameter from the cache objectScript('someCacheScript.getAndCache(args[0])', value()) // If not cached, run the NQ. ) ) ``` IIRC, `coalesce()` short circuits when the first arg is non-null. The accompanying script would take a lock, recheck the cache within the lock, then if still not present, run the NQ and stuff into the globalVarMap before releasing the lock and returning. ---
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [June 19, 2025, 7:51pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/7 "2025-06-19T19:51:34Z")
> two named queries, one that caches and one that doesn't Not quite the issue I'm trying to resolve. Our users open dashboards made up of mostly independent sub-views, and while each view has it's own call to this query, sets of sub-views that open at the same time tend to use the same parameters. If a user changes something in the query results, we need all the sub-views to get the new value sooner than later. > After you do your clearNamedQueryCache ... call a system.db.runNamedQuery ... to set the cache The problem is that with multiple users viewing dashboards at the same time, the caches for all of them are going to be invalidated at the same time and each user likely has two or three set of parameters to this query on their views. I know only ONE set of parameters needs to be removed from the cache but when I clear the whole thing it starts fetching for everyone. > Sounds like a job for my `globalVarMap()` expression function. How many parameters does the NQ use? Two strings, usually 16 characters or less in length each. But I can guarantee there's never a `/` in either string, so I could plausibly adapt the cache key to be `CONCAT(one_param,'/',second_param)` or whatever the Ignition expression is. (My mind is stuck in SQL right now) I would have to change a LOT of views, but it would do what I want... ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 19, 2025, 8:29pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/8 "2025-06-19T20:29:46Z")
Another option entirely: Cache in MariaDB directly? > **[Query Cache | MariaDB Documentation](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/query-cache)** I only just skimmed that it but it looks like you might be able to 'hint' in your named queries that they should cache, and presumably you could write another named query to explicitly clear the cache/some cache entry. Having this capability in Ignition would be nice, and I think it's a worthwhile feature. I spent a few minutes seeing if it would be doable to hack your way in to the cache via scripting, and the short answer is no 😃 ---
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [June 19, 2025, 8:55pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/9 "2025-06-19T20:55:07Z")
> [@paul-griffith](#): > > Having this capability in Ignition would be nice, and I think it's a worthwhile feature. It might be easier to document/explain as an optional parameter on clearNamedQueryCache, where you can pass in a `parameters` value just like you would've used to runNamedQuery and it will clear only that part of the cache. I could see it working either as a "targeted clear" or a "forced bypass" of the cache, though. ---
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [June 19, 2025, 9:11pm UTC](https://forum.inductiveautomation.com/t/forcing-one-result-of-a-named-query-cache-to-update/107591/10 "2025-06-19T21:11:58Z")
I made a feature suggestion if anyone wants to vote or comment: > **[Targeted named query cache updating | Ignition Features and Ideas | Inductive...](https://ideas.inductiveautomation.com/ignition-features-and-ideas/p/targeted-named-query-cache-updating)** > > Caching named queries is very useful, but there are cases when our system updates one row in a table and knows a specific result in the cache is no longer