Bulk CRUDs in Traditional and React Apps

Hi, 

I was wondering about the best way to insert, update and delete records.

One feature the platform could offer would do this, by accepting a list of records to perform the action needed, which is already an idea, but that's not why I'm writing this question.

The main point is to understand how this operation currently works. Does the platform detect the user is iterating through a list of records that will be deleted/created/updated and generate a prepared statement that then executes on the list of items to delete/insert/update?

Thanks,

João Franco

mvp_badge
MVP

Hi João,


It can be really really bad for performance running queries in a for each since we are talking about multiple round trips to the database sequentially (current behavior of the platform with queries in for each).

An alternative is to use an advanced sql to bulk delete, create or update some records which is way way more performance.

Additionally, if we are talking about heavy processing, one should consider to do it asynchronously to avoid tampering the user experience (for example a timer running on off-peak hours that fills thousands of records).


Kind Regards,

João

Hi João,


Well, I was just wondering if OutSystems run some kind of optimization, with code analysis, like check that insert/update is inside a cycle and generate the SQL in a batch and then execute it, with some kind of limit per batch.


I'm familiar with all those approaches, but thank you. 


Kind Regards,

mvp_badge
MVP

Hi João,


From my experience it absolutely does not.

And it is very easy to check it by importing an excel file with 1000 rows or do a bulk insert of 1000 records and the difference of duration between both approaches speaks for itself.


Kind Regards,
João

Hi João,

well, all this said, I think the platform users would benefit a lot from the idea that I linked above, then. Because, currently you need to SQL code which may be harder to maintain and to change.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.