Forum OpenACS Development: Response to ACS 4.x won't scale (I hope I am wrong)

That's a pretty small amount of data. Things might be different with more.

Hm, well, the trace for you're Query 2 has a "MERGE JOIN CARTESIAN" (167707 rows) in it. That sure seems suspicious... I just tried a similar query:

select decode( count(*), 0,'f', 't')
from dual where exists (
    select 'x'
      from acs_object_grantee_priv_map ogpm,
           rel_seg_approved_member_map rsmm
     where object_id = 5204
       and rsmm.member_id = 3305
       and privilege = 'admin'
       and ogpm.grantee_id = rsmm.segment_id
);

on my patched ACS 4.2 Dev site, and I did not get a cartesian join. When I run tkprof on the trace file, I see only 4 query rows, vs. 335512 rows on your system.

Now, I have even less data in my Dev system then you do, I don't have BBoard installed, and my users, groups, and permissions are different from yours, so none of this is conclusive. That merge join you're getting has got to be symptomatic of some problem, though... But whether it's something weird on your site, or a problem common to OpenACS, I don't know.

You're sure your tables are analyzed? If not, try:

begin dbms_stats.gather_schema_stats (:db_user, :percent, cascade => true); end;

(A percent of 20 or so should be good enough.)

Also, what sort of performance difference are you seeing, anyway, time-wise?