Best practice for bulk insert/update in REST APIs

Hi,

I have two questions,

1. Is there a best practice for bulk insert/update that is supposed to happen inside an Exposed Rest API(from outsystems) without looping through all the entries and inserting/updating one by one?
I know there's a bulk insert component, but other than that is there any best practice thatis recommended in outsytems?

2. And if there's a scenario where a result of an aggregate (10k+) needs to be inserted in a new entity, what is the best practice for doing so?

Solution

Hi,

1. For this scenario I suggest you use the BulkInsert component, as bulk insert tends to perform better than looping and single insert. My first observation is there is is less network traffic (between web server and database server), but also there are other considerations from the database point of view that make it faster. See this post.

2. For this scenario, you can use an INSERT INTO (check how to use here). Once again, this will avoid network traffic between webserver and database server, as you just issue an SQL statement to the database server and is up to the database to perform the operation.

You can also test your options and measure their performance.

Best,

Tiago

mvp_badge
MVP
Solution

Hi Amol,


Using Advanced SQL, your syntax should be accordingly to your database server (MySQL, SQL Server, Oracle).

In the case of SQL Server (the syntax from MySQL and Oracle should not differ that much from it), a bulk insert and a bulk update would look like this:



In the example above, I have Country_H to archive the countries and my update will bulk insert the active countries in the archive entity. The update will set the IsActive attribute to True in the archive table.


This of course, depends a lot on your use case but it should look something like the mentioned above.


Kind Regards,
João

mvp_badge
MVP

Hi Amol,


The best practice in both scenarios is to make a bulk INSERT or a bulk UPDATE using Advanced SQL, precisely to avoid doing N amount of calls to the database.


You can read more about this best practice in more detail in this article from OutSystems documentation.


Kind Regards,
João

Hi Joao, thanks for your prompt response.

Can you show a sample of how a bulk insert/update would look using Advanced SQL in outsystems?

Thanks,
Amol Rane

mvp_badge
MVP
Solution

Hi Amol,


Using Advanced SQL, your syntax should be accordingly to your database server (MySQL, SQL Server, Oracle).

In the case of SQL Server (the syntax from MySQL and Oracle should not differ that much from it), a bulk insert and a bulk update would look like this:



In the example above, I have Country_H to archive the countries and my update will bulk insert the active countries in the archive entity. The update will set the IsActive attribute to True in the archive table.


This of course, depends a lot on your use case but it should look something like the mentioned above.


Kind Regards,
João

Solution

Hi,

1. For this scenario I suggest you use the BulkInsert component, as bulk insert tends to perform better than looping and single insert. My first observation is there is is less network traffic (between web server and database server), but also there are other considerations from the database point of view that make it faster. See this post.

2. For this scenario, you can use an INSERT INTO (check how to use here). Once again, this will avoid network traffic between webserver and database server, as you just issue an SQL statement to the database server and is up to the database to perform the operation.

You can also test your options and measure their performance.

Best,

Tiago

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