[WEB] Is there any better way to do this? Optimization needed (Post Service, JSON)

[WEB] Is there any better way to do this? Optimization needed (Post Service, JSON)

  

First of all, thank you all by advance for your time.


So far this is 100% working with 0 issues but I'm just trying to think about any other way that might use less local variable which will end up with a faster service.


What I'm trying to do with this Post Service:

-Get the body from the service, which will be deserialized to become a Policy object.

-Use that object together with some needed local variables to access all the fields in order to insert them into the database.

-Return the object that has just been added but this time it will have the internal ID (the one the database sets to every single new item added).



-As you can guess, the input body string becomes a Policy object (which is a datatype from one of the Entities I'm currently using).

-Next, the object gotten from the JSON is set to a local variable with the same datatype which will be used to access all the fields so that I can put them into the SQL Query.

-A new row is added to the Policies database successfully.

- In order to get some an item from the database, I'll be using a GetPolicies componente.

-Lastly, the output object (datatype Policy) is returned from a GetPolicies(Id:(GetPolicies.List.Length) query.


In case you're wondering, the warning is about data type conversions so nothing to worry about.

Unfortunately I am not able to share the .oml so please don't ask for it.



Let me know if there is any better or more optimized way to do this.


Thank you all again.


Hi,

You can consume REST or SOAP services from Outsytems. This will enable you not to have to deserialize JSON explicitly. You can check Integration documentation here.

With that being said, I would consume the service in Outsystems (Logic Tab) and it would return a Policy structure. 

In the action you showed, I would call the service and assign the service output structure to the action's output variable (Policy entity), mapping the structure's attributes to the entity's.

Then I would use a CreateOrUpdate action to create the Policy in the database. This action returns an Id, which I would then assign to the variable used to create the record. The action would look like the image below.

Note: I am assuming that the service only returns one Policy, and not a list.

Regards,

João

João Mateus wrote:

Hi,

You can consume REST or SOAP services from Outsytems. This will enable you not to have to deserialize JSON explicitly. You can check Integration documentation here.

With that being said, I would consume the service in Outsystems (Logic Tab) and it would return a Policy structure. 

In the action you showed, I would call the service and assign the service output structure to the action's output variable (Policy entity), mapping the structure's attributes to the entity's.

Then I would use a CreateOrUpdate action to create the Policy in the database. This action returns an Id, which I would then assign to the variable used to create the record. The action would look like the image below.

Note: I am assuming that the service only returns one Policy, and not a list.

Regards,

João


Thank you so much for your answer, João.


I can see the point on your answer and why it may work better but maybe I forgot to say that all the data we are "playing" with is stored in another back-end so in this case, the OutSystems interface will only trigger some services that will end up getting or sending information from our very own server. That's why I had to use SQL Querys instead of OS Database methods like the one you used above (CreateOrUpdatePolicy).


So yeah, I really appreciate your answer and I'm so sorry for not pointing out that the data and files we are using have nothing to do at all with OutSystems databases.


I hope you get what I'm trying to say. If you come up with another answer that will work with this purpose (as I said, something like a service trigger and data pass-through) feel free to share with me, I'm willing to test it out.


Thank you kindly.


P.s: Yeah, this service is supposed to only return 1 policy.

Why do you want to improve your solution?

Do you encounter performance issues?


J. wrote:

Why do you want to improve your solution?

Do you encounter performance issues?


Of course I want to improve my service. I really appreciate every single improvement in order to get much better overall performance. Why would anyone decline a better coded and more efficient solution ?


As this will end up being a huge project, every single detail matters. It's not like I have performance issues so far though I don't really know where this project will be working on at some point.


P.s: sorry about the bump thingy, I'm just so new with this technology that I felt like I was doing it not in the best way possible.


Hi,

I have a few questions regarding your implementation:

  • What kind of service are you using to fetch the Policy information? Is it SOAP, REST?
  • What are the Local Variables you are using? 
  • What is in the Assign?
  • What is in the SQL1 Query?
  • In the GetPolicies aggregate, what joins are you performing and what filters are you using? 
  • Do you need to get several policies from the aggregate, or just one? In the latter case, you should set MaxRecord to 1.

From what I can see, you will probably not need a local variable to store the 'JSONDeserializePolicies' information, as that action already exposes a variable you can change and use in the Advanced Query.

Cheers,

João

Like João said, you can access output from JsonDeserialize directly.

No need for another local variable.

But I doubt removing it will gave an iota of perceivable performance improvement.

João Mateus wrote:

Hi,

I have a few questions regarding your implementation:

  • What kind of service are you using to fetch the Policy information? Is it SOAP, REST?
  • What are the Local Variables you are using? 
  • What is in the Assign?
  • What is in the SQL1 Query?
  • In the GetPolicies aggregate, what joins are you performing and what filters are you using? 
  • Do you need to get several policies from the aggregate, or just one? In the latter case, you should set MaxRecord to 1.

From what I can see, you will probably not need a local variable to store the 'JSONDeserializePolicies' information, as that action already exposes a variable you can change and use in the Advanced Query.

Cheers,

João

  • Right know it is a REST service.
  • 1 variable to assign JSON's value and 1 variable for every single field of the JSON's value, variables that I'll be using on the SQL Query so they are needed as the DeserializeJson can't be referenced on SQL Queries.
  • JSONDeserialize -> related Outsystem Object and 1 assignation for every field from every object field or property.
  • Insert into table_name VALUES(JsonDeserialize.Data.Value1, JsonDeserialize.Data.Value2, etc..)
  • No joins and no filters but now the bell rang on my head and I thought about sorting the GetPolicies in order to get the latest item ( the one that I have just added) once I set Max Record to 1. In case it's not clear yet, this service will return the object that is about to be added so yes, same object but with the internal ID that Outsystems database will set to it.
  • That's what I thought when I read your previous point, it would be so much better if the aggregate returns only one value.



Can you believe this? I made myself realized some lacks of performance from just reading your question though I still want to read your opinion.


Feel free to ask again if what I've replied is not clear enough.


Jordi Gisbert Ponsoda wrote:

João Mateus wrote:

Hi,

I have a few questions regarding your implementation:

  • What kind of service are you using to fetch the Policy information? Is it SOAP, REST?
  • What are the Local Variables you are using? 
  • What is in the Assign?
  • What is in the SQL1 Query?
  • In the GetPolicies aggregate, what joins are you performing and what filters are you using? 
  • Do you need to get several policies from the aggregate, or just one? In the latter case, you should set MaxRecord to 1.

From what I can see, you will probably not need a local variable to store the 'JSONDeserializePolicies' information, as that action already exposes a variable you can change and use in the Advanced Query.

Cheers,

João

  • Right know it is a REST service.
  • 1 variable to assign JSON's value and 1 variable for every single field of the JSON's value, variables that I'll be using on the SQL Query so they are needed as the DeserializeJson can't be referenced on SQL Queries.
  • JSONDeserialize -> related Outsystem Object and 1 assignation for every field from every object field or property.
  • Insert into table_name VALUES(JsonDeserialize.Data.Value1, JsonDeserialize.Data.Value2, etc..)
  • No joins and no filters but now the bell rang on my head and I thought about sorting the GetPolicies in order to get the latest item ( the one that I have just added) once I set Max Record to 1. In case it's not clear yet, this service will return the object that is about to be added so yes, same object but with the internal ID that Outsystems database will set to it.
  • That's what I thought when I read your previous point, it would be so much better if the aggregate returns only one value.



Can you believe this? I made myself realized some lacks of performance from just reading your question though I still want to read your opinion.


Feel free to ask again if what I've replied is not clear enough.


Hi,

Outsystems allows you to consume RESTful services which automatically convert your JSON to an Outsystems structure, eliminating the need to call the Deserialize action. Check Consume REST API.

You seem to be inserting one Record in the Database using an Advanced Query and then using an Aggregate to fetch the Record you inserted. 

You should have an Outsystems Policy Record local variable where you will assign the DeserializeJSON output values (Outsystems allows you to map a structure to an entity) and then use an Outsystems CreateOrUpdate action to create the Record in your Outsystems database. 

This CreateOrUpdate action will return the Outsystem internal identifier for the created record, eliminating the need for you to do an aggregate to get the record you just inserted. This way, by using the CreateOrUpdate action, you will not need to have a local variable for each JSON field (I now understand why you wanted to find a better solution, that was not very efficient).

Performance-wise returning every Policy (by using no filter or joins) in the Outsystems database just to get the latest created policy is a very poor solution and this would degrade performance greatly if your have many Policy entries in your database.

Even though it is not necessary if you use a CreateOrUpdate action I recommended, you at least should have set MaxRecords to 1 in the aggregate to get only the latest policy. The best solution, in that case, would be to filter the aggregate by the Policy identifier from the object returned from the service. (policies should have policy numbers)

I hope my answer can help you develop a better solution.

Cheers,

João


João Mateus wrote:

Jordi Gisbert Ponsoda wrote:

João Mateus wrote:

Hi,

I have a few questions regarding your implementation:

  • What kind of service are you using to fetch the Policy information? Is it SOAP, REST?
  • What are the Local Variables you are using? 
  • What is in the Assign?
  • What is in the SQL1 Query?
  • In the GetPolicies aggregate, what joins are you performing and what filters are you using? 
  • Do you need to get several policies from the aggregate, or just one? In the latter case, you should set MaxRecord to 1.

From what I can see, you will probably not need a local variable to store the 'JSONDeserializePolicies' information, as that action already exposes a variable you can change and use in the Advanced Query.

Cheers,

João

  • Right know it is a REST service.
  • 1 variable to assign JSON's value and 1 variable for every single field of the JSON's value, variables that I'll be using on the SQL Query so they are needed as the DeserializeJson can't be referenced on SQL Queries.
  • JSONDeserialize -> related Outsystem Object and 1 assignation for every field from every object field or property.
  • Insert into table_name VALUES(JsonDeserialize.Data.Value1, JsonDeserialize.Data.Value2, etc..)
  • No joins and no filters but now the bell rang on my head and I thought about sorting the GetPolicies in order to get the latest item ( the one that I have just added) once I set Max Record to 1. In case it's not clear yet, this service will return the object that is about to be added so yes, same object but with the internal ID that Outsystems database will set to it.
  • That's what I thought when I read your previous point, it would be so much better if the aggregate returns only one value.



Can you believe this? I made myself realized some lacks of performance from just reading your question though I still want to read your opinion.


Feel free to ask again if what I've replied is not clear enough.


Hi,

Outsystems allows you to consume RESTful services which automatically convert your JSON to an Outsystems structure, eliminating the need to call the Deserialize action. Check Consume REST API.

You seem to be inserting one Record in the Database using an Advanced Query and then using an Aggregate to fetch the Record you inserted. 

You should have an Outsystems Policy Record local variable where you will assign the DeserializeJSON output values (Outsystems allows you to map a structure to an entity) and then use an Outsystems CreateOrUpdate action to create the Record in your Outsystems database. 

This CreateOrUpdate action will return the Outsystem internal identifier for the created record, eliminating the need for you to do an aggregate to get the record you just inserted. This way, by using the CreateOrUpdate action, you will not need to have a local variable for each JSON field (I now understand why you wanted to find a better solution, that was not very efficient).

Performance-wise returning every Policy (by using no filter or joins) in the Outsystems database just to get the latest created policy is a very poor solution and this would degrade performance greatly if your have many Policy entries in your database.

Even though it is not necessary if you use a CreateOrUpdate action I recommended, you at least should have set MaxRecords to 1 in the aggregate to get only the latest policy. The best solution, in that case, would be to filter the aggregate by the Policy identifier from the object returned from the service. (policies should have policy numbers)

I hope my answer can help you develop a better solution.

Cheers,

João



To make things "easier" to read I'll be replying from paragraph to paragraph..


1.- Thank you for pointing that out, I did the changes required to get rid of the JSON Deserialize component and as you said, it works. As the JSON Deserialize component comes installed by default (no need to get it from Manage Dependencies) I thought my service will need it in order to work properly. Quite interesting that Outsystems does all the transformations needed just by setting the type of input you'll use.


2.- Indeed.


3.- Yes. An that's one thing from Outsystems that I find myself a little bit "sketchy". So if I'm not wrong, every time I have to work with new non primitive variable types, I must have a database/entity with all the fields that this new variable will have so that Outsystem will let me work with that "custom" variable data type. Am I right? Is there any other way to make Outsystems recognize my own data type ? The create or update is done by myself by hand with sql.


4.- Again that looks quite interesting but my service does not return the item identifier, instead it returns the whole item ( with all the fields, of course ) so if this CreateOrUpdate method has no way of returning the whole item/object is completely useless for me no matter how much optimized it is.


5.- I get what you mean but it's not up to me. "They" tell me to get it working like this, returning the item I have just added to the database, from the database in order to get the actual item with its internal ID. Just in case "they" tell me that we need a service that will return the ID of the current item added, I'll the solution your providing me so thank you so much.


6.- The way the aggregate shown on the screenshot before is set up now is DESC sorting from ID and Max Records to 1 just to make sure I return the latest item added. Correct me if I'm wrong but isn't it faster to sort all the rows upside down and get the first one rather than filtering? I mean, if "under the hood", Outsystems uses SQL and its IDs are indexed, I'd say that filtering by ID would actual fast but how is it compared to just DESC sorting and getting 1 record? If your knowledge about this is that deep, tell me how will this work out in a case with thousands of items in the database and right the oppositve, less than 50 items in the database.



Again, thank you for your well knowledged point of view. I really appreciate it.

Solution

Hi,

I will comment on your latest reply:

3 - If you need to save information in the database you will either need to create an Entity with the fields you need to store or have a normalized data model with different entities to represent your data. The reason you can't have non-primitive attributes in Entities is because Outsystems uses relational databases (i.e. SQL Server), this is standard practice. If you don't require information in the database you can manipulate structures that will remain in memory, this way you can use non-primitive types.

4 - Your service doesn't need to return the item identifier, in fact, the CreateOrUpdate action creates a new database record if you leave the Id null. It then returns the internal Id created in the database. It is not good practice to use Advanced Queries to insert one record in the database. At this point, using this SQL query to insert a record in the database is your main performance problem, as it forces you to use an aggregate to get the new record. Using the CreateOrUpdate action will only require you to assign the Id returned from it to the record you used to create the Policy in the database. This will be the same record you will get if you do a query afterwards.

5 - It is best if you filter by an attribute with an Index and use Max Records 1 than it is to sort desc with Max Records 1. Primary keys in Outsytems have Indexes by default (you can add your custom index to other attributes too). The more records in the database there are, the more computationally expensive the sorting solution is. For more information on this please check this link.

Cheers,

João

Solution

João Mateus wrote:

Hi,

I will comment on your latest reply:

3 - If you need to save information in the database you will either need to create an Entity with the fields you need to store or have a normalized data model with different entities to represent your data. The reason you can't have non-primitive attributes in Entities is because Outsystems uses relational databases (i.e. SQL Server), this is standard practice. If you don't require information in the database you can manipulate structures that will remain in memory, this way you can use non-primitive types.

4 - Your service doesn't need to return the item identifier, in fact, the CreateOrUpdate action creates a new database record if you leave the Id null. It then returns the internal Id created in the database. It is not good practice to use Advanced Queries to insert one record in the database. At this point, using this SQL query to insert a record in the database is your main performance problem, as it forces you to use an aggregate to get the new record. Using the CreateOrUpdate action will only require you to assign the Id returned from it to the record you used to create the Policy in the database. This will be the same record you will get if you do a query afterwards.

5 - It is best if you filter by an attribute with an Index and use Max Records 1 than it is to sort desc with Max Records 1. Primary keys in Outsytems have Indexes by default (you can add your custom index to other attributes too). The more records in the database there are, the more computationally expensive the sorting solution is. For more information on this please check this link.

Cheers,

João


Hi again,


3.- Gotcha, will note that for future needs.


4.-  Fixed! This is how I wanted it to be from the very start. That's why I opened this post, because I knew there could be other ways to get the same result more efficiently.

5.- Nice link, I really enjoy reading about how thinks work on its lower layer/level. As I expected, relying on Indexed items from the BBDD will way faster.

So to sum up:


-CreateOrUpdatePolicies: Gets the source from the input required on the service.

-Assign: Sets the output value (policy data type ) to the input value (policy as well). And finally, the policy.id gotten from the CreateOrUpdatePolicies is added to the output value so that it will look and be the same as if it was called from the database/aggregate.



Seriously, forgive me for being so stubborn about asking all the way about ways to optimize this solution. Just take yourself a look from what I started and what I ended up with. Postman will give me a response in less time than before so my point has been accomplished definitely.






Thank you again. This post made me learn so much in such a short period of time!

Jordi Gisbert Ponsoda wrote:

João Mateus wrote:

Hi,

I will comment on your latest reply:

3 - If you need to save information in the database you will either need to create an Entity with the fields you need to store or have a normalized data model with different entities to represent your data. The reason you can't have non-primitive attributes in Entities is because Outsystems uses relational databases (i.e. SQL Server), this is standard practice. If you don't require information in the database you can manipulate structures that will remain in memory, this way you can use non-primitive types.

4 - Your service doesn't need to return the item identifier, in fact, the CreateOrUpdate action creates a new database record if you leave the Id null. It then returns the internal Id created in the database. It is not good practice to use Advanced Queries to insert one record in the database. At this point, using this SQL query to insert a record in the database is your main performance problem, as it forces you to use an aggregate to get the new record. Using the CreateOrUpdate action will only require you to assign the Id returned from it to the record you used to create the Policy in the database. This will be the same record you will get if you do a query afterwards.

5 - It is best if you filter by an attribute with an Index and use Max Records 1 than it is to sort desc with Max Records 1. Primary keys in Outsytems have Indexes by default (you can add your custom index to other attributes too). The more records in the database there are, the more computationally expensive the sorting solution is. For more information on this please check this link.

Cheers,

João


Hi again,


3.- Gotcha, will note that for future needs.


4.-  Fixed! This is how I wanted it to be from the very start. That's why I opened this post, because I knew there could be other ways to get the same result more efficiently.

5.- Nice link, I really enjoy reading about how thinks work on its lower layer/level. As I expected, relying on Indexed items from the BBDD will way faster.

So to sum up:


-CreateOrUpdatePolicies: Gets the source from the input required on the service.

-Assign: Sets the output value (policy data type ) to the input value (policy as well). And finally, the policy.id gotten from the CreateOrUpdatePolicies is added to the output value so that it will look and be the same as if it was called from the database/aggregate.



Seriously, forgive me for being so stubborn about asking all the way about ways to optimize this solution. Just take yourself a look from what I started and what I ended up with. Postman will give me a response in less time than before so my point has been accomplished definitely.






Thank you again. This post made me learn so much in such a short period of time!

Hi,

Great, I am glad you got that solution and managed to improve the performance of your application. 

That looks very good. =)

Regards,

João