2N/A
2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A
2N/A#!/usr/bin/tclsh
2N/A#
2N/A# Run this script using TCLSH to do a speed comparison between
2N/A# various versions of SQLite and PostgreSQL and MySQL
2N/A#
2N/A
2N/A# Run a test
2N/A#
2N/Aset cnt 1
2N/Aproc runtest {title} {
2N/A global cnt
2N/A set sqlfile test$cnt.sql
2N/A puts "<h2>Test $cnt: $title</h2>"
2N/A incr cnt
2N/A set fd [open $sqlfile r]
2N/A set sql [string trim [read $fd [file size $sqlfile]]]
2N/A close $fd
2N/A set sx [split $sql \n]
2N/A set n [llength $sx]
2N/A if {$n>8} {
2N/A set sql {}
2N/A for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n}
2N/A append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n"
2N/A for {set i [expr {$n-3}]} {$i<$n} {incr i} {
2N/A append sql [lindex $sx $i]<br>\n
2N/A }
2N/A } else {
2N/A regsub -all \n [string trim $sql] <br> sql
2N/A }
2N/A puts "<blockquote>"
2N/A puts "$sql"
2N/A puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>"
2N/A set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
2N/A set delay 1000
2N/A exec sync; after $delay;
2N/A set t [time "exec psql drh <$sqlfile" 1]
2N/A set t [expr {[lindex $t 0]/1000000.0}]
2N/A puts [format $format PostgreSQL: $t]
2N/A exec sync; after $delay;
2N/A set t [time "exec mysql -f drh <$sqlfile" 1]
2N/A set t [expr {[lindex $t 0]/1000000.0}]
2N/A puts [format $format MySQL: $t]
2N/A# set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
2N/A# set t [expr {[lindex $t 0]/1000000.0}]
2N/A# puts [format $format {SQLite 2.3.2:} $t]
2N/A# set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
2N/A# set t [expr {[lindex $t 0]/1000000.0}]
2N/A# puts [format $format {SQLite 2.4 (cache=100):} $t]
2N/A exec sync; after $delay;
2N/A set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1]
2N/A set t [expr {[lindex $t 0]/1000000.0}]
2N/A puts [format $format {SQLite 2.4:} $t]
2N/A exec sync; after $delay;
2N/A set t [time "exec ./sqlite240 sns.db <$sqlfile" 1]
2N/A set t [expr {[lindex $t 0]/1000000.0}]
2N/A puts [format $format {SQLite 2.4 (nosync):} $t]
2N/A# set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
2N/A# set t [expr {[lindex $t 0]/1000000.0}]
2N/A# puts [format $format {SQLite 2.4 (test):} $t]
2N/A puts "</table>"
2N/A}
2N/A
2N/A# Initialize the environment
2N/A#
2N/Aexpr srand(1)
2N/Acatch {exec /bin/sh -c {rm -f s*.db}}
2N/Aset fd [open clear.sql w]
2N/Aputs $fd {
2N/A drop table t1;
2N/A drop table t2;
2N/A}
2N/Aclose $fd
2N/Acatch {exec psql drh <clear.sql}
2N/Acatch {exec mysql drh <clear.sql}
2N/Aset fd [open 2kinit.sql w]
2N/Aputs $fd {
2N/A PRAGMA default_cache_size=2000;
2N/A PRAGMA default_synchronous=on;
2N/A}
2N/Aclose $fd
2N/Aexec ./sqlite240 s2k.db <2kinit.sql
2N/Aexec ./sqlite-t1 st1.db <2kinit.sql
2N/Aset fd [open nosync-init.sql w]
2N/Aputs $fd {
2N/A PRAGMA default_cache_size=2000;
2N/A PRAGMA default_synchronous=off;
2N/A}
2N/Aclose $fd
2N/Aexec ./sqlite240 sns.db <nosync-init.sql
2N/Aset ones {zero one two three four five six seven eight nine
2N/A ten eleven twelve thirteen fourteen fifteen sixteen seventeen
2N/A eighteen nineteen}
2N/Aset tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
2N/Aproc number_name {n} {
2N/A if {$n>=1000} {
2N/A set txt "[number_name [expr {$n/1000}]] thousand"
2N/A set n [expr {$n%1000}]
2N/A } else {
2N/A set txt {}
2N/A }
2N/A if {$n>=100} {
2N/A append txt " [lindex $::ones [expr {$n/100}]] hundred"
2N/A set n [expr {$n%100}]
2N/A }
2N/A if {$n>=20} {
2N/A append txt " [lindex $::tens [expr {$n/10}]]"
2N/A set n [expr {$n%10}]
2N/A }
2N/A if {$n>0} {
2N/A append txt " [lindex $::ones $n]"
2N/A }
2N/A set txt [string trim $txt]
2N/A if {$txt==""} {set txt zero}
2N/A return $txt
2N/A}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "BEGIN;"
2N/Aputs $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
2N/Afor {set i 1} {$i<=25000} {incr i} {
2N/A set r [expr {int(rand()*500000)}]
2N/A puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
2N/A}
2N/Aputs $fd "COMMIT;"
2N/Aclose $fd
2N/Aruntest {25000 INSERTs in a transaction}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "DELETE FROM t1;"
2N/Aclose $fd
2N/Aruntest {DELETE everything}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "BEGIN;"
2N/Afor {set i 1} {$i<=25000} {incr i} {
2N/A set r [expr {int(rand()*500000)}]
2N/A puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
2N/A}
2N/Aputs $fd "COMMIT;"
2N/Aclose $fd
2N/Aruntest {25000 INSERTs in a transaction}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "DELETE FROM t1;"
2N/Aclose $fd
2N/Aruntest {DELETE everything}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "BEGIN;"
2N/Afor {set i 1} {$i<=25000} {incr i} {
2N/A set r [expr {int(rand()*500000)}]
2N/A puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
2N/A}
2N/Aputs $fd "COMMIT;"
2N/Aclose $fd
2N/Aruntest {25000 INSERTs in a transaction}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "DELETE FROM t1;"
2N/Aclose $fd
2N/Aruntest {DELETE everything}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "BEGIN;"
2N/Afor {set i 1} {$i<=25000} {incr i} {
2N/A set r [expr {int(rand()*500000)}]
2N/A puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
2N/A}
2N/Aputs $fd "COMMIT;"
2N/Aclose $fd
2N/Aruntest {25000 INSERTs in a transaction}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "DELETE FROM t1;"
2N/Aclose $fd
2N/Aruntest {DELETE everything}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "BEGIN;"
2N/Afor {set i 1} {$i<=25000} {incr i} {
2N/A set r [expr {int(rand()*500000)}]
2N/A puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
2N/A}
2N/Aputs $fd "COMMIT;"
2N/Aclose $fd
2N/Aruntest {25000 INSERTs in a transaction}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd "DELETE FROM t1;"
2N/Aclose $fd
2N/Aruntest {DELETE everything}
2N/A
2N/A
2N/Aset fd [open test$cnt.sql w]
2N/Aputs $fd {DROP TABLE t1;}
2N/Aclose $fd
2N/Aruntest {DROP TABLE}