The permissions system has several serious problems as has been pointed out in
this thread.
I worked on tuning the permissions system when it was first released. The
system (after some tuning) performed very well for the common cases, i.e. "Can
user X perform action Y?" and was also able to answer other questions with very
reasonable performance.
Since then, more functionality was added to the core, causing the permissions
system to have some of these problems. This is our fault because we did not
perform any more tuning and performance testing on the system after the
changes.
I think both Finn and Henry have some good ideas for optimizations that can be
performed right away to speed the system up.
First I would build all views on top of base tables instead of other views. Oracle
will not optimize through views (I don't know about other databases). Once the
views are rebuilt, I would tune each of them individually.
One thing I see often is people using the acs_permission.permission_p in where
clauses. Oracle does not optimize this either (I also don't know about other
databases). This is very bad; if you must have this in a where clause wrap the
rest of the query in a view on the fly with this call on the outside (to minimize
the number of rows you have to execute this on).
Henry is right, the permission system is not light weight. Developers should use
it at a coarser level, e.g. permission forums not individual messages, etc.
I have seen the permission system, in an earlier stage, perform exceptionally,
with some work it can again.
