I had a query almost like
select site_node.url(node_id) as url, acs_object.name(object_id) as name
from site_nodes
where parent_id = site_node.node_id('/')
and object_id is not null
and acs_permission.permission_p(
object_id,
acs.magic_object_id('the_public'),
'read') = 't'
in my system until a few weeks ago and it became to slow to use. First of all site_node.url and acs_object.name are just as slow if not slower than permission_p. Also calling permission_p in the where clause (at least with oracle) is asking for trouble.
I've got more than 5000 site nodes so I created a seperate table to hold the url and name and ended up with something like
select url, name, acs_permission.permission_p(object_id,:user_id,'read) perm
from site_nodes n, site_nodesextra e
where n.node_id = e.extra_id
and paerent_id = :slash_id
You can maintain the extra stuff with a trigger. I stuffed the whole mess in a function and only return rows where perm is true. I also memoized the result for 5 minutes. In your case you could also add the public permission.
The last time I looked at the permission docs I think they say the permission system is not designed to retreive permissions on large row sets. Hopefully it still says this because it's true.
This is not as bad as it sounds and it might be helpful if the docs suggested some alternitives. If you need to group things make a package, put the object_id of the package in the context_id and look for that. I think the docs say don't do this but it works really well. You can also check for direct permssions which is really fast but not as flexible.
I've been running a production system for a couple of years now. We put about .5 gig of data into it every day and everything is an object. It's better to make everything an object and deal with whatever permission problems arrise, than to deal with inconsistancies caused by not making things objects.
