usr/src/lib/libsqlite/tool/speedtest2.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 ./sqlite240 s2k.db <$sqlfile" 1]
  54   set t [expr {[lindex $t 0]/1000000.0}]
  55   puts [format $format {SQLite 2.4:} $t]
  56   exec sync; after $delay;
  57   set t [time "exec ./sqlite240 sns.db <$sqlfile" 1]
  58   set t [expr {[lindex $t 0]/1000000.0}]
  59   puts [format $format {SQLite 2.4 (nosync):} $t]
  60 #  set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
  61 #  set t [expr {[lindex $t 0]/1000000.0}]
  62 #  puts [format $format {SQLite 2.4 (test):} $t]
  63   puts "</table>"
  64 }
  65
  66 # Initialize the environment
  67 #
  68 expr srand(1)
  69 catch {exec /bin/sh -c {rm -f s*.db}}
  70 set fd [open clear.sql w]
  71 puts $fd {
  72   drop table t1;
  73   drop table t2;
  74 }
  75 close $fd
  76 catch {exec psql drh <clear.sql}
  77 catch {exec mysql drh <clear.sql}
  78 set fd [open 2kinit.sql w]
  79 puts $fd {
  80   PRAGMA default_cache_size=2000;
  81   PRAGMA default_synchronous=on;
  82 }
  83 close $fd
  84 exec ./sqlite240 s2k.db <2kinit.sql
  85 exec ./sqlite-t1 st1.db <2kinit.sql
  86 set fd [open nosync-init.sql w]
  87 puts $fd {
  88   PRAGMA default_cache_size=2000;
  89   PRAGMA default_synchronous=off;
  90 }
  91 close $fd
  92 exec ./sqlite240 sns.db <nosync-init.sql
  93 set ones {zero one two three four five six seven eight nine
  94           ten eleven twelve thirteen fourteen fifteen sixteen seventeen
  95           eighteen nineteen}
  96 set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
  97 proc number_name {n} {
  98   if {$n>=1000} {
  99     set txt "[number_name [expr {$n/1000}]] thousand"
 100     set n [expr {$n%1000}]
 101   } else {
 102     set txt {}
 103   }
 104   if {$n>=100} {
 105     append txt " [lindex $::ones [expr {$n/100}]] hundred"
 106     set n [expr {$n%100}]
 107   }
 108   if {$n>=20} {
 109     append txt " [lindex $::tens [expr {$n/10}]]"
 110     set n [expr {$n%10}]
 111   }
 112   if {$n>0} {
 113     append txt " [lindex $::ones $n]"
 114   }
 115   set txt [string trim $txt]
 116   if {$txt==""} {set txt zero}
 117   return $txt
 118 }
 119
 120
 121 set fd [open test$cnt.sql w]
 122 puts $fd "BEGIN;"
 123 puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
 124 for {set i 1} {$i<=25000} {incr i} {
 125   set r [expr {int(rand()*500000)}]
 126   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
 127 }
 128 puts $fd "COMMIT;"
 129 close $fd
 130 runtest {25000 INSERTs in a transaction}
 131
 132
 133 set fd [open test$cnt.sql w]
 134 puts $fd "DELETE FROM t1;"
 135 close $fd
 136 runtest {DELETE everything}
 137
 138
 139 set fd [open test$cnt.sql w]
 140 puts $fd "BEGIN;"
 141 for {set i 1} {$i<=25000} {incr i} {
 142   set r [expr {int(rand()*500000)}]
 143   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
 144 }
 145 puts $fd "COMMIT;"
 146 close $fd
 147 runtest {25000 INSERTs in a transaction}
 148
 149
 150 set fd [open test$cnt.sql w]
 151 puts $fd "DELETE FROM t1;"
 152 close $fd
 153 runtest {DELETE everything}
 154
 155
 156 set fd [open test$cnt.sql w]
 157 puts $fd "BEGIN;"
 158 for {set i 1} {$i<=25000} {incr i} {
 159   set r [expr {int(rand()*500000)}]
 160   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
 161 }
 162 puts $fd "COMMIT;"
 163 close $fd
 164 runtest {25000 INSERTs in a transaction}
 165
 166
 167 set fd [open test$cnt.sql w]
 168 puts $fd "DELETE FROM t1;"
 169 close $fd
 170 runtest {DELETE everything}
 171
 172
 173 set fd [open test$cnt.sql w]
 174 puts $fd "BEGIN;"
 175 for {set i 1} {$i<=25000} {incr i} {
 176   set r [expr {int(rand()*500000)}]
 177   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
 178 }
 179 puts $fd "COMMIT;"
 180 close $fd
 181 runtest {25000 INSERTs in a transaction}
 182
 183
 184 set fd [open test$cnt.sql w]
 185 puts $fd "DELETE FROM t1;"
 186 close $fd
 187 runtest {DELETE everything}
 188
 189
 190 set fd [open test$cnt.sql w]
 191 puts $fd "BEGIN;"
 192 for {set i 1} {$i<=25000} {incr i} {
 193   set r [expr {int(rand()*500000)}]
 194   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
 195 }
 196 puts $fd "COMMIT;"
 197 close $fd
 198 runtest {25000 INSERTs in a transaction}
 199
 200
 201 set fd [open test$cnt.sql w]
 202 puts $fd "DELETE FROM t1;"
 203 close $fd
 204 runtest {DELETE everything}
 205
 206
 207 set fd [open test$cnt.sql w]
 208 puts $fd {DROP TABLE t1;}
 209 close $fd
 210 runtest {DROP TABLE}