Automation Professionals is pleased to announce the release of Time Series Database Cache, a public marketplace module intended for use with very large and/or very high resolution historical datasets. From the marketplace description:
[quote]Delivers very large datasets to clients without GUI stalls by chunking the data and updating in the background. Includes expression functions and scripting functions to access the caches and manipulate resulting datasets.[/quote]Key Features:
[ul][li]Optimized for wide SQL Bridge historical tables[/li]
[li]Push data delivery to clients to reduce latency[/li]
[li]Queries from multiple clients consolidated at the gateway into shared queries[/li]
[li]Highly configurable timing properties on a cache-by-cache basis[/li]
[li]Includes a variety of helper functions and data types[/li][/ul]
Check out the Documentation for more details.
No, I haven’t put together a sample project for this yet. Not easy to demonstrate without millions of rows to play with. Anyways, the cache expression function requests data equivalent to a SQL Query of the form:SELECT t_stamp, value1, value2, value3 ...
FROM sometable
WHERE t_stamp >= ? and t_stamp < ? AND (optional extra where clause)
ORDER BY t_stamp;… just like the queries generated by dbPens in the EasyChart component. However, the request passes through caching layers in the client and in the gateway that supply as much data as possible from memory.
Real queries from the cache to the DB only request missing information, and the rows transmitted from the gateway to each client are tracked to avoid retransmitting. Real queries are also limited to a chunk size, and the the chunks transmitted to clients as they arrive. Essentially, use more RAM to avoid DB requests and minimize per-client bandwidth.
Data is held in cache for several minutes after the last client stops asking, in case such a user goes back to it. Makes for blistering fast movement back and forth on a date range slider after viewing a time span the first time.
The cache includes some optimizations for time spans that include real time, tolerating a bit of clock skew, and ensuring that fresh data is quickly pushed to all watching clients. If there’s a very-high-resolution realtime chart you’d like to put on many executive’s dashboards, this tool makes it possible without bringing your database to its knees.
Only one real unsatifying part: it can’t be shimmed into an EasyChart. You have to use a Classic Chart and either a custom realtime control menu, or a date range slider.
The Time Series Database Cache module has been updated in the marketplace with the following new features:[ol][li]Added support for Ignition v7.8.x[/li]
[li]Implemented Transient Datasets on the cache result data to avoid storing huge datasets in your saved windows’ components. Dramatically improved designer responsiveness and client window open speed.[/li]
[li]Added convenience functions for using Transient Datasets in general contexts.[/li]
[li]Fixed a bug in gateway-client cache status tracking for historical data.[/li][/ol]