Db perf

# Db perf tests as discussed in the chat


#! /usr/local/bin/tclsh8.6

set fn t.sqlite

package require tdbc::sqlite3
namespace import tdbc::sqlite3::*

proc q {qry {out {0}}} {
	db foreach -as dicts q $qry { if {$out} { puts [lindex $q end] } } 
}
proc e {qry} {
	q "explain query plan $qry" yes
}

tdbc::sqlite3::connection create db $fn

timerate -calibrate {q "select 1 from t limit 1;"}

set qrys [dict create \
              1 {SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n
 LIMIT 20;}   2 {SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n
 LIMIT 200;}  3 {SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n
 LIMIT 2000;} 4 {SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n;}]


dict for {n qry} $qrys {
	puts q:$qry
	puts -nonewline e:
	e $qry
	puts t:[timerate {q $qry}]
	puts ""
}


db close

# EOF


if 0 {
q:SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n
 LIMIT 20;
e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
USE TEMP B-TREE FOR ORDER BY
t:344.686 µs/# 1796 # 2901.2 #/sec 619.056 net-ms

q:SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n
 LIMIT 200;
e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
USE TEMP B-TREE FOR ORDER BY
t:3325.70 µs/# 283 # 300.69 #/sec 941.172 net-ms

q:SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n
 LIMIT 2000;
e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
USE TEMP B-TREE FOR ORDER BY
t:33765.7 µs/# 30 # 29.616 #/sec 1012.971 net-ms

q:SELECT n, v
  FROM t
 WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
 ORDER BY n;
e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
USE TEMP B-TREE FOR ORDER BY
t:183268.2 µs/# 6 # 5.456 #/sec 1099.609 net-ms
}