Posted to tcl by Stu at Thu Sep 08 17:54:13 GMT 2022view raw

  1. # Db perf tests as discussed in the chat
  2.  
  3.  
  4. #! /usr/local/bin/tclsh8.6
  5.  
  6. set fn t.sqlite
  7.  
  8. package require tdbc::sqlite3
  9. namespace import tdbc::sqlite3::*
  10.  
  11. proc q {qry {out {0}}} {
  12. db foreach -as dicts q $qry { if {$out} { puts [lindex $q end] } }
  13. }
  14. proc e {qry} {
  15. q "explain query plan $qry" yes
  16. }
  17.  
  18. tdbc::sqlite3::connection create db $fn
  19.  
  20. timerate -calibrate {q "select 1 from t limit 1;"}
  21.  
  22. set qrys [dict create \
  23. 1 {SELECT n, v
  24. FROM t
  25. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  26. ORDER BY n
  27. LIMIT 20;} 2 {SELECT n, v
  28. FROM t
  29. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  30. ORDER BY n
  31. LIMIT 200;} 3 {SELECT n, v
  32. FROM t
  33. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  34. ORDER BY n
  35. LIMIT 2000;} 4 {SELECT n, v
  36. FROM t
  37. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  38. ORDER BY n;}]
  39.  
  40.  
  41. dict for {n qry} $qrys {
  42. puts q:$qry
  43. puts -nonewline e:
  44. e $qry
  45. puts t:[timerate {q $qry}]
  46. puts ""
  47. }
  48.  
  49.  
  50. db close
  51.  
  52. # EOF
  53.  
  54.  
  55.  
  56. if 0 {
  57. q:SELECT n, v
  58. FROM t
  59. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  60. ORDER BY n
  61. LIMIT 20;
  62. e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
  63. USE TEMP B-TREE FOR ORDER BY
  64. t:344.686 µs/# 1796 # 2901.2 #/sec 619.056 net-ms
  65.  
  66. q:SELECT n, v
  67. FROM t
  68. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  69. ORDER BY n
  70. LIMIT 200;
  71. e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
  72. USE TEMP B-TREE FOR ORDER BY
  73. t:3325.70 µs/# 283 # 300.69 #/sec 941.172 net-ms
  74.  
  75. q:SELECT n, v
  76. FROM t
  77. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  78. ORDER BY n
  79. LIMIT 2000;
  80. e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
  81. USE TEMP B-TREE FOR ORDER BY
  82. t:33765.7 µs/# 30 # 29.616 #/sec 1012.971 net-ms
  83.  
  84. q:SELECT n, v
  85. FROM t
  86. WHERE v IN ('A', 'Q') AND n >= 'NAESDCEJWEJUNWEQFJYLJAWMRQUKO'
  87. ORDER BY n;
  88. e:SEARCH t USING COVERING INDEX tvn (v=? AND n>?)
  89. USE TEMP B-TREE FOR ORDER BY
  90. t:183268.2 µs/# 6 # 5.456 #/sec 1099.609 net-ms
  91. }