Re: Need suggestion high-level suggestion on how to solve a performance problem

Madison,

>    The problem comes when the user toggles a directory branch's backup
> flag (a simple check box beside the directory name). If it's a directory
> near the end of a branch it is fast enough. If they toggle a single file
> it is nearly instant. However if they toggle say the root directory, so
> every file and directory below it needs to be updated, it can take
> 500-600sec to return. Obviously this is no good.
>
>    What I need is a scheme for being able to say, essentially:
>
> UPDATE file_info_1 SET file_backup='t' WHERE file_parent_dir~'^/';

Well, from the sound of it the problem is not selecting the files to be
updated, it's updating them.

What I would do, personally, is *not* store an update flag for each file.
Instead, I would store the update flag for the directory which was
selected.  If users want to exclude certain files and subdirectories, I'd
also include a dont_update flag.  When it's time to back up, you simply
check the tree for the most immediate update or don't update flag above
each file.

For the table itself, I'd consider using ltree for the directory tree
structure.  It has some nice features which makes it siginifcanly better
than using a delimited text field.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco