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# 12set cnt 1 13proc 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"> %.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# 76expr srand(1) 77catch {exec /bin/sh -c {rm -f s*.db}} 78set fd [open clear.sql w] 79puts $fd { 80 drop table t1; 81 drop table t2; 82} 83close $fd 84catch {exec psql drh <clear.sql} 85catch {exec mysql drh <clear.sql} 86set fd [open 2kinit.sql w] 87puts $fd { 88 PRAGMA default_cache_size=2000; 89 PRAGMA default_synchronous=on; 90} 91close $fd 92exec ./sqlite248 s2k.db <2kinit.sql 93exec ./sqlite2412 s2kb.db <2kinit.sql 94set fd [open nosync-init.sql w] 95puts $fd { 96 PRAGMA default_cache_size=2000; 97 PRAGMA default_synchronous=off; 98} 99close $fd 100exec ./sqlite248 sns.db <nosync-init.sql 101exec ./sqlite2412 snsb.db <nosync-init.sql 102set ones {zero one two three four five six seven eight nine 103 ten eleven twelve thirteen fourteen fifteen sixteen seventeen 104 eighteen nineteen} 105set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} 106proc 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 131set fd [open test$cnt.sql w] 132puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" 133for {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} 137close $fd 138runtest {1000 INSERTs} 139 140 141 142set fd [open test$cnt.sql w] 143puts $fd "BEGIN;" 144puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));" 145for {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} 149puts $fd "COMMIT;" 150close $fd 151runtest {25000 INSERTs in a transaction} 152 153 154 155set fd [open test$cnt.sql w] 156for {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} 161close $fd 162runtest {100 SELECTs without an index} 163 164 165 166set fd [open test$cnt.sql w] 167for {set i 1} {$i<=100} {incr i} { 168 puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';" 169} 170close $fd 171runtest {100 SELECTs on a string comparison} 172 173 174 175set fd [open test$cnt.sql w] 176puts $fd {CREATE INDEX i2a ON t2(a);} 177puts $fd {CREATE INDEX i2b ON t2(b);} 178close $fd 179runtest {Creating an index} 180 181 182 183set fd [open test$cnt.sql w] 184for {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} 189close $fd 190runtest {5000 SELECTs with an index} 191 192 193 194set fd [open test$cnt.sql w] 195puts $fd "BEGIN;" 196for {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} 201puts $fd "COMMIT;" 202close $fd 203runtest {1000 UPDATEs without an index} 204 205 206 207set fd [open test$cnt.sql w] 208puts $fd "BEGIN;" 209for {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} 213puts $fd "COMMIT;" 214close $fd 215runtest {25000 UPDATEs with an index} 216 217 218set fd [open test$cnt.sql w] 219puts $fd "BEGIN;" 220for {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} 224puts $fd "COMMIT;" 225close $fd 226runtest {25000 text UPDATEs with an index} 227 228 229 230set fd [open test$cnt.sql w] 231puts $fd "BEGIN;" 232puts $fd "INSERT INTO t1 SELECT * FROM t2;" 233puts $fd "INSERT INTO t2 SELECT * FROM t1;" 234puts $fd "COMMIT;" 235close $fd 236runtest {INSERTs from a SELECT} 237 238 239 240set fd [open test$cnt.sql w] 241puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';} 242close $fd 243runtest {DELETE without an index} 244 245 246 247set fd [open test$cnt.sql w] 248puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;} 249close $fd 250runtest {DELETE with an index} 251 252 253 254set fd [open test$cnt.sql w] 255puts $fd {INSERT INTO t2 SELECT * FROM t1;} 256close $fd 257runtest {A big INSERT after a big DELETE} 258 259 260 261set fd [open test$cnt.sql w] 262puts $fd {BEGIN;} 263puts $fd {DELETE FROM t1;} 264for {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} 268puts $fd {COMMIT;} 269close $fd 270runtest {A big DELETE followed by many small INSERTs} 271 272 273 274set fd [open test$cnt.sql w] 275puts $fd {DROP TABLE t1;} 276puts $fd {DROP TABLE t2;} 277close $fd 278runtest {DROP TABLE} 279