Transaction Groups vs SQLTag Historian

Hello,

We are currently in talks of the best way to revamp our system (will require a phonecall to tech support as it’s a bit too complex/lengthy to describe here). This system was currently in place when I started working here, and we migrated from FPMI/FSQL since then, so a lot of our methodologies were already established before I got here.

I am currently reading through the documentation in-depth to see if there’s any neat features/improvements that can be made when we’re in the process of revamping. One of the things that I came across was the SQLTags historian option.

Currently, to store history on items, we create a new transaction group, and drag the tags we want to store history for into it from the OPC browser and we’re good to go. I read through the SQLTags historian/transaction group documentation a few times, and I see how they both work, but I can’t figure out in which situation it would be best to use one over the other. What are the advantages/disadvantages of each?

Thanks!

That is a good question. There are significant differences between SQLTags historian and transaction groups.

SQLTags Historian Pros

  • Data is only logged when the value changes, by exception according to a deadbad
  • Data is compressed using a built-in compression algorithm. An average we see around 54% compression.
  • Data is partitioned into separate tables by month to keep the size of the tables smaller for faster retrieval.
  • Minimal database maintenance
  • Fast retrieval times for large amounts of data
  • Easy to graph and view in tables

SQLTags Historian Cons

  • Not in control of the database schema. The data is in an open format but Ignition decides the schema.
  • Not very easy to query yourself because of the partitions and compression.
  • Have to use Ignition “Tag History” binding or scripting function to retrieve data.

Transaction Group Pros

  • You are in control of the database schema. You can put data into any format that you choose.
  • Easy to query yourself.
  • Can use the SQL language for more powerful analysis (of course you have to know the SQL language)
  • Has the ability to delete records older than a certain period of time
  • Groups can log data based on a schedule and by exception
  • Easy to graph and view in tables
  • Can use more components such as bar charts and pie charts since you are in control of querying the data

Transaction Groups Cons

  • Data is stored in a single table so you have to make sure the table doesn’t grow forever or performance will be slow
  • You don’t get compression or data partitioning
  • Bigger database size
  • You can only use around 100 tags per group
  • You have to know more about databases and SQL

As you can see transaction groups are used when the user wants to control the database and query the data any way they want. These people usually know more about SQL. The historian is great for beginner users since they don’t have to know much about SQL. However they are limited in how they can retrieve the data. We are constantly adding new features to our historian to make it more powerful in retrieving data.

You have to way the pros and cons to see which one really fits your needs. Hopefully this helps out.

2 Likes

Thank you Travis, that sums it up pretty nicely. I hadn’t considered that SQL queries would be more difficult since things aren’t named by tag, so it looks like continuing to use Transaction Groups will be the way to go for us.

We have a number of PHP scripts which query the transaction group tables for things such as the duration that our generators were running, capacity analysis, etc., so that would be a bit more difficult to maintain for us otherwise.

You might want to consider using both. I use the SLQTag Historian just for trends and then use Transaction Groups for special data (counters, runtimes, 24hr running averages).

I love the historian as many features and functions are built in for reporting on them.

I also use the transaction groups when I need event driven logic and special read/write functions based events and values of multiple items. But I have trouble using built in functions

It must be noted that I am still relatively new to this software and I know there is a lot I have to learn. My observation is that both are very useful. Use both.