Author Topic: SQlite3 (Thumbs8.tpdb8s) Performance  (Read 13990 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: 22
    • 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

leedell

  • Member
  • **
  • Posts: 4
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #15 on: 2016-10-26 22:11:02 »
Speed comes with a price. When OS/2 went live, it used Microsoft's code for disk transactions, among other things, rather than the code that IBM had presented. The reason was that in testing, Microsoft's code was much, much faster. Later, they found out why: Microsoft had removed all parameter checking from the test code. So, as long as the calling program never supplied incorrect or impossible values....

To my point, though, using some of those options eliminates the safety nets in the client.
As stated at https://www.sqlite.org/pragma.html#pragma_journal_mode

PRAGMA schema.journal_mode;
PRAGMA schema.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF

This pragma queries or sets the journal mode for databases associated with the current database connection.

The first form of this pragma queries the current journaling mode for database. When database is omitted, the "main" database is queried.

The second form changes the journaling mode for "database" or for all attached databases if "database" is omitted. The new journal mode is returned. If the journal mode could not be changed, the original journal mode is returned.

The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit. (See the document titled Atomic Commit In SQLite for additional detail.)

The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it. On many systems, truncating a file is much faster than deleting the file since the containing directory does not need to be changed.

The PERSIST journaling mode prevents the rollback journal from being deleted at the end of each transaction. Instead, the header of the journal is overwritten with zeros. This will prevent other database connections from rolling the journal back. The PERSIST journaling mode is useful as an optimization on platforms where deleting or truncating a file is much more expensive than overwriting the first block of a file with zeros. See also: PRAGMA journal_size_limit and SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT.

The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

The WAL journaling mode uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database. A database in WAL journaling mode can only be accessed by SQLite version 3.7.0 (2010-07-21) or later.

The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt.

Note that the journal_mode for an in-memory database is either MEMORY or OFF and can not be changed to a different value. An attempt to change the journal_mode of an in-memory database to any setting other than MEMORY or OFF is ignored. Note also that the journal_mode cannot be changed while a transaction is active.

IlseKasten

  • Full Member
  • ***
  • Posts: 50
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #16 on: 2016-10-28 11:48:22 »
These points are wonderfully explained by you.

In this thread, however, the acceleration of SQLITE was the goal.

Each user should decide how much security or speed is important to him.

In my opinion, TP is rarely executing transactions that require this high level of security. The images are never endangered, everything else could easily be restored manually.
A backup copy of the DB after major changes also increases the security.

In my view, the advantage of rapid processing in daily use is the main advantage.


Regards Ilse

Lengo

  • Member
  • **
  • Posts: 22
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #17 on: 2017-09-26 20:59:02 »
Re: Upgrading installation of SQLite.

Tell us where files exist, and where to make the entries. Provide notes of everything that must be changed IN ONE PLACE! Make it complete. I don't want to work that hard, or take the risk of getting something wrong.

Write a how to, and ask that it be put on Cerious' website where it can easily be found.

Thanks.

IlseKasten

  • Full Member
  • ***
  • Posts: 50
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #18 on: 2017-09-29 00:57:50 »
If these hints are unclear to you, you should not use them. There should already be some PC knowledge.
 
The best way to speed up TP is, according to my experience, the use of an SSD. The 'Thumbs.tpdb8s' should be on the SSD. So you speed up TP much more than any other tip can do.

regards Ilse

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #19 on: 2017-10-21 13:59:02 »
> Re: Upgrading installation of SQLite.
>
> Tell us where files exist, and where to make the entries. Provide notes of everything that must be changed IN ONE PLACE! Make it complete. I don't want to work that hard, or take the risk of getting something wrong.
>
> Write a how to, and ask that it be put on Cerious' website where it can easily be found.
>
> Thanks.

IF ANY OF THE FOLLOWING MAKES YOU UNCOMFORTABLE, DO NOT DO THIS!!

For Windows 7 and 10 these are the steps I took to improve the performance of the SQLite database with TP10 (I would guess this will work with TP9 and TP8):

Before you start, be sure that TP10 is not running!

1) Go to the following web site and download the sqliteodbc drivers:

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

Scroll down the page if necessary for this grouping of text:

Current version
sqliteodbc.exe <--click to download (even for 64bit Windows!)
sqliteodbc_w64.exe <--NO!
sqliteodbc-0.9995.tar.gz <--NO!
sqliteodbc-0.9995-1.src.rpm <--NO!

2) Go to folder where "sqliteodbc.exe" was saved and double click to install (virus scan first if you have doubts, but I have never had a single problem with this exe file).

The odbc driver will be installed in the folder:

"C:\Program Files (x86)\SQLite ODBC Driver"

NOTE!  Save the "sqliteodbc.exe" as you will need to repeat steps 2 thru 6 if you install/reinstall TP10.

3) I am not sure if this step is necessary, but I do it regardless:

As the administrator using a file manager:

- go to the folder "C:\Program Files (x86)\SQLite ODBC Driver"
- copy (DO NOT MOVE!) the file "sqlite3odbc.dll" to the folder
"C:\Program Files (x86)\ThumbsPlus 10\Odbc"
- This will overwrite the existing "sqlite3odbc.dll" file.

4) Use a text editor to modify the file:

"C:\Users\\AppData\Roaming\ThumbsPlus\Thumbs10.ini"

Edit this line to include the name=value parameters shown:

InitialDatabase=Database=\Thumbs8.tpdb8s;
Driver={ThumbsPlus SQLite3 ODBC Driver};Timeout=5000;NoCreat=Yes;JournalMode=Off;SyncPragma=NORMAL

Save the file.

5) This is likely not necessary, but I reboot the computer at this point.

6) Start TP10 and you should see a dramatic improvement in performance.

I hope you find this write up useful.

Again, if any of this gives you concern, DO NOT DO ANY OF IT.

Cheers.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #20 on: 2017-10-22 22:39:41 »
Revision.

In further testing, it appears the following step is NOT necessary.  Likely the original sqlite3odbc.dll file in this folder is used during the TP10 installation process and not when the program itself is run:

3) I am not sure if this step is necessary, but I do it regardless:

As the administrator using a file manager:

- go to the folder "C:\Program Files (x86)\SQLite ODBC Driver"
- copy (DO NOT MOVE!) the file "sqlite3odbc.dll" to the folder
"C:\Program Files (x86)\ThumbsPlus 10\Odbc"
- This will overwrite the existing "sqlite3odbc.dll" file.


The "sqliteodbc.exe" install will place the "sqlite3odbc.dll" odbc driver in the folder "C:\Windows\SysWOW64" for 64-bit Windows 7 and Windows 10.

I do not have access to a 32-bit Windows system, so I don't know where the dll file would end up in that case.

Jens Rieger

  • Member
  • **
  • Posts: 22
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #21 on: 2017-10-26 10:37:43 »
I use a Windows 7 64bit, too. The 32bit files are stored in c:/windows/system32

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #22 on: 2017-11-04 09:17:54 »
> Revision.
>
> In further testing, it appears the following step is NOT necessary.  Likely the original sqlite3odbc.dll file in this folder is used during the TP10 installation process and not when the program itself is run:
>
> 3) I am not sure if this step is necessary, but I do it regardless:
>
> As the administrator using a file manager:
>
> - go to the folder "C:\Program Files (x86)\SQLite ODBC Driver"
> - copy (DO NOT MOVE!) the file "sqlite3odbc.dll" to the folder
> "C:\Program Files (x86)\ThumbsPlus 10\Odbc"
> - This will overwrite the existing "sqlite3odbc.dll" file.
>

>
> The "sqliteodbc.exe" install will place the "sqlite3odbc.dll" odbc driver in the folder "C:\Windows\SysWOW64" for 64-bit Windows 7 and Windows 10.
>
> I do not have access to a 32-bit Windows system, so I don't know where the dll file would end up in that case.

Ugh.  Step 3) IS necessary! especially in Windows 10

mschnell

  • Full Member
  • ***
  • Posts: 98
    • View Profile
SQlite3 (Thumbs8.tpdb8s) Performance
« Reply #23 on: 2017-12-11 13:22:39 »
We use the database on a NAS. nearly 2 GByte "Access" Database. No speed issues at all.

-Michael