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