How to Fetch Data in parts from an Outsystems DB?

Hi All,

Let me explain the problem first:

Scenario:

I am trying to save the data from Log tables (Platform Logs) into Local entities for longer retention and future use. We would like to achieve this by creating a server action and scheduling a timer job to run periodically to trigger it. Half way through, I have observed that the data is huge and if I fetch all the records at a time and try to save it by either using for each and iterating through the list/ using Bulk insert it will result in a timeout. 

To overcome this, we decided to fetch the data in chunks of say 10000 records at a time and Save it to local DB and then repeat the same for rest of the records. I am using a server action to fetch the records and save it into local entity. 

So far I have tried two methods and failed:

1. Using SQL : I tried to use OFFSET and LIMIT in the advanced query, but this works only if I am fetching data from a SQL DB and not from OS DB.

2. Using Aggregate : I cant set the Start at while using this since Pagination in Aggregates is only available for UI.


I would like to know if there is a solution to achieve this that I am not aware of.


Thanks for your help in advance. Cheers!!


Solution

Hi Amrutha,

You are on the right track, but fetching the records in chunks is only part of the solution.

Please follow this masterclass that explains how to build good timers for massive data processing that:

1) never time out 

2) don’t repeat work 

3) ensure completeness

4) ensure data integrity

I’m sure it will be of help to you.

Best regards,

Nordin

Solution