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