Log in to AnswerLog in to Follow
2022-06-30 16-18-30
Diogo Miguel Fernandes
31Views
4Comments
Solved
Advanced SQL instead of a For Each
Question
Reactive
Application Type
Reactive
Service Studio Version
11.53.7 (Build 60881)

Hi,
I have an Entity for Certifications(Id, Name, Certifier, Cost) and an Entity for Exams(Id, CertificationId, Name).
Since a Certification can have 1 or several exams, I have a server action that saves both, a Certification and a List of Exams.
For that i have this logic.

The maximum number of Exams in a Certification i've seen so far is 5, so this is not a bad way to Create or Update data to my database.
However let's say a new Certification comes out and it has 1000 Exams...
Performance wise, I'd like to replace the ForEach triangle for an Advanced SQL that could both create or update information, and even though I know SQL, i'm not too advanced in it yet.

Can someone help me write an SQL code that could replace that ForEach triangle please?

Thank you!

1
0
13 Jul

They don't prohibit the use of forge assets, but since I'm fairly new in OutSystems, my Mentor doesn't want me to use ForEach -> CreateOrUpdates, just for good practices...of course it would be an unrealistic case a certification with thousands of exams, but tomorrow I might be working for a client that needs to create or update thousands of registries, so he wants me to use performance guidelines. I get that :)  

See solution in context
1
0
14 Jul

I can try the SQL code later but in the meantime (or while no one else comes up here with the requested solution) you could consider BulkInsert. As seen in James Andrade's video below, you could create/update 50,000 rows in less than a second with BulkInsert while the 'CreateOrUpdate' action would take 23 seconds - and the 'Create' action 12 seconds)


4
0
13 Jul

@Anderson! thank you very much for that video, it has very useful information and i also didn't know there were performance differences between create or createorupdate, also i didn't know about that BulkInsert.
Still that BulkInsert, as useful as it may be, it's not an option for me. I need to be able to do it without forge assets unless absolutely necessary.

So the Advanced SQL is the only way to go for me if i want max performance.

1
0
14 Jul

Hi Diogo,

I don't have a solution for you, but as this is an SQL question, you could google for it, no reason to limit your resources to the Outsystems community.  See for example this.

Just 2 observations :

1) The strength of low code platforms like Outsystems lies for a part in the availability of ready to use forge assets, and the platform allowing you to manage them and deal with new versions of consumed assets, etc.  On top of that, the BulkInsert asset has the label of being validated and supported by the community experts.  So it's sad that your company guidelines prohibit you from making use of this.  Maybe someone should challenge that, but I understand that this may not be something you want to take upon yourself.

2) How realistic is your example of 1000 exams for a single certification ??? And even if it would exists once in a blue moon, do you really want to do premature optimization for this exceptional hypothetical outlier ? At worst, it will take a few minutes to create those 1000 exams.  So I would just replace the CreateOrUpdate with an Update and leave it at that.  Just monitor production data from time to time, and if it becomes common to have many many exams for a single certification, you can make a user story at that time to make this more performant.

Dorine

1
0
14 Jul

They don't prohibit the use of forge assets, but since I'm fairly new in OutSystems, my Mentor doesn't want me to use ForEach -> CreateOrUpdates, just for good practices...of course it would be an unrealistic case a certification with thousands of exams, but tomorrow I might be working for a client that needs to create or update thousands of registries, so he wants me to use performance guidelines. I get that :)  

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