loading optimization

[Barba]

Hi all,

Sorry in advance for my english. So I need your help to optimize the loading of the workflow. I have a workflow connected to an Oracle database. This workflow will bring back the data of the last 12 rolling months, which takes me 2 hours a day. I would like to be able to create a “buffer” database that will store this data, and each day only have to reload the last 7 days and delete the 7 oldest dates at the same time.
How can I do this?

Many thanks in advance

[ScottF]

Hi @Barba and welcome to the forum.

Correct me if I’m wrong, but it sounds like your process would look something like this, for a workflow you want to run once each week:

0. One-time creation of buffer table. Start actual process one week later.
1. Submit query to obtain latest week of data, and append to buffer table.
2. Remove oldest week of data in buffer table.

The trick here is to create appropriate SQL queries based on the current date, most likely using flow variables in KNIME. Maybe something like this in a DB Query to grab the newest records for Step 1.

SELECT * FROM "MyTable"
WHERE ("Date" >= $${SMy7DayLag}$$ )

The trick here is to come up with the appropriate flow variable for My7DayLag using nodes. Something like this could work:

Then of course you would need to come up with an appropriate date variable (and associated query) for Step 2, but you get the idea.

Also, this design of course requires you to run the queries at the appropriate time. There’s probably a more robust way to approach it, but I leave that to you. Hope this helps.