Hi,I am having performance issue with using aggregate which contains multiple long text field, each max can contains 10K character.The aggregate is bind to a table and and used for export to excel too.The requirement is the the long text field doesn't require to show in table UI, but need to be exported to excel.I notice that although I don't configure to display these long text field in table, but when we load the page, it is very slow, and found that the root cause is due to the number of character in the text field, the more character it has, will be slower. I tried run the sql in sql server directly, and it return fast, so not sql query issue.May I know is this the expected result with long text field?Now the workaround I have is create another aggregate purely for export to excel instead of share the same aggregate with table. Is there any other better way to solve this problem?
To show data in table if not required to show while text field data then use substr() to show only required number of character so it will work for table .also for export you can use second aggregate with on demand property so it will get hitted only when clicked on export button.
thanks
Yogesh
I would decouple the excel export from that aggregate. Then the form won't fetch those fields.
If you want to export you can use another aggregate with the same filters etc and export that to Excel.
So this is the expected result when using aggregate?I thought since we are not binding the long text field to table, it shouldn't affect the page rendering speed, but seems like as long as it is in aggregate, it will impact to the speed.Looks like i have no choice but to have two aggregate, one for table, one for export then.
Yogesh Javir, Stefano Valente ,
Thanks for the feedback.
But if i understand correct you do use the long text fields?
When your logic needs those fields in export, how can OS not put them in the aggregate output?
Hi Wei Lun Wong,
I believe the issue you are facing is because what you're doing is against best practices suggested by OutSystems. Please check the link: https://success.outsystems.com/Documentation/11/Managing_the_Applications_Lifecycle/Manage_technical_debt/Code_Patterns/Best_practices/Don't_abuse_scope_information
Also check that everytime you create a field which is above 2000 chars, OutSystems converts that field into a binary data types as you can also check here: https://success.outsystems.com/Documentation/Best_Practices/Performance_and_Monitoring/Performance_Best_Practices_-_Data_model#Isolate_large_text_and_binary_data
So, I believe the issue you're facing would be quite usual based on the description you gave about the problem. The solution passes by isolating the fields into a table dedicated to this kind of storage (Binary Contents or Large Texts). Notice that also doing 2 queries, one on preparation and another on the export file is also fine like Yogesh Javir suggested. If you need to show the information on the table you can use substr() function to only display X chars.
I hope this helps. Best regards!