i have a orders in orders entity have Sales employeeID , Sales employee1 have multiple regions A,C,H and Sales employee2 have B,C,D,F in the slaes head have A,B,C,D,F,H. sales employee logged in that orders are visible its worked fine but sales head logged in that under regions sales employees related orders But not fetching correctly in that way i taken list with regions getting orders and after list distrinct it should take lengthy process and loading issue give me the shortest way in the outsystems reactive application
Hello SreenivasuluReddy,
I do not fully understand the business requirement, but based on the screenshot, the main thing I see that could have a significant impact on performance is the aggregate inside the loop. Every time that aggregate is reached, it triggers a server-side call, depending of course on how many iterations there are in the RegionList.
The filters themselves look normal to me, I do not think any violations there. What you might want to look into more closely is the aggregate inside the loop, GetOrdersByPlant2.
Hi,
It might also help to check whether you really need the LIKE operations, or if the values are always the same.Since LIKE doesn’t always use indexes and searches for partial matches, it can be slower. If the values are exact matches, it would be faster to use = instead, especially if you don’t need to search for text that merely contains the value.
Hi @SreenivasuluReddy Lingala ,
Because a region can be assigned to multiple employees, and an employee can belong to multiple regions, the correct approach is to model this as a many-to-many relationship. This can be achieved by introducing a third entity, EmployeeRegion, with the following attributes:
EmployeeId
RegionId
Then you might replace the loop and some of your filtering by using this entity.
Additionally, I strongly recommend adding indexes on the columns that are frequently used in filters and joins to improve performance. You can follow this guide: How to optimize your database search request with indexing in Outsystems
looking at how your filter conditions are written, i suspect we would have to start with looking at your datamodel, it seems that regions and plants are not modelled out as entities, or else i would have expected joins to Region and Plant entity instead of these weird LIKE conditions
@Sherif El-Habibi has pointed out the important issue that can badly impact your app performance.
The second issue is with the LIKE %% which will ignore all the indexes in your database
Recommendation: you can get the total list with appropriate condition like 1 and 4, then in the loop, filter more by using ListFilter on condition 2 and 3 .
Best solution: use IN list filter (no loops) Create a single list of all regions the logged-in user can access. Example: UserRegionList (text list)
Then use 1 single Aggregate/Data Action like this:
Order.Region in UserRegionListThis means• If order region exists inside UserRegionList → fetch it• No need for looping• No need for ListAppend• No need to run GetOrdersByPlant2 multiple times• No need ListDistinct
We can see there is direct impact on performance due to the aggregate inside the loop.
first and immediate solution to have direct sql block where based on condition we can return OrdersbyPlant.
Another thing we can try out to further performance boost we can create many-many relationship in employee and region table followed by use that table in query.
Thanks
Gaurav