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)