Author Topic: Images Browsing with Sqlite3 Database  (Read 4957 times)

0 Members and 1 Guest are viewing this topic.

aamodio

  • Member
  • **
  • Posts: 8
    • View Profile
Images Browsing with Sqlite3 Database
« 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

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #1 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

R. Kasten

  • Member
  • **
  • Posts: 24
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #2 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

aamodio

  • Member
  • **
  • Posts: 8
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #3 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

Laura Shook

  • Administrator
  • Sr. Member
  • *****
  • Posts: 143
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #4 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.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #5 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

aamodio

  • Member
  • **
  • Posts: 8
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #6 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

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #7 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.

mschnell

  • Full Member
  • ***
  • Posts: 98
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #8 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

aamodio

  • Member
  • **
  • Posts: 8
    • View Profile
Images Browsing with Sqlite3 Database
« Reply #9 on: 2013-08-05 04:39:34 »
Thank you Michael for your report.