[vbRichClient] RC6 increase SQLite performance

[Mith]

my app scans a lot of files recursively and add/update the file information in the DB.

im searching for some hints/tips/tricks to increase the performance when using a lot of INSERT and SELECT statements.

i already have created all necessary indizies (single+multi) for the columns that i use at the WHERE clause's.

and i already know i have to use .BeginTrans before i start the file scan and using .CommitTrans after the whole scan is completed.
without this SQlite uses a transaction for every INSERT/UPDATE statement and slows down the whole procress

some other web search pointed out to use the following PRAGMA options to speed up the process:

PRAGMA JOURNAL_MODE=OFF
PRAGMA JOURNAL_MODE=WAL
PRAGMA synchronous=OFF
PRAGMA LOCKING_MODE=EXCLUSIVE

See blog.devart: increasing-sqlite-performance.
See JOURNAL_MODE=WAL

Are all these options supported by RC6?

my tweaks so far to improve the performance:

- using .BeginTrans before executing a lot of INSERT/UPDATE statements
- using "LOCKING_MODE=EXCLUSIVE" when executing a lot of SELECT statements
- split the SQL-statement with a table join into 2 SQL-statements

Any other hints/tips/tricks to increase performance using RC6 SQlite?

[jpbro]

RC6 will support all the PRAGMAs via the cConnection.Execute method. I think you've already found the biggest performance "tweak" which is using transactions.

Outside of SQLite itself, faster hardware will result in faster performance of course (so an SSD would be preferable to a spinning platter drive).

One thing that might be worth experimenting with - using a memory DB instead of a file DB (perhaps writing it out to a file periodically via the cConnection.CopyDatabase method). This might not be possible depending on your workload though.

Are there any statements that you are finding particularly slow? How slow is a "slow" statement? How many records are you inserting/selecting? Note also that indexes will typically make INSERTs slower, but SELECTs against indexed columns faster.

[jpbro]

This blog post also has some other ideas that you might want to experiment with: https://phiresky.github.io/blog/2020...rmance-tuning/