I was wondering if creating schemas or materialized views for the AMS lists would speed up the application? At least the initial retrieval of values would be considerably sped up (we have lists with 50 attributes and the list is used by 30.000 objects, resulting in around 150ms per attribute_value to be retrieved). This might also get rid of the need for caching (AMS is heavy in using the ns_cache).
Obviously this would only work if the refreshing of the materialized view does not involve refreshing all the rows, but only those rows that are changed.
Alternatively we could solve this in TCL as well, creating a new table for each list. In AMS we would change the lists. Once we are done with changing we say "publish" (somewhat equivalent to "map", though you can have multiple versions of a list, with only the published version mapped). Upon publish, the old ams_${list_id} table is dropped and a new table recreated prefilled with a column per attribute and a row per mapped object_id.
Once we save a new value we additionally update the row in all the list tables that contain the attribute_id.
This is massively storing more data in the system, but it might speed things up considerably for retrieval of values. Additionally, upon saving the value, we could first update the list's tables and then run a scheduled proc in a seperate thread to insert properly into AMS.
Multiple values (aka options) could then be stored as TCL lists in the various lists' tables.
But again, not sure if this would be worth the effort.
