* COMMENT -*- mode: org -*-
#+Date: 2016-12-16
Time-stamp: <2019-02-23>

* database binding benchmark
I want to know the efficiency of the db binding for different language,
libraries and database servers.

- table:
  CREATE TABLE foo
  (id integer primary key,
   bar integer);
  INSERT INTO foo (id, bar) VALUES (1, 1);

- run many times:
  SELECT * FROM foo
  UPDATE foo SET bar=bar+1 WHERE id=1

* benchmark results on x201 laptop, postgres runs on 5400rpm SATA disk.
each entry has format: db_server, compiler, libraries
all build is release mode (with -O or equivalent).

| db    | language    | runtime       | lib               | select (s) | update (s) |
|-------+-------------+---------------+-------------------+------------+------------|
| pg9.4 | python      | cpython 2.7.9 | psycopg2          |       0.37 |       26.3 |
| pg9.4 | C           | gcc 4.9.2     | libpq5            |       0.32 |       26.5 |
| pg9.4 | haskell     | ghc 8.0.1     | postgresql-simple |       0.63 |       27.2 |
| pg9.4 | haskell     | ghc 7.10.3    | postgresql-simple |       0.61 |       26.6 |
| pg9.4 | rust        | rust 1.13.0   | postgres          |       0.50 |       26.3 |
| pg9.4 | go          | go 1.7.3      | pq                |       0.37 |       27.2 |
| pg9.4 | racket      | racket 6.5    | built-in db       |       1.99 |       26.7 |
| pg9.4 | common lisp | sbcl 1.2.11   | postmodern        |       0.57 |       27.0 |

- Remarks:
  - python and go's db binding is as fast as C.
  - rust, haskell, cl is slower. C is 1.5x faster.
  - racket is much slower. C is 5x faster than racket.

** postgres 9.4, cpython 2.7, psycopg2==2.6.1
http://initd.org/psycopg/docs/usage.html
0.35
28.77
--
0.38
26.15
--
0.37
26.35
** postgres 9.4, C, libpq5
https://www.postgresql.org/docs/9.4/static/libpq.html
0.33
26.6
--
0.32
26.5
--
0.30
26.2

//side note: why adding -shared in LDFLAGS will just segfault the program?
** postgres 9.4, lts-7.13, ghc 8.0.1, postgresql-simple
0.71
27.5
--
0.63
27.2
--
0.61
26.2
** postgres 9.4, lts-5.18, ghc 7.10.3, postgresql-simple
0.61
26.6
--
0.59
26.8
--
0.63
26.6
** postgres 9.4, rust 1.13.0, postgres 0.13
0.48
26.3
--
0.53
26.2
--
0.50
28.0
** postgres 9.4, go 1.7.3, pq
0.385
27.0
--
0.367
27.6
--
0.423
30.7
--
0.362
27.2
--
0.372
26.4
** postgres 9.4, racket 6.5
2.21
26.72
--
1.85
26.63
--
2.19
26.78

- I think I maybe testing disk IO performance. should use the pg bench cluster
  for benchmarking. bench cluster listen on port 5433.

  when running against bench cluster.
  result is
  2.15
  2.29
  --
  2.10
  2.21

  So write performance is definitely testing disk IO performance. Read
  performance is related to the language and runtime.
- try compile it with raco exe first.
  2.00
  27.84
  --
  2.02
  27.62

  it's about the same.

- try use query-rows instead of query.
  1.78
  26.94
  --
  2.02
  29.08
  --
  1.99
  26.79

  it's still the slowest.
** postgres 9.4, sbcl 1.2.11, postmodern 20160208
0.54
26.69
--
0.57
27.02
--
0.58
27.16

- try enable optimize
  C-c C-k with negative prefix

  time arithmetic doesn't matter.
  0.58
  27.28
  --
  0.54
  28.51
  no effect.

* benchmark result on new ryzen5 PC. postgres runs on M2 SSD.
** 2019-02-23 pg 9.6, python 2.7.13, psycopg2==2.6.2
INFO     running benchmark...
DEBUG    trace_time, func=select_test, duration=0.18
DEBUG    trace_time, func=update_test, duration=2.78
--
INFO     running benchmark...
DEBUG    trace_time, func=select_test, duration=0.19
DEBUG    trace_time, func=update_test, duration=2.76

** 2019-02-23 pg 9.6. openjdk 1.8.0_162, jdbc postgresql 42.2.5
warming up JVM...
select time cost: 0.29s
update time cost: 2.68s
--
warming up JVM...
select time cost: 0.27s
update time cost: 2.67s
--
select time cost: 0.35s
update time cost: 2.72s
--
select time cost: 0.33s
update time cost: 2.75s
--

** 2019-02-23 pg 9.6, ghc 8.2.2, lts 11.7
seconds: 0.249821547
seconds: 2.703614265
--
seconds: 0.252873544
seconds: 2.662445433
--
seconds: 0.244120763
seconds: 2.655961013
--

** 2019-02-23 pg 9.6, gcc 6.3.0, libpq-dev 10.3-1
0.170
2.598
--
0.172
2.551
--
0.178
2.562
--
0.172
2.589