;; This file:
;;   http://anggtwu.net/SQLITE/surprisingly-slow-2.el.html
;;   http://anggtwu.net/SQLITE/surprisingly-slow-2.el
;;          (find-angg "SQLITE/surprisingly-slow-2.el")
;; Author: Eduardo Ochs <eduardoochs@gmail.com>
;;
;; See: (find-es "sqlite" "surprisingly-slow")
;;      (find-es "sqlite" "surprisingly-slow-2")
;;
;; (defun e () (interactive) (find-angg "SQLITE/surprisingly-slow-2.el"))

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

;; Test: (my-insert1 1)
;;       (my-insert1 2)
;;       (my-inserts 4)
;;
(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)))

;; Fast:    (find-eppp my-memcmds)
;; Slowest: (find-eppp my-diskcmds)
;;          (find-eppp my-diskcmds2)
;;          (find-epp  my-diskcmds3)
;;
(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")