Annotation of embedaddon/sqlite3/tool/speedtest.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 ./sqlite248 s2k.db <$sqlfile" 1]
! 51: set t [expr {[lindex $t 0]/1000000.0}]
! 52: puts [format $format {SQLite 2.4.8:} $t]
! 53: exec sync; after $delay;
! 54: set t [time "exec ./sqlite248 sns.db <$sqlfile" 1]
! 55: set t [expr {[lindex $t 0]/1000000.0}]
! 56: puts [format $format {SQLite 2.4.8 (nosync):} $t]
! 57: exec sync; after $delay;
! 58: set t [time "exec ./sqlite2412 s2kb.db <$sqlfile" 1]
! 59: set t [expr {[lindex $t 0]/1000000.0}]
! 60: puts [format $format {SQLite 2.4.12:} $t]
! 61: exec sync; after $delay;
! 62: set t [time "exec ./sqlite2412 snsb.db <$sqlfile" 1]
! 63: set t [expr {[lindex $t 0]/1000000.0}]
! 64: puts [format $format {SQLite 2.4.12 (nosync):} $t]
! 65: # set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
! 66: # set t [expr {[lindex $t 0]/1000000.0}]
! 67: # puts [format $format {SQLite 2.4 (test):} $t]
! 68: puts "</table>"
! 69: }
! 70:
! 71: # Initialize the environment
! 72: #
! 73: expr srand(1)
! 74: catch {exec /bin/sh -c {rm -f s*.db}}
! 75: set fd [open clear.sql w]
! 76: puts $fd {
! 77: drop table t1;
! 78: drop table t2;
! 79: }
! 80: close $fd
! 81: catch {exec psql drh <clear.sql}
! 82: catch {exec mysql drh <clear.sql}
! 83: set fd [open 2kinit.sql w]
! 84: puts $fd {
! 85: PRAGMA default_cache_size=2000;
! 86: PRAGMA default_synchronous=on;
! 87: }
! 88: close $fd
! 89: exec ./sqlite248 s2k.db <2kinit.sql
! 90: exec ./sqlite2412 s2kb.db <2kinit.sql
! 91: set fd [open nosync-init.sql w]
! 92: puts $fd {
! 93: PRAGMA default_cache_size=2000;
! 94: PRAGMA default_synchronous=off;
! 95: }
! 96: close $fd
! 97: exec ./sqlite248 sns.db <nosync-init.sql
! 98: exec ./sqlite2412 snsb.db <nosync-init.sql
! 99: set ones {zero one two three four five six seven eight nine
! 100: ten eleven twelve thirteen fourteen fifteen sixteen seventeen
! 101: eighteen nineteen}
! 102: set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
! 103: proc number_name {n} {
! 104: if {$n>=1000} {
! 105: set txt "[number_name [expr {$n/1000}]] thousand"
! 106: set n [expr {$n%1000}]
! 107: } else {
! 108: set txt {}
! 109: }
! 110: if {$n>=100} {
! 111: append txt " [lindex $::ones [expr {$n/100}]] hundred"
! 112: set n [expr {$n%100}]
! 113: }
! 114: if {$n>=20} {
! 115: append txt " [lindex $::tens [expr {$n/10}]]"
! 116: set n [expr {$n%10}]
! 117: }
! 118: if {$n>0} {
! 119: append txt " [lindex $::ones $n]"
! 120: }
! 121: set txt [string trim $txt]
! 122: if {$txt==""} {set txt zero}
! 123: return $txt
! 124: }
! 125:
! 126:
! 127:
! 128: set fd [open test$cnt.sql w]
! 129: puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
! 130: for {set i 1} {$i<=1000} {incr i} {
! 131: set r [expr {int(rand()*100000)}]
! 132: puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
! 133: }
! 134: close $fd
! 135: runtest {1000 INSERTs}
! 136:
! 137:
! 138:
! 139: set fd [open test$cnt.sql w]
! 140: puts $fd "BEGIN;"
! 141: puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));"
! 142: for {set i 1} {$i<=25000} {incr i} {
! 143: set r [expr {int(rand()*500000)}]
! 144: puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');"
! 145: }
! 146: puts $fd "COMMIT;"
! 147: close $fd
! 148: runtest {25000 INSERTs in a transaction}
! 149:
! 150:
! 151:
! 152: set fd [open test$cnt.sql w]
! 153: for {set i 0} {$i<100} {incr i} {
! 154: set lwr [expr {$i*100}]
! 155: set upr [expr {($i+10)*100}]
! 156: puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
! 157: }
! 158: close $fd
! 159: runtest {100 SELECTs without an index}
! 160:
! 161:
! 162:
! 163: set fd [open test$cnt.sql w]
! 164: for {set i 1} {$i<=100} {incr i} {
! 165: puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';"
! 166: }
! 167: close $fd
! 168: runtest {100 SELECTs on a string comparison}
! 169:
! 170:
! 171:
! 172: set fd [open test$cnt.sql w]
! 173: puts $fd {CREATE INDEX i2a ON t2(a);}
! 174: puts $fd {CREATE INDEX i2b ON t2(b);}
! 175: close $fd
! 176: runtest {Creating an index}
! 177:
! 178:
! 179:
! 180: set fd [open test$cnt.sql w]
! 181: for {set i 0} {$i<5000} {incr i} {
! 182: set lwr [expr {$i*100}]
! 183: set upr [expr {($i+1)*100}]
! 184: puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
! 185: }
! 186: close $fd
! 187: runtest {5000 SELECTs with an index}
! 188:
! 189:
! 190:
! 191: set fd [open test$cnt.sql w]
! 192: puts $fd "BEGIN;"
! 193: for {set i 0} {$i<1000} {incr i} {
! 194: set lwr [expr {$i*10}]
! 195: set upr [expr {($i+1)*10}]
! 196: puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
! 197: }
! 198: puts $fd "COMMIT;"
! 199: close $fd
! 200: runtest {1000 UPDATEs without an index}
! 201:
! 202:
! 203:
! 204: set fd [open test$cnt.sql w]
! 205: puts $fd "BEGIN;"
! 206: for {set i 1} {$i<=25000} {incr i} {
! 207: set r [expr {int(rand()*500000)}]
! 208: puts $fd "UPDATE t2 SET b=$r WHERE a=$i;"
! 209: }
! 210: puts $fd "COMMIT;"
! 211: close $fd
! 212: runtest {25000 UPDATEs with an index}
! 213:
! 214:
! 215: set fd [open test$cnt.sql w]
! 216: puts $fd "BEGIN;"
! 217: for {set i 1} {$i<=25000} {incr i} {
! 218: set r [expr {int(rand()*500000)}]
! 219: puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;"
! 220: }
! 221: puts $fd "COMMIT;"
! 222: close $fd
! 223: runtest {25000 text UPDATEs with an index}
! 224:
! 225:
! 226:
! 227: set fd [open test$cnt.sql w]
! 228: puts $fd "BEGIN;"
! 229: puts $fd "INSERT INTO t1 SELECT * FROM t2;"
! 230: puts $fd "INSERT INTO t2 SELECT * FROM t1;"
! 231: puts $fd "COMMIT;"
! 232: close $fd
! 233: runtest {INSERTs from a SELECT}
! 234:
! 235:
! 236:
! 237: set fd [open test$cnt.sql w]
! 238: puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';}
! 239: close $fd
! 240: runtest {DELETE without an index}
! 241:
! 242:
! 243:
! 244: set fd [open test$cnt.sql w]
! 245: puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;}
! 246: close $fd
! 247: runtest {DELETE with an index}
! 248:
! 249:
! 250:
! 251: set fd [open test$cnt.sql w]
! 252: puts $fd {INSERT INTO t2 SELECT * FROM t1;}
! 253: close $fd
! 254: runtest {A big INSERT after a big DELETE}
! 255:
! 256:
! 257:
! 258: set fd [open test$cnt.sql w]
! 259: puts $fd {BEGIN;}
! 260: puts $fd {DELETE FROM t1;}
! 261: for {set i 1} {$i<=3000} {incr i} {
! 262: set r [expr {int(rand()*100000)}]
! 263: puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
! 264: }
! 265: puts $fd {COMMIT;}
! 266: close $fd
! 267: runtest {A big DELETE followed by many small INSERTs}
! 268:
! 269:
! 270:
! 271: set fd [open test$cnt.sql w]
! 272: puts $fd {DROP TABLE t1;}
! 273: puts $fd {DROP TABLE t2;}
! 274: close $fd
! 275: runtest {DROP TABLE}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speedtest.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