Historical table optimization/partitioning

Ignition
1

Ok, maybe I’m missing something obvious, but getting meaningful information out of the historical tables is starting to get unwieldy.

For example, building custom reports takes a really long time when my query has to handle 10+ million rows of data in a single table, and even more if I join tables together in a view (150m+ rows). To me, the biggest issue seems to be everything is optimized to the ORM that Ignition is using when much of this can be offloaded to the database itself.

For example, instead of partitioning all the data into logical tables, SQL Server can partition data on indexes into separate file structures and range boundaries. To me, this would be much faster than building a single material view of the tables Ignition creates.

If the feature request isn’t evident, I’m asking for you to have the ORM let go a bit, or program it to use the right features for at least the big 4 (SQL Server, MySQL, Oracle, Postgre)

Example:

-- create a partition function that splits on 3,000,000,000ms intervals, so about 34.7 days
DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION timestamp_partfunc (bigint) AS RANGE RIGHT FOR VALUES (';
DECLARE @i int = 1325390000000;
WHILE @i < 1469390000000
BEGIN
	SET @IntegerPartitionFunction += CAST(@i as nvarchar(14)) + N', ';
	SET @i += 3000000000;	
END
SET @IntegerPartitionFunction += CAST(@i as nvarchar(14)) + N');';
EXEC sp_executesql @IntegerPartitionFunction;
GO

-- partition on 48 file groups, so about 4 years worth of historical growth before you have to add room to the partition scheme
CREATE PARTITION SCHEME timestamp_partscheme
 AS PARTITION timestamp_partfunc
 TO (fg101, fg102, fg103, fg104, fg105, fg106, fg107, fg108, fg109, fg110, fg111, fg112, 
       fg111, fg202, fg203, fg204, fg205, fg206, fg207, fg208, fg209, fg210, fg211, fg212, 
       fg121, fg302, fg303, fg304, fg305, fg306, fg307, fg308, fg309, fg310, fg311, fg312, 
       fg131, fg402, fg403, fg404, fg405, fg406, fg407, fg408, fg409, fg410, fg411, fg412
 )

-- create the sqlt_data table using said scheme and the timestamp as the primary key.
CREATE TABLE sqlt_data(
	[tagid] [int] NULL,
	[intvalue] [bigint] NULL,
	[floatvalue] [float] NULL,
	[stringvalue] [varchar](255) NULL,
	[datevalue] [datetime] NULL,
	[dataintegrity] [int] NULL,
	[t_stamp] [bigint] NULL
)
 ON timestamp_partfunc (t_stamp)
2

What you’re missing is that SQLTags history was never intended to be queried by hand. It was supposed to be an “easy” way for people to store history, and then use it in charts and tables, where Ignition is handling the querying. For reports and more complex data uses, we expected that people would keep using Transaction Groups like they did before.

Of course, when its just so easy to use, everyone is going to use it and then expect it to do everything :laughing: That’s not a surprise, but something we’ll have to deal with over time, with improved query options, and perhaps various configurations for how data is stored.

Now, as for partitioning: the current mechanism was built to give some form of partitioning that could work across all dbs, with full knowledge that the main DBs have better mechanisms. It strikes me that the biggest missing feature is the ability to turn off partitioning and simply define a data table name, but short of that, you could set the partition size to 10 years, and then set up the table yourself to partition however you’d like. Us adding built in partition support for the main dbs is a noble goal, but complex, as the databases vary a lot between versions, and there are usually various choices involved. Still, in a general sense, the strategy of introducing specific optimizations for the most common dbs, instead of broad support for everything, is one that I can see us going towards.

What did you have in mind when you say “let go a bit”, do you mean just the ability to turn off partitioning? Because we can definitely get that in for 7.5. If you had something else in mind, please let me know.

Regards,

3

I can definitely appreciate the complexities of what is going on in the background and the transitions to new versions. What I meant by "let go a bit" was that it would be really nice to see the ORM try to use the database features rather than try to reinvent things (for example having the ORM be aware that the table may be partitioned using a partition function and partition scheme rather than separate isolated tables).