SQLite surprisingly slow

SQLite surprisingly slow

[Eduardo Ochs]

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--

Re: SQLite surprisingly slow

[Tassilo Horn]

Eduardo Ochs <eduardoochs@gmail.com> writes:

Hi Eduardo,

> 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.

For me, everything is instant (thinkpad t440p from 2015):

(mapcar 'my-benchmark-elapse diskcmds)
;=> (9.2139e-05 0.000137043 0.000237876 7.6672e-05 6.8938e-05 2.6967e-05 1.9757e-05)

Ah, well, but that might be because my /tmp is tmpfs.  So I've tried
with a normal ext4 path for the database file.

;=> (8.3249e-05 0.003061723 0.016174854 0.012242847 0.014563132 5.3762e-05 3.2495e-05)

Well, it's still on a SSD, so it might be slower with spinning disks.
But a second for a CREATE TABLE, two INSERTS, and a SELECT seems too
long.  And one minute for 300 inserts sounds wrong, too.  But I have no
clue where to find the culprit.  Does it take that long when issuing the
SQL commands from the prompt you get with "sqlite3 /tmp/foo.db"?

I'm using sqlite 3.46.1 here.

Bye,
  Tassilo

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--