Have to insert bulk items in the database using the action of a button click.

Have to insert bulk items in the database using the action of a button click.

  

I am having one editable table widget and on a button click I want to save all the added dynamic values in the database. I don't want to use a for loop and save each data at a time as it will cause performance issues.

Is there any way to insert data as a list in the table from widgets?

Hi Aditi,

Why do you thing a For Each will cause performance issues? It's an Editable Table, so manual user input - surely a user can't have manually entered 10,000s of records? Any less and you definitely shouldn't have performance problems!

As for your question, on Mobile you can (with the SaveAll Entity Actions), but not on Web.

Kilian Hekhuis wrote:

Hi Aditi,

Why do you thing a For Each will cause performance issues? It's an Editable Table, so manual user input - surely a user can't have manually entered 10,000s of records? Any less and you definitely shouldn't have performance problems!

As for your question, on Mobile you can (with the SaveAll Entity Actions), but not on Web.

Hi Killan ,

Actually every time a for - loop will have CreateandUpdateEntity action and it will hit the database 10 times if we have 10 records dynamically entered. Hitting the database for each time will reduce the performance.


Hi Aditi,

We're not living in the 80s or 90s anymore! You should be able to "hit" the database 1000s of times a second without any noticeable performance problems!

(Also, review the topic of "premature optimization".)

Hi Aditi,

You can try one option: the BulkInsert forge component.

To this component  - you need to pass the list records structure.

BulkInsert Component:
https://www.outsystems.com/forge/component/1117/bulkinsert/?Unfollow=False


I have used this component in one of our projects and it has worked good.

If all you need is to insert multiple records in a single entitiy, the bulkInsert should work fine. 

Chetan Yewale wrote:

Hi Aditi,

You can try one option: the BulkInsert forge component.

To this component  - you need to pass the list records structure.

BulkInsert Component:
https://www.outsystems.com/forge/component/1117/bulkinsert/?Unfollow=False


I have used this component in one of our projects and it has worked good.

If all you need is to insert multiple records in a single entitiy, the bulkInsert should work fine. 

Note that BulkInsert component is intended for web applications.


Chetan Yewale wrote:

Chetan Yewale wrote:

Hi Aditi,

You can try one option: the BulkInsert forge component.

To this component  - you need to pass the list records structure.

BulkInsert Component:
https://www.outsystems.com/forge/component/1117/bulkinsert/?Unfollow=False


I have used this component in one of our projects and it has worked good.

If all you need is to insert multiple records in a single entitiy, the bulkInsert should work fine. 

Note that BulkInsert component is intended for web applications.


Hi Chetan,

How can we include bulk insert in action (like of a button click)? And yes i am using Aggregate is it applicable on the same?


Aditi Choudhary wrote:

Chetan Yewale wrote:

Chetan Yewale wrote:

Hi Aditi,

You can try one option: the BulkInsert forge component.

To this component  - you need to pass the list records structure.

BulkInsert Component:
https://www.outsystems.com/forge/component/1117/bulkinsert/?Unfollow=False


I have used this component in one of our projects and it has worked good.

If all you need is to insert multiple records in a single entitiy, the bulkInsert should work fine. 

Note that BulkInsert component is intended for web applications.


Hi Chetan,

How can we include bulk insert in action (like of a button click)?


That depends.

Whatever approach - you need to keep track of the items which were modified on the UI in a list record variable. So, every time the onchange event fires, keep tracking the changed elements. I am not sure if you are using an editable grid or a list record (you can have editable widgets in List Record..)

If you are using OutSystems web app dev:

Define a screen action for the button click. In this screen action - you can use the action provided by BulkInsert and pass the appropriate params. 

If you are using Outsystems mobile app dev: please use the "SaveAll" entity action as suggested by Killian.


Chetan Yewale wrote:

Aditi Choudhary wrote:

Chetan Yewale wrote:

Chetan Yewale wrote:

Hi Aditi,

You can try one option: the BulkInsert forge component.

To this component  - you need to pass the list records structure.

BulkInsert Component:
https://www.outsystems.com/forge/component/1117/bulkinsert/?Unfollow=False


I have used this component in one of our projects and it has worked good.

If all you need is to insert multiple records in a single entitiy, the bulkInsert should work fine. 

Note that BulkInsert component is intended for web applications.


Hi Chetan,

How can we include bulk insert in action (like of a button click)?


That depends.

Whatever approach - you need to keep track of the items which were modified on the UI in a list record variable. So, every time the onchange event fires, keep tracking the changed elements. I am not sure if you are using an editable grid or a list record (you can have editable widgets in List Record..)

If you are using OutSystems web app dev:

Define a screen action for the button click. In this screen action - you can use the action provided by BulkInsert and pass the appropriate params. 

If you are using Outsystems mobile app dev: please use the "SaveAll" entity action as suggested by Killian.


Is it possible for you to upload a sample of it.....as I am new to out systems and also elaborate on how to use Bulk insert component?

 


People,

First, please don't hit the "Reply" button without deleting older parts of the conversation, you're copying whole swaths of text, and that makes it pretty difficult to follow what's going on.

Secondly, the bulk insert is meant for bulk insert, so if you have 1000s of records, and should not be used if you have just 10 or so. Using bulk insert for that is a bad idea, for multiple reasons (for example, the bulk insert is asynchronous, so refreshing the screen directly after saving might not show the added records, or not all).

Kilian Hekhuis wrote:

People,

First, please don't hit the "Reply" button without deleting older parts of the conversation, you're copying whole swaths of text, and that makes it pretty difficult to follow what's going on.

Secondly, the bulk insert is meant for bulk insert, so if you have 1000s of records, and should not be used if you have just 10 or so. Using bulk insert for that is a bad idea, for multiple reasons (for example, the bulk insert is asynchronous, so refreshing the screen directly after saving might not show the added records, or not all).

Thanks Kilian ..:)


Hello Killian,

1. Agree that if it is just 10 records or so - bulkInsert is an overkill. But,  there certainly can be a scenario where a single record has many attributes and on the UI the user is changing say 50 records.  In this case, the default CreateOrUpdateAction will be slow with the looping. Every time you need to loop, OS has to generate the query and commit - and the process has to be repeated.  The need for bulkInsert would come as soon as you have many records being modified on the UI and every record has many attributes. It would be upto the developer to decide if they want the bulkInsert - and they can judge this by measuring how long it takes for typical user interaction. If a typical user interaction  - gives the impression of slowness with the sequential data insert - bulk insert will be required.

2. The BulkInsert is asynchronous - good to know this fact. I did not know this.

 But- database engines are very good at executing a script. The bulkInsert generates an INSERT statement script and the whole script is run. Database engines are so good at inserting records in bulk that 500 of such records would be inserted in 5 seconds or so. So - the delay is very small when using bulkInsert. Unless you are required to INSERT 1000s of records - this delay is ignorable.

In our project we had a requirement where multiple records had to be updated in very short duration and immediate feedback given. I implemented it using BulkInsert  - because the normal CreateOrUpdateAction simply was too slow with looping. 

You cannot justify to the end user any noticeable delay - just because you need time to commit multiple records to database. Had OutSystems had a similar action to what is there in mobile app development for "SaveAllEntities" - that would have been great. But - since that is not the case currently  - it is left to third party components like BulkInsert. BulkInsert has been battle tested in a project which is very UI heavy and has been working fine.

Till the time, we do the AJAX refresh , the database transaction is already committed in 99% cases (the 1% is when you really are inserting 1000s or more records). The pattern we used in our project was: do the BulkInsert, commit the transaction and then refresh the UI. A general user interaction required updating on average 100 records at a time.


3. Will keep in mind not to use the reply button as it creates swathes of text which is hard to follow. Thanks for pointing out.

Here is one scenario (and this comes from a real world project) - say I have an excel like UI. The user may edit the records in any of the row. Editing a record in any row triggers calculations which cause values in next few rows to be modified. In this case - it can easily happen that the user is modifying only 10 records by hand - but it is causing 500 records to get changed.

Imagine a project planner like feature - where updating the deadline of a task causes the deadlines of the future tasks to be re-calculated. In such cases, a single record change on UI will cause many other records to be updated.


Hi Chetan,

1. When you have many Entities, the Update Behaviour of the Entity should be set to "Changed Attributes", so updating a record takes less time. Of course, when all Attributes are indeed changed, this doesn't help.

2. I agree with the major points, and your use case seems to be a valid one, and you've obviously put a lot of thought in it. However, the OP seems to be a novice developer at best, and is worried about performance without seemingly actually running into problems, in which case it's imho better to suggest a typical solution instead of an advanced one. Nevertheless, your insight here is welcome, so thank you for that.

Hi Killian,


Yes, the update behavior setting to "changed attributes" is a good setting to avoid performance issues.

Agree that the OP is worried about the performance too early. As if its a typical user interaction where the user modifies only few records, the in-built CreateOrUpdate action with looping is just fine and it will not cause any noticeable delay. As typical user interactions involve modifying say 5 or 10 records at most.


@Aditi, I suggest you go with the in-built approach atleast initially. Once things are clear and users start interacting - and you find that users require updating many records and slowness is observed, you can think of the other approach. If it is a typical requirement, the normal CreateOrUpdate should be just fine.

I was not sure how many such records are being modified in one user interaction in your usecase.


Hi Chetan and Kilian,

I am currently working on developing a proof of concept for what challenges can be solved by Out systems.

I am looking at the bigger picture for now....i know updating 10 - 20 records can be done easily ......but I am also concerned about the bulk scenarios.

Thanks for pointing out the bulk insert component. It will be helpful if I get more insights on it, as there are less resources concerning it online.