Tag history db architecture

General Discussion
1

I’m curious about the best way to store parameters in an SQL Server database. In the beginning, I used the Tag Historian to save data, and Ignition automatically created its own database architecture, storing each parameter as a row and partitioning the tables by month.

Later, I ran into a problem: in production, they wanted to save a group of parameters per batch. I tried using Transaction Groups, but it became messy because I ended up creating a separate table for each position in the factory. To display the data, I first had to know the position in the factory and then write a specific query for each one.

Because of this, I decided to copy the same architecture used by the Tag Historian. I now save all parameters in a batch, linked by a column that indicates the batch_id. The problem is that when I try to display the data in a table, I need to pivot the results so they appear as columns instead of rows. Ignition does this automatically for historian tags, but in my case I need to do it manually in the SELECT query. Performance becomes terrible when large amounts of data are involved — it can take minutes to load the table, and I often get timeouts.

Is there a better way to pivot data in a table? I’ve read in some SQL forums that Excel has more powerful tools for pivoting than SQL, but what about Ignition?

2

You can write whatever you want to process data from a database query in Jython, it's justa programming language, and it will do what you write. I regularly write code that processes data that I have grabbed from a massive database with simple filters. This means that the client is the one that deals with the processing overhead.

3

Do you have indexes setup on the tables in question?

e.g. https://www.sqlshack.com/designing-effective-sql-server-non-clustered-indexes/

1 Like
4

The standard approach here would be to have only one table for all positions in the factory but add a column for the position name. This way you can retrieve data for that position in the SQL:

SELECT ....
FROM ...
WHERE
    position = :positionNum
...

You would pass positionNum as a query parameter.

This method would also allow you to create aggregate reports for all or multiple lines without having to UNION query results from multiple tables.

5

I think what op means is that each machine has different numbers of parameters to store, which means storing thm in a tall table rather than wide, but then there’s the additional effort required to bring it back to wide for display purposes

1 Like