Database hello_world contains table world with 10000 records and two columns id, randomnumber. Here we are fetching 5 records in a loop. Using Tungsten 100 loops took about 22 on my VPS, whereas similar code using CL-POSTGRES runs 100000 loops in 40 second on the same machine.
CL-POSTGRES code uses get-a-random-record
defined like this:
(defun get-a-random-record (id)
(declare (fixnum id))
(unless *prepared*
(cl-postgres:prepare-query
postmodern:*database*
"get-a-random-record"
"SELECT randomnumber FROM world WHERE id = $1"
'(100))
(setf *prepared* t))
(cl-postgres:exec-prepared
postmodern:*database*
"get-a-random-record"
(list id)
'cl-postgres:list-row-reader))
(defun get-some-random-records (n)
(declare (fixnum n))
(loop repeat n
for id fixnum = (1+ (random 10000))
do (get-a-random-record id)))
(postmodern:with-connection (list "hello_world" "benchmarkdbuser"
"benchmarkdbpass" *db-host* :port *db-port*
:use-binary t :pooled-p t)
(loop repeat 100000
do (get-some-random-records-5 5)))
There is a simple way to define such fetching function using Postmodern:
(postmodern:defprepared get-a-random-record
(:select 'randomnumber
:from 'world
:where (:= 'id '$1))
:single)
and it shows the same performance:
CL-USER> (time
(with-binary-connection ()
(loop repeat 100000
do (get-some-random-records-4 5))))
Evaluation took:
40.616 seconds of real time
20.493448 seconds of total run time (6.930499 user, 13.562949 system)
[ Run times consist of 0.021 seconds GC time, and 20.473 seconds non-GC time. ]
50.46% CPU
81,040,620,754 processor cycles
559,914,672 bytes consed