to focus more on AR SQL generation, I have created this spin off of We want to start, but our Smart Reporting is very slow ((goes more in the direction of missing caching).
We want to use Smart Reporting in production environment, but actually the performance is too bad and it means "show stopper" for us. We urgently need your support as AR experts.
- dedicated SmartReporting server with 4 cpu and 12GB
- dedicated AR Reporting Server with 4CPU and 16GB
- Tomcat currently can use 6GB
- 76k incident tickets
- Test case: OOB Incident Dashboard
Opening the Smart Reporting OOB "Incident Dashboard" takes a very long time for non "AR Administrator" users, or is not possible at all.
Every diagram takes up to 60+ sec, every time you open the dashboard. This is not fun for any user and cannot be used in this way.
Sample user and his authorization groups
User has some modul permissions, one support group, 2 companies and 2 functional groups (I think a normal use case )
We can't stress our live environment, so here "only" the values from our QS environment with a few fewer tickets. But the problem still becomes very clear, I think.
With each ticket and each additional module permission, support group, functional role it gets worse and 70k tickets aren't really much now, are they?
- New RLS enabled
SQL Log shows runtimes for each diagram of the dashboard of more than 30 seconds with parameter "Disable-New-RLS-Implementation: F"
- New RLS disabled
- Checking the user permission in field 112 takes a very long time, if you remove this field from the permissions, you get SQL runtimes of about 1 second.
- A descending sort order of the "OR (J0.C112 LIKE '%;-20032;%') OR (J0.C112 LIKE '%;-20003;%')" WHERE Clause generates SQL runtimes of 2.2 seconds (Five times faster!!!) because the support groups/companies are in the permission fields and not the module or role permissions of the users.
OOB sort order is module or role permissions (first), support groups/companies (last)
- Unfortunately the sort order in the AR, but should!? And now?
- How did you get the performance under control or do the users have no module permissions, support groups etc.?