1*c5c4113dSnw141292#!/usr/bin/tclsh 2*c5c4113dSnw141292# 3*c5c4113dSnw141292# Run this script using TCLSH to do a speed comparison between 4*c5c4113dSnw141292# various versions of SQLite and PostgreSQL and MySQL 5*c5c4113dSnw141292# 6*c5c4113dSnw141292 7*c5c4113dSnw141292# Run a test 8*c5c4113dSnw141292# 9*c5c4113dSnw141292set cnt 1 10*c5c4113dSnw141292proc runtest {title} { 11*c5c4113dSnw141292 global cnt 12*c5c4113dSnw141292 set sqlfile test$cnt.sql 13*c5c4113dSnw141292 puts "<h2>Test $cnt: $title</h2>" 14*c5c4113dSnw141292 incr cnt 15*c5c4113dSnw141292 set fd [open $sqlfile r] 16*c5c4113dSnw141292 set sql [string trim [read $fd [file size $sqlfile]]] 17*c5c4113dSnw141292 close $fd 18*c5c4113dSnw141292 set sx [split $sql \n] 19*c5c4113dSnw141292 set n [llength $sx] 20*c5c4113dSnw141292 if {$n>8} { 21*c5c4113dSnw141292 set sql {} 22*c5c4113dSnw141292 for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n} 23*c5c4113dSnw141292 append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n" 24*c5c4113dSnw141292 for {set i [expr {$n-3}]} {$i<$n} {incr i} { 25*c5c4113dSnw141292 append sql [lindex $sx $i]<br>\n 26*c5c4113dSnw141292 } 27*c5c4113dSnw141292 } else { 28*c5c4113dSnw141292 regsub -all \n [string trim $sql] <br> sql 29*c5c4113dSnw141292 } 30*c5c4113dSnw141292 puts "<blockquote>" 31*c5c4113dSnw141292 puts "$sql" 32*c5c4113dSnw141292 puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>" 33*c5c4113dSnw141292 set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} 34*c5c4113dSnw141292 set delay 1000 35*c5c4113dSnw141292# exec sync; after $delay; 36*c5c4113dSnw141292# set t [time "exec psql drh <$sqlfile" 1] 37*c5c4113dSnw141292# set t [expr {[lindex $t 0]/1000000.0}] 38*c5c4113dSnw141292# puts [format $format PostgreSQL: $t] 39*c5c4113dSnw141292 exec sync; after $delay; 40*c5c4113dSnw141292 set t [time "exec mysql -f drh <$sqlfile" 1] 41*c5c4113dSnw141292 set t [expr {[lindex $t 0]/1000000.0}] 42*c5c4113dSnw141292 puts [format $format MySQL: $t] 43*c5c4113dSnw141292# set t [time "exec ./sqlite232 s232.db <$sqlfile" 1] 44*c5c4113dSnw141292# set t [expr {[lindex $t 0]/1000000.0}] 45*c5c4113dSnw141292# puts [format $format {SQLite 2.3.2:} $t] 46*c5c4113dSnw141292# set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1] 47*c5c4113dSnw141292# set t [expr {[lindex $t 0]/1000000.0}] 48*c5c4113dSnw141292# puts [format $format {SQLite 2.4 (cache=100):} $t] 49*c5c4113dSnw141292 exec sync; after $delay; 50*c5c4113dSnw141292 set t [time "exec ./sqlite248 s2k.db <$sqlfile" 1] 51*c5c4113dSnw141292 set t [expr {[lindex $t 0]/1000000.0}] 52*c5c4113dSnw141292 puts [format $format {SQLite 2.4.8:} $t] 53*c5c4113dSnw141292 exec sync; after $delay; 54*c5c4113dSnw141292 set t [time "exec ./sqlite248 sns.db <$sqlfile" 1] 55*c5c4113dSnw141292 set t [expr {[lindex $t 0]/1000000.0}] 56*c5c4113dSnw141292 puts [format $format {SQLite 2.4.8 (nosync):} $t] 57*c5c4113dSnw141292 exec sync; after $delay; 58*c5c4113dSnw141292 set t [time "exec ./sqlite2412 s2kb.db <$sqlfile" 1] 59*c5c4113dSnw141292 set t [expr {[lindex $t 0]/1000000.0}] 60*c5c4113dSnw141292 puts [format $format {SQLite 2.4.12:} $t] 61*c5c4113dSnw141292 exec sync; after $delay; 62*c5c4113dSnw141292 set t [time "exec ./sqlite2412 snsb.db <$sqlfile" 1] 63*c5c4113dSnw141292 set t [expr {[lindex $t 0]/1000000.0}] 64*c5c4113dSnw141292 puts [format $format {SQLite 2.4.12 (nosync):} $t] 65*c5c4113dSnw141292# set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1] 66*c5c4113dSnw141292# set t [expr {[lindex $t 0]/1000000.0}] 67*c5c4113dSnw141292# puts [format $format {SQLite 2.4 (test):} $t] 68*c5c4113dSnw141292 puts "</table>" 69*c5c4113dSnw141292} 70*c5c4113dSnw141292 71*c5c4113dSnw141292# Initialize the environment 72*c5c4113dSnw141292# 73*c5c4113dSnw141292expr srand(1) 74*c5c4113dSnw141292catch {exec /bin/sh -c {rm -f s*.db}} 75*c5c4113dSnw141292set fd [open clear.sql w] 76*c5c4113dSnw141292puts $fd { 77*c5c4113dSnw141292 drop table t1; 78*c5c4113dSnw141292 drop table t2; 79*c5c4113dSnw141292} 80*c5c4113dSnw141292close $fd 81*c5c4113dSnw141292catch {exec psql drh <clear.sql} 82*c5c4113dSnw141292catch {exec mysql drh <clear.sql} 83*c5c4113dSnw141292set fd [open 2kinit.sql w] 84*c5c4113dSnw141292puts $fd { 85*c5c4113dSnw141292 PRAGMA default_cache_size=2000; 86*c5c4113dSnw141292 PRAGMA default_synchronous=on; 87*c5c4113dSnw141292} 88*c5c4113dSnw141292close $fd 89*c5c4113dSnw141292exec ./sqlite248 s2k.db <2kinit.sql 90*c5c4113dSnw141292exec ./sqlite2412 s2kb.db <2kinit.sql 91*c5c4113dSnw141292set fd [open nosync-init.sql w] 92*c5c4113dSnw141292puts $fd { 93*c5c4113dSnw141292 PRAGMA default_cache_size=2000; 94*c5c4113dSnw141292 PRAGMA default_synchronous=off; 95*c5c4113dSnw141292} 96*c5c4113dSnw141292close $fd 97*c5c4113dSnw141292exec ./sqlite248 sns.db <nosync-init.sql 98*c5c4113dSnw141292exec ./sqlite2412 snsb.db <nosync-init.sql 99*c5c4113dSnw141292set ones {zero one two three four five six seven eight nine 100*c5c4113dSnw141292 ten eleven twelve thirteen fourteen fifteen sixteen seventeen 101*c5c4113dSnw141292 eighteen nineteen} 102*c5c4113dSnw141292set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} 103*c5c4113dSnw141292proc number_name {n} { 104*c5c4113dSnw141292 if {$n>=1000} { 105*c5c4113dSnw141292 set txt "[number_name [expr {$n/1000}]] thousand" 106*c5c4113dSnw141292 set n [expr {$n%1000}] 107*c5c4113dSnw141292 } else { 108*c5c4113dSnw141292 set txt {} 109*c5c4113dSnw141292 } 110*c5c4113dSnw141292 if {$n>=100} { 111*c5c4113dSnw141292 append txt " [lindex $::ones [expr {$n/100}]] hundred" 112*c5c4113dSnw141292 set n [expr {$n%100}] 113*c5c4113dSnw141292 } 114*c5c4113dSnw141292 if {$n>=20} { 115*c5c4113dSnw141292 append txt " [lindex $::tens [expr {$n/10}]]" 116*c5c4113dSnw141292 set n [expr {$n%10}] 117*c5c4113dSnw141292 } 118*c5c4113dSnw141292 if {$n>0} { 119*c5c4113dSnw141292 append txt " [lindex $::ones $n]" 120*c5c4113dSnw141292 } 121*c5c4113dSnw141292 set txt [string trim $txt] 122*c5c4113dSnw141292 if {$txt==""} {set txt zero} 123*c5c4113dSnw141292 return $txt 124*c5c4113dSnw141292} 125*c5c4113dSnw141292 126*c5c4113dSnw141292 127*c5c4113dSnw141292 128*c5c4113dSnw141292set fd [open test$cnt.sql w] 129*c5c4113dSnw141292puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" 130*c5c4113dSnw141292for {set i 1} {$i<=1000} {incr i} { 131*c5c4113dSnw141292 set r [expr {int(rand()*100000)}] 132*c5c4113dSnw141292 puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 133*c5c4113dSnw141292} 134*c5c4113dSnw141292close $fd 135*c5c4113dSnw141292runtest {1000 INSERTs} 136*c5c4113dSnw141292 137*c5c4113dSnw141292 138*c5c4113dSnw141292 139*c5c4113dSnw141292set fd [open test$cnt.sql w] 140*c5c4113dSnw141292puts $fd "BEGIN;" 141*c5c4113dSnw141292puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));" 142*c5c4113dSnw141292for {set i 1} {$i<=25000} {incr i} { 143*c5c4113dSnw141292 set r [expr {int(rand()*500000)}] 144*c5c4113dSnw141292 puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');" 145*c5c4113dSnw141292} 146*c5c4113dSnw141292puts $fd "COMMIT;" 147*c5c4113dSnw141292close $fd 148*c5c4113dSnw141292runtest {25000 INSERTs in a transaction} 149*c5c4113dSnw141292 150*c5c4113dSnw141292 151*c5c4113dSnw141292 152*c5c4113dSnw141292set fd [open test$cnt.sql w] 153*c5c4113dSnw141292for {set i 0} {$i<100} {incr i} { 154*c5c4113dSnw141292 set lwr [expr {$i*100}] 155*c5c4113dSnw141292 set upr [expr {($i+10)*100}] 156*c5c4113dSnw141292 puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;" 157*c5c4113dSnw141292} 158*c5c4113dSnw141292close $fd 159*c5c4113dSnw141292runtest {100 SELECTs without an index} 160*c5c4113dSnw141292 161*c5c4113dSnw141292 162*c5c4113dSnw141292 163*c5c4113dSnw141292set fd [open test$cnt.sql w] 164*c5c4113dSnw141292for {set i 1} {$i<=100} {incr i} { 165*c5c4113dSnw141292 puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';" 166*c5c4113dSnw141292} 167*c5c4113dSnw141292close $fd 168*c5c4113dSnw141292runtest {100 SELECTs on a string comparison} 169*c5c4113dSnw141292 170*c5c4113dSnw141292 171*c5c4113dSnw141292 172*c5c4113dSnw141292set fd [open test$cnt.sql w] 173*c5c4113dSnw141292puts $fd {CREATE INDEX i2a ON t2(a);} 174*c5c4113dSnw141292puts $fd {CREATE INDEX i2b ON t2(b);} 175*c5c4113dSnw141292close $fd 176*c5c4113dSnw141292runtest {Creating an index} 177*c5c4113dSnw141292 178*c5c4113dSnw141292 179*c5c4113dSnw141292 180*c5c4113dSnw141292set fd [open test$cnt.sql w] 181*c5c4113dSnw141292for {set i 0} {$i<5000} {incr i} { 182*c5c4113dSnw141292 set lwr [expr {$i*100}] 183*c5c4113dSnw141292 set upr [expr {($i+1)*100}] 184*c5c4113dSnw141292 puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;" 185*c5c4113dSnw141292} 186*c5c4113dSnw141292close $fd 187*c5c4113dSnw141292runtest {5000 SELECTs with an index} 188*c5c4113dSnw141292 189*c5c4113dSnw141292 190*c5c4113dSnw141292 191*c5c4113dSnw141292set fd [open test$cnt.sql w] 192*c5c4113dSnw141292puts $fd "BEGIN;" 193*c5c4113dSnw141292for {set i 0} {$i<1000} {incr i} { 194*c5c4113dSnw141292 set lwr [expr {$i*10}] 195*c5c4113dSnw141292 set upr [expr {($i+1)*10}] 196*c5c4113dSnw141292 puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;" 197*c5c4113dSnw141292} 198*c5c4113dSnw141292puts $fd "COMMIT;" 199*c5c4113dSnw141292close $fd 200*c5c4113dSnw141292runtest {1000 UPDATEs without an index} 201*c5c4113dSnw141292 202*c5c4113dSnw141292 203*c5c4113dSnw141292 204*c5c4113dSnw141292set fd [open test$cnt.sql w] 205*c5c4113dSnw141292puts $fd "BEGIN;" 206*c5c4113dSnw141292for {set i 1} {$i<=25000} {incr i} { 207*c5c4113dSnw141292 set r [expr {int(rand()*500000)}] 208*c5c4113dSnw141292 puts $fd "UPDATE t2 SET b=$r WHERE a=$i;" 209*c5c4113dSnw141292} 210*c5c4113dSnw141292puts $fd "COMMIT;" 211*c5c4113dSnw141292close $fd 212*c5c4113dSnw141292runtest {25000 UPDATEs with an index} 213*c5c4113dSnw141292 214*c5c4113dSnw141292 215*c5c4113dSnw141292set fd [open test$cnt.sql w] 216*c5c4113dSnw141292puts $fd "BEGIN;" 217*c5c4113dSnw141292for {set i 1} {$i<=25000} {incr i} { 218*c5c4113dSnw141292 set r [expr {int(rand()*500000)}] 219*c5c4113dSnw141292 puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;" 220*c5c4113dSnw141292} 221*c5c4113dSnw141292puts $fd "COMMIT;" 222*c5c4113dSnw141292close $fd 223*c5c4113dSnw141292runtest {25000 text UPDATEs with an index} 224*c5c4113dSnw141292 225*c5c4113dSnw141292 226*c5c4113dSnw141292 227*c5c4113dSnw141292set fd [open test$cnt.sql w] 228*c5c4113dSnw141292puts $fd "BEGIN;" 229*c5c4113dSnw141292puts $fd "INSERT INTO t1 SELECT * FROM t2;" 230*c5c4113dSnw141292puts $fd "INSERT INTO t2 SELECT * FROM t1;" 231*c5c4113dSnw141292puts $fd "COMMIT;" 232*c5c4113dSnw141292close $fd 233*c5c4113dSnw141292runtest {INSERTs from a SELECT} 234*c5c4113dSnw141292 235*c5c4113dSnw141292 236*c5c4113dSnw141292 237*c5c4113dSnw141292set fd [open test$cnt.sql w] 238*c5c4113dSnw141292puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';} 239*c5c4113dSnw141292close $fd 240*c5c4113dSnw141292runtest {DELETE without an index} 241*c5c4113dSnw141292 242*c5c4113dSnw141292 243*c5c4113dSnw141292 244*c5c4113dSnw141292set fd [open test$cnt.sql w] 245*c5c4113dSnw141292puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;} 246*c5c4113dSnw141292close $fd 247*c5c4113dSnw141292runtest {DELETE with an index} 248*c5c4113dSnw141292 249*c5c4113dSnw141292 250*c5c4113dSnw141292 251*c5c4113dSnw141292set fd [open test$cnt.sql w] 252*c5c4113dSnw141292puts $fd {INSERT INTO t2 SELECT * FROM t1;} 253*c5c4113dSnw141292close $fd 254*c5c4113dSnw141292runtest {A big INSERT after a big DELETE} 255*c5c4113dSnw141292 256*c5c4113dSnw141292 257*c5c4113dSnw141292 258*c5c4113dSnw141292set fd [open test$cnt.sql w] 259*c5c4113dSnw141292puts $fd {BEGIN;} 260*c5c4113dSnw141292puts $fd {DELETE FROM t1;} 261*c5c4113dSnw141292for {set i 1} {$i<=3000} {incr i} { 262*c5c4113dSnw141292 set r [expr {int(rand()*100000)}] 263*c5c4113dSnw141292 puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 264*c5c4113dSnw141292} 265*c5c4113dSnw141292puts $fd {COMMIT;} 266*c5c4113dSnw141292close $fd 267*c5c4113dSnw141292runtest {A big DELETE followed by many small INSERTs} 268*c5c4113dSnw141292 269*c5c4113dSnw141292 270*c5c4113dSnw141292 271*c5c4113dSnw141292set fd [open test$cnt.sql w] 272*c5c4113dSnw141292puts $fd {DROP TABLE t1;} 273*c5c4113dSnw141292puts $fd {DROP TABLE t2;} 274*c5c4113dSnw141292close $fd 275*c5c4113dSnw141292runtest {DROP TABLE} 276