tool/speedtest.tcl

   1 #!/usr/bin/tclsh
   2 #
   3 # Run this script using TCLSH to do a speed comparison between
   4 # various versions of SQLite and PostgreSQL and MySQL
   5 #
   6
   7 # Run a test
   8 #
   9 set cnt 1
  10 proc runtest {title} {
  11   global cnt
  12   set sqlfile test$cnt.sql
  13   puts "<h2>Test $cnt: $title</h2>"
  14   incr cnt
  15   set fd [open $sqlfile r]
  16   set sql [string trim [read $fd [file size $sqlfile]]]
  17   close $fd
  18   set sx [split $sql \n]
  19   set n [llength $sx]
  20   if {$n>8} {
  21     set sql {}
  22     for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n}
  23     append sql  "<i>... [expr {$n-6}] lines omitted</i><br>\n"
  24     for {set i [expr {$n-3}]} {$i<$n} {incr i} {
  25       append sql [lindex $sx $i]<br>\n
  26     }
  27   } else {
  28     regsub -all \n [string trim $sql] <br> sql
  29   }
  30   puts "<blockquote>"
  31   puts "$sql"
  32   puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>"
  33   set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
  34   set delay 1000
  35 #  exec sync; after $delay;
  36 #  set t [time "exec psql drh <$sqlfile" 1]
  37 #  set t [expr {[lindex $t 0]/1000000.0}]
  38 #  puts [format $format PostgreSQL: $t]
  39   exec sync; after $delay;
  40   set t [time "exec mysql -f drh <$sqlfile" 1]
  41   set t [expr {[lindex $t 0]/1000000.0}]
  42   puts [format $format MySQL: $t]
  43 #  set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
  44 #  set t [expr {[lindex $t 0]/1000000.0}]
  45 #  puts [format $format {SQLite 2.3.2:} $t]
  46 #  set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
  47 #  set t [expr {[lindex $t 0]/1000000.0}]
  48 #  puts [format $format {SQLite 2.4 (cache=100):} $t]
  49   exec sync; after $delay;
  50   set t [time "exec ./sqlite248 s2k.db <$sqlfile" 1]
  51   set t [expr {[lindex $t 0]/1000000.0}]
  52   puts [format $format {SQLite 2.4.8:} $t]
  53   exec sync; after $delay;
  54   set t [time "exec ./sqlite248 sns.db <$sqlfile" 1]
  55   set t [expr {[lindex $t 0]/1000000.0}]
  56   puts [format $format {SQLite 2.4.8 (nosync):} $t]
  57   exec sync; after $delay;
  58   set t [time "exec ./sqlite2412 s2kb.db <$sqlfile" 1]
  59   set t [expr {[lindex $t 0]/1000000.0}]
  60   puts [format $format {SQLite 2.4.12:} $t]
  61   exec sync; after $delay;
  62   set t [time "exec ./sqlite2412 snsb.db <$sqlfile" 1]
  63   set t [expr {[lindex $t 0]/1000000.0}]
  64   puts [format $format {SQLite 2.4.12 (nosync):} $t]
  65 #  set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
  66 #  set t [expr {[lindex $t 0]/1000000.0}]
  67 #  puts [format $format {SQLite 2.4 (test):} $t]
  68   puts "</table>"
  69 }
  70
  71 # Initialize the environment
  72 #
  73 expr srand(1)
  74 catch {exec /bin/sh -c {rm -f s*.db}}
  75 set fd [open clear.sql w]
  76 puts $fd {
  77   drop table t1;
  78   drop table t2;
  79 }
  80 close $fd
  81 catch {exec psql drh <clear.sql}
  82 catch {exec mysql drh <clear.sql}
  83 set fd [open 2kinit.sql w]
  84 puts $fd {
  85   PRAGMA default_cache_size=2000;
  86   PRAGMA default_synchronous=on;
  87 }
  88 close $fd
  89 exec ./sqlite248 s2k.db <2kinit.sql
  90 exec ./sqlite2412 s2kb.db <2kinit.sql
  91 set fd [open nosync-init.sql w]
  92 puts $fd {
  93   PRAGMA default_cache_size=2000;
  94   PRAGMA default_synchronous=off;
  95 }
  96 close $fd
  97 exec ./sqlite248 sns.db <nosync-init.sql
  98 exec ./sqlite2412 snsb.db <nosync-init.sql
  99 set ones {zero one two three four five six seven eight nine
 100           ten eleven twelve thirteen fourteen fifteen sixteen seventeen
 101           eighteen nineteen}
 102 set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
 103 proc number_name {n} {
 104   if {$n>=1000} {
 105     set txt "[number_name [expr {$n/1000}]] thousand"
 106     set n [expr {$n%1000}]
 107   } else {
 108     set txt {}
 109   }
 110   if {$n>=100} {
 111     append txt " [lindex $::ones [expr {$n/100}]] hundred"
 112     set n [expr {$n%100}]
 113   }
 114   if {$n>=20} {
 115     append txt " [lindex $::tens [expr {$n/10}]]"
 116     set n [expr {$n%10}]
 117   }
 118   if {$n>0} {
 119     append txt " [lindex $::ones $n]"
 120   }
 121   set txt [string trim $txt]
 122   if {$txt==""} {set txt zero}
 123   return $txt
 124 }
 125
 126
 127
 128 set fd [open test$cnt.sql w]
 129 puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
 130 for {set i 1} {$i<=1000} {incr i} {
 131   set r [expr {int(rand()*100000)}]
 132   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
 133 }
 134 close $fd
 135 runtest {1000 INSERTs}
 136
 137
 138
 139 set fd [open test$cnt.sql w]
 140 puts $fd "BEGIN;"
 141 puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));"
 142 for {set i 1} {$i<=25000} {incr i} {
 143   set r [expr {int(rand()*500000)}]
 144   puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');"
 145 }
 146 puts $fd "COMMIT;"
 147 close $fd
 148 runtest {25000 INSERTs in a transaction}
 149
 150
 151
 152 set fd [open test$cnt.sql w]
 153 for {set i 0} {$i<100} {incr i} {
 154   set lwr [expr {$i*100}]
 155   set upr [expr {($i+10)*100}]
 156   puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
 157 }
 158 close $fd
 159 runtest {100 SELECTs without an index}
 160
 161
 162
 163 set fd [open test$cnt.sql w]
 164 for {set i 1} {$i<=100} {incr i} {
 165   puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';"
 166 }
 167 close $fd
 168 runtest {100 SELECTs on a string comparison}
 169
 170
 171
 172 set fd [open test$cnt.sql w]
 173 puts $fd {CREATE INDEX i2a ON t2(a);}
 174 puts $fd {CREATE INDEX i2b ON t2(b);}
 175 close $fd
 176 runtest {Creating an index}
 177
 178
 179
 180 set fd [open test$cnt.sql w]
 181 for {set i 0} {$i<5000} {incr i} {
 182   set lwr [expr {$i*100}]
 183   set upr [expr {($i+1)*100}]
 184   puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
 185 }
 186 close $fd
 187 runtest {5000 SELECTs with an index}
 188
 189
 190
 191 set fd [open test$cnt.sql w]
 192 puts $fd "BEGIN;"
 193 for {set i 0} {$i<1000} {incr i} {
 194   set lwr [expr {$i*10}]
 195   set upr [expr {($i+1)*10}]
 196   puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
 197 }
 198 puts $fd "COMMIT;"
 199 close $fd
 200 runtest {1000 UPDATEs without an index}
 201
 202
 203
 204 set fd [open test$cnt.sql w]
 205 puts $fd "BEGIN;"
 206 for {set i 1} {$i<=25000} {incr i} {
 207   set r [expr {int(rand()*500000)}]
 208   puts $fd "UPDATE t2 SET b=$r WHERE a=$i;"
 209 }
 210 puts $fd "COMMIT;"
 211 close $fd
 212 runtest {25000 UPDATEs with an index}
 213
 214
 215 set fd [open test$cnt.sql w]
 216 puts $fd "BEGIN;"
 217 for {set i 1} {$i<=25000} {incr i} {
 218   set r [expr {int(rand()*500000)}]
 219   puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;"
 220 }
 221 puts $fd "COMMIT;"
 222 close $fd
 223 runtest {25000 text UPDATEs with an index}
 224
 225
 226
 227 set fd [open test$cnt.sql w]
 228 puts $fd "BEGIN;"
 229 puts $fd "INSERT INTO t1 SELECT * FROM t2;"
 230 puts $fd "INSERT INTO t2 SELECT * FROM t1;"
 231 puts $fd "COMMIT;"
 232 close $fd
 233 runtest {INSERTs from a SELECT}
 234
 235
 236
 237 set fd [open test$cnt.sql w]
 238 puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';}
 239 close $fd
 240 runtest {DELETE without an index}
 241
 242
 243
 244 set fd [open test$cnt.sql w]
 245 puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;}
 246 close $fd
 247 runtest {DELETE with an index}
 248
 249
 250
 251 set fd [open test$cnt.sql w]
 252 puts $fd {INSERT INTO t2 SELECT * FROM t1;}
 253 close $fd
 254 runtest {A big INSERT after a big DELETE}
 255
 256
 257
 258 set fd [open test$cnt.sql w]
 259 puts $fd {BEGIN;}
 260 puts $fd {DELETE FROM t1;}
 261 for {set i 1} {$i<=3000} {incr i} {
 262   set r [expr {int(rand()*100000)}]
 263   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
 264 }
 265 puts $fd {COMMIT;}
 266 close $fd
 267 runtest {A big DELETE followed by many small INSERTs}
 268
 269
 270
 271 set fd [open test$cnt.sql w]
 272 puts $fd {DROP TABLE t1;}
 273 puts $fd {DROP TABLE t2;}
 274 close $fd
 275 runtest {DROP TABLE}