Multiple Databases

[JoeVA63]

I have a situation where I have too many files I would like in a Thumbs database.  I have 2 SSD drives dedicated to my files.  My situation is, I like thumbnails, they allow me to easily identify my files.  If I scan 1 drive to create thumbnails, a database is created.  I am assuming when I scan the second drive, the additional thumbs will be added to that database.  But the problem is, I believe the database file becomes too large.  So my question is this... Can I create separate databases, one for each drive, so i avoid this situation and am allowed to have thumbs created for all my files?

Thanks for your help!

[Gary]

I do this myself with TP7 and the Access Database.  To create a new database, open TP, click File|Database|New Database.  To switch between databases, click File|Database|Open Database.

You can also create unique icons to start different databases.  For example create a desktop icon for TP, then right click, select properties and change the target:

"C:\Program Files (x86)\ThumbsPlus7\Thumbs.exe" ""


If you are using TP10, use the SQlite database to lift database size restrictions, otherwise follow the above suggestions if you want to create multiple databases.  However, click Database|New... or Database|Open.

Hopefully this is of some help.

Cheers,
Gary

[Daan van Rooijen]

Some additional comments:

The default .tpdb8 database (which is actually an MS Access MDB file) has a 2 GB filesize limit. Using default thumbnail settings, that will give you approximately 400,000 thumbnails. See Database | Statistics to see the figures for your database. If you are getting close to the limit, you may still be able to regain some space by running Database | Advanced | Compact and Repair. This will physically remove deleted records from the database file. Before doing so, you may also want to remove any orphaned thumbnails from your database. Those are thumbnails for images that no longer exist on the hard drive. To do so, right-click on a drive in the tree and pick Remove Orphans (there's also an option under Options | Preferences | Thumbnails that does this automatically whenever you open a folder).

[Gary]

When you hit the access database limit without any orphans present, compacting does not matter.  You are out of space.

[Daan van Rooijen]

> When you hit the access database limit without any orphans present, compacting does not matter.  You are out of space.

No, your database may still hold many deleted records that you can clean up (they have only been flagged inactive but are still physically present).

[Gary]

Daan,

When you hit the limit of 2GB and there are no orphans or deleted records, nothing you can do will reclaim space as there is nothing to reclaim.  I have hit the limit and it is not pretty.

Gary

[JoeVA63]

Hey everyone, first of all I want to thank each of you for your replies.  You have been a great help!  I agree with Gary, when you hit the 2GB limit, it can be a disaster.  And that's my problem.

I am currently running some tests, playing with Thumbs to see what my best solution is.  And from what I can tell, it's not about the number of files, but the total file size of the Thumbs database file.  Theoretically, if I had 1 file that was 2GB in size, I will have used up my entire database.  Let's hope that never happens!

First what I did was move all my files to 1 drive, scan the drive to create thumbs, and ran into problems.  As expected, the database file reached the 2GB limit.  Then what I did was move some of the files to the second drive.  I went back to the first drive, removed any orphans, and did a Compact & Repaid of the database.  I checked the 2 options for Cleanup dangling rows before compact, and Remove unused keywords from database.  That decreased the size of the database file.  I apologize I did not record the before and after file sizes.

I then moved to the second drive, created a second database, scanned, and successfully completed the task of creating thumbs for all my files on 2 drives.

So it appears I can create 2 separate database files, and keep an eye on my drives to keep certain I do not exceed the file size limitations.

Once again, I thank you all for your comments and suggestions, and thoughts.  All of you have been a great help.

I would be interested if anyone has any additional comments to add to this thread.

[Daan van Rooijen]

> When you hit the limit of 2GB and there are no orphans or deleted records, nothing you can do will reclaim space as there is nothing to reclaim.

But that's what I said in my first message.

[Daan van Rooijen]

> So it appears I can create 2 separate database files, and keep an eye on my drives to keep certain I do not exceed the file size limitations.

Correct, however the location of your image files doesn't matter (but it may be practical for you to organize them this way, on two drives, each with a database of its own).

The size of your database is a function of the number of images that you catalog, and of the average size of the thumbnails that ThumbsPlus stores in its database (it also stores some additional info but the thumbnails occupy the most space). By splitting the database you have reduced the number of files cataloged in each database. For any additional savings, you'd have to reduce the thumbnail dimensions or their quality (JPG compression). These settings are found in the lower left corner of Options | Preferences | Thumbnails. Changes that you make here don't affect existing thumbnails, so you have to re-catalog them if you want to change their size or quality.

[Christian]

I found two ways for working around the 2 GigB-Limit of MS-Jet-Database:
1. the thumbanils stored in the databse file needs far more space then every other database field. So make the thuunbnail size small. I used the size of 144x108 px with 25% jpg-Quality, a little bit eye-powder:-(. With 38 user fields and 470,000 Thumbnails the database needed about 1.6 GB.
2. dirty trick: move the userfields table in MS-Access in a separate mdb-file and add a link to this table in the thumbs-database. The tables "thumbnail" and "userfields" are 1:1 related via the Keys "idThumb" <-> "idThumbUDF". In my case the 1.6 GB were divided in 1.2 GB for the Thumbs-Database and 0.4 GB for the userfields table. In this case run database-compact/repair operations within MS-Access and disable this command in T+.The other tables in the T+-database like Keyword, Galleries etc. are tiny compared to Thumnbnail and userfields.

[Daan van Rooijen]

Interesting! I would have expected though, that with a JPG quality setting of only 25%, you could store even more thumbnails in a 1.5 GB database.

Is performance still good now that you have moved the thumbnail table to a different database file, or is there no difference in speed?

You probably picked your 144x108 thumbnail size carefully to keep them in a 4:3 aspect ratio. In general though, you get the best efficiency out of jpg thumbnails by using multiples of 8 for their X and Y dimensions (because jpeg compression operates on blocks of 8x8 pixels).

Have you seen this thread? It seems that SQLite3 (without the 2GB MS-Access limit) is now a workable solution too: http://forums.cerious.com/forum/index.php?id=4942

[Christian]

Daan.

thank for Your reply.

with the separate userfields-file database speed is same as in the standard configuration.
Mainreason for this dirty method: if yo try to reconfigure the userfiels structure with a databse near the 2GB-Limit, you will always run off this limit with data loss, either in T+ or in MS-Access. If you change/add userfiels in the the separate file, don't forget to change also the "switchboard"- table userfieldsinfo.

I use the MS-Jet-Databas format, because i make some of semi-automatic work in MS-Access Examples:
1. i generate the annotation mostly by concetaning a short title like "Church" and the userfield "city" and "sublocation" (i fill these userfiels semi-automatic by geonames.org)with an update query in MS-Access:

UPDATE auThumbs SET auThumbs.annotation = IIf([uf_City]<>[uf_Sublocation],[annotation] & " in " & [uf_City] & "-" & [uf_sublocation],[annotation] & " in " & [uf_City])
WHERE (((auThumbs.annotation) Not Like "* in *") AND ((auThumbs.Path.name) Like [Bitte Pfadnamen mit Platzhalter]) AND ((auThumbs.uf_City) Is Not Null) AND ((auThumbs.uf_Sublocation) Is Not Null));
Example annotation= "church" City = "Venlo", Sublacation= "Blerick": resukt is annotation = "Church in Venlo-Blerick"

2. i calculate the sheet Number of topographic map (1:25000) from the photo-coordinates using a self constructed Visual-Basic-Function. etc.

I made some work to generate these queries und functions. I do not want to lose big parts of this work by changing the databse format.

BTW: This flexibility and extensibility is the reason why I'm loyal to T + from 2001 until today.

Now i'm trying to migrate to T+ 10 Build 4007 - after several frustrating tries with earlier buggy versions. Big advantage: move thumbnails to files!

[Daan van Rooijen]

Thanks for your explanation, that's some pretty advanced use of ThumbsPlus! Best of luck with the migration to the new version.

P.S. maybe this is of use with the conversion to thumbnails in separate files:
http://forums.cerious.com/forum/index.php?id=484

[Christian]

Daan,
move Thumbnails to files seems easy in T10 Buidl 4007, see Release notes:
"Added Database - Advanced - Move Thumbnails to Files to make the process easier (in earlier versions you had to type several Python commands)"