# Tag history db architecture **URL:** \ **Category:** General Discussion\ **Tags:** database\ **Created:** [November 26, 2025, 8:36am UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442 "2025-11-26T08:36:24Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![amolinse](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@amolinse](https://forum.inductiveautomation.com/u/amolinse)\ **Post date:** [November 26, 2025, 8:36am UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442/1 "2025-11-26T08:36:24Z")
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? ---
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [November 26, 2025, 9:23am UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442/2 "2025-11-26T09:23:00Z")
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. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [November 26, 2025, 9:29am UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442/3 "2025-11-26T09:29:01Z")
> [@amolinse](#): > > Performance becomes terrible when large amounts of data are involved — it can take minutes to load the table, and I often get timeouts. Do you have indexes setup on the tables in question? e.g. [https://www.sqlshack.com/designing-effective-sql-server-non-clustered-indexes/](https://www.sqlshack.com/designing-effective-sql-server-non-clustered-indexes/) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 26, 2025, 12:04pm UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442/4 "2025-11-26T12:04:08Z")
> [@amolinse](#): > > ... but it became messy because I ended up creating a separate table for each position in the factory. 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: ```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. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 26, 2025, 9:35pm UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442/5 "2025-11-26T21:35:56Z")
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 ---
**Author:** ![amolinse](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@amolinse](https://forum.inductiveautomation.com/u/amolinse)\ **Post date:** [November 27, 2025, 8:19am UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442/6 "2025-11-27T08:19:49Z")
Yes exactly this is what I exactly mean. I have already created a tall table partitioned by date where I have copied historian tag history table architecture just adding 2 extra columns: Batch\_id and position\_id. My question is how to pivot (transpose) this sql table when showing in a power table in a efficient way. I have already tried using pivot in sql query, but as I said performance is terrible for positions with a lot of parameters (it means a lot of columns). I have also tried to transpose my table looping the select result and transforming it to a table with columns, but I get same bad result as with the other solution. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 27, 2025, 5:14pm UTC](https://forum.inductiveautomation.com/t/tag-history-db-architecture/112442/7 "2025-11-27T17:14:05Z")
Have you got any indexes on your tables? I would use EXPLAIN (check the right term to use for your DB) to get the execution plan and post that here