Hello,

I am trying to fix a performance issue of my Haskell program which uses direct-sqlite.

In the strace log below, I find that the Haskell program is slow because the journal file gets re-created for every statement execution:

newfstatat(AT_FDCWD, ".../a.db-journal", 0x7ffeb2b57df0, 0) = -1 ENOENT (No such file or directory)
...
newfstatat(AT_FDCWD, ".../a.db-wal", 0x7ffeb2b57df0, 0) = -1 ENOENT (No such file or directory)
...
openat(AT_FDCWD, ".../a.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 41
newfstatat(41, "", {st_mode=S_IFREG|0644, st_size=0, ...}, AT_EMPTY_PATH) = 0
...
close(41)                               = 0
unlink(".../a.db-journal") = 0

The above log repeats constantly.

Here are the .dbinfo from the resulted db of both the Python and Haskell programs:

Python:
sqlite> .dbinfo
database page size:  4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 8
database page count: 156
freelist page count: 0
schema cookie:       1
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3036000
number of tables:    1
number of indexes:   1
number of triggers:  0
number of views:     0
schema size:         453
data version         1

Haskell:
sqlite> .dbinfo
database page size:  4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 5841
database page count: 142
freelist page count: 1
schema cookie:       1
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3028000
number of tables:    1
number of indexes:   1
number of triggers:  0
number of views:     0
schema size:         495
data version         1

The file change counter for the Haskell program is exceedingly high.

Could someone please shed light on what to look into next?

Thanks in advance.

With the default setting of the Python sqlite3 module it opens an implicit transaction when you start doing queries and won't commit that transaction until you explicitly tell it to commit.

The most likely problem with your Haskell program is that it's doing every insert as its own transaction. Wrap the inserts with an explicit transaction with begin and commit, and see if that fixes it.

FAQ: (19) INSERT is really slow - I can only do few dozen INSERTs per second