I'm searching for a way to get a table insert incremental. I have a table (about 30 million records). This has a row for each hour of the day for 10.000 machines, so this table is getting bigger very quickly.
I do a lot of transformations in this table. This resulted in a load time of over 10 hours. I've discussed this with our TimeXtender Solution Specialist and we've decided to split the table into multiple smaller tables.
Now I do have 1 table (loaded incrementally from ODX), this table has a lot of lookups and simple transformations. This table is loading quickly because it's incrementally loaded.
The result of this table is inserted in a new table (Raw table), using a table insert. This table is converting cumulative numbers to noncumulative, using the SQL functions LAG, OVER, PARTITION BY and ORDER BY. Because it is a table insert I have no idea how to do this incremental. I've set NonClusted Indexes (also on Raw table) on all fields used in the PARTION BY function. And enabled Batch Cleasing using 500.000 rows. This gives me mutch faster loading speeds. But this table is still taking almost 4 hours to load. This is increasing because every day a lot of new rows are added to this table.
I'm looking for a way to get the table insert incremental, this data is never changed. So no need to handle updates.
I also find an idea on the Idea and Suggestion page asking for this feature: https://support.timextender.com/hc/en-us/community/posts/360060702652-Incremental-selection-rule-for-table-inserts?input_string=Incremental%20Table%20insert