Adding rows to Sqlite3 is very slow - why?

[Mirko Vukovic]

This is on Windows 10, CCL, & Sqlite3 on a relatively modern laptop.

I learning how to interface to SQlite using CL-DBI.

As a learning problem I am transcribing Python code from
a Programming Praxis problem

(see https://programmingpraxis.com/2017/05/16/license-plates/#comment-64356)

The code inserts 1000 lines into a table and it takes 20 seconds!

I timed it, and lisp actually takes very little time. TIME outputs:

(CREATE-RANDOM-PLATES 1000)
took 23,083,000 microseconds (23.083000 seconds) to run.
During that period, and with 4 available CPU cores,
328,125 microseconds ( 0.328125 seconds) were spent in user mode
1,796,875 microseconds ( 1.796875 seconds) were spent in system mode
758,288 bytes of memory allocated.

During the run, the disk (SSD) is churning at 100% at 1.5 MB/s

I am looking for ideas as to what could be causing this problem.

I am posting the code in case someone wants to run it.

(require :cl-dbi)

(defconstant +letters+ "ABCDEFGHIJKLMNOPQRSTUVWXYZ")
(defconstant +digits+ "0123456789")

(defun random-3-letter-string ()
(let ((str (make-array 3 :element-type 'character)))
(dotimes (i 3)
(setf (aref str i)
(aref +letters+ (random 26))))
str))

(defun random-3-digit-string ()
(let (str)
(setf str (make-array 0 :element-type 'character :adjustable t :fill-pointer 0))
(dotimes (i 3)
(vector-push-extend
(aref +digits+ (random 10))
str))
str))

(defun make-random-plate ()
(concatenate 'string (random-3-letter-string) "-" (random-3-digit-string)))


(defun create-random-plates (&optional (number 10))
(when (probe-file "licenseP.db")
(delete-file "licenseP.db"))
(cl-dbi:with-connection (conn :sqlite3 :database-name "licenseP.db")
(let* ((table "LicensePlates")
(statement (format nil
"CREATE TABLE ~a (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Chr TEXT NOT NULL)" table))
(request (dbi:prepare conn statement)))
(dbi:execute request)
(dotimes (i number)
(let* ((lic-plate (make-random-plate))
(statement
(format nil "insert into ~a (Chr) values('~a')" table lic-plate))
(request (dbi:prepare conn statement)))
(dbi:execute request))))))


Thank you,

Mirko

[Dimitri Fontaine]

Mirko Vukovic <mirko....@gmail.com> writes:
> I learning how to interface to SQlite using CL-DBI.

Maybe you could work with PostgreSQL directly?

> (dotimes (i number)
> (let* ((lic-plate (make-random-plate))
> (statement
> (format nil "insert into ~a (Chr) values('~a')" table lic-plate))
> (request (dbi:prepare conn statement)))
> (dbi:execute request))))))

Prepare the query only once, then execute it with different bindings
each time, if that's supported. Then see about generating

insert into … values(), (), ()…;

With about 100 rows per statement, but maybe just the thousand of them
in one go.

Then check your transaction boundaries and make it all in a single
transaction, because you might be doing 1000 transactions here for all I
known, which would explain the SSD activity because of the WAL.

If you consider using PostgreSQL instead, which I do recommend. then
have a look at the COPY streaming protocol and tooling, and see about
the Postmodern Common Lisp driver.

Regards,
--
dim

[Mirko Vukovic]

I reformatted my code a bit to use parameters, and placed the loop with
insert statements into a transaction.

But now I don't get anything written into the database. I must be missing
something. I expanded the `with-transaction' macro, and traced the
commit command. That looks OK.

For info, here is the relevant code (remainder of code is in the first
post)

(defun create-random-plates-1 (&optional (number 10))
(when (probe-file "licenseP-wt.db")
(delete-file "licenseP-wt.db"))
(cl-dbi:with-connection (conn :sqlite3 :database-name "licenseP-wt.db")

(let* ((table "LicensePlates")
(statement (format nil
"CREATE TABLE ~a (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Chr TEXT NOT NULL)" table))

(request (dbi:prepare conn statement)))
(dbi:execute request)

(dbi:with-transaction conn
(let* ((request (dbi:prepare conn "INSERT INTO 'LicensePlates' (Chr) VALUES (?)")))
(dotimes (i number)
(let* ((lic-plate (make-random-plate)))
(dbi:execute request lic-plate))))))))

[Robert L.]

(use gauche.generator)
(use srfi-14) ;; character sets
(use data.random)

(define random-3-letter-string$
(strings-of 3 (chars$ char-set:upper-case)))

(define random-3-digit-string$ (strings-of 3 (chars$ char-set:digit)))

(map
(lambda(xs) (string-join xs "-"))
(generator->list
(tuples-of random-3-letter-string$ random-3-digit-string$)
9))

===>
("EWY-682" "QCW-482" "EKX-853" "UDX-339" "CHF-640" "VJW-301" "NNK-426"
"DHI-291" "IFN-574")


--
[E]verything that happens in the world is for the benefit of the Jewish
People.... G-D ... created the world for the sake of the Jewish People, and it
is our responsibility to implement the Torah---absolute morality and the
blueprint of creation---in it.
www.IsraelNationalNews.com/Articles/Article.aspx/2125#.UUnBZxx4xn8

[Mirko Vukovic]

What I was missing was that I was looking at an incorrect database file.
Things were working all along.

The world is sane:
- with-transaction works
- 1,000,000 insertions in a single transactions takes 4.9 seconds

Thank you Dima for reading my problem. I may transfer to another database
if my project requires it.

Mirko