Re: Moving disk files by export/import?

[Bill Moseley <moseley hank org>]

On Mon, Jan 05, 2009 at 09:41:13AM +0800, Bengt Thuree wrote:
> Bill Moseley wrote:
> > On Sun, Jan 04, 2009 at 07:57:43AM +0100, Lorenzo Milesi wrote:
> >> Il giorno dom, 04/01/2009 alle 09.42 +0800, Bengt Thuree ha scritto:
> >>> Check the Change Photo Path extension...
> >> exactly!
> > 
> > http://svn.gnome.org/svn/f-spot/trunk/extensions/Tools/ChangePhotoPath/
> 
> > Anyway, I can imagine why the author noted that it was slow.  My guess
> > is with 20K photos sqlite would do it in just a few seconds, if that.
> > 
> This is a problem with sqlite.
> sqlite does the update in a few seconds to a few minutes, but then it
> takes a LONG time to actually update the database.
> That is, the code executes very quickly. But if you do select in the
> tables you will see it is not actually updated.

There's no sqlite server running in the background to finish an
update.  And you can't have an update that returns without updating
the database.  A not-so-small chunk of the Internet would come to a
grinding halt if that was true. ;)

My guess the reason it is slow is because it's doing all the work in
the application -- 20K images, two select * from photos, process every
image one-by-one building the arrays, and then again process every
image one-by-one reading again and one update for every
photo plus for every photo_version row associated with a photo.
That's a lot database queries when it seems it could be done in just
two.  (Again, I'm not clear if there's anything else that has to
happen in f-spot, but discussion the db updates here.)


It's always best to let the database do the things it is good at:

$ cp  .gnome2/f-spot/photos.db test.db

$ sqlite3 test.db 'select count(*) from photos'
7458

Here's the time to alter all of those and roll back the changes.


$ time sqlite3 test.db "begin; update photos set uri = replace( uri, 'photo','image'); select uri from photos limit 10; rollback; select uri from photos limit 10;"
file:///media/store/images/2004/05/06/catherine0511.jpg
file:///media/store/images/2004/11/25/jack_snowstrut.jpg
file:///media/store/images/2004/11/25/catherine_glasses.jpg
file:///media/store/images/2004/11/27/anna__mugshot.jpg
file:///media/store/images/2004/11/24/northstargang1.jpg
file:///media/store/images/2004/11/25/_annaski.jpg
file:///media/store/images/2004/11/25/jack_cool.jpg
file:///media/store/images/2004/11/25/_annaski_sml.jpg
file:///media/store/images/2004/01/05/santa.jpg
file:///media/store/images/2004/10/18/stitches-pokemon.jpg
file:///media/store/photos/2004/05/06/catherine0511.jpg
file:///media/store/photos/2004/11/25/jack_snowstrut.jpg
file:///media/store/photos/2004/11/25/catherine_glasses.jpg
file:///media/store/photos/2004/11/27/anna__mugshot.jpg
file:///media/store/photos/2004/11/24/northstargang1.jpg
file:///media/store/photos/2004/11/25/_annaski.jpg
file:///media/store/photos/2004/11/25/jack_cool.jpg
file:///media/store/photos/2004/11/25/_annaski_sml.jpg
file:///media/store/photos/2004/01/05/santa.jpg
file:///media/store/photos/2004/10/18/stitches-pokemon.jpg

real    0m0.101s
user    0m0.072s
sys     0m0.020s


And with a commit:


$ time sqlite3 test.db "begin; update photos set uri = replace( uri, 'photo','image'); select uri from photos limit 10; commit; select uri from photos limit 10;"
file:///media/store/images/2004/05/06/catherine0511.jpg
file:///media/store/images/2004/11/25/jack_snowstrut.jpg
file:///media/store/images/2004/11/25/catherine_glasses.jpg
file:///media/store/images/2004/11/27/anna__mugshot.jpg
file:///media/store/images/2004/11/24/northstargang1.jpg
file:///media/store/images/2004/11/25/_annaski.jpg
file:///media/store/images/2004/11/25/jack_cool.jpg
file:///media/store/images/2004/11/25/_annaski_sml.jpg
file:///media/store/images/2004/01/05/santa.jpg
file:///media/store/images/2004/10/18/stitches-pokemon.jpg
file:///media/store/images/2004/05/06/catherine0511.jpg
file:///media/store/images/2004/11/25/jack_snowstrut.jpg
file:///media/store/images/2004/11/25/catherine_glasses.jpg
file:///media/store/images/2004/11/27/anna__mugshot.jpg
file:///media/store/images/2004/11/24/northstargang1.jpg
file:///media/store/images/2004/11/25/_annaski.jpg
file:///media/store/images/2004/11/25/jack_cool.jpg
file:///media/store/images/2004/11/25/_annaski_sml.jpg
file:///media/store/images/2004/01/05/santa.jpg
file:///media/store/images/2004/10/18/stitches-pokemon.jpg

real    0m0.308s
user    0m0.068s
sys     0m0.028s




-- 
Bill Moseley
moseley hank org
Sent from my iMutt