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