SQLite surprisingly slow

[Eduardo Ochs <eduardoochs@gmail.com>]

Hi list,

I am trying to learn how to use SQLite from Emacs, but it is much
slower than I expected... my laptop is very old - it is a refurbished
Thinkpad T400, that is from 2008 in some sense - but my first "real"
program using SQLite took more than one minute to insert 300 rows, so
I obviously did something wrong in it...

Below is a self-contained miniature. Its sexps are intended to be
executed with the reader's favorite variant of C-e C-x C-e, and
executing the "diskcmds" _usually_ takes more than one second here.

What am I missing? Do I need to tell SQLite to change some flags?
Which ones? Pointers, please?

I am on Debian Oldstable ("Bookworm") and I tested the code below with
both emacs29 and emacs31, both compiled from the git repository some
weeks ago.

  Thanks in advance =(,
    Eduardo Ochs
    http://anggtwu.net/#eev
    http://anggtwu.net/eepitch.html

--snip--snip--
(require 'benchmark)
(require 'sqlite)
(require 'sqlite-mode)

(setq memcmds
  '((setq db (sqlite-open))
    (sqlite-execute db "CREATE  TABLE tbl1 (col1, col2);")
    (sqlite-execute db "INSERT   INTO tbl1 VALUES (10, 20);")
    (sqlite-execute db "INSERT   INTO tbl1 VALUES (30, 40);")
    (sqlite-select  db "SELECT * FROM tbl1;")
    (sqlite-close db)))

(setq diskcmds
  '((delete-file "/tmp/foo.db")
    (setq db (sqlite-open "/tmp/foo.db"))
    (sqlite-execute db "CREATE  TABLE tbl1 (col1, col2);")
    (sqlite-execute db "INSERT   INTO tbl1 VALUES (10, 20);")
    (sqlite-execute db "INSERT   INTO tbl1 VALUES (30, 40);")
    (sqlite-select  db "SELECT * FROM tbl1;")
    (sqlite-close db)))

(defun my-benchmark-elapse (&rest forms)
  (eval `(benchmark-elapse ,@forms)))

;; (delete-file "/tmp/foo.db")
(mapcar 'eval memcmds)
(mapcar 'eval diskcmds)
;; (sqlite-mode-open-file "/tmp/foo.db")

;; (delete-file "/tmp/foo.db")
(mapcar 'my-benchmark-elapse memcmds)
(mapcar 'my-benchmark-elapse diskcmds)
;; (sqlite-mode-open-file "/tmp/foo.db")
--snip--snip--