Hold on to your hats folks, today we are going to talk about database performance! Seriously though, one of the common arguments people use to justify choosing one platform or another for historical data is how long it takes to query your data—and how you can't possibly use a SQL database a system with a lot of data and still get decent performance. Just about everything you interact with online has a database behind it, and if it can work for social media sites, it can work for manufacturing.

Yes, you will probably run into some hurdles if you are storing 20 million tags every second or faster. But, you will run into issues with any system at that scale. Consider what’s arguably the most massive data generator in the world, CERN, in that situation, we would be talking about something else entirely*. Since our processes are not at the scale of particle accelerators, rest assured, Ignition can work for your historical data needs. And, if you wanted to run a particle accelerator on Ignition, Corso Systems can help you figure out how to do it!

Ignition’s Tag History Splitter

Primarily, we use a Tag History Splitter to store data in a similar format to what we 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 that data differently than if you are going to be pulling in a year’s worth at once.

The Tag History Splitter allows 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: if most of your data access will be real-time or from the past few days, then set up your main history with a 1-week partition. Now, you will be querying from the most recent table for your most recent data. Then, add in a second Tag History Provider with a 1-month partition for data that will be accessed less often than real-time. Finally, set up a Tag History Splitter with a 1-month cutoff for anything older than a week, and for 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. 

Nested Splitters

You can create a third Tag History Provider with a 1-year partition, and use the same concept as the 1 week vs. 1 month splitters. Now, anything older than a month comes out of this table and not from a handful of smaller tables. Reduce the overhead in pulling from multiple tables even further by nesting splitters to cover ever-increasing time spans. Querying two months of data from one 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. This will both reduce the overall data storage requirements, and speed 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 they’re built for super high-speed data collection with the same software they use at CERN.

Learn more about Corso's Ignition projects.

Updated - 6/14/2022