I am making a production metric dashboard in Perspective. It will be displayed in several locations throughout one department, and support staff may also have the project open to see department progress throughout the day. In summary there will be many sessions open at once all displaying the same data.
The SQL query that populates this view is moderately heavy, and I would like to minimize the number of times it needs to run. I would like to update the data displayed to users every ~5 minutes; I'm less excited about running this query for every open session every 5 minutes.
Is there a way I can run the SQL query once, cache the results, and reference the cache from all the open sessions?
Put it in a named query and enable caching?
The named query cache is determined by the parameters, so as long as you're passing the same parameters you'll hit the cache and avoid the round trip to the DB.
Note that the cache is relatively "dumb" - that is, if a second session hits the named query while the first query is still executing, the second query will still hit the database, it won't wait for the first query to complete and use the cache. So you can still get multiple executions within the cache duration if executions line up right.
That won't be a catastrophe in this case. I don't expect the sessions to synchronized, so I doubt they will be updating at exactly the same time. If they do line up exactly, the database will handle it, but why waste resources when caching is this easy.
I assume this implies that a named query with no parameters will cache for other executions of that named query with no parameters. Is that correct?