Forum OpenACS Development: Re: OpenACS Attribute Management - Requirements/Requests

Frank,

Thanks for the input. The issues that you raise are important and we have been careful to consider them.

We agree that there are always scaleability issues with the introduction of a join to a table that has lots of rows. There are however ways to implement this kind of attribute storage that avoids a join and the resulting performance hit.

One of the most performance hungry examples of using self-joined tables is storage of any hierarchy. A hierarchy trace 'on the fly' can be terribly slow. But even so, in a situation where queries are frequent but storage is not a problem and updates are infrequent, it becomes workable to use triggers to generate intermediate tables that store all descendents of each object for quick retrieval. I believe that the openacs permissions system uses something akin to this method and this works well.

In AMS there will be no hierarchy stored, and the selection of the attributes can be done as a 'grab me all attributes with this object_id' query. With appropriate indices, there is no reason to suspect that this will pose an unacceptable performance problem and initial tests (including the first version of contacts which used this scheme) work very quickly with several thousand rows.

Even the use of the 'ams_list' that groups attributes together in what could be described as 'virtual data rows' can be implemented by using an ams_list_id field in the main skinny attribute value table so that no join is necessary to the table that stores the details of the ams_list(s).

As a general principal, we will work towards 'munching' only integer keys only to gather the necessary ids which we will then go to the attribute values table with in a single (ideally) unjoined query. It is quicker to extract a result set of keys and then fetch their human readable values as a seperate step, than to join everything together and do it all in one query.

This makes sense to us but if you think we are mistaken or have missed the point, please do let us know.

Thanks for reviewing the document - your input is much appreciated.

Regards
Richard