SQL Database Conversion: what is file_time_iso for?

[stevelon]

Hi I have a massive SQL Server thumbnail database I'm trying to convert to v8 (for use in thumbs 9): some 26 million rows.

The upgrade has been running for about 5 hours and is probably only 5% of the way through.

I've checked the script (upgradeserver.py) and it is very close to the end. The "Populating new Thumbnail columns" step finished relatively quickly, but the slowness is on the "- iso times" step, which appears to be updating the new file_time_iso field to the file's time/date stamp.

After this all that is left to do (I think) is drop some obsolete columns.

However, at this rate the "update_times" step is going to take DAYS.

So my question is: Do I need to let this "update_times" step complete? What's the file_time_iso and thumbnail_time_iso fields used for (beyond being information properties)? What functionality in ThumbsPlus do I lose if kill the upgrade process as is leaving the vast majority of rows with these fields unset?(and probably manually drop the obsolete fields and tables using the script .py script as a guide)?

Many thanks

[Pete Wieland]

It is a while since I upgraded a large database, but I seem to remember that around a million lines took 3-4 hours, so your timings seem to be about right.

This does all seem to add up; the thumbnails are being modified within the database, which will be quick, but to read every image file header from the OS, and then write the time to the database is going to be VERY slow!

Afraid I can't help you with advice on stopping the process. I don't know what TP does with the file_time_iso field, and don't know what else there is left to do when it has finished that.

[mschnell]

> I've checked the script (upgradeserver.py) and it is very close to the end.

How did you check this ?


I am asking, because I would like to do my own Python scrips and (somehow) integrate them in the TP9 user interface (icon, mene entry and/or hotkey).

While I am sure that I can easily learn how do do Python scrips, I did not find any documentation on how to start and debug them. So I did not take on the task yet.

Thanks for any pointers.
-Michael

[mschnell]

> but to read every image file header from the OS, and then write the time to the database is going to be VERY slow!

Do you suggest that the "upgrade stript" in fact loads the image file and not just works on the database entries ?

We would need to take a look at the python scrip to find this out. Happily the source code pf the script is provided by Cerious and this it can be examined (and modified if appropriate).

-Michael

[mschnell]

I checked and the inner loop is:

        cols, utimes = tdbcon.execute_with_data(u'select [idThumb], [file_time], [thumbnail_time] from [Thumbnail] where [idThumb] >= %s and [idThumb] <= %s' % (i, i + 999) )
        i = i + 1000
        if not cols:
            continue
        for utime in utimes:
            tdbcon.execute_direct(u"update [Thumbnail] set [file_time_iso] = '%s', [thumbnail_time_iso] = '%s' where idThumb = %s" % (isotime(utime[1]), isotime(utime[2]), utime[0]) )
        done += len(utimes)
        tpprog.doing_progress(done)


I understand that the "tdbcon" object handles access to the database. I doubt that it looks at the image files.

Thus this supposedly just is a very slow database operation.

I am not a database expert, but I do know that in many cases using an index on appropriate fields can speed up certain operations greatly. I understand that this can be done with the mySQL admin tool.

-Michael

[stevelon]

Hi

Sorry, I didn't run the script in python. I just read it. I could see that the only following steps were to remove some obsolete columns and tables.