Affects Version/s: 6.2.10 EE GA1, 7.0.0 M5
Component/s: Core Infrastructure > Permissions
ServiceBuilder filterFindBy queries are not performant:
Steps to reproduce:
- Generate 1000 sites.
- Clone and deploy this portlet: https://github.com/sammso/tester-liferay-permission
- Create page "Bar" and add Bar portlet to page.
- Generate teams using recently deployed portlet going to Control Panel -> Apps -> Generate. Click in "generate-teams".
- Create one userx, link it to "Liferay" site (or the one on which these tests are running), then click in "Site Teams" and assign as a member of any of the generated teams (Team A---Team D).
- IMPORTANT: This step need some free HDD memory (maybe 1GB) and requires a lot of time to execute (maybe 2-3 hours). Generate bar entities with generate portlet that you can find from control panel (Control Panel -> Apps -> Generate. Click in "generate-bars").
- When previous content creation has finished, execute attached get_performance.groovy from Control Panel -> Server Administration -> Script, changing userId by _User.userId field value from previously created "userx". Change groupId to the site groupId too.
Before the fix:
After the fix:
The performance has increased a lot.
The inline resource permissions JOIN before the fix was using an INNER SELECT like this:
Having this inner SELECT caused the query to be far from optimal as this MySQL Query Execution plan shows:
After the fix, the INNER JOIN looks like this:
Note: ResourceBlock permissions are not suffering performance degration