Annotation of embedaddon/sqlite3/tool/speedtest2.tcl, revision 1.1
1.1 ! misho 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}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speedtest2.tcl CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool