Forum OpenACS Q&A: Performance problems with a CR application

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.