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 ./sqlite240 s2k.db <$sqlfile" 1] 54 set t [expr {[lindex $t 0]/1000000.0}] 55 puts [format $format {SQLite 2.4:} $t] 56 exec sync; after $delay; 57 set t [time "exec ./sqlite240 sns.db <$sqlfile" 1] 58 set t [expr {[lindex $t 0]/1000000.0}] 59 puts [format $format {SQLite 2.4 (nosync):} $t] 60# set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1] 61# set t [expr {[lindex $t 0]/1000000.0}] 62# puts [format $format {SQLite 2.4 (test):} $t] 63 puts "</table>" 64} 65 66# Initialize the environment 67# 68expr srand(1) 69catch {exec /bin/sh -c {rm -f s*.db}} 70set fd [open clear.sql w] 71puts $fd { 72 drop table t1; 73 drop table t2; 74} 75close $fd 76catch {exec psql drh <clear.sql} 77catch {exec mysql drh <clear.sql} 78set fd [open 2kinit.sql w] 79puts $fd { 80 PRAGMA default_cache_size=2000; 81 PRAGMA default_synchronous=on; 82} 83close $fd 84exec ./sqlite240 s2k.db <2kinit.sql 85exec ./sqlite-t1 st1.db <2kinit.sql 86set fd [open nosync-init.sql w] 87puts $fd { 88 PRAGMA default_cache_size=2000; 89 PRAGMA default_synchronous=off; 90} 91close $fd 92exec ./sqlite240 sns.db <nosync-init.sql 93set ones {zero one two three four five six seven eight nine 94 ten eleven twelve thirteen fourteen fifteen sixteen seventeen 95 eighteen nineteen} 96set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} 97proc number_name {n} { 98 if {$n>=1000} { 99 set txt "[number_name [expr {$n/1000}]] thousand" 100 set n [expr {$n%1000}] 101 } else { 102 set txt {} 103 } 104 if {$n>=100} { 105 append txt " [lindex $::ones [expr {$n/100}]] hundred" 106 set n [expr {$n%100}] 107 } 108 if {$n>=20} { 109 append txt " [lindex $::tens [expr {$n/10}]]" 110 set n [expr {$n%10}] 111 } 112 if {$n>0} { 113 append txt " [lindex $::ones $n]" 114 } 115 set txt [string trim $txt] 116 if {$txt==""} {set txt zero} 117 return $txt 118} 119 120 121set fd [open test$cnt.sql w] 122puts $fd "BEGIN;" 123puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" 124for {set i 1} {$i<=25000} {incr i} { 125 set r [expr {int(rand()*500000)}] 126 puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 127} 128puts $fd "COMMIT;" 129close $fd 130runtest {25000 INSERTs in a transaction} 131 132 133set fd [open test$cnt.sql w] 134puts $fd "DELETE FROM t1;" 135close $fd 136runtest {DELETE everything} 137 138 139set fd [open test$cnt.sql w] 140puts $fd "BEGIN;" 141for {set i 1} {$i<=25000} {incr i} { 142 set r [expr {int(rand()*500000)}] 143 puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 144} 145puts $fd "COMMIT;" 146close $fd 147runtest {25000 INSERTs in a transaction} 148 149 150set fd [open test$cnt.sql w] 151puts $fd "DELETE FROM t1;" 152close $fd 153runtest {DELETE everything} 154 155 156set fd [open test$cnt.sql w] 157puts $fd "BEGIN;" 158for {set i 1} {$i<=25000} {incr i} { 159 set r [expr {int(rand()*500000)}] 160 puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 161} 162puts $fd "COMMIT;" 163close $fd 164runtest {25000 INSERTs in a transaction} 165 166 167set fd [open test$cnt.sql w] 168puts $fd "DELETE FROM t1;" 169close $fd 170runtest {DELETE everything} 171 172 173set fd [open test$cnt.sql w] 174puts $fd "BEGIN;" 175for {set i 1} {$i<=25000} {incr i} { 176 set r [expr {int(rand()*500000)}] 177 puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 178} 179puts $fd "COMMIT;" 180close $fd 181runtest {25000 INSERTs in a transaction} 182 183 184set fd [open test$cnt.sql w] 185puts $fd "DELETE FROM t1;" 186close $fd 187runtest {DELETE everything} 188 189 190set fd [open test$cnt.sql w] 191puts $fd "BEGIN;" 192for {set i 1} {$i<=25000} {incr i} { 193 set r [expr {int(rand()*500000)}] 194 puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 195} 196puts $fd "COMMIT;" 197close $fd 198runtest {25000 INSERTs in a transaction} 199 200 201set fd [open test$cnt.sql w] 202puts $fd "DELETE FROM t1;" 203close $fd 204runtest {DELETE everything} 205 206 207set fd [open test$cnt.sql w] 208puts $fd {DROP TABLE t1;} 209close $fd 210runtest {DROP TABLE} 211