ThumbsPlus

All forums => ThumbsPlus v7-v9 Questions => Topic started by: aamodio on 2013-07-29 10:32:13

Title: Images Browsing with Sqlite3 Database
Post by: aamodio on 2013-07-29 10:32:13

Hi All,

We are doing some tests, comparing usage of Sqlite3 Db versus Access Db.
We found that browsing images from view window, loading next or previous item, with Sqlite3 Bd is a very slow operation.
The sample images are JPEGS at about 300 Kb size each.
With Access Db the operation is rapid and without delay.

We use TP to review a lot of images images that come from a scan process, so the speed of browsing needs is important.

Any tricks to speed up image browsing in Sqlite3 Db?

Thanks,
Alessandro

Title: Images Browsing with Sqlite3 Database
Post by: Gary on 2013-07-30 14:48:52

Alessandro,

Sadly, there is very little you can do to speed up the next/previous.  I have found the best results with the following settings:

Options
  Viewing
    Window tab

      Initial size:  check "Automatic"
      Initial position:  check "Upper left" (although I am using Centered now)
      Initial Stretching:  check "Fit images to window" and "Maintain aspect ratio"
                           (all others unchecked)

Kindest regards,
Gary

Title: Images Browsing with Sqlite3 Database
Post by: R. Kasten on 2013-07-31 01:57:40

Hi aamodio

I recently posted a message
Quote
I have tried to change SQLite setting to accelerate tasks with assigning PRAGMA-keywords. In Thumbs9.ini this seems to be possible with the entry:

[Startup]
InitialDatabase=Database=Y:\Thumbs.tpdb8s; Driver = {ThumbsPlus SQLite3 ODBC Driver}; Timeout=5000; NoCreat=Yes; SyncPragma=NORMAL

I changed this entry to:

InitialDatabase=Database=Y:\Thumbs.tpdb8s; Driver = {ThumbsPlus SQLite3 ODBC Driver}; Timeout=5000; NoCreat=Yes; SyncPragma=OFF; PRAGMA journal_mode=OFF

I am aware that i lowered the reliability, but with the new setting the work is much faster.
Unfortunately, TP9 overwrites this setting on closing.

It would be nice if this setting is stored permanently.

Regards Rainer

Title: Images Browsing with Sqlite3 Database
Post by: aamodio on 2013-07-31 06:01:31

Thank you Gary and Rainer. I had applied the suggested tricks but the performance is still very poor.
I have also disabled VM as read here http://forums.cerious.com/forum/index.php?mode=thread&id=17

I was wondering if the performance with MySQL database will be the same (assuming that db and images are on the same workstation).

Thank you.

Alessandro

Title: Images Browsing with Sqlite3 Database
Post by: Laura Shook on 2013-08-01 15:38:55

Thank you for your support.

The sqlite database is indeed much slower than using another ODBC database.

1.  Do you have an SSD drive? If so you will find ThumbsPlus runs much faster.

2.  You can use the MS ACCESS database as it is really much faster. Note that the database size is virtually infinite (depending on your disk space of course)! You must create it with the option to include thumbnails in the files. When creating a new database just make sure that you mark the option to "store thumbnails in files".

3. Using SQL or another ODBC database that we support is the other alternative if you have it available to you. The scripts are all up date!

I hope that this helps. Let me know if you need further assistance.

Kind regards,
Laura Shook
Cerious Software, Inc.

Title: Images Browsing with Sqlite3 Database
Post by: Gary on 2013-08-01 20:41:59

Laura,

Thanks for this information!  I am in the processing of building the database as you recommended.  So far, the performance seems almost on par with TP7.  Good call.  I'm thinking I will dump the Sqlite3 database.

Kindest regards,
Gary

Title: Images Browsing with Sqlite3 Database
Post by: aamodio on 2013-08-02 03:53:06

Hi Laura,

Thank you for your suggestion. I'll try to switch to MS Access DB with external thumbnails.

Best Regards,
Alessandro

Title: Images Browsing with Sqlite3 Database
Post by: Gary on 2013-08-02 12:59:59

After working with Access and external files, I've decided to put up with the performance issues with Sqlite and dump the external file concept for the time being.

Title: Images Browsing with Sqlite3 Database
Post by: mschnell on 2013-08-04 04:44:46

I tried using en external SQL server (Postgres on a Linux box).

I found that the action you mention is definitively slower than when using the standard local Microsoft database engine (aka "Access") on a file located on the same Linux box and accessed via CIFS (aka Samba),  but by far not as sluggy as you describe.

Of course searching the database is fast as lightning :)

-Michael

Title: Images Browsing with Sqlite3 Database
Post by: aamodio on 2013-08-05 04:39:34

Thank you Michael for your report.