15821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#!/usr/bin/tclsh 25821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 35821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Run this script using TCLSH to do a speed comparison between 45821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# various versions of SQLite and PostgreSQL and MySQL 55821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 65821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 75821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Run a test 85821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 95821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set cnt 1 105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)proc runtest {title} { 115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) global cnt 125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set sqlfile test$cnt.sql 135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts "<h2>Test $cnt: $title</h2>" 145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) incr cnt 155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set fd [open $sqlfile r] 165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set sql [string trim [read $fd [file size $sqlfile]]] 175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) close $fd 185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set sx [split $sql \n] 195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set n [llength $sx] 205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$n>8} { 215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set sql {} 225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n} 235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n" 245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) for {set i [expr {$n-3}]} {$i<$n} {incr i} { 255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) append sql [lindex $sx $i]<br>\n 265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } else { 285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) regsub -all \n [string trim $sql] <br> sql 295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts "<blockquote>" 315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts "$sql" 325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>" 335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} 345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set delay 1000 355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) exec sync; after $delay; 365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [time "exec psql drh <$sqlfile" 1] 375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [expr {[lindex $t 0]/1000000.0}] 385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts [format $format PostgreSQL: $t] 395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) exec sync; after $delay; 405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [time "exec mysql -f drh <$sqlfile" 1] 415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [expr {[lindex $t 0]/1000000.0}] 425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts [format $format MySQL: $t] 435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# set t [time "exec ./sqlite232 s232.db <$sqlfile" 1] 445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# set t [expr {[lindex $t 0]/1000000.0}] 455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# puts [format $format {SQLite 2.3.2:} $t] 465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1] 475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# set t [expr {[lindex $t 0]/1000000.0}] 485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# puts [format $format {SQLite 2.4 (cache=100):} $t] 495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) exec sync; after $delay; 505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1] 515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [expr {[lindex $t 0]/1000000.0}] 525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts [format $format {SQLite 2.4:} $t] 535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) exec sync; after $delay; 545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [time "exec ./sqlite240 sns.db <$sqlfile" 1] 555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t [expr {[lindex $t 0]/1000000.0}] 565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts [format $format {SQLite 2.4 (nosync):} $t] 575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1] 585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# set t [expr {[lindex $t 0]/1000000.0}] 595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# puts [format $format {SQLite 2.4 (test):} $t] 605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts "</table>" 615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Initialize the environment 645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)expr srand(1) 665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)catch {exec /bin/sh -c {rm -f s*.db}} 675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open clear.sql w] 685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd { 695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) drop table t1; 705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) drop table t2; 715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)catch {exec psql drh <clear.sql} 745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)catch {exec mysql drh <clear.sql} 755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open 2kinit.sql w] 765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd { 775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA default_cache_size=2000; 785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA default_synchronous=on; 795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)exec ./sqlite240 s2k.db <2kinit.sql 825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)exec ./sqlite-t1 st1.db <2kinit.sql 835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open nosync-init.sql w] 845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd { 855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA default_cache_size=2000; 865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA default_synchronous=off; 875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)exec ./sqlite240 sns.db <nosync-init.sql 905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set ones {zero one two three four five six seven eight nine 915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ten eleven twelve thirteen fourteen fifteen sixteen seventeen 925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) eighteen nineteen} 935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} 945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)proc number_name {n} { 955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$n>=1000} { 965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set txt "[number_name [expr {$n/1000}]] thousand" 975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set n [expr {$n%1000}] 985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } else { 995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set txt {} 1005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$n>=100} { 1025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) append txt " [lindex $::ones [expr {$n/100}]] hundred" 1035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set n [expr {$n%100}] 1045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$n>=20} { 1065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) append txt " [lindex $::tens [expr {$n/10}]]" 1075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set n [expr {$n%10}] 1085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$n>0} { 1105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) append txt " [lindex $::ones $n]" 1115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set txt [string trim $txt] 1135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$txt==""} {set txt zero} 1145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return $txt 1155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "BEGIN;" 1205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" 1215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)for {set i 1} {$i<=25000} {incr i} { 1225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r [expr {int(rand()*500000)}] 1235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 1245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "COMMIT;" 1265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {25000 INSERTs in a transaction} 1285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "DELETE FROM t1;" 1325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {DELETE everything} 1345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "BEGIN;" 1385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)for {set i 1} {$i<=25000} {incr i} { 1395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r [expr {int(rand()*500000)}] 1405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 1415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "COMMIT;" 1435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {25000 INSERTs in a transaction} 1455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "DELETE FROM t1;" 1495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {DELETE everything} 1515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "BEGIN;" 1555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)for {set i 1} {$i<=25000} {incr i} { 1565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r [expr {int(rand()*500000)}] 1575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 1585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "COMMIT;" 1605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {25000 INSERTs in a transaction} 1625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "DELETE FROM t1;" 1665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {DELETE everything} 1685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "BEGIN;" 1725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)for {set i 1} {$i<=25000} {incr i} { 1735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r [expr {int(rand()*500000)}] 1745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 1755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "COMMIT;" 1775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {25000 INSERTs in a transaction} 1795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "DELETE FROM t1;" 1835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {DELETE everything} 1855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "BEGIN;" 1895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)for {set i 1} {$i<=25000} {incr i} { 1905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r [expr {int(rand()*500000)}] 1915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 1925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "COMMIT;" 1945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 1955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {25000 INSERTs in a transaction} 1965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 1995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd "DELETE FROM t1;" 2005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 2015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {DELETE everything} 2025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set fd [open test$cnt.sql w] 2055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)puts $fd {DROP TABLE t1;} 2065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)close $fd 2075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)runtest {DROP TABLE} 208