Forum OpenACS Development: cc_users considered harmful (sometimes at least)

I'm currently working on an OpenACS system with about 100.000 registered users.

The module which caused most gried wrt to cc_users was bug-tracker. It shows the following submitter information: first name, last name, email. To do this it accesses cc_users - which is incredibly expensive, because it joins a lot more tables than needed for these three attributes. But it seems that there is no "obvious way" to retrieve first_name, last_name, email altogether.

I've created this view - and I propose to include it into OpenACS core:

create or replace view cc_users_slim
as
select pa.*, pe.*, u.*
from  parties pa, persons pe, users u
where  pa.party_id = pe.person_id
and pe.person_id = u.user_id;

Using cc_users_slim makes the bugtracker bugs query on index.tcl around 500 times faster for this system.

The name cc_users_slim is a slight misnomer, because cc_users only shows registered_users. Maybe the view should be called people_with_emails?

Why do we need a view for this at all? Why not encourage people to join against the persons table if they need first_names, last_name, and additionally against parties in case email is needed too? For neither of those attributes the users table is necessary, since the user_id is recorded in the applications table (e.g. the bug assignee), so it is already assured that the party is a user.

Wouldn't that be a little more efficient in most cases, without much overhead (e.g. writing a join against persons is as easy as writing a join against cc_users_lite).

As a side note - usage of person.name in queries that return more than one row should be strongly discouraged for efficiency reasons, right?

See this thread also: https://openacs.org/forums/message-view?message_id=66660 referring to dir_all_users which is a package-specific version of your view.

Using a view that retrieves basic info for all users regarless of status is the right thing to do when showing a list of objects with their owners.

Generally on any system a list of registered users will be too long to display, see the original permissions page for a wonderfully bad example.

Besides that, we probably want to make the type of operations that use cc_users to display a list of users to be restricted to a subsite to make them more reusable.

I checked in to HEAD all_users. Have fun!