Boss Mode Ignition’s Tag History Splitter
Hold on to your hats folks, today we are going to talk about database performance!
Seriously though, one of the common things people will use to say you should use one platform or another for your historical data is how long it takes to query your data, and how you can’t possibly use a SQL database for anything with decent speed. Just about everything you interact with online has a database behind it, and if it can work for things that have their hooks into everything as Facebook does, it can work for manufacturing.
Yes, you will probably run into some hurdles if you are storing 20 million of tags every second or faster. You will run into issues with any system at that scale. If we were discussing what is arguably the most massive data generator in the world, CERN, we would be talking about something else entirely*. Since this isn’t directed to processes at the scale of particle accelerators, rest assured, Ignition can work for your historical data needs.
Tag History Splitter
The primary concept behind how we use the Tag History Splitter is to collect data in a similar format to what you will be querying. For example, if you are going to be querying a couple of days worth of data at a time, you want to segment things differently than if you are going to be pulling in a year of data at a time.
The Tag History Splitter handles this by allowing you to configure splitters to use a particular connection depending on the date range of your query. In conjunction with the Tag History Provider’s partition length property, you can structure your data to match your queries.
Think about it like this, most of your data access is going to be real-time or from the past few days. Set up your main history with a 1-week partition, so you are querying from the most recent table for your most recent data. Add in a second Tag History Provider with a 1-month Partition for data that will be accessed less often than real-time. Then set up a Tag History Splitter with a 1-month cutoff so anything that is older than a week, and when you query the data you will pull from the monthly partition. This cutoff approach will increase performance by querying the data from a single table.
You can create a third Tag History Provider with a 1-year partition, and a similar splitter, so anything older than a month comes out of this table and not a handful of smaller tables.
You can nest your splitters like this to cover ever-increasing timespans to reduce the overhead in pulling from multiple tables even further. Querying two months of data from a year ago in a 1-year partition will be noticeably faster than from pulling it from 8 or 9 weekly partition tables from the same period. The Tag History Splitter will do this aggregation for you automatically.
You can further increase performance by breaking down the tags into different providers to both reduce the overall data storage requirements, and speeding up your queries with smaller overall tables in the first place.
If you want more information on Tag History Splitters in Ignition, and how they can help your system, please reach out let us know!
*Even then, the systems they use are on another level from just about anything in the manufacturing world based on what we have seen and built for super high-speed data collection with the same software they use at CERN.
Learn more about Corso’s Ignition projects.
Find more Boss Mode Posts!