Hello,
i'm struggling with some performance problems with an application i'm building on top of the Content Repository. the database used is a PostgreSQL 9.0.4 installation, and i'm using a fresh OpenACS 5.7.0b1 installation.
when i load a page that displays some tens of CR items (which attributes i retrieve using content::item::get), it takes relatively long (30 seconds for example).
i used Developer Support to try to find a possible problem/bottleneck. apparently the db_0or1row executions behind the content::item::get calls take up ~100 ms each (says DS):
108 ms 0or1row dbqd.acs-content-repository.tcl.content-item-procs.content::item::get.get_item: 0or1row pool1 nsdb0
select cx.*,
ci.live_revision,
ci.latest_revision,
ci.locale,
ci.publish_status,
ci.content_type,
ci.storage_type
from myapp_thingsx cx,
cr_items ci
where ci.live_revision = cx.revision_id
and ci.item_id = '3041'
however, when i open a psql client to the same database, and execute the sam SQL query, a whole different picture is drawn:
openacs=# explain analyse select cx.*,
ci.live_revision,
ci.latest_revision,
ci.locale,
ci.publish_status,
ci.content_type,
ci.storage_type
from myapp_thingsx cx,
cr_items ci
where ci.live_revision = cx.revision_id
and ci.item_id = '3041';
[...]
Total runtime: 1.836 ms
(31 rows)
the query spans a lot of tables as the content_type of the item is deep in a hierarchy of content_types (so content::item::get needs to return attributes of all parent content_types too)
so, as a summary:
using content::item::get, it takes ~100 ms to get my information.
using the same query in a psql client, it takes about ~2ms to get the same information.
i've tried several things to find out where this huge difference comes from, but am stuck at the moment. anybody has any ideas/suggestions?
thanks very much!
greetz,
k.
