usr/src/lib/libsqlite/tool/speedtest.tcl

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