17c478bd9Sstevel@tonic-gate#!/usr/bin/tclsh 27c478bd9Sstevel@tonic-gate# 37c478bd9Sstevel@tonic-gate# Run this script using TCLSH to do a speed comparison between 47c478bd9Sstevel@tonic-gate# various versions of SQLite and PostgreSQL and MySQL 57c478bd9Sstevel@tonic-gate# 67c478bd9Sstevel@tonic-gate 77c478bd9Sstevel@tonic-gate# Run a test 87c478bd9Sstevel@tonic-gate# 97c478bd9Sstevel@tonic-gateset cnt 1 107c478bd9Sstevel@tonic-gateproc runtest {title} { 117c478bd9Sstevel@tonic-gate global cnt 127c478bd9Sstevel@tonic-gate set sqlfile test$cnt.sql 137c478bd9Sstevel@tonic-gate puts "<h2>Test $cnt: $title</h2>" 147c478bd9Sstevel@tonic-gate incr cnt 157c478bd9Sstevel@tonic-gate set fd [open $sqlfile r] 167c478bd9Sstevel@tonic-gate set sql [string trim [read $fd [file size $sqlfile]]] 177c478bd9Sstevel@tonic-gate close $fd 187c478bd9Sstevel@tonic-gate set sx [split $sql \n] 197c478bd9Sstevel@tonic-gate set n [llength $sx] 207c478bd9Sstevel@tonic-gate if {$n>8} { 217c478bd9Sstevel@tonic-gate set sql {} 227c478bd9Sstevel@tonic-gate for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n} 237c478bd9Sstevel@tonic-gate append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n" 247c478bd9Sstevel@tonic-gate for {set i [expr {$n-3}]} {$i<$n} {incr i} { 257c478bd9Sstevel@tonic-gate append sql [lindex $sx $i]<br>\n 267c478bd9Sstevel@tonic-gate } 277c478bd9Sstevel@tonic-gate } else { 287c478bd9Sstevel@tonic-gate regsub -all \n [string trim $sql] <br> sql 297c478bd9Sstevel@tonic-gate } 307c478bd9Sstevel@tonic-gate puts "<blockquote>" 317c478bd9Sstevel@tonic-gate puts "$sql" 327c478bd9Sstevel@tonic-gate puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>" 337c478bd9Sstevel@tonic-gate set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} 347c478bd9Sstevel@tonic-gate set delay 1000 357c478bd9Sstevel@tonic-gate# exec sync; after $delay; 367c478bd9Sstevel@tonic-gate# set t [time "exec psql drh <$sqlfile" 1] 377c478bd9Sstevel@tonic-gate# set t [expr {[lindex $t 0]/1000000.0}] 387c478bd9Sstevel@tonic-gate# puts [format $format PostgreSQL: $t] 397c478bd9Sstevel@tonic-gate exec sync; after $delay; 407c478bd9Sstevel@tonic-gate set t [time "exec mysql -f drh <$sqlfile" 1] 417c478bd9Sstevel@tonic-gate set t [expr {[lindex $t 0]/1000000.0}] 427c478bd9Sstevel@tonic-gate puts [format $format MySQL: $t] 437c478bd9Sstevel@tonic-gate# set t [time "exec ./sqlite232 s232.db <$sqlfile" 1] 447c478bd9Sstevel@tonic-gate# set t [expr {[lindex $t 0]/1000000.0}] 457c478bd9Sstevel@tonic-gate# puts [format $format {SQLite 2.3.2:} $t] 467c478bd9Sstevel@tonic-gate# set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1] 477c478bd9Sstevel@tonic-gate# set t [expr {[lindex $t 0]/1000000.0}] 487c478bd9Sstevel@tonic-gate# puts [format $format {SQLite 2.4 (cache=100):} $t] 497c478bd9Sstevel@tonic-gate exec sync; after $delay; 507c478bd9Sstevel@tonic-gate set t [time "exec ./sqlite248 s2k.db <$sqlfile" 1] 517c478bd9Sstevel@tonic-gate set t [expr {[lindex $t 0]/1000000.0}] 527c478bd9Sstevel@tonic-gate puts [format $format {SQLite 2.4.8:} $t] 537c478bd9Sstevel@tonic-gate exec sync; after $delay; 547c478bd9Sstevel@tonic-gate set t [time "exec ./sqlite248 sns.db <$sqlfile" 1] 557c478bd9Sstevel@tonic-gate set t [expr {[lindex $t 0]/1000000.0}] 567c478bd9Sstevel@tonic-gate puts [format $format {SQLite 2.4.8 (nosync):} $t] 577c478bd9Sstevel@tonic-gate exec sync; after $delay; 587c478bd9Sstevel@tonic-gate set t [time "exec ./sqlite2412 s2kb.db <$sqlfile" 1] 597c478bd9Sstevel@tonic-gate set t [expr {[lindex $t 0]/1000000.0}] 607c478bd9Sstevel@tonic-gate puts [format $format {SQLite 2.4.12:} $t] 617c478bd9Sstevel@tonic-gate exec sync; after $delay; 627c478bd9Sstevel@tonic-gate set t [time "exec ./sqlite2412 snsb.db <$sqlfile" 1] 637c478bd9Sstevel@tonic-gate set t [expr {[lindex $t 0]/1000000.0}] 647c478bd9Sstevel@tonic-gate puts [format $format {SQLite 2.4.12 (nosync):} $t] 657c478bd9Sstevel@tonic-gate# set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1] 667c478bd9Sstevel@tonic-gate# set t [expr {[lindex $t 0]/1000000.0}] 677c478bd9Sstevel@tonic-gate# puts [format $format {SQLite 2.4 (test):} $t] 687c478bd9Sstevel@tonic-gate puts "</table>" 697c478bd9Sstevel@tonic-gate} 707c478bd9Sstevel@tonic-gate 717c478bd9Sstevel@tonic-gate# Initialize the environment 727c478bd9Sstevel@tonic-gate# 737c478bd9Sstevel@tonic-gateexpr srand(1) 747c478bd9Sstevel@tonic-gatecatch {exec /bin/sh -c {rm -f s*.db}} 757c478bd9Sstevel@tonic-gateset fd [open clear.sql w] 767c478bd9Sstevel@tonic-gateputs $fd { 777c478bd9Sstevel@tonic-gate drop table t1; 787c478bd9Sstevel@tonic-gate drop table t2; 797c478bd9Sstevel@tonic-gate} 807c478bd9Sstevel@tonic-gateclose $fd 817c478bd9Sstevel@tonic-gatecatch {exec psql drh <clear.sql} 827c478bd9Sstevel@tonic-gatecatch {exec mysql drh <clear.sql} 837c478bd9Sstevel@tonic-gateset fd [open 2kinit.sql w] 847c478bd9Sstevel@tonic-gateputs $fd { 857c478bd9Sstevel@tonic-gate PRAGMA default_cache_size=2000; 867c478bd9Sstevel@tonic-gate PRAGMA default_synchronous=on; 877c478bd9Sstevel@tonic-gate} 887c478bd9Sstevel@tonic-gateclose $fd 897c478bd9Sstevel@tonic-gateexec ./sqlite248 s2k.db <2kinit.sql 907c478bd9Sstevel@tonic-gateexec ./sqlite2412 s2kb.db <2kinit.sql 917c478bd9Sstevel@tonic-gateset fd [open nosync-init.sql w] 927c478bd9Sstevel@tonic-gateputs $fd { 937c478bd9Sstevel@tonic-gate PRAGMA default_cache_size=2000; 947c478bd9Sstevel@tonic-gate PRAGMA default_synchronous=off; 957c478bd9Sstevel@tonic-gate} 967c478bd9Sstevel@tonic-gateclose $fd 977c478bd9Sstevel@tonic-gateexec ./sqlite248 sns.db <nosync-init.sql 987c478bd9Sstevel@tonic-gateexec ./sqlite2412 snsb.db <nosync-init.sql 997c478bd9Sstevel@tonic-gateset ones {zero one two three four five six seven eight nine 1007c478bd9Sstevel@tonic-gate ten eleven twelve thirteen fourteen fifteen sixteen seventeen 1017c478bd9Sstevel@tonic-gate eighteen nineteen} 1027c478bd9Sstevel@tonic-gateset tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} 1037c478bd9Sstevel@tonic-gateproc number_name {n} { 1047c478bd9Sstevel@tonic-gate if {$n>=1000} { 1057c478bd9Sstevel@tonic-gate set txt "[number_name [expr {$n/1000}]] thousand" 1067c478bd9Sstevel@tonic-gate set n [expr {$n%1000}] 1077c478bd9Sstevel@tonic-gate } else { 1087c478bd9Sstevel@tonic-gate set txt {} 1097c478bd9Sstevel@tonic-gate } 1107c478bd9Sstevel@tonic-gate if {$n>=100} { 1117c478bd9Sstevel@tonic-gate append txt " [lindex $::ones [expr {$n/100}]] hundred" 1127c478bd9Sstevel@tonic-gate set n [expr {$n%100}] 1137c478bd9Sstevel@tonic-gate } 1147c478bd9Sstevel@tonic-gate if {$n>=20} { 1157c478bd9Sstevel@tonic-gate append txt " [lindex $::tens [expr {$n/10}]]" 1167c478bd9Sstevel@tonic-gate set n [expr {$n%10}] 1177c478bd9Sstevel@tonic-gate } 1187c478bd9Sstevel@tonic-gate if {$n>0} { 1197c478bd9Sstevel@tonic-gate append txt " [lindex $::ones $n]" 1207c478bd9Sstevel@tonic-gate } 1217c478bd9Sstevel@tonic-gate set txt [string trim $txt] 1227c478bd9Sstevel@tonic-gate if {$txt==""} {set txt zero} 1237c478bd9Sstevel@tonic-gate return $txt 1247c478bd9Sstevel@tonic-gate} 1257c478bd9Sstevel@tonic-gate 1267c478bd9Sstevel@tonic-gate 1277c478bd9Sstevel@tonic-gate 1287c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 1297c478bd9Sstevel@tonic-gateputs $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" 1307c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=1000} {incr i} { 1317c478bd9Sstevel@tonic-gate set r [expr {int(rand()*100000)}] 1327c478bd9Sstevel@tonic-gate puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 1337c478bd9Sstevel@tonic-gate} 1347c478bd9Sstevel@tonic-gateclose $fd 1357c478bd9Sstevel@tonic-gateruntest {1000 INSERTs} 1367c478bd9Sstevel@tonic-gate 1377c478bd9Sstevel@tonic-gate 1387c478bd9Sstevel@tonic-gate 1397c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 1407c478bd9Sstevel@tonic-gateputs $fd "BEGIN;" 1417c478bd9Sstevel@tonic-gateputs $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));" 1427c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=25000} {incr i} { 1437c478bd9Sstevel@tonic-gate set r [expr {int(rand()*500000)}] 1447c478bd9Sstevel@tonic-gate puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');" 1457c478bd9Sstevel@tonic-gate} 1467c478bd9Sstevel@tonic-gateputs $fd "COMMIT;" 1477c478bd9Sstevel@tonic-gateclose $fd 1487c478bd9Sstevel@tonic-gateruntest {25000 INSERTs in a transaction} 1497c478bd9Sstevel@tonic-gate 1507c478bd9Sstevel@tonic-gate 1517c478bd9Sstevel@tonic-gate 1527c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 1537c478bd9Sstevel@tonic-gatefor {set i 0} {$i<100} {incr i} { 1547c478bd9Sstevel@tonic-gate set lwr [expr {$i*100}] 1557c478bd9Sstevel@tonic-gate set upr [expr {($i+10)*100}] 1567c478bd9Sstevel@tonic-gate puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;" 1577c478bd9Sstevel@tonic-gate} 1587c478bd9Sstevel@tonic-gateclose $fd 1597c478bd9Sstevel@tonic-gateruntest {100 SELECTs without an index} 1607c478bd9Sstevel@tonic-gate 1617c478bd9Sstevel@tonic-gate 1627c478bd9Sstevel@tonic-gate 1637c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 1647c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=100} {incr i} { 1657c478bd9Sstevel@tonic-gate puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';" 1667c478bd9Sstevel@tonic-gate} 1677c478bd9Sstevel@tonic-gateclose $fd 1687c478bd9Sstevel@tonic-gateruntest {100 SELECTs on a string comparison} 1697c478bd9Sstevel@tonic-gate 1707c478bd9Sstevel@tonic-gate 1717c478bd9Sstevel@tonic-gate 1727c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 1737c478bd9Sstevel@tonic-gateputs $fd {CREATE INDEX i2a ON t2(a);} 1747c478bd9Sstevel@tonic-gateputs $fd {CREATE INDEX i2b ON t2(b);} 1757c478bd9Sstevel@tonic-gateclose $fd 1767c478bd9Sstevel@tonic-gateruntest {Creating an index} 1777c478bd9Sstevel@tonic-gate 1787c478bd9Sstevel@tonic-gate 1797c478bd9Sstevel@tonic-gate 1807c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 1817c478bd9Sstevel@tonic-gatefor {set i 0} {$i<5000} {incr i} { 1827c478bd9Sstevel@tonic-gate set lwr [expr {$i*100}] 1837c478bd9Sstevel@tonic-gate set upr [expr {($i+1)*100}] 1847c478bd9Sstevel@tonic-gate puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;" 1857c478bd9Sstevel@tonic-gate} 1867c478bd9Sstevel@tonic-gateclose $fd 1877c478bd9Sstevel@tonic-gateruntest {5000 SELECTs with an index} 1887c478bd9Sstevel@tonic-gate 1897c478bd9Sstevel@tonic-gate 1907c478bd9Sstevel@tonic-gate 1917c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 1927c478bd9Sstevel@tonic-gateputs $fd "BEGIN;" 1937c478bd9Sstevel@tonic-gatefor {set i 0} {$i<1000} {incr i} { 1947c478bd9Sstevel@tonic-gate set lwr [expr {$i*10}] 1957c478bd9Sstevel@tonic-gate set upr [expr {($i+1)*10}] 1967c478bd9Sstevel@tonic-gate puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;" 1977c478bd9Sstevel@tonic-gate} 1987c478bd9Sstevel@tonic-gateputs $fd "COMMIT;" 1997c478bd9Sstevel@tonic-gateclose $fd 2007c478bd9Sstevel@tonic-gateruntest {1000 UPDATEs without an index} 2017c478bd9Sstevel@tonic-gate 2027c478bd9Sstevel@tonic-gate 2037c478bd9Sstevel@tonic-gate 2047c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2057c478bd9Sstevel@tonic-gateputs $fd "BEGIN;" 2067c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=25000} {incr i} { 2077c478bd9Sstevel@tonic-gate set r [expr {int(rand()*500000)}] 2087c478bd9Sstevel@tonic-gate puts $fd "UPDATE t2 SET b=$r WHERE a=$i;" 2097c478bd9Sstevel@tonic-gate} 2107c478bd9Sstevel@tonic-gateputs $fd "COMMIT;" 2117c478bd9Sstevel@tonic-gateclose $fd 2127c478bd9Sstevel@tonic-gateruntest {25000 UPDATEs with an index} 2137c478bd9Sstevel@tonic-gate 2147c478bd9Sstevel@tonic-gate 2157c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2167c478bd9Sstevel@tonic-gateputs $fd "BEGIN;" 2177c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=25000} {incr i} { 2187c478bd9Sstevel@tonic-gate set r [expr {int(rand()*500000)}] 2197c478bd9Sstevel@tonic-gate puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;" 2207c478bd9Sstevel@tonic-gate} 2217c478bd9Sstevel@tonic-gateputs $fd "COMMIT;" 2227c478bd9Sstevel@tonic-gateclose $fd 2237c478bd9Sstevel@tonic-gateruntest {25000 text UPDATEs with an index} 2247c478bd9Sstevel@tonic-gate 2257c478bd9Sstevel@tonic-gate 2267c478bd9Sstevel@tonic-gate 2277c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2287c478bd9Sstevel@tonic-gateputs $fd "BEGIN;" 2297c478bd9Sstevel@tonic-gateputs $fd "INSERT INTO t1 SELECT * FROM t2;" 2307c478bd9Sstevel@tonic-gateputs $fd "INSERT INTO t2 SELECT * FROM t1;" 2317c478bd9Sstevel@tonic-gateputs $fd "COMMIT;" 2327c478bd9Sstevel@tonic-gateclose $fd 2337c478bd9Sstevel@tonic-gateruntest {INSERTs from a SELECT} 2347c478bd9Sstevel@tonic-gate 2357c478bd9Sstevel@tonic-gate 2367c478bd9Sstevel@tonic-gate 2377c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2387c478bd9Sstevel@tonic-gateputs $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';} 2397c478bd9Sstevel@tonic-gateclose $fd 2407c478bd9Sstevel@tonic-gateruntest {DELETE without an index} 2417c478bd9Sstevel@tonic-gate 2427c478bd9Sstevel@tonic-gate 2437c478bd9Sstevel@tonic-gate 2447c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2457c478bd9Sstevel@tonic-gateputs $fd {DELETE FROM t2 WHERE a>10 AND a<20000;} 2467c478bd9Sstevel@tonic-gateclose $fd 2477c478bd9Sstevel@tonic-gateruntest {DELETE with an index} 2487c478bd9Sstevel@tonic-gate 2497c478bd9Sstevel@tonic-gate 2507c478bd9Sstevel@tonic-gate 2517c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2527c478bd9Sstevel@tonic-gateputs $fd {INSERT INTO t2 SELECT * FROM t1;} 2537c478bd9Sstevel@tonic-gateclose $fd 2547c478bd9Sstevel@tonic-gateruntest {A big INSERT after a big DELETE} 2557c478bd9Sstevel@tonic-gate 2567c478bd9Sstevel@tonic-gate 2577c478bd9Sstevel@tonic-gate 2587c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2597c478bd9Sstevel@tonic-gateputs $fd {BEGIN;} 2607c478bd9Sstevel@tonic-gateputs $fd {DELETE FROM t1;} 2617c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=3000} {incr i} { 2627c478bd9Sstevel@tonic-gate set r [expr {int(rand()*100000)}] 2637c478bd9Sstevel@tonic-gate puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" 2647c478bd9Sstevel@tonic-gate} 2657c478bd9Sstevel@tonic-gateputs $fd {COMMIT;} 2667c478bd9Sstevel@tonic-gateclose $fd 2677c478bd9Sstevel@tonic-gateruntest {A big DELETE followed by many small INSERTs} 2687c478bd9Sstevel@tonic-gate 2697c478bd9Sstevel@tonic-gate 2707c478bd9Sstevel@tonic-gate 2717c478bd9Sstevel@tonic-gateset fd [open test$cnt.sql w] 2727c478bd9Sstevel@tonic-gateputs $fd {DROP TABLE t1;} 2737c478bd9Sstevel@tonic-gateputs $fd {DROP TABLE t2;} 2747c478bd9Sstevel@tonic-gateclose $fd 2757c478bd9Sstevel@tonic-gateruntest {DROP TABLE} 276