Return to speedtest2.tcl CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool

sqlite3

    1: #!/usr/bin/tclsh
    2: #
    3: # Run this script using TCLSH to do a speed comparison between
    4: # various versions of SQLite and PostgreSQL and MySQL
    5: #
    6: 
    7: # Run a test
    8: #
    9: set cnt 1
   10: proc runtest {title} {
   11:   global cnt
   12:   set sqlfile test$cnt.sql
   13:   puts "<h2>Test $cnt: $title</h2>"
   14:   incr cnt
   15:   set fd [open $sqlfile r]
   16:   set sql [string trim [read $fd [file size $sqlfile]]]
   17:   close $fd
   18:   set sx [split $sql \n]
   19:   set n [llength $sx]
   20:   if {$n>8} {
   21:     set sql {}
   22:     for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n}
   23:     append sql  "<i>... [expr {$n-6}] lines omitted</i><br>\n"
   24:     for {set i [expr {$n-3}]} {$i<$n} {incr i} {
   25:       append sql [lindex $sx $i]<br>\n
   26:     }
   27:   } else {
   28:     regsub -all \n [string trim $sql] <br> sql
   29:   }
   30:   puts "<blockquote>"
   31:   puts "$sql"
   32:   puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>"
   33:   set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
   34:   set delay 1000
   35:   exec sync; after $delay;
   36:   set t [time "exec psql drh <$sqlfile" 1]
   37:   set t [expr {[lindex $t 0]/1000000.0}]
   38:   puts [format $format PostgreSQL: $t]
   39:   exec sync; after $delay;
   40:   set t [time "exec mysql -f drh <$sqlfile" 1]
   41:   set t [expr {[lindex $t 0]/1000000.0}]
   42:   puts [format $format MySQL: $t]
   43: #  set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
   44: #  set t [expr {[lindex $t 0]/1000000.0}]
   45: #  puts [format $format {SQLite 2.3.2:} $t]
   46: #  set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
   47: #  set t [expr {[lindex $t 0]/1000000.0}]
   48: #  puts [format $format {SQLite 2.4 (cache=100):} $t]
   49:   exec sync; after $delay;
   50:   set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1]
   51:   set t [expr {[lindex $t 0]/1000000.0}]
   52:   puts [format $format {SQLite 2.4:} $t]
   53:   exec sync; after $delay;
   54:   set t [time "exec ./sqlite240 sns.db <$sqlfile" 1]
   55:   set t [expr {[lindex $t 0]/1000000.0}]
   56:   puts [format $format {SQLite 2.4 (nosync):} $t]
   57: #  set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
   58: #  set t [expr {[lindex $t 0]/1000000.0}]
   59: #  puts [format $format {SQLite 2.4 (test):} $t]
   60:   puts "</table>"
   61: }
   62: 
   63: # Initialize the environment
   64: #
   65: expr srand(1)
   66: catch {exec /bin/sh -c {rm -f s*.db}}
   67: set fd [open clear.sql w]
   68: puts $fd {
   69:   drop table t1;
   70:   drop table t2;
   71: }
   72: close $fd
   73: catch {exec psql drh <clear.sql}
   74: catch {exec mysql drh <clear.sql}
   75: set fd [open 2kinit.sql w]
   76: puts $fd {
   77:   PRAGMA default_cache_size=2000;
   78:   PRAGMA default_synchronous=on;
   79: }
   80: close $fd
   81: exec ./sqlite240 s2k.db <2kinit.sql
   82: exec ./sqlite-t1 st1.db <2kinit.sql
   83: set fd [open nosync-init.sql w]
   84: puts $fd {
   85:   PRAGMA default_cache_size=2000;
   86:   PRAGMA default_synchronous=off;
   87: }
   88: close $fd
   89: exec ./sqlite240 sns.db <nosync-init.sql
   90: set ones {zero one two three four five six seven eight nine
   91:           ten eleven twelve thirteen fourteen fifteen sixteen seventeen
   92:           eighteen nineteen}
   93: set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
   94: proc number_name {n} {
   95:   if {$n>=1000} {
   96:     set txt "[number_name [expr {$n/1000}]] thousand"
   97:     set n [expr {$n%1000}]
   98:   } else {
   99:     set txt {}
  100:   }
  101:   if {$n>=100} {
  102:     append txt " [lindex $::ones [expr {$n/100}]] hundred"
  103:     set n [expr {$n%100}]
  104:   }
  105:   if {$n>=20} {
  106:     append txt " [lindex $::tens [expr {$n/10}]]"
  107:     set n [expr {$n%10}]
  108:   }
  109:   if {$n>0} {
  110:     append txt " [lindex $::ones $n]"
  111:   }
  112:   set txt [string trim $txt]
  113:   if {$txt==""} {set txt zero}
  114:   return $txt
  115: }
  116: 
  117: 
  118: set fd [open test$cnt.sql w]
  119: puts $fd "BEGIN;"
  120: puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
  121: for {set i 1} {$i<=25000} {incr i} {
  122:   set r [expr {int(rand()*500000)}]
  123:   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
  124: }
  125: puts $fd "COMMIT;"
  126: close $fd
  127: runtest {25000 INSERTs in a transaction}
  128: 
  129: 
  130: set fd [open test$cnt.sql w]
  131: puts $fd "DELETE FROM t1;"
  132: close $fd
  133: runtest {DELETE everything}
  134: 
  135: 
  136: set fd [open test$cnt.sql w]
  137: puts $fd "BEGIN;"
  138: for {set i 1} {$i<=25000} {incr i} {
  139:   set r [expr {int(rand()*500000)}]
  140:   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
  141: }
  142: puts $fd "COMMIT;"
  143: close $fd
  144: runtest {25000 INSERTs in a transaction}
  145: 
  146: 
  147: set fd [open test$cnt.sql w]
  148: puts $fd "DELETE FROM t1;"
  149: close $fd
  150: runtest {DELETE everything}
  151: 
  152: 
  153: set fd [open test$cnt.sql w]
  154: puts $fd "BEGIN;"
  155: for {set i 1} {$i<=25000} {incr i} {
  156:   set r [expr {int(rand()*500000)}]
  157:   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
  158: }
  159: puts $fd "COMMIT;"
  160: close $fd
  161: runtest {25000 INSERTs in a transaction}
  162: 
  163: 
  164: set fd [open test$cnt.sql w]
  165: puts $fd "DELETE FROM t1;"
  166: close $fd
  167: runtest {DELETE everything}
  168: 
  169: 
  170: set fd [open test$cnt.sql w]
  171: puts $fd "BEGIN;"
  172: for {set i 1} {$i<=25000} {incr i} {
  173:   set r [expr {int(rand()*500000)}]
  174:   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
  175: }
  176: puts $fd "COMMIT;"
  177: close $fd
  178: runtest {25000 INSERTs in a transaction}
  179: 
  180: 
  181: set fd [open test$cnt.sql w]
  182: puts $fd "DELETE FROM t1;"
  183: close $fd
  184: runtest {DELETE everything}
  185: 
  186: 
  187: set fd [open test$cnt.sql w]
  188: puts $fd "BEGIN;"
  189: for {set i 1} {$i<=25000} {incr i} {
  190:   set r [expr {int(rand()*500000)}]
  191:   puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
  192: }
  193: puts $fd "COMMIT;"
  194: close $fd
  195: runtest {25000 INSERTs in a transaction}
  196: 
  197: 
  198: set fd [open test$cnt.sql w]
  199: puts $fd "DELETE FROM t1;"
  200: close $fd
  201: runtest {DELETE everything}
  202: 
  203: 
  204: set fd [open test$cnt.sql w]
  205: puts $fd {DROP TABLE t1;}
  206: close $fd
  207: runtest {DROP TABLE}