My homepage was being very sluggish so I ran developer support on it. What is striking is that permissions is by far what is hurting OpenACS's performance (response times) the most and I am very glad that Don's optimizations are under way. For example, on my index page there were two permission_p calls (from the request processor maybe) and each one was taking around 800 milli seconds, alone far more than all other queries on the page combined. I therefore decided to turn on permissions caching despite the warning that some applications access the permissions in the database directly (we really need to fix that). In general we need to make sure that package developers use a Tcl API when they access the database, at least if they are accessing the core datamodel.

I then turned to my bookmarks index page that still was quite slow. It turned out permissions was the problem again. By removing a check of whether the user can write in any of the displayed folders I cut the response time by seconds. The bookmarks select query still takes about 400 ms though and I'd like to remove that if possible.

So thus, finally my question - what is the best way to cache a multirow? I know Don has used caching for the db API for Greenpeace - can we use that already, or should I use a different approach such as manually convert the multirow to a list and cache that?

Thanks!

Jeff,
thanks for the tip about vacuuming, I should do that nightly, right?

On the topic of caching, I just discovered the Cache control page where you can see everything cached by util_memoize (find it at /acs-admin/cache). I added a link to the page so that people can find it.

We are going to be using ns_cache quite heavily now for the workflow Tcl API.

For performance it is always going to be faster to access permissions directly in queries, for instance to query for all the content in a content folder one has permission to read.

But for high-level checks on permissions on a single object yes, the Tcl API should be used.

If you want to play with the caching db_* code I wrote for Greenpeace I'll send you a copy.  I don't know if it's the best way to cache a multirow but you can't beat "db_multirow -cache" for simplicity.

I would like to generalize that code a bit more so it could be more useful.  Caching is easy ... the hard problem is synchronizing the cache with db contents in a reasonably easy-to-use way, and there's a follow-on relatively simple problem to solve, namely plug into the code that memoize now uses to keep cache contents consistent across a server cluster.

you should also check and make sure you increased your shared buffers on postgresql and that the db cache is populated. It might be that in practice it is much faster once it has run a couple times.

Whats the result of "explain analyze select ..." on that query?

I wrote a proc that does just that. Jeff suggested that we have some kind of switch on the list_to_multirow that would allow my code to be used when desired.

Here's the code (which was in the util-procs.tcl file):

ad_proc -public template::util::list_to_multiple { __multiple __nameList __rowList { __level 1 } }  {
    
    Converts list values to multiple tag format. Appends rows to 
    existing multiple.
    
    @param __multiple multiple tag name attribute
    @param __nameList list of array element names
    @param __rowList list of array element values
} {
    
    upvar $__level ${__multiple}:rowcount __rowcount
    
    if {[info exists __rowcount]} {
        set __i [expr $__rowcount + 1]
    } else {
        set __i 1
    }
    
    foreach $__nameList $__rowList {
        upvar $__level ${__multiple}:${__i} __localArray 
        set __localArray(rownum) $__i
        foreach __element $__nameList {
            set __localArray($__element) [set $__element]
        }
        incr __i
    }
    
    set __rowcount [expr $__i -1]
}