"The connection has timed out" when creating lots of data in table

Back to Forums

Cute Bear

Question

Reactive

External Database

Application Type

Reactive

Good morning,

I have a server action with a loop that cycles through a list and uses the Create action from a table (external DB - Microsoft SQL Server). My list has almost 4000 records to be created in the table, and in the future it may have over 10 or 15 thousand.

I am getting a "The connection has timed out" error and not even 1 record is being saved to the DB. If I limit the maximum iterations of the For Each loop to a 10, for example, then the 10 records are saved. However, I need all of them to be saved.

What can I do to avoid this timeout error?

Thank you.

06 Mar 2023

Alexandre Yip

Hi Cute Bear,

My suggestion instead of increasing the timeout is to use the logic with the fix records(you can adjust the number by using a site property)

and use a timer to relaunch the requests till you have records to save.

Whenever you save a record in a dB save an attribute with that information to skip in the next iteration

Hope that it helps you

06 Mar 2023

Gitansh Anand

Hi @Cute Bear, I would suggest you use a process to do this work along with a save attribute as @Alexandre Yip suggested and the "CommitTransaction" server action provided by OutSystems so that you don't lose any progress.

I am adding an OML for the same implementation.

Also, I have not added this logic, but you can save the process ID and create a screen to check if the process has finished.

Thanks
Gitansh Anand

Test.oml

06 Mar 2023

Sanjay Kumar Sahu

Hi Cute Bear,

As per the best practice, You should use timer to insert bulk data in DB. Inside the timer action you can retrieve records in batch and save. But 10 - 15K records are not bulk so I would like to suggest you simple approach i.e. using forge component 'BulkInsert', its easy to use and faster, works like a charm with Sql Server, try it.

06 Mar 2023

Tousif Khan

Champion

Hello @Cute Bear
Since your data has a possibility to increase by 10k-20k in the future, I am not gonna suggest you to increase a timeout,

The best way I can suggest you is to create a timer, and you need to create an action flow that will process some records at a time asynchronously around 1000, and after iteration of those records you can commit a transaction so that the records that are already processed, won't be processed again.

Example:

But for that, you need to create logic or update flags in such a way that the start index of your loop will start from the index where you have new records to process. also at the start of the action flow add a time to a variable that will insure your action will not be time out and if that time is passed you can wake the timer in a flow.