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

Posted by Andrew Piskorski on
Jon, that sounds like good work. Also, there was a conversation on web/db aseveral months back which concluded that "select count(*) from dual where exists (select 1 from ... )" should always be at least as fast as "select decode(count(*),0,0,1) from ...", not to mention more portable, so you probably want to replace that too.

FYI, here's what my acs_permission.permission_p (Oracle) looks like right now. As you can see, I have not ditched the acs_object_party_privilege_map view yet as you have, despite Michael B.'s comment in the source suggesting it...

  function permission_p (
    object_id	 acs_objects.object_id%TYPE,
    party_id	 parties.party_id%TYPE,
    privilege	 acs_privileges.privilege%TYPE
  ) return char
    exists_p char(1);
    -- We should question whether we really want to use the
    -- acs_object_party_privilege_map since it unions the
    -- difference queries. UNION ALL would be more efficient.
    -- Also, we may want to test replacing the decode with
    --  select count(*) from dual where exists ...
    -- 1/12/2001, mbryzek

    -- I've gone ahead and replaced it the "select decode(count(*),0,0,1)
    -- from ..." with the superior "select count(*) from dual where exists
    -- (select 1 from ... )".  However, mbryzek's comments re. union
    -- vs. union all, above, are still quite cogent...
    --, 2001/04/29 05:29 EDT

    select decode(count(*), 0,'f', 't') into exists_p
    from dual where exists (select 1
      from acs_object_party_privilege_map
     where object_id = permission_p.object_id
       and party_id = permission_p.party_id
       and privilege = permission_p.privilege
    return exists_p;