A small test checking Tungsten Postgres Driver in fetching random records from WebframeworkBenchmars' database

README.md

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

tungsten-pg-test.lisp

(uiop:define-package #:tungsten-test
  (:use #:cl))
(in-package #:tungsten-test)


(defun get-a-random-record (id)
  (postgresql:query "SELECT randomnumber from world where id = $1"
                    (list id)))


(defun get-some-random-records (n)
  (declare (fixnum n))
  (loop repeat n
        for id fixnum = (1+ (random 10000))
        do (get-a-random-record id)))

(defun test ()
  (time
   (postgresql:with-client (:user "benchmarkdbuser"
                            :password "benchmarkdbpass"
                            :database "hello_world"
                            :application-name "tungsten-test"
                            :max-connections 10)
     (loop repeat 100
           do (get-some-random-records 5)))))