?? speedtest.tcl
字號:
#!/usr/bin/tclsh## Run this script using TCLSH to do a speed comparison between# various versions of SQLite and PostgreSQL and MySQL## Run a test#set cnt 1proc runtest {title} { global cnt set sqlfile test$cnt.sql puts "<h2>Test $cnt: $title</h2>" incr cnt set fd [open $sqlfile r] set sql [string trim [read $fd [file size $sqlfile]]] close $fd set sx [split $sql \n] set n [llength $sx] if {$n>8} { set sql {} for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n} append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n" for {set i [expr {$n-3}]} {$i<$n} {incr i} { append sql [lindex $sx $i]<br>\n } } else { regsub -all \n [string trim $sql] <br> sql } puts "<blockquote>" puts "$sql" puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>" set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} set delay 1000# exec sync; after $delay;# set t [time "exec psql drh <$sqlfile" 1]# set t [expr {[lindex $t 0]/1000000.0}]# puts [format $format PostgreSQL: $t] exec sync; after $delay; set t [time "exec mysql -f drh <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format MySQL: $t]# set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]# set t [expr {[lindex $t 0]/1000000.0}]# puts [format $format {SQLite 2.3.2:} $t]# set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]# set t [expr {[lindex $t 0]/1000000.0}]# puts [format $format {SQLite 2.4 (cache=100):} $t] exec sync; after $delay; set t [time "exec ./sqlite248 s2k.db <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format {SQLite 2.4.8:} $t] exec sync; after $delay; set t [time "exec ./sqlite248 sns.db <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format {SQLite 2.4.8 (nosync):} $t] exec sync; after $delay; set t [time "exec ./sqlite2412 s2kb.db <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format {SQLite 2.4.12:} $t] exec sync; after $delay; set t [time "exec ./sqlite2412 snsb.db <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format {SQLite 2.4.12 (nosync):} $t]# set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]# set t [expr {[lindex $t 0]/1000000.0}]# puts [format $format {SQLite 2.4 (test):} $t] puts "</table>"}# Initialize the environment#expr srand(1)catch {exec /bin/sh -c {rm -f s*.db}}set fd [open clear.sql w]puts $fd { drop table t1; drop table t2;}close $fdcatch {exec psql drh <clear.sql}catch {exec mysql drh <clear.sql}set fd [open 2kinit.sql w]puts $fd { PRAGMA default_cache_size=2000; PRAGMA default_synchronous=on;}close $fdexec ./sqlite248 s2k.db <2kinit.sqlexec ./sqlite2412 s2kb.db <2kinit.sqlset fd [open nosync-init.sql w]puts $fd { PRAGMA default_cache_size=2000; PRAGMA default_synchronous=off;}close $fdexec ./sqlite248 sns.db <nosync-init.sqlexec ./sqlite2412 snsb.db <nosync-init.sqlset ones {zero one two three four five six seven eight nine ten eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen}set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}proc number_name {n} { if {$n>=1000} { set txt "[number_name [expr {$n/1000}]] thousand" set n [expr {$n%1000}] } else { set txt {} } if {$n>=100} { append txt " [lindex $::ones [expr {$n/100}]] hundred" set n [expr {$n%100}] } if {$n>=20} { append txt " [lindex $::tens [expr {$n/10}]]" set n [expr {$n%10}] } if {$n>0} { append txt " [lindex $::ones $n]" } set txt [string trim $txt] if {$txt==""} {set txt zero} return $txt}set fd [open test$cnt.sql w]puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"for {set i 1} {$i<=1000} {incr i} { set r [expr {int(rand()*100000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"}close $fdruntest {1000 INSERTs}set fd [open test$cnt.sql w]puts $fd "BEGIN;"puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));"for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');"}puts $fd "COMMIT;"close $fdruntest {25000 INSERTs in a transaction}set fd [open test$cnt.sql w]for {set i 0} {$i<100} {incr i} { set lwr [expr {$i*100}] set upr [expr {($i+10)*100}] puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"}close $fdruntest {100 SELECTs without an index}set fd [open test$cnt.sql w]for {set i 1} {$i<=100} {incr i} { puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';"}close $fdruntest {100 SELECTs on a string comparison}set fd [open test$cnt.sql w]puts $fd {CREATE INDEX i2a ON t2(a);}puts $fd {CREATE INDEX i2b ON t2(b);}close $fdruntest {Creating an index}set fd [open test$cnt.sql w]for {set i 0} {$i<5000} {incr i} { set lwr [expr {$i*100}] set upr [expr {($i+1)*100}] puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"}close $fdruntest {5000 SELECTs with an index}set fd [open test$cnt.sql w]puts $fd "BEGIN;"for {set i 0} {$i<1000} {incr i} { set lwr [expr {$i*10}] set upr [expr {($i+1)*10}] puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"}puts $fd "COMMIT;"close $fdruntest {1000 UPDATEs without an index}set fd [open test$cnt.sql w]puts $fd "BEGIN;"for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "UPDATE t2 SET b=$r WHERE a=$i;"}puts $fd "COMMIT;"close $fdruntest {25000 UPDATEs with an index}set fd [open test$cnt.sql w]puts $fd "BEGIN;"for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;"}puts $fd "COMMIT;"close $fdruntest {25000 text UPDATEs with an index}set fd [open test$cnt.sql w]puts $fd "BEGIN;"puts $fd "INSERT INTO t1 SELECT * FROM t2;"puts $fd "INSERT INTO t2 SELECT * FROM t1;"puts $fd "COMMIT;"close $fdruntest {INSERTs from a SELECT}set fd [open test$cnt.sql w]puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';}close $fdruntest {DELETE without an index}set fd [open test$cnt.sql w]puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;}close $fdruntest {DELETE with an index}set fd [open test$cnt.sql w]puts $fd {INSERT INTO t2 SELECT * FROM t1;}close $fdruntest {A big INSERT after a big DELETE}set fd [open test$cnt.sql w]puts $fd {BEGIN;}puts $fd {DELETE FROM t1;}for {set i 1} {$i<=3000} {incr i} { set r [expr {int(rand()*100000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"}puts $fd {COMMIT;}close $fdruntest {A big DELETE followed by many small INSERTs}set fd [open test$cnt.sql w]puts $fd {DROP TABLE t1;}puts $fd {DROP TABLE t2;}close $fdruntest {DROP TABLE}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -