| #!/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 1 |
| proc runtest {title} { |
| global cnt |
| incr cnt |
| } |
| |
| # Initialize the environment |
| # |
| expr srand(1) |
| set 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<=100} {incr i} { |
| set r [expr {int(rand()*100000)}] |
| puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" |
| } |
| close $fd |
| runtest {100 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<=2500} {incr i} { |
| set r [expr {int(rand()*500000)}] |
| puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');" |
| } |
| puts $fd "COMMIT;" |
| close $fd |
| runtest {2500 INSERTs in a transaction} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| for {set i 0} {$i<10} {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 $fd |
| runtest {10 SELECTs without an index} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| for {set i 1} {$i<=10} {incr i} { |
| puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';" |
| } |
| close $fd |
| runtest {10 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 $fd |
| runtest {Creating an index} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| for {set i 0} {$i<500} {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 $fd |
| runtest {500 SELECTs with an index} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| puts $fd "BEGIN;" |
| for {set i 0} {$i<100} {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 $fd |
| runtest {100 UPDATEs without an index} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| puts $fd "BEGIN;" |
| for {set i 1} {$i<=2500} {incr i} { |
| set r [expr {int(rand()*500000)}] |
| puts $fd "UPDATE t2 SET b=$r WHERE a=$i;" |
| } |
| puts $fd "COMMIT;" |
| close $fd |
| runtest {2500 UPDATEs with an index} |
| |
| |
| set fd [open test$cnt.sql w] |
| puts $fd "BEGIN;" |
| for {set i 1} {$i<=2500} {incr i} { |
| set r [expr {int(rand()*500000)}] |
| puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;" |
| } |
| puts $fd "COMMIT;" |
| close $fd |
| runtest {2500 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 $fd |
| runtest {INSERTs from a SELECT} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';} |
| close $fd |
| runtest {DELETE without an index} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;} |
| close $fd |
| runtest {DELETE with an index} |
| |
| |
| |
| set fd [open test$cnt.sql w] |
| puts $fd {INSERT INTO t2 SELECT * FROM t1;} |
| close $fd |
| runtest {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<=300} {incr i} { |
| set r [expr {int(rand()*100000)}] |
| puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" |
| } |
| puts $fd {COMMIT;} |
| close $fd |
| runtest {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 $fd |
| runtest {DROP TABLE} |