Query too slow, when using request endpoint

[marcoAntonioNina]

Issue & Reproduction Steps

The query could be found when using the '/requests' endpoint, and the user is a non-admin user and also has selfservice assignments.

It could be found that several subqueries are performed to verify the user's data, such as:

• the user has participated
• the user has selfServiceTask (users and groups)
• the user can edit (EDIT_DATA)

This query is time-consuming the more information is in the process_request_tokens table.

Solution

• The change is to remove the subqueries and execute them in different queries than the original.

How to Test

It is a difficult environment to test, you need to have a large database and a user who presents the slowness problem. (preferably a client database) Perform searches in the requests.

Related Tickets & Packages

• FOUR-18182 ci:next

Code Review Checklist

• [ ] I have pulled this code locally and tested it on my instance, along with any associated packages.
• [ ] This code adheres to ProcessMaker Coding Guidelines.
• [ ] This code includes a unit test or an E2E test that tests its functionality, or is covered by an existing test.
• [ ] This solution fixes the bug reported in the original ticket.
• [ ] This solution does not alter the expected output of a component in a way that would break existing Processes.
• [ ] This solution does not implement any breaking changes that would invalidate documentation or cause existing Processes to fail.
• [ ] This solution has been tested with enterprise packages that rely on its functionality and does not introduce bugs in those packages.
• [ ] This code does not duplicate functionality that already exists in the framework or in ProcessMaker.
• [ ] This ticket conforms to the PRD associated with this part of ProcessMaker.

[processmaker-sonarqube[bot]]

Quality Gate passed

Kudos, no new issues were introduced!

0 New issues
0 Security Hotspots
100.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarQube

[boliviacoca]

From Mireya Caro DBA: Thank you for your attention. The solution has been tested. Please implement it to improve the slow query