Consider running sqlite inserts in background

[phiresky]

(semi-relevant to #2)

I've just benchmarked a bit by adding time in front of the sqlite3 command in _histdb_query, and with a ~15MB history file, it takes ~50-100ms before a command is executed, and ~30ms for the time update.

Currently, these insert/updates are run synchronously, but it seems to me that (since the output of these commands is not used), it should work the same way to just run them in the background, which makes the shell noticeably more responsive:

diff --git a/sqlite-history.zsh b/sqlite-history.zsh
index 033199c..acbace7 100644
--- a/sqlite-history.zsh
+++ b/sqlite-history.zsh
@@ -60,7 +60,7 @@ histdb-update-outcome () {
     local finished=$(date +%s)

     _histdb_init
-    _histdb_query <<EOF
+    (_histdb_query <<EOF &
 update history set 
       exit_status = ${retval}, 
       duration = ${finished} - start_time
@@ -68,6 +68,7 @@ where id = (select max(id) from history) and
       session = ${HISTDB_SESSION} and
       exit_status is NULL;
 EOF
+    )
 }

 zshaddhistory () {
@@ -85,7 +86,7 @@ zshaddhistory () {
     _histdb_init

     if [[ "$cmd" != "''" ]]; then
-        _histdb_query \
+        (_histdb_query \
 "insert into commands (argv) values (${cmd});
 insert into places   (host, dir) values (${HISTDB_HOST}, ${pwd});
 insert into history
@@ -101,7 +102,7 @@ where
   commands.argv = ${cmd} and
   places.host = ${HISTDB_HOST} and
   places.dir = ${pwd}
-;"
+;" & )
     fi
     return 0
 }

The only problem I can see here is that it maybe could cause histdb-update-outcome to run before the insert in zshaddhistory?

[phiresky]

Also, setting PRAGMA journal_mode = WAL seems to improve insert performance a lot (50% maybe)

Here are some other settings that usually improve performance, but most of these do not persist:

pragma temp_store = memory;
pragma journal_mode = WAL;
pragma synchronous = normal;
pragma cache_size=-30000;
pragma optimize;

[larkery]

I like this, but I will have to meditate a little on the update outcome race.

[phiresky]

I think this was fixed here: https://github.com/larkery/zsh-histdb/commit/873610f13d2f6568363cfa9767bc0d3e75dbe2db