Forum OpenACS Q&A: File Storage Module Extremely Slow

Yes, the new-file-storage is massively faster. The most
inefficient part is the hierarchy calculation, and that will be
improved, too.

The BLOB hack is CPU intensive, as it encodes/decodes the binary data in order to stuff it into Postgres.  This expands the size of the data  by 4/3.  Also, in order to avoid tying up the connection the BLOB is spooled to a file before it is returned to the user.  aD has removed this from the Oracle driver, presumably because of the additional overhead - file storage ain't blindingly fast in the Oracle world, either.  If I thought we'd be stuck with my BLOB hack much longer I'd remove it from the PG driver, too (PG 7.1 will have reasonable large objects, and of course we won't use them anymore for file storage anyway).

Any idea what effect this will have elsewhere?  Are we courting danger or DB corruption here?

The history is that the original Oracle query, stripped to the bit relevant to this bug, did this:

select fsf2.parent_title from fs_files fsf1, fs_files fsf2
where fsf1.parent_id = fsf2.file_id(+)

Since pre-7.1 PG doesn't have outer joins, this query was split into two:

select fsf1.parent_id from fs_files fsf1, fs_files fsf2
where fsf1.file_id = $file_id

set parent_title [database_to_tcl_string_or_null $db "
   select parent_title from fs_files where file_id = $parent_id"]

Oops!

ns_log warning "entering fs_order at [ns_time]"
    set ordering_query "select file_id, fs_level_gen(file_id,1) as the_level, s
ort_key as old_sort_key, depth as old_depth
                          from fs_files
                         order by fs_connect_by(file_id), file_title"

    set order_depth_list [database_to_tcl_list_list $db $ordering_query]
    set sort_key 1
    set changed_count 0

    foreach id_depth $order_depth_list {
        set id [lindex $id_depth 0]
        set depth [expr [lindex $id_depth 1]-1]
        set old_sort_key [lindex $id_depth 2]
        set old_depth [lindex $id_depth 3]
        if {$old_sort_key != $sort_key || $old_depth != $depth} {
            incr changed_count
            ns_db dml $db "update fs_files set sort_key=$sort_key, depth=$depth
 where file_id=$id"
        }
        incr sort_key
    }
    ns_log warning "Done with fs_order at [ns_time] changed $changed_count files out of a total of $sort_key"

Minimizing the calls to permission_p is undoubtably important, of course, but I think we've sped up the permissions checking considerably from ACS 4 classic.  A best-of-both-worlds scenario with file-storage, I should hope.

We should also just get rid of the file sort key in the postgres version.  We already have the tree_sortkey and it's sufficient and it's already being maintained by triggers on the content repository. fs_order_files could be a NOP for the PG version.

I check to see if this bug was in ACES or ACS3.5 and it turns out that Eric Cholankeril wrote a PLSQL funtion (see packages/file-storage/file-storage.sql) that does the entire sort inside the database which is probably a better solution then my quick fix.  If I find my quicky solution doesn't give me the performance I need I'll translate it for Postgres.