Forum OpenACS Development: Permissions Performance
4.5, but I just wanted to reiterate that developers should try to
avoid using acs_permissions__permission_p in a WHERE clause of a
query. I did a first pass performance optimization on my client's
site and by removing acs_permissions__permission_p from several WHERE
clauses, I got a 3-7x improvement on page loads! I was able to
modify the permissions check so that I called
acs_permissions__permission_p on a significantly reduced set of rows,
but that was due to how I setup the permissions rules for this site.
I'd like to use this thread to collect ideas/code fragments/examples
of how to work around using acs_permissions__permission_p in a WHERE
clause when you absolutely need to do a permissions check in a query.
I'll start by explaining how I setup my permissions checking for my
client's site. The query that was killing the server performance was
a query which grabbed the most recent forum messages. Since the site
has several hidden/private members only forums, I had to do
permission checks each time I wanted to display the most recent
messages. A normal user should not be able to see latest messages in
an administrative forum, etc.
The way I implemented private forums was to set the securty_inherit_p
flag to false for private forums. Then I would add members to that
forum by granting read permissions (a child privilege of read) on
that forum. Messages are tied to a forum by a forum message map
table. Therefore, to see if a user could read a message, I needed to
grab the forum_id and check to see if a user had permissions to view
My first query used acs_permissions__permission_p in a WHERE clause.
The performance was very poor because it ran that expensive function
for each row. :) To speed up the query, I created a plsql function
which checked whether or not the security_inherit_p flag was set to
true, if it was, then I knew it was a public forum and this user
could view it. If not, the function ran the
acs_permissions__permission_p function to check permissions, and
returned the appropriate response. As I mentioned, the speedup was
Please feel free to comment on my solution (I'm sure there are better
ways to solve the problem) and provide examples of how you solved the
permissions checking problem. Thanks.
It's really great to have a general way to get permissions on any object. It sucks you can't do it in a where clause. It's unfortunate that the more objects you have the slower it gets.
Some thoughts on how to fix this.
acs_permssion_p should work on any object but not all objects need to store permission information. If acs_object_types where objects you could set permissions on object types. This would allow grouping in the same way the context_id allows grouping. For example create a private_bboard object and make its supertype bboard. Then give a group read access to private_bboard and presto, 1 object in the permission table controls a whole group of objects. Same for attributes, give admin rights to make_live attribute and only admins can change it.
I also think context_id and inherit should be in a different table than acs_objects. If you want to set explicit permissions on the object put a row in the table. Permissions then become like friends in C++. Perhaps add a flag to objects that says if the creator has admin rights. While all this is kinda kludgy it preserves the permission_p model with less object_id in the tables. I think the permission model is scaleable to around 1 million objects on a dual processor box. Depending on how you look at it that's either a lot or hardly any. If every bboard post need permissions you've got a problem. If every forum need permissions that's not so bad.
They key, though, is to just avoid calling ad_perrmissions__permission_p in a where clause.
There's a bit of a struggle here in that the idea behind this proc is to abstract the check from the datamodel. I don't really like the idea of spawning dummy types and having permissions work on them, as then we'd have to worry about the scalability of the acs_object_types structure which itself is subjected to plenty of hierarchical queries.
But in general joining against the "all_object_party_permissions_map" directly and checking for permissions against the view is *much* faster if a lot of rows are involved. Use the "all" not the "acs_object_party_permission_map" though, as the latter's use of UNION will result in *very* slow queries, too.
In Oracle in particular the optimizer seems to like running permission_p on all rows before using join qualifiers to throw most of them away. Ugh. One way around this is to use a subselect to throw away the rows then use permission_p to check against those that survive the subselect.
Thanks. Did you mean "all_object_party_privilege_map"? That looks like the right view to me.
This might be known to others, but i noticed that one of the queries where i had to use ad_require_permission in a where clause got much faster when i reordered the different parts of the where clause.
I changed the where clause from
WHERE acs_permission__permission_p(lr_object_id,:user_id,'admin')='t' and lr_objects.restype='excs'
WHERE lr_objects.restype='excs' and acs_permission__permission_p(lr_object_id,:user_id,'admin')='t'
and the query got much faster (did use the function only on the smaller set where restype=excs).
Which database are you using? Does it make a difference if it Postgresql or Oracle?
You can always force it to call ad_permission_p on the smaller set by using a subselect ...
PG does join re-ordering but apparently not expression re-ordering, interesting ...
Still ... we shouldn't really depend on this. It would be too easy for someone to decide to change things around if they're tweaking the query for other reasons, and unknowlingly put it back into its really slow form.