Forum OpenACS Q&A: universal way of displaying lists of objects needed

Currently i'm working on a categorization package which packages can use to let users categorize their objects. It will contain an object directory that displays all objects of a selected category. To generate such an object list i need a FAST way (forget about acs_object.name - it's just way too slow) to get the object_name and hopefully also object_overview (possibly with object_overview_html_p). Although Dirk Gomez' proposal didn't get accepted, unfortunately i have to get this into the discussion once more because not all packages do use the CR and for some objects using the CR would just not be necessary (site_nodes, apm_packages etc). Therefore, i will now use the following datamodel for the categorization package and patch all necessary packages to make use of that table.

create table acs_objects_description (
object_id integer not null constraint acs_objects_desc_pk primary key constraint acs_objects_desc_object_id_fk references acs_objects(object_id),
shortname varchar2(200),
overview varchar2(4000),
overview_html_p char(1) default 'f' constraint acs_objects_desc_overview_h_ck check (overview_html_p in ('t','f')),
datastore char(1) default 'a' not null,
package_id integer,
state char(1) default 'l' not null constraint acs_objects_desc_state_ck check (state in ('l', 'd', 'a'))
);

• Do you have a spec for the categorization package that you can share? How will it differ from the one you and I wrote in Munich for aD? For reference, the old categorization package can be found this thread.
• Which object types do you plan to be browsing?
• Would it be feasible to convert the object types that you need to using the CR?
• Is it established that acs_object.name is too slow? Can you try the new categorization package and alternate between using a name column and the plsql proc and see what the difference is in response time?

Yeah... I'm back! To be honest, i don't really remember the old categorization system too well. Can you provide a link to a demo system where i can have quick look? The links you provided in your old posting aren't working any longer....

To answer your question regarding acs_object.name: Yes, i am sure that acs_object.name just has to be slow if used displaying a list of objects. Why? Well, if you think about it, it just has to be slow to have to invoke this proc for every single object whose name you want to display in an object list, because this proc itself will execute a few queries to get the correct name. If you compare this with an approach in which you just have one single query to get the objects together with their names out of a table then you don't need to set up a test system to compare speed because it's evident that the second approach has to be way way faster than the first one. In my opinion query speed is the topmost design-criteria overruling every other criteria possible, so therefore i see it as established that acs_object.name could be used to get the name of one or some few objects, but for displaying an object list my proposal for a table like acs_objects_description is the better since faster solution.

In my opinion query speed is the topmost design-criteria overruling every other criteria possible

I've measured acs_object.name() and on my P500 it runs about 3 milliseconds in both Oracle and Postgres, not terribly slow but not terribly fast, either. On a modern system I'd expect performance to be closer to a millisecond.

Denormalizing by carrying the name in acs_objects isn't that big a deal, but ... it does involve munging code to make sure it's maintained correctly. At minimum anyone doing this who expects to find their work accepted into our mainstream OpenACS core is going to have to address the fact that content items already contain a name field, and provide upgrade scripts to migrate that to acs_object as well as fix all our standard packages that use the CR. That would not be a lot of work but it is necessary work.

The name column was the one proposed extension to acs_objects that I felt had some merit, others just seemed to widen objects in ways better handled by keeping content in the already-wide content repository. Denormalizing content URLs for instance makes sense but I'd rather see that stored in content item (in part to heavily encourage folks to put their content there instead of ignoring the CR's existence)

Regarding the speed of acs_object.name(): It is nice that it runs fast on your machine, but i don't know many million objects your system is holding and how many concurrent page requests your system does have.... Under load a system has to serve every page as fast as possible and i think i made it pretty clear that the invocation of pl/sql procs for every object to be displayed in a possible long object list (yes, normally a long list should be split on several pages, but still...) just has to be way slower than my proposed approach. That doesn't mean that acs_object.name() should not even be used to get the names of a limited number of objects. I'm just saying that this proc is one of the ad inventions that seemed nice on first sight, but if you really think about it and really try to use it in a huge system then you realize how flawed that approach is.

Regarding migration: Yes, i will make sure that migration scripts for the major packages will be written (anyone here for help?).

If you already agree that adding a name column to acs_objects (or a second table as i suggested to not blow up acs_objects unnecessarily since we don't need these infos for every single acs_object) makes some sense, then why not also adding at least package_id if not state, overview and overview_html_p?

Are those mailing list objects are like postings of this forum?  If yes then I guess it would be ok to put them into CR.  So they can also be searchable.  I am not 100% sure but I think this forums is also a CR app.  I think the bboard package is also a CR app, also not sure (a bit out of touch of OACS).

Since you are also looking at using package_id for your templating you may want to look at CR capability of relating a template on an item level.  Also the templating of CR does enable you to relate many templates on a single item.  You can use different context (not context_id) for each item.

Also I think the size of the table rowise is not much an issue for rdbms.  As long as things are properly queried and indexed.  This is what I have picked up here in OACS, of course I am not a db guru.

On the other hand CCM I think adopted the use of acs_objects.name column to display the name of the object, not sure also.

Anyway I am not saying that your proposal is not good, I am just offering some thoughts.  I can not decide on its merits as I am trying to keep up with OACS.  I think Don and others can give better thougths... Long day for me, I better sleep.

I'm personally not afraid at all to maintain the naming information in cr_items and my extra table acs_objects_description, because we could use a trigger to copy the data from cr_items to the other table. Ok, it would mean redundant information but it would speed up queries in the CR, i assume (selects are by far more common than inserts/updates, so i think it's more important to focus on fast select queries). The bottom line to this would be: It's not important at all whether a package stores a copy of the object names in their own tables as long as it makes sure that the acs_objects_description table is always uptodate, although i totally agree with you in saying that the datamodel should be normalized if and where possible, but in the end that shouldn't lead to expensive queries if avoidable.

Attempts to cut down the size of CR tables are always good, i think!

PS: I want to clarify that the mailing list objects mentioned above are just objects each describing a mailing list, i didn't mean actual mailing list postings.

PPS: I thought about mime-types, but i thought that when displaying an object in a list of objects the object overview could only be either text or html, but if other scenarios come to your mind, then i would also opt for a mime_type column.

here's the PROOF that acs_objects.name (and get_url even more so) is expensive:

https://openacs.org/forums/message-view?message_id=75566