Skip to main content
To homepage To homepage

Hi,

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 page asking for this feature: https://support.timextender.com/ideas/incremental-selection-rule-for-table-inserts-321

 

 

Hi Bas, here is how I would do it: 

In the screenshot below:

  1. Is my original table. 
  2. Create a view of this table. 
  3. Insert the view into a copy of the original table. (drag the vew to the tables node to create a copy & table insert)
  4. Make your copy a History table. (This just prevent the table being truncated on subsequent executions)
  5. Edit the view. Add a WHERE clause & Sub-SELECT that grabs only the records with a timestamp greater than the previous load.

 

Do you think that will work? 

 

 

Hi Joseph,

Thanks for the quick reply! 

I've tested it using an Excel file. It works like expected.

Nevertheless, it would be easier if we can use an Incremental Selection Rule on table inserts.

Reply


Terms and ConditionsCookie settings