Hi,
I'm seek your advice regarding what is the best approach to achieve mass database update using for each loop. I'm aware the we should implement Mass Update using only one query to avoid communication overhead in database based on OutSystems Best Practices (here). However, in some cases, we can't avoid to use for each loop for mass database update.So far, I think only two option that we can do to achieve mass database update using foreach loop:
1. We can use the CreateOrUpdate or Update database action that provided by OutSystems.2. We can put the Update queries inside the Advanced SQL, and put the Advanced SQL into the for each loop.What is the best approach based on these two options in terms of performance? Please also include the reason.Thank you.
Hi Chris,
With the mass update option ruled out, for performing the plain Create/Update operation I don't think there is any difference in terms of performance while using advanced SQL as opposed to built in entity actions. Only reason I can think of is the low code benefits, OutSystems being a low code platform I would encourage usage of built in features as much possible.
Thanks,
Junaid
Hi @Chris Andre Sitompul, if you are not capturing values from frontend and just simply want to update existing entries based on some logic, you should use a timer, refer to Implementing a Good Timer Exercise for best practice.
As for your two questions:
1. For updating an already existing entry you should use Update database action, use Create when you are sure that entry does not exist and use CreateOrUpdate only when you are not sure whether an entry exists or not .2. You can use Advanced SQL but we should use Advanced SQL only when we can not achieve what we want using an Aggregate.
ThanksGitansh Anand
The CreateOrUpdate/Update actions are generally the better choice as they are simpler and maintainable, especially when dealing with straightforward updates or small sets of records. These built-in actions are optimized for common CRUD operations, reducing the chances of errors and offering smooth integration with the OutSystems environment. While you are using the Advanced Query, the optimization and maintainability efforts will be high.
Thanks
I suggest you to create a dynamic advance sql create a single bulk update query string and pass it to the advance sql.
Instead of updating each record individually, process them in batches using Advanced SQL. This reduces the number of database calls and improves efficiency.
As per your scenario:
Use the CreateOrUpdate or Update actions, as OutSystems optimizes them internally to handle database operations efficiently.
Process them in batches with timer and use OS's in-built Update CRUD operation.Why batches -: Because we have huge data and an instance of timer can get timed-out after certain time. So we need to reiterate it in batches.Why timer -: We can achieve batch processing approach using timers.Why built-in CRUD operations -: Because these are optimized by platform server and that is why we need to utilize it instead of going with manual approach.