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"> %.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} 276
