One thing we've done is implemented an nsv_shared permissions cache, which is flushed on certain actions. It's not particularly smart, and it's not particularly fancy, but it's sped up a number of queries remarkably well.
The syntax changes from
db_multirow query query_with_perms {
select o1.object_id, o2.name from acs_objects o1, acs_objects o2
where o1.blah = o2.blah
and acs_permission.permission_p(:user_id,o1.object_id,'read') = 't'
}
to
db_multirow .. .. {
select o1.object_id, o2.name from acs_objects o1, acs_objects o2
where o1.blah = o2.blah
} {
if {![yb_object_permission_p $object_id "read"]} {
continue
}
Which looks, oh, so ugly, but turns out to be much faster. This seems to be due to the fact that in the first case, oracle will do full table scans on any query with a pl/sql call in the where clause. The second case, the optimizer uses the where clause intelligently, because we've taken away the confusing bit.
The method also relies on an undocumented feature of the db_multirow proc, which will execute the string after the query, a-la db_foreach.
