Re: SQLite surprisingly slow

[Eduardo Ochs]

On Wed, 2 Oct 2024 at 03:38, Tassilo Horn <tsdh@gnu.org> wrote:
> (...)

Hi Tassilo and all,

Basile Starynkevitch told me - in a private e-mail - that I could get
a big speedup if I wrapped all writes in a BEGIN/COMMIT block. It
worked, and the test is below; people on fast machines can try to
change the "(my-inserts 100)" by "(my-inserts 1000)" or "(my-inserts
10000)".

  Cheers! =)
    Eduardo Ochs

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

(defun my-insert1 (n)
  (let ((cmd (format "INSERT INTO tbl1 VALUES (%d, %d);"
     n (* 10 n))))
    `(sqlite-execute db ,cmd)))

(defun my-inserts (n)
  (cl-loop for i from 1 to n
   collect (my-insert1 i)))

;; Test: (my-insert1 1)
;;       (my-insert1 2)
;;       (my-inserts 4)

(setq my-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 my-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)))

(setq my-diskcmds2
  `((delete-file "/tmp/foo.db")
    (setq db (sqlite-open "/tmp/foo.db"))
    (sqlite-execute db "BEGIN;")
    (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-execute db "COMMIT;")
    (sqlite-select  db "SELECT * FROM tbl1;")
    (sqlite-close db)))

(setq my-diskcmds3
  `((delete-file "/tmp/foo.db")
    (setq db (sqlite-open "/tmp/foo.db"))
    (sqlite-execute db "BEGIN;")
    (sqlite-execute db "CREATE  TABLE tbl1 (col1, col2);")
    (progn ,@(my-inserts 100))
    (sqlite-execute db "COMMIT;")
    (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 my-memcmds)
(mapcar 'eval my-diskcmds)
(mapcar 'eval my-diskcmds2)
(mapcar 'eval my-diskcmds3)
;; (sqlite-mode-open-file "/tmp/foo.db")

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