Howdy - I get various one-off requests for historical data from management types. I just use the script console and a snippet that uses system.tag.queryTagHistory to grab the data and export it to an excel file.
The problem comes in when I need to get more than a handful of data points. Today's request is for a single tag at one hour intervals over a year. I am grabbing only a month at a time, but the query is taking over 10 minutes to execute.
Am I missing something? It seems to me that this should be a relatively easy query.
I'm using Postgres and am on 8.1.17. The DB resides on the gateway and it has 32 GB of RAM.
There are a ton of reasons why your query could be slower than expected, and because of the level of investigation required to drill down I'd recommend reaching out to support and making a ticket. You can head on over to https://support.inductiveautomation.com/hc/en-us and get to us in whatever medium is most convenient for you.
Here's some steps I'd consider taking without knowing more:
- Are queries in pgAdmin slower than expected as well, or only queries coming from Ignition?
- It's going to be pretty hard to do an apples to apples comparison of that tag history query to a simple select as it could be doing a lot more, but getting a baseline comparison would be a good start
- Are you using scan class validation? If your sqlth_sce table has grown out of control, this can bloat query times
- What aggregation mode are you using for your query? Just because it's pulling only 1 item every hour doesn't necessarily mean it is truly only dealing with 1 record out of an entire hour slice.
- It is typically not recommended to have the DB and Ignition on the same system as memory (particularly for queries over long ranges like the one you describe) is going to be constricted. When you run this query, what does memory usage on the host machine look like?
Those are just some general tips, but I would definitely recommend submitting a ticket to our support team.