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 ./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# 65expr srand(1) 66catch {exec /bin/sh -c {rm -f s*.db}} 67set fd [open clear.sql w] 68puts $fd { 69 drop table t1; 70 drop table t2; 71} 72close $fd 73catch {exec psql drh <clear.sql} 74catch {exec mysql drh <clear.sql} 75set fd [open 2kinit.sql w] 76puts $fd { 77 PRAGMA default_cache_size=2000; 78 PRAGMA default_synchronous=on; 79} 80close $fd 81exec ./sqlite240 s2k.db <2kinit.sql 82exec ./sqlite-t1 st1.db <2kinit.sql 83set fd [open nosync-init.sql w] 84puts $fd { 85 PRAGMA default_cache_size=2000; 86 PRAGMA default_synchronous=off; 87} 88close $fd 89exec ./sqlite240 sns.db <nosync-init.sql 90set ones {zero one two three four five six seven eight nine 91 ten eleven twelve thirteen fourteen fifteen sixteen seventeen 92 eighteen nineteen} 93set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} 94proc 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 118set fd [open test$cnt.sql w] 119puts $fd "BEGIN;" 120puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" 121for {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} 125puts $fd "COMMIT;" 126close $fd 127runtest {25000 INSERTs in a transaction} 128 129 130set fd [open test$cnt.sql w] 131puts $fd "DELETE FROM t1;" 132close $fd 133runtest {DELETE everything} 134 135 136set fd [open test$cnt.sql w] 137puts $fd "BEGIN;" 138for {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} 142puts $fd "COMMIT;" 143close $fd 144runtest {25000 INSERTs in a transaction} 145 146 147set fd [open test$cnt.sql w] 148puts $fd "DELETE FROM t1;" 149close $fd 150runtest {DELETE everything} 151 152 153set fd [open test$cnt.sql w] 154puts $fd "BEGIN;" 155for {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} 159puts $fd "COMMIT;" 160close $fd 161runtest {25000 INSERTs in a transaction} 162 163 164set fd [open test$cnt.sql w] 165puts $fd "DELETE FROM t1;" 166close $fd 167runtest {DELETE everything} 168 169 170set fd [open test$cnt.sql w] 171puts $fd "BEGIN;" 172for {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} 176puts $fd "COMMIT;" 177close $fd 178runtest {25000 INSERTs in a transaction} 179 180 181set fd [open test$cnt.sql w] 182puts $fd "DELETE FROM t1;" 183close $fd 184runtest {DELETE everything} 185 186 187set fd [open test$cnt.sql w] 188puts $fd "BEGIN;" 189for {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} 193puts $fd "COMMIT;" 194close $fd 195runtest {25000 INSERTs in a transaction} 196 197 198set fd [open test$cnt.sql w] 199puts $fd "DELETE FROM t1;" 200close $fd 201runtest {DELETE everything} 202 203 204set fd [open test$cnt.sql w] 205puts $fd {DROP TABLE t1;} 206close $fd 207runtest {DROP TABLE} 208