Author Topic: SQlite3 (Thumbs8.tpdb8s) Performance  (Read 14476 times)

0 Members and 1 Guest are viewing this topic.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« on: 2016-01-08 20:51:30 »
Win7 64-bit.  TP10 build 4007.

For what it is worth and information provided for use at your own risk:

I googled sqlite3odbc.dll which led me to this web site:

  http://www.ch-werner.de/sqliteodbc/

I took a chance and downloaded the sqliteodbc.exe (under Current Version) sqlite odbc driver installation program and installed it.  Ran all the virus checks with no issues.

The sqlite3odbc.dll that comes with TP10 is version 0.81 based on sqlite 3.6.13.

The odbc driver program installs version 0.9992 based on sqlite 3.8.9.

The performance improvement thus far is an order of magnitude.  Still testing but so far so very good.  I am guessing I will have to reinstall this driver with each new version of TP10.

Cheers,
Gary

IlseKasten

  • Full Member
  • ***
  • Posts: 50
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #1 on: 2016-01-09 03:58:46 »
A good tip, thanks.
I've installed 'sqlite odbc w64.exe'. For TP, it should not matter, since it acts as a frontend for SQLite.
I think to determine a significant acceleration of many operations, but for objective measurements I have no options.
The installation must be performed only once, when the 'sqlite3odbc' from the SQL program directory is copied into the ODBC directory of TP10.
This will probably be necessary with every update.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #2 on: 2016-01-09 10:25:52 »
> A good tip, thanks.
> I've installed 'sqlite odbc w64.exe'. For TP, it should not matter, since it acts as a frontend for SQLite.
> I think to determine a significant acceleration of many operations, but for objective measurements I have no options.
> The installation must be performed only once, when the 'sqlite3odbc' from the SQL program directory is copied into the ODBC directory of TP10.
> This will probably be necessary with every update.

Thanks for your reply! I thought I would need to copy sqlite3odbc.dll to the TP10\ODBC directory, but so far it has not been necessary.  The updated sqlite3odbc.dll is installed into C:\Windows\sysWOW64 replacing the one installed by TP10.  This seems to be the one that gets used.  The difference in performance is staggering (to the good).

ducky

  • Member
  • **
  • Posts: 17
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #3 on: 2016-01-09 10:50:46 »
I also installed the x64 package. When I copy sqlite3odbc.dll from "C:\Program Files\SQLite ODBC Driver for Win64" to "C:\Program Files (x86)\ThumbsPlus 10\Odbc" TP10 does not start and I get the following message.



So I had to restore the original file to get tp working again. No surprise, the database performance has not changed. In "C:\Windows\SysWOW64" are no sqlite* files.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #4 on: 2016-01-09 11:37:13 »
I think you need the 32-bit version.  TP10 is a 32-bit program and I installed the 32-bit version from the referenced website.

IlseKasten

  • Full Member
  • ***
  • Posts: 50
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #5 on: 2016-01-09 12:20:17 »
You are right. TP only works with the 32bit version, a copy in the TP directory is not required.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #6 on: 2016-01-09 13:07:57 »
Are you running Win10?  On my Win10 machine, there indeed are no sqlite* files until I installed the driver I referenced in my original post.  Not sure how TP10 works on Win10.  Ugh.

ducky

  • Member
  • **
  • Posts: 17
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #7 on: 2016-01-09 16:05:03 »
I removed the 64bit package and installed the 32bit drivers. Now I can find sqlite* files in the windows folders system32 and syswow64. But I am not sure which driver is used by tp10. When I remove "C:\Program Files (x86)\ThumbsPlus 10\Odbc\sqlite3odbc.dll" tp does not start. When this dll is replaced by the one contained in the 32bit driver package tp works fine, but without noticeable changes of performance.

Windows 7x64 SP1

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #8 on: 2016-01-09 18:58:31 »
Be sure your Thumbs10.ini file database options are set like:

InitialDatabase=Database=;Driver={ThumbsPlus SQLite3 ODBC Driver};Timeout=5000;NoCreat=Yes;JournalMode=Off;SyncPragma=Off

ducky

  • Member
  • **
  • Posts: 17
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #9 on: 2016-01-09 22:06:10 »
Wow, your suggested settings accelerate database handling a lot. Thanks very much.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #10 on: 2016-01-10 08:29:53 »
I find everything runs much faster with the updated sqlite odbc drivers.  Performance seems to meet or exceed that of the legacy access database.  Hopefully Cerious will address this issue.

Jens Rieger

  • Member
  • **
  • Posts: 23
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #11 on: 2016-01-12 13:44:53 »
The improvement of performance is unbelievable. It's like a new program.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #12 on: 2016-01-12 22:09:14 »
Yup.  The performance improvement makes TP10 an entirely different program.

mschnell

  • Full Member
  • ***
  • Posts: 98
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #13 on: 2016-08-21 02:47:35 »
In discussions about an unrelated project I was told that with sqlite3 there is an option

'PRAGMA synchronous = OFF;'

That speeds up the performance dramatically.

-Michael

IlseKasten

  • Full Member
  • ***
  • Posts: 50
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #14 on: 2016-08-21 10:01:17 »
As I mentioned earlier in a TP9-Thread:
This is the correct entry in
Thumbs10.ini under Startup

InitialDatabase=Database=PathTo.tpdb8s;Driver={ThumbsPlus SQLite3 ODBC Driver};Timeout=5000;NoCreat=Yes;SyncPragma=OFF;PRAGMA synchronous = OFF;PRAGMA journal_mode = OFF