Hi @Anushka singh ,

Yes, processing millions of records in batches would be a good and scalable approach.

You can implement the processing flow as follows:

1. Add a processing status attribute
   • Add an IsProcessed Boolean attribute to the entity.
   • Set the default value to False.
2. Timer-based batch processing
   • Create a Timer/Server Action to process the records asynchronously.
   • At the beginning of each execution, check whether there are any records where IsProcessed = False.
   • If records are available, fetch them using SQL pagination with an appropriate batch size.
3. Configurable batch size
   • The MaxBatchSize should be configurable through a Site Property rather than hardcoded.
   • For example, the Timer can process 5,000 or 10,000 records per execution depending on the expected volume and database performance.
4. Business processing
   • Process the selected batch according to the required business logic.
   • Once a record is successfully processed, update IsProcessed = True.
   • This ensures that successfully processed records are not picked up again in the next execution.
5. Continue processing
   • After completing the current batch, check whether the remaining unprocessed record count is greater than the configured MaxBatchSize.
   • If records are still available, trigger the Timer again to process the next batch.
   • Continue this process until all records are processed.
6. Exception handling
   • Implement proper exception handling around the batch processing logic.
   • If an individual record fails, capture the exception details in a logging entity with information such as Record ID, error message, timestamp, and processing status.
   • Do not mark a failed record as IsProcessed = True; this allows it to be retried later.
   • If the entire batch fails due to a system/database exception, log the exception and allow the remaining records to be processed in the next Timer execution.
   • Consider implementing a retry mechanism with a configurable maximum retry count to prevent continuously retrying a permanently failed record.

This approach provides scalability, controlled database load, retry capability, fault isolation, and protection against user request timeouts, especially when processing millions of records.
Let us know if you want more information.

Regards
Deepa

Hi @Anushka singh ,

Here is a more in-depth article you can follow that should answer most of your questions: How to Manage Heavy Timers in OutSystems .

To answer your questions briefly:

1. What is the recommended approach for processing a large number of records using a Timer?

You already described the recommended approach: process the records in manageable batches instead of handling everything in a single Timer execution.

2. How should I implement batch processing/pagination?

Define a configurable batch size,  and use appropriate filtering and deterministic sorting in the Aggregate. Each Timer execution should retrieve and process only the next eligible batch.

3. Should I use GetRecords with StartIndex/MaxRecords, Aggregates, or SQL?

Aggregates are generally easier to understand and maintain. I would use SQL only when the query is too complex or cannot be efficiently implemented with an Aggregate.

If processed records are excluded through a status filter, you may not need StartIndex at all. You can repeatedly fetch the first batch of unprocessed records.

4. How can I prevent records from being processed twice?

The safest approach is to track the processing status on each record, for example Pending, Processing, Processed, or Failed. Fetch only eligible records and update the status after processing.

Alternatively, you can maintain a separate execution entity containing information such as:

• Execution status
• Start and end times
• Number of processed and failed records
• Last successfully processed record ID

You can then retrieve the last processed ID when the Timer starts, filter for records with a greater ID, and sort by ID ascending. However, be careful with this approach because advancing the last processed ID could cause failed records to be skipped unless they are tracked separately for retry.

5. What is the best way to handle failures during processing?

Usually, one failed record should not stop the entire batch. Catch the error, record the failure, and continue processing the remaining records.

Depending on the reporting requirements, you can:

• Log only the number of failed records.
• Store each failed record and the corresponding error message in an entity.
• Generate an Excel report and send it by email.

Retrying failed records or generating reports should preferably be handled by a separate action or Timer, triggered only when necessary.

      6. Are there any best practices regarding Timer timeout, batch size, and transaction handling?

You can read this:  Optimizing OutSystems timers according best practices

Hello @Anushka singh,

Along with my colleagues answers, I’ve attached an OML as an example for your reference. 

Hello.

Large timers and batch processing is a topic well-documented so I don't need to repeat what others have said.

Just a note on Batch Size. It will depend a lot on the volume of work. Optimize memory taken and number of reads done.

• If is to a simple update (like a "assign tasks" or "delete expired orders" process) you can do thousands at once.
• If it is to send emails or move data, maybe hundreds.
• If the record you fetch has multiple entities involved, maybe start with dozens.
• If it is calling an API, maybe you need to set a delay between calls to prevent getting blocked.

Trial and error will get you there and the configurable BatchSize property allows you to adjust it at runtime (at least in O11).

From experience, I usually do 500 to 3000. The timer logic you see on the links has a loop to fetch more records in one execution so it can still do millions in under 20 minutes.