Hello,
I am developing a new system in OutSystems, and I need to receive data from an external source via an API. The number of records I need to import into the system is approximately 600 records, each containing additional data (such as arrays of other records).
Additionally, I will be performing queries on the data received from the API within the OutSystems platform, so I need to know if OutSystems can handle a JSON of this size and number of records without affecting performance, especially when it comes to running additional queries on that data.
Is there a recommendation to receive the data in a different format, such as using temporary tables within OutSystems, instead of using JSON? Could this improve performance when dealing with large data volumes like in my case?
I would appreciate your insights and recommendations on this matter.
Thank you in advance,
Hi @hava sh,
For best practices on temporary storage and JSON handling, refer below post link -
By storing JSON data in database entities, you gain better control over performance while supporting complex queries.
Thanks,
Vipin Yadav
Those are just links to other posts, without much information. Try to answer forum posts with your own information or with links to official documentation, and only refer to other posts if they actually give a good solution. Thanks.
@Kilian Hekhuis if you see the json post they have mentioned details how you can manage that.
Hi Hava,
When the Platform receives JSON from an API, it is converted to a record or list. When you have a Reactive application, the JSON is first received server-side, then client-side. I'm not aware of the specific conversions the Platform does here, so whether it first converts the JSON server-side, then converts it back to JSON for client-side. I expect it does this, as it should send only the data to the client-side that you are actually using, for performance reasons.
Note that 600 records is really not that much, so you shouldn't experience any performance problems. If you were talking 6000 or even 60,000 records, that could slow down things (especially when sending all of this data to the client).
Note that in OutSystems, you cannot perform queries on data in a List. You can use actions like ListFilter, but those are O(n), as they perform a linear search, so you should try to avoid it on larger lists (though with 600 records you're probably fine). Also note that there are no "temporary tables" in OutSystems. Though if you need to perform joins on the received data, you probably want to store the data in an Entity (though this is of course pretty slow if you want to call the API often).
So it all depends on your use case what the best options are.
Is there a way to perform a direct JOIN between the data received in JSON format and existing tables in OutSystems?If yes, what is the most efficient way to achieve this?
If this is not possible:If a direct JOIN is not possible, I plan to change the format received from the API to another format and load the data into an Entity in OutSystems.
I would appreciate any guidance or insights to help me choose the right solution.
Thank you very much!
Hi hava,
Hi, @hava sh
Is there a way to perform a direct JOIN between the data received in JSON format and existing tables in OutSystems?
Step:
1. Send Json text data into SQL.2.Deserialize Json in SQL and name each column.3.After you can easy join with Entity available in outsystems.4. you can perform insert or update query
Example of SQL
with APITable AS (
SELECT Id, FirstName, LastName, MobileNumber, Email, Address
FROM OPENJSON(REPLACE(@Json, '/', ''))
WITH (
Id INT '$.Id', FirstName NVARCHAR(50) '$.FirstName', LastName NVARCHAR(50) '$.LastName', MobileNumber NVARCHAR(15) '$.MobileNumber', Email NVARCHAR(100) '$.Email', Address NVARCHAR(255) '$.Address'
) )
Select APITable.FirstName, APITable.lastName, {user}.[username]
from {User}
Inner join APITable on {User}.[email] = APITable.Email
Based on the json the SQL query Changes when deserializing
HI, @hava sh
Previously, I worked with JSON data containing over 50,000 records. To process this efficiently, I split the JSON into smaller batches of 1,000 records each. I deserialized each batch in SQL, applied the necessary conditions, and inserted the records into the database.
To manage the process, I stored the JSON text in an entity along with the total record count and number of inserted records. When the inserted record count matched the total, I stopped the timer.
Note: SQL can handle the insertion of only up to 1,000 records at a time.
Before starting this process, I executed the complete code within a timer. However, since your case involves only 600 records, you can directly run it using a Server or Service action. Just ensure you increase the server timeout.
In what scenario you what to handle the json text?
Hi Nani,
Thank you for the detailed explanation. I wanted to clarify:
Did you run the SQL query you provided (using OPENJSON and performing the JOIN) outside of OutSystems, directly in the SQL Server? Or did you manage to run this query within OutSystems using an Advanced Query or another approach?
I’d appreciate your clarification on this!
The Completed code like Deserialize Json , joining and inserting was executed inside the Outsystems SQL no Third-party server.
If you go to the server side action you can find the SQL node.
Please check pervious reply i have even given the SQL example how to hadle the json text in sql.
Hi,
First of all, thank you so much for the detailed explanations and assistance! The solutions you suggested really helped me understand the different approaches, and I greatly appreciate it.
I have a follow-up question – if I want to use the solution Nani suggested, is there any downside to the data being temporary?I need the data to be available over time and across different parts of the system. Do you think this could affect performance or the ability to display user-specific data?
Additionally, if I proceed with Nani's solution, is there a way to store the JSON in a user-specific manner?For example, would it be advisable to store the JSON in a temporary variable at the Session/User level, or is there another recommended way to store it to ensure each user only sees records relevant to them?
I would love to hear how you approach this and what solution you think works best in OutSystems.
Thank you again for your time and help!
Best regards,
I have a few questions that will help refine the solution:
1. Is the JSON data from the API consistent every time or does it structure Changes?2. JSON data from API is single user's data or multiple users' data?
Using session variables is not an ideal approach for this case, especially when dealing with large data volumes, as they are not designed to handle such scenarios efficiently.
Here’s my recommended approach
1.Create a entity in outsystems with user's specific By UserId.
2.Make sure structure of the entity matches with JSON structure . So, you can directly map the data into the entity when storing.
3.Deserialize the JSON and storing in entity makes to easy to store and retrieve the data and no extra processing is required beyond fetching the data.
4.If you store the data in its original JSON format, you can still query it using SQL with filters to extract user-specific information. However, this approach requires processing the JSON every time you need the data, which could impact performance.
Storing the data in a structured entity is generally the more efficient approach, as it ensures the data is readily available, minimizes processing overhead, and supports better query performance.
I hope this helps!
Thank you,Nani