CODEWITHSUNDEEP
sqlite
Michael Chourdakis
Michael Chourdakis

Reputation: 11158

sqlite3 multiple inserts really slow

I have build a file archiver in Windows which uses sqlite3 to store files and takes advantage of multicore techniques to complete the archive faster.

I am trying a backup of 100.000 files now and insertion is slow.

When I comment the line which inserts, the app uses 100% CPU which is normal. With the insertion line on, it rarely gets above 25%.

As the archiving progresses, insertion gets more and more slow, processing a few files/second with a cpu usage of 11%. No disk usage is shown, so the bottleneck can't be the disk.

I 've:

PRAGMA temp_store = MEMORY
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF

and the entire insertion is within a transaction.

After further analysis it seems that SQLite's problem is to bind the blob64 (if I pass 0, it seems to be fine).

Why SQLite would have a problem inserting a raw blob of data into the archive?

Any ideas?

Thanks.

Upvotes: 1

Views: 137

Answers (1)

catnip
catnip

Reputation: 25388

Your answer may lie here:

https://www.sqlite.org/threadsafe.html

Because it says there that:

The default mode is serialized.

which might explain your observations.

According to that document, you can either configure this at compile time (which I would most definitely not myself do) or via:

sqlite3_config (SQLITE_CONFIG_MULTITHREAD);

Just how stratospherically it then performs I wouldn't know.

Upvotes: 2

Related Questions