SQL query limit/fetchSize profiling

benchmark_sql_limit.clj

(ns foo
  (:use [criterium.core])
  (:require [clojure.java.jdbc :as sql]
            [clojure.java.jdbc.internal :as sql-internal]))

(defn my-bench [desc f]
  (println (format "\n
Beginning benchmark for '%s'
-----------------------------------------------------" desc))
  (time (with-progress-reporting (bench (f)
                                   :verbose
;                                   :target-execution-time (* 10 60 1000 1000)
                                   )))
  (println "\n\n"))

(defn build-statement [sql]
  (sql-internal/prepare-statement* (:connection sql-internal/*db*) sql))

(defn execute-statement [stmt]
  (with-open [stmt stmt]
    (.executeQuery stmt)
    nil))

(defn noop-select []
  (execute-statement (build-statement "SELECT 1 as foo")))

(defn select-with-limit []
  (execute-statement (build-statement "select * from catalog_resources where type = 'File' LIMIT 1000")))

(defn select-with-fetch-size []
  (execute-statement
    (let [stmt (build-statement "select * from catalog_resources where type = 'File'")]
      (.setFetchSize stmt 1000)
      stmt)))

(defn dumb-select []
  (execute-statement (build-statement "select * from catalog_resources where type = 'File'")))



(sql/with-connection
  { :classname "org.postgresql.Driver"
    :subprotocol "postgresql"
    :subname "//localhost:5432/puppetdb_large"
    :user "user"
    :password "passwd"
    }
  (my-bench "no-op select" noop-select)
  (my-bench "select with limit" select-with-limit)
  (my-bench "dumb select" dumb-select)
  (my-bench "select with fetch size" select-with-fetch-size)

  )

(println "Done")

results.txt

Beginning benchmark for 'no-op select'
-----------------------------------------------------
Cleaning JVM allocations ...
Warming up for JIT optimisations ...
Estimating execution count ...
Running with sample-count 60 exec-count 8334 reducing results
Checking GC...
Cleaning JVM allocations ...
Finding outliers ...
Bootstrapping ...
Checking outlier significance
amd64 Linux 3.0.0-16-generic 8 cpu(s)
Java HotSpot(TM) 64-Bit Server VM 20.6-b01
Runtime arguments: -Didea.launcher.port=7538 -Didea.launcher.bin.path=/opt/software/idea/idea-IU-117.418/bin -Dfile.encoding=UTF-8
Evaluation count             : 500040
             Execution time mean : 141.127103 us  95.0% CI: (141.065158 us, 141.200794 us)
    Execution time std-deviation : 7.159292 us  95.0% CI: (7.080122 us, 7.238037 us)
         Execution time lower ci : 122.771105 us  95.0% CI: (122.771105 us, 122.771105 us)
         Execution time upper ci : 149.211951 us  95.0% CI: (149.211951 us, 149.288172 us)

Found 3 outliers in 60 samples (5.0000 %)
	low-severe	 2 (3.3333 %)
	low-mild	 1 (1.6667 %)
 Variance from outliers : 36.8464 % Variance is moderately inflated by outliers
"Elapsed time: 86086.996466 msecs"





Beginning benchmark for 'select with limit'
-----------------------------------------------------
Cleaning JVM allocations ...
Warming up for JIT optimisations ...
Estimating execution count ...
Running with sample-count 60 exec-count 253 reducing results
Checking GC...
Cleaning JVM allocations ...
Finding outliers ...
Bootstrapping ...
Checking outlier significance
amd64 Linux 3.0.0-16-generic 8 cpu(s)
Java HotSpot(TM) 64-Bit Server VM 20.6-b01
Runtime arguments: -Didea.launcher.port=7538 -Didea.launcher.bin.path=/opt/software/idea/idea-IU-117.418/bin -Dfile.encoding=UTF-8
Evaluation count             : 15180
             Execution time mean : 3.986181 ms  95.0% CI: (3.984358 ms, 3.987361 ms)
    Execution time std-deviation : 176.959378 us  95.0% CI: (176.207866 us, 177.682229 us)
         Execution time lower ci : 3.639350 ms  95.0% CI: (3.638858 ms, 3.639350 ms)
         Execution time upper ci : 4.183198 ms  95.0% CI: (4.182421 ms, 4.183198 ms)
"Elapsed time: 74328.503914 msecs"





Beginning benchmark for 'dumb select'
-----------------------------------------------------
Cleaning JVM allocations ...
Warming up for JIT optimisations ...
Estimating execution count ...
Running with sample-count 60 exec-count 1 reducing results
Checking GC...
Cleaning JVM allocations ...
Finding outliers ...
Bootstrapping ...
Checking outlier significance
amd64 Linux 3.0.0-16-generic 8 cpu(s)
Java HotSpot(TM) 64-Bit Server VM 20.6-b01
Runtime arguments: -Didea.launcher.port=7538 -Didea.launcher.bin.path=/opt/software/idea/idea-IU-117.418/bin -Dfile.encoding=UTF-8
Evaluation count             : 60
             Execution time mean : 1.373474 sec  95.0% CI: (1.372981 sec, 1.374063 sec)
    Execution time std-deviation : 56.110450 ms  95.0% CI: (55.548095 ms, 56.530332 ms)
         Execution time lower ci : 1.238060 sec  95.0% CI: (1.238060 sec, 1.246110 sec)
         Execution time upper ci : 1.432952 sec  95.0% CI: (1.432623 sec, 1.432952 sec)

Found 7 outliers in 60 samples (11.6667 %)
	low-severe	 7 (11.6667 %)
 Variance from outliers : 27.0807 % Variance is moderately inflated by outliers
"Elapsed time: 96212.804422 msecs"





Beginning benchmark for 'select with fetch size'
-----------------------------------------------------
Cleaning JVM allocations ...
Warming up for JIT optimisations ...
Estimating execution count ...
Running with sample-count 60 exec-count 1 reducing results
Checking GC...
Cleaning JVM allocations ...
Finding outliers ...
Bootstrapping ...
Checking outlier significance
amd64 Linux 3.0.0-16-generic 8 cpu(s)
Java HotSpot(TM) 64-Bit Server VM 20.6-b01
Runtime arguments: -Didea.launcher.port=7538 -Didea.launcher.bin.path=/opt/software/idea/idea-IU-117.418/bin -Dfile.encoding=UTF-8
Evaluation count             : 60
             Execution time mean : 1.358694 sec  95.0% CI: (1.358267 sec, 1.359193 sec)
    Execution time std-deviation : 67.307577 ms  95.0% CI: (66.965507 ms, 67.712235 ms)
         Execution time lower ci : 1.222011 sec  95.0% CI: (1.222011 sec, 1.222011 sec)
         Execution time upper ci : 1.426556 sec  95.0% CI: (1.426466 sec, 1.426556 sec)
"Elapsed time: 95923.536116 msecs"



Done