os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/speed4p.explain
changeset 0 bde4ae8d615e
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/speed4p.explain	Fri Jun 15 03:10:57 2012 +0200
     1.3 @@ -0,0 +1,283 @@
     1.4 +# 2007 October 23
     1.5 +#
     1.6 +# The author disclaims copyright to this source code.  In place of
     1.7 +# a legal notice, here is a blessing:
     1.8 +#
     1.9 +#    May you do good and not evil.
    1.10 +#    May you find forgiveness for yourself and forgive others.
    1.11 +#    May you share freely, never taking more than you give.
    1.12 +#
    1.13 +#*************************************************************************
    1.14 +# This file implements regression tests for SQLite library.  The
    1.15 +# focus of this script is measuring executing speed. More specifically,
    1.16 +# the focus is on the speed of:
    1.17 +#
    1.18 +#   * joins
    1.19 +#   * views
    1.20 +#   * sub-selects
    1.21 +#   * triggers
    1.22 +#
    1.23 +# $Id: speed4p.explain,v 1.1 2008/04/16 12:57:48 drh Exp $
    1.24 +#
    1.25 +
    1.26 +set testdir [file dirname $argv0]
    1.27 +source $testdir/tester.tcl
    1.28 +speed_trial_init speed1
    1.29 +
    1.30 +# Set a uniform random seed
    1.31 +expr srand(0)
    1.32 +
    1.33 +set sqlout [open speed1.txt w]
    1.34 +proc tracesql {sql} {
    1.35 +  puts $::sqlout $sql\;
    1.36 +}
    1.37 +#db trace tracesql
    1.38 +
    1.39 +# The number_name procedure below converts its argment (an integer)
    1.40 +# into a string which is the English-language name for that number.
    1.41 +#
    1.42 +# Example:
    1.43 +#
    1.44 +#     puts [number_name 123]   ->  "one hundred twenty three"
    1.45 +#
    1.46 +set ones {zero one two three four five six seven eight nine
    1.47 +          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
    1.48 +          eighteen nineteen}
    1.49 +set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
    1.50 +proc number_name {n} {
    1.51 +  if {$n>=1000} {
    1.52 +    set txt "[number_name [expr {$n/1000}]] thousand"
    1.53 +    set n [expr {$n%1000}]
    1.54 +  } else {
    1.55 +    set txt {}
    1.56 +  }
    1.57 +  if {$n>=100} {
    1.58 +    append txt " [lindex $::ones [expr {$n/100}]] hundred"
    1.59 +    set n [expr {$n%100}]
    1.60 +  }
    1.61 +  if {$n>=20} {
    1.62 +    append txt " [lindex $::tens [expr {$n/10}]]"
    1.63 +    set n [expr {$n%10}]
    1.64 +  }
    1.65 +  if {$n>0} {
    1.66 +    append txt " [lindex $::ones $n]"
    1.67 +  }
    1.68 +  set txt [string trim $txt]
    1.69 +  if {$txt==""} {set txt zero}
    1.70 +  return $txt
    1.71 +}
    1.72 +
    1.73 +# Summary of tests:
    1.74 +#
    1.75 +#   speed4p-join1: Join three tables using IPK index.
    1.76 +#   speed4p-join2: Join three tables using an index.
    1.77 +#   speed4p-join3: Join two tables without an index.
    1.78 +#
    1.79 +#   speed4p-view1:  Querying a view.
    1.80 +#   speed4p-table1: Same queries as in speed4p-view1, but run directly against
    1.81 +#                  the tables for comparison purposes.
    1.82 +#
    1.83 +#   speed4p-subselect1: A SELECT statement that uses many sub-queries..
    1.84 +#
    1.85 +#   speed4p-trigger1: An INSERT statement that fires a trigger.
    1.86 +#   speed4p-trigger2: An UPDATE statement that fires a trigger.
    1.87 +#   speed4p-trigger3: A DELETE statement that fires a trigger.
    1.88 +#   speed4p-notrigger1: Same operation as trigger1, but without the trigger.
    1.89 +#   speed4p-notrigger2:        "          trigger2           "
    1.90 +#   speed4p-notrigger3:        "          trigger3           "
    1.91 +#
    1.92 +
    1.93 +# Set up the schema. Each of the tables t1, t2 and t3 contain 50,000 rows.
    1.94 +# This creates a database of around 16MB.
    1.95 +execsql {
    1.96 +  PRAGMA page_size=1024;
    1.97 +  PRAGMA cache_size=8192;
    1.98 +  PRAGMA locking_mode=EXCLUSIVE;
    1.99 +  BEGIN;
   1.100 +  CREATE TABLE t1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
   1.101 +  CREATE TABLE t2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
   1.102 +  CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
   1.103 +
   1.104 +  CREATE VIEW v1 AS SELECT rowid, i, t FROM t1;
   1.105 +  CREATE VIEW v2 AS SELECT rowid, i, t FROM t2;
   1.106 +  CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;
   1.107 +}
   1.108 +for {set jj 1} {$jj <= 3} {incr jj} {
   1.109 +  set stmt [string map "%T% t$jj" {INSERT INTO %T% VALUES(NULL, $i, $t)}]
   1.110 +  for {set ii 0} {$ii < 50000} {incr ii} {
   1.111 +    set i [expr {int(rand()*50000)}]
   1.112 +    set t [number_name $i]
   1.113 +    execsql $stmt
   1.114 +  }
   1.115 +}
   1.116 +execsql {
   1.117 +  CREATE INDEX i1 ON t1(t);
   1.118 +  CREATE INDEX i2 ON t2(t);
   1.119 +  CREATE INDEX i3 ON t3(t);
   1.120 +  COMMIT;
   1.121 +}
   1.122 +
   1.123 +# Before running these tests, disable the compiled statement cache built into
   1.124 +# the Tcl interface. This is because we want to test the speed of SQL
   1.125 +# compilation as well as execution.
   1.126 +#
   1.127 +db cache size 0
   1.128 +
   1.129 +# Join t1, t2, t3 on IPK.
   1.130 +set sql "SELECT * FROM t1, t2, t3 WHERE t1.oid = t2.oid AND t2.oid = t3.oid"
   1.131 +explain $sql
   1.132 +speed_trial speed4p-join1 50000 row $sql
   1.133 +
   1.134 +# Join t1, t2, t3 on the non-IPK index.
   1.135 +set sql "SELECT * FROM t1, t2, t3 WHERE t1.t = t2.t AND t2.t = t3.t"
   1.136 +explain $sql
   1.137 +speed_trial speed4p-join2 50000 row $sql
   1.138 +
   1.139 +# Run 10000 simple queries against the views.
   1.140 +set script {
   1.141 +  for {set ii 1} {$ii < 10000} {incr ii} {
   1.142 +    set v [expr {$ii*3}]
   1.143 +    set t [expr {$ii%3+1}]
   1.144 +    db eval "SELECT * FROM v$t WHERE rowid = \$v"
   1.145 +  }
   1.146 +}
   1.147 +explain {SELECT * FROm v1 WHERE rowid=$v}
   1.148 +speed_trial_tcl speed4p-view1 10000 stmt $script
   1.149 +
   1.150 +# Run the same 10000 simple queries as in the previous test case against
   1.151 +# the underlying tables. The compiled vdbe programs should be identical, so
   1.152 +# the only difference in running time is the extra time taken to compile
   1.153 +# the view definitions.
   1.154 +#
   1.155 +set script {
   1.156 +  for {set ii 1} {$ii < 10000} {incr ii} {
   1.157 +    set v [expr {$ii*3}]
   1.158 +    set t [expr {$ii%3+1}]
   1.159 +    db eval "SELECT t FROM t$t WHERE rowid = \$v"
   1.160 +  }
   1.161 +}
   1.162 +explain {SELECT * FROM t1 WHERE rowid=$v}
   1.163 +speed_trial_tcl speed4p-table1 10000 stmt $script
   1.164 +
   1.165 +# Run a SELECT that uses sub-queries 10000 times. A total of 30000 sub-selects.
   1.166 +#
   1.167 +set script {
   1.168 +  for {set ii 1} {$ii < 10000} {incr ii} {
   1.169 +    set v [expr {$ii*3}]
   1.170 +    db eval {
   1.171 +      SELECT (SELECT t FROM t1 WHERE rowid = $v), 
   1.172 +             (SELECT t FROM t2 WHERE rowid = $v), 
   1.173 +             (SELECT t FROM t3 WHERE rowid = $v)
   1.174 +    }
   1.175 +  }
   1.176 +}
   1.177 +explain {
   1.178 +      SELECT (SELECT t FROM t1 WHERE rowid = $v), 
   1.179 +             (SELECT t FROM t2 WHERE rowid = $v), 
   1.180 +             (SELECT t FROM t3 WHERE rowid = $v)
   1.181 +}
   1.182 +speed_trial_tcl speed4p-subselect1 10000 stmt $script
   1.183 +
   1.184 +# The following block tests the speed of some DML statements that cause
   1.185 +# triggers to fire.
   1.186 +#
   1.187 +execsql {
   1.188 +  CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);
   1.189 +  CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
   1.190 +  CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN
   1.191 +    INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);
   1.192 +  END;
   1.193 +  CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN
   1.194 +    INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);
   1.195 +  END;
   1.196 +  CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN
   1.197 +    INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);
   1.198 +  END;
   1.199 +  BEGIN;
   1.200 +}
   1.201 +set list {}
   1.202 +for {set ii 1} {$ii < 10000} {incr ii} {
   1.203 +  lappend list $ii [number_name $ii]
   1.204 +}
   1.205 +set script {
   1.206 +  foreach {ii name} $::list {
   1.207 +    db eval {INSERT INTO t4 VALUES(NULL, $ii, $name)}
   1.208 +  }
   1.209 +}
   1.210 +explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
   1.211 +speed_trial_tcl speed4p-trigger1 10000 stmt $script
   1.212 +
   1.213 +set list {}
   1.214 +for {set ii 1} {$ii < 20000} {incr ii 2} {
   1.215 +  set ii2 [expr {$ii*2}]
   1.216 +  lappend list $ii $ii2 [number_name $ii2]
   1.217 +}
   1.218 +set script {
   1.219 +  foreach {ii ii2 name} $::list {
   1.220 +    db eval {
   1.221 +      UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
   1.222 +    }
   1.223 +  }
   1.224 +}
   1.225 +explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
   1.226 +speed_trial_tcl speed4p-trigger2 10000 stmt $script
   1.227 +
   1.228 +set script {
   1.229 +  for {set ii 1} {$ii < 20000} {incr ii 2} {
   1.230 +    db eval {DELETE FROM t4 WHERE rowid = $ii}
   1.231 +  }
   1.232 +}
   1.233 +explain {DELETE FROM t4 WHERE rowid = $ii}
   1.234 +speed_trial_tcl speed4p-trigger3 10000 stmt $script
   1.235 +execsql {COMMIT}
   1.236 +
   1.237 +# The following block contains the same tests as the above block that
   1.238 +# tests triggers, with one crucial difference: no triggers are defined.
   1.239 +# So the difference in speed between these tests and the preceding ones
   1.240 +# is the amount of time taken to compile and execute the trigger programs.
   1.241 +#
   1.242 +execsql {
   1.243 +  DROP TABLE t4;
   1.244 +  DROP TABLE log;
   1.245 +  VACUUM;
   1.246 +  CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
   1.247 +  BEGIN;
   1.248 +}
   1.249 +set list {}
   1.250 +for {set ii 1} {$ii < 10000} {incr ii} {
   1.251 +  lappend list $ii [number_name $ii]
   1.252 +}
   1.253 +set script {
   1.254 +  foreach {ii name} $::list {
   1.255 +    db eval {INSERT INTO t4 VALUES(NULL, $ii, $name);}
   1.256 +  }
   1.257 +}
   1.258 +explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
   1.259 +speed_trial_tcl speed4p-notrigger1 10000 stmt $script
   1.260 +
   1.261 +set list {}
   1.262 +for {set ii 1} {$ii < 20000} {incr ii 2} {
   1.263 +  set ii2 [expr {$ii*2}]
   1.264 +  lappend list $ii $ii2 [number_name $ii2]
   1.265 +}
   1.266 +set script {
   1.267 +  foreach {ii ii2 name} $::list {
   1.268 +    db eval {
   1.269 +      UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
   1.270 +    }
   1.271 +  }
   1.272 +}
   1.273 +explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
   1.274 +speed_trial_tcl speed4p-notrigger2 10000 stmt $script
   1.275 +
   1.276 +set script {
   1.277 +  for {set ii 1} {$ii < 20000} {incr ii 2} {
   1.278 +    db eval {DELETE FROM t4 WHERE rowid = $ii}
   1.279 +  }
   1.280 +}
   1.281 +explain {DELETE FROM t4 WHERE rowid = $ii}
   1.282 +speed_trial_tcl speed4p-notrigger3 10000 stmt $script
   1.283 +execsql {COMMIT}
   1.284 +
   1.285 +speed_trial_summary speed4
   1.286 +finish_test