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?

Collapse
Posted by Janine Ohmer on
cc_users is very expensive on the acs-admin/users page too, because it does a count(*) of registered users (I think, I'm not looking at the code at the moment) and it takes quite a while once you get past 10,000 or so users.

If it doesn't kill performance too much maybe you could include the minimum needed for that page as well?  Then it truly could be called cc_users_slim, or maybe cc_users_lite.

Collapse
Posted by Tilmann Singer on
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?

Collapse
Posted by Dirk Gomez on
Views are nice for abstracting information - in fact they are the Oracle-recommended way of "information encapsulation". We still very much rely on PL/SQL functions for doing so which usually gives us performance grieving...

The danger with views - evidenced frequently enough in OpenACS - that they retrieve more information than needed in the "master query".

No PL/SQL functions that perform banal operations :)

Collapse
Posted by Dave Bauer on
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.

Collapse
Posted by Dave Bauer on
Dirk,

Did we decide? Bugtracker needs this for openacs.org. Can we call it all_users?

When we are listing users already assigned, its safe to use all_users without checking for registered users etc. Mainly we are looking for Name and Email.

Collapse
Posted by Dave Bauer on
I checked in to HEAD all_users. Have fun!
Collapse
Posted by Dirk Gomez on
Yeah fine enough name :) I will close the ticket.