We intend to increase the security of our application by using xpaths on access rules, however encounter serious performance issues during testing. When analyzing the SQL queries (set log node ConnectionBus_Retrieve to Trace) generated by the mendix application it turns out that huge amounts of SQL are generated due to the fact that we use generalizations/specializations extensively (in some cases we have up to 4 levels with e.g level 2 containing 30 objects). Each specialization requires a comparable access rule xpath (very basic xpath) and it seems like Mendix checks all 30 access rules when opening a form that only contains 1 specific specialization. In combination with the fact that some users have multiple roles assigned (and as such qualify for multiple access rules) this results in a dramatically decreased performance of the application. Anyone any recommendations on how to handle this issue (of course we would like to prevent having to remove specializations since we are in a live environment for quite some time now)? EDIT: More testing shows that (at least in 1 case) the main performance seems to be caused by the sort requested in the form that is displaying the data. The ORDER BY is 95% of the SQL generated, it contains a subquery for each specialization involved applying the full access rule xpath every single time. Removing the sort from the form dramatically increases the performance (from 10-14 sec opening a form of 15 records (of 17000) to 2-3 seconds).
Each specialization level adds 2 joins to the query plus the access rules. You can choose for some redundancy instead of specializations, but that is a difficult migration for a big existing application. You 'll first leave all specialization in the system, upgrade/move the data and in the next update remove the specializations. I guess it is no option in your case.
If the access rule is a basic XPath you can not optimize there. I have seen access rules over 4+ assocations which should be avoided if possible if you care about performance.
Only workaround I can think about is a 'net' securitystring or code which is used in access rules.
Chris de Gelder
Have you Xpath constraints with OR statements? these will increase the load time of the query. Try to avoid these.