I have a project containg some pages showing trend from the historian.
These pages are very popular 
so it is shown on multiple client.
My problem is it seems to keep the SQL server pretty busy. How should I optimize that?
Could I have a dataset on the gateway containg the data from the historian and then show the dataset in the client? Or any other idea to avoid multiples client to query the same data from the historian?
Thanks a lot.
If you are using named queries, there’s automatic caching is the substitution parameters match. When using a datetime window, I recommend rounding start/end timestamps to fixed intervals to maximize the chance the cache will have the data. At least, truncate milliseconds for real-time charting purposes.
The tag historian does some caching, too, but that is rather opaque. If you need caching for Vision’s EasyChart DB pens, I strongly recommend my own solution: NoteChart and TimeSeriesDBCache modules.
I have a similar question ... we have a table with 5million rows. Each machine inserts a record every 30 seconds, and there are 3 machines, so ... on average once every 10 seconds. We want each operator to see "their" machine's last 20 records. The power-table is bound to a named-query to do the SELECT ... the only parameter is in the machine-name/id fields ... the select statement is SELECT TOP 20 ...
Is that defeating the caching somehow?
But ... we are seeing that each Vision client is opening it's own session on the database ... we have lots of connections that are running identical queries. We didn't think that would happen ... we thought that the named-query subsystem would NOT create a new connection if there was already another one that was executing.
We have had to increase the pool size.
We are also having some INSERTS fail because the SELECT is blocking the tables.
We are considering refactoring to have a common shared dataset (in the tag system) to store the TOP20 for each machine, and then let Vision filter that tag by the specific machine.
Is there something we aren't doing with the named-query that is making them request new connections?
Unless you have thousands of unique stations, this shouldn't be a heavy load.
Do you have an composite index on that table that includes the columns in the where clause and then the column (presumably t_stamp) in the ORDER BY clause?
Properly indexed, these queries should execute in a few milliseconds.
(Since only the machine ID parameter would be supplied to the NQ, but you want fresh data every time, caching is not appropriate here.)
Not true at all. Named queries are fully parallelizable.
Will the system create NEW connections for each use of the NQ ... even if the NQ statement is identical? Or ... since the NQ statement is identical, as long as a connection is in the process of executing that statement, the new request will NOT result in a new connection ... and both requests (the one that is pending, and the one that was detected as identical) will be fed by the returned data from the one that is pending.
Note: I did find after more digging, that the NQ in my post had Polling turned off, but a timer component was being sued to refresh the data (pseudo-polling). Also found out that the index had not been refreshed/reorganized/defragged ... and that made a difference too
Connections are created when the pool is not at its limit and a query (not just NQs) wants to run.
As I understand it, NQ caching looks if a result is available in the cache that matches the given parameters, and returns it if present. That's it. No check for a running query that will match (because it might not finish).
If a cached result is not present, a new query kicks off.
But as I noted, your application should not be cached. Every UI request for fresh data needs to run the query, because you haven't described anything that can trigger this.
(And your DB shouldn't be choking on this--it's just not that difficult a workload.)
If you really need to scale, and Ignition is doing the inserts, consider having the insert code broadcast a message afterwards to tell open UIs that a particular machine should refresh. Then you don't need polling bindings at all.