Forum OpenACS Development: Response to OpenACS 4 file storage slow query

Collapse
Posted by Jun Yamog on
Hi,
<p>
I have uncovered another performance problem in file-storage.  Although the code is 4.5 beta.  Here is the offending query.
<pre>
    select  r.title,
            r.revision_id as version_id,
            person__name(o.creation_user) as author,
            r.mime_type as type,
            to_char(o.last_modified,'YYYY-MM-DD HH24:MI') as last_modified,
            r.description,
            acs_permission__permission_p(r.revision_id,'2426','admin') as admin_p,
            acs_permission__permission_p(r.revision_id,'2426','delete') as delete_p,
            r.content_length as content_size
    from  acs_objects o, cr_revisions r, cr_items i
    where  o.object_id = r.revision_id
    and    acs_permission__permission_p(r.revision_id, '2426', 'read') = 't'
    and    r.item_id = i.item_id
    and    r.item_id = '7416'
    and r.revision_id = i.live_revision
</pre>

A possible fix is this

<pre>
    select  r.title,
            r.revision_id as version_id,
            person__name(o.creation_user) as author,
            r.mime_type as type,
            to_char(o.last_modified,'YYYY-MM-DD HH24:MI') as last_modified,
            r.description,
                acs_permission__permission_p(r.revision_id, '2426', 'read') as read_p,
acs_permission__permission_p(r.revision_id,'2426','admin') as admin_p,
            acs_permission__permission_p(r.revision_id,'2426','delete') as delete_p,
            r.content_length as content_size
    from  acs_objects o, cr_revisions r, cr_items i
    where  o.object_id = r.revision_id
    and    r.item_id = i.item_id
    and    r.item_id = '7416'
    and r.revision_id = i.live_revision
</pre>
<p>
And just check the perm on tcl level.  The 2nd query is significantly faster.  And I only need to count my fingers to justify the speed no fancy explain stuff.  In fact if I run explain is just gives the same plan.  Odd.  But I am sure they are very different.
<p>
I haven't taken a look at the latest file-storage code but any opinions about this?