os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/speed1p.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/speed1p.explain	Fri Jun 15 03:10:57 2012 +0200
     1.3 @@ -0,0 +1,366 @@
     1.4 +# 2008 March 21
     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.  
    1.16 +#
    1.17 +# This is a copy of speed1.test modified to user prepared statements.
    1.18 +#
    1.19 +# $Id: speed1p.explain,v 1.1 2008/04/16 12:57:48 drh Exp $
    1.20 +#
    1.21 +
    1.22 +set testdir [file dirname $argv0]
    1.23 +source $testdir/tester.tcl
    1.24 +speed_trial_init speed1
    1.25 +
    1.26 +# Set a uniform random seed
    1.27 +expr srand(0)
    1.28 +
    1.29 +set sqlout [open speed1.txt w]
    1.30 +proc tracesql {sql} {
    1.31 +  puts $::sqlout $sql\;
    1.32 +}
    1.33 +#db trace tracesql
    1.34 +
    1.35 +# The number_name procedure below converts its argment (an integer)
    1.36 +# into a string which is the English-language name for that number.
    1.37 +#
    1.38 +# Example:
    1.39 +#
    1.40 +#     puts [number_name 123]   ->  "one hundred twenty three"
    1.41 +#
    1.42 +set ones {zero one two three four five six seven eight nine
    1.43 +          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
    1.44 +          eighteen nineteen}
    1.45 +set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
    1.46 +proc number_name {n} {
    1.47 +  if {$n>=1000} {
    1.48 +    set txt "[number_name [expr {$n/1000}]] thousand"
    1.49 +    set n [expr {$n%1000}]
    1.50 +  } else {
    1.51 +    set txt {}
    1.52 +  }
    1.53 +  if {$n>=100} {
    1.54 +    append txt " [lindex $::ones [expr {$n/100}]] hundred"
    1.55 +    set n [expr {$n%100}]
    1.56 +  }
    1.57 +  if {$n>=20} {
    1.58 +    append txt " [lindex $::tens [expr {$n/10}]]"
    1.59 +    set n [expr {$n%10}]
    1.60 +  }
    1.61 +  if {$n>0} {
    1.62 +    append txt " [lindex $::ones $n]"
    1.63 +  }
    1.64 +  set txt [string trim $txt]
    1.65 +  if {$txt==""} {set txt zero}
    1.66 +  return $txt
    1.67 +}
    1.68 +
    1.69 +# Create a database schema.
    1.70 +#
    1.71 +do_test speed1p-1.0 {
    1.72 +  execsql {
    1.73 +    PRAGMA page_size=1024;
    1.74 +    PRAGMA cache_size=8192;
    1.75 +    PRAGMA locking_mode=EXCLUSIVE;
    1.76 +    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
    1.77 +    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
    1.78 +    CREATE INDEX i2a ON t2(a);
    1.79 +    CREATE INDEX i2b ON t2(b);
    1.80 +  }
    1.81 +  execsql {
    1.82 +    SELECT name FROM sqlite_master ORDER BY 1;
    1.83 +  }
    1.84 +} {i2a i2b t1 t2}
    1.85 +
    1.86 +
    1.87 +# 50000 INSERTs on an unindexed table
    1.88 +#
    1.89 +set list {}
    1.90 +for {set i 1} {$i<=50000} {incr i} {
    1.91 +  set r [expr {int(rand()*500000)}]
    1.92 +  set x [number_name $r]
    1.93 +  lappend list $i $r $x
    1.94 +}
    1.95 +set script {
    1.96 +  foreach {i r x} $::list {
    1.97 +    db eval {INSERT INTO t1 VALUES($i,$r,$x)}
    1.98 +  }
    1.99 +}
   1.100 +explain {INSERT INTO t1 VALUES($i,$r,$x)}
   1.101 +db eval BEGIN
   1.102 +speed_trial_tcl speed1p-insert1 50000 row $script
   1.103 +db eval COMMIT
   1.104 +
   1.105 +# 50000 INSERTs on an indexed table
   1.106 +#
   1.107 +set list {}
   1.108 +for {set i 1} {$i<=50000} {incr i} {
   1.109 +  set r [expr {int(rand()*500000)}]
   1.110 +  set x [number_name $r]
   1.111 +  lappend list $i $r $x
   1.112 +}
   1.113 +set script {
   1.114 +  foreach {i r x} $::list {
   1.115 +    db eval {INSERT INTO t2 VALUES($i,$r,$x)}
   1.116 +  }
   1.117 +}
   1.118 +explain {INSERT INTO t2 VALUES($i,$r,$x)}
   1.119 +db eval BEGIN
   1.120 +speed_trial_tcl speed1p-insert2 50000 row $script
   1.121 +db eval COMMIT
   1.122 +
   1.123 +
   1.124 +
   1.125 +# 50 SELECTs on an integer comparison.  There is no index so
   1.126 +# a full table scan is required.
   1.127 +#
   1.128 +set list {}
   1.129 +for {set i 0} {$i<50} {incr i} {
   1.130 +  set lwr [expr {$i*100}]
   1.131 +  set upr [expr {($i+10)*100}]
   1.132 +  lappend list $lwr $upr
   1.133 +}
   1.134 +set script {
   1.135 +  foreach {lwr upr} $::list {
   1.136 +    db eval  {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
   1.137 +  }
   1.138 +}
   1.139 +explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
   1.140 +db eval BEGIN
   1.141 +speed_trial_tcl speed1p-select1 [expr {50*50000}] row $script
   1.142 +db eval COMMIT
   1.143 +
   1.144 +# 50 SELECTs on an LIKE comparison.  There is no index so a full
   1.145 +# table scan is required.
   1.146 +#
   1.147 +set list {}
   1.148 +for {set i 0} {$i<50} {incr i} {
   1.149 +  lappend list "%[number_name $i]%"
   1.150 +}
   1.151 +set script {
   1.152 +  foreach pattern $::list {
   1.153 +    db eval {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern}
   1.154 +  }
   1.155 +}
   1.156 +explain {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern}
   1.157 +db eval BEGIN
   1.158 +speed_trial_tcl speed1p-select2 [expr {50*50000}] row $script
   1.159 +db eval COMMIT
   1.160 +
   1.161 +# Create indices
   1.162 +#
   1.163 +explain {CREATE INDEX i1a ON t1(a)}
   1.164 +explain {CREATE INDEX i1b ON t1(b)}
   1.165 +db eval BEGIN
   1.166 +speed_trial speed1p-createidx 150000 row {
   1.167 +  CREATE INDEX i1a ON t1(a);
   1.168 +  CREATE INDEX i1b ON t1(b);
   1.169 +  CREATE INDEX i1c ON t1(c);
   1.170 +}
   1.171 +db eval COMMIT
   1.172 +
   1.173 +# 5000 SELECTs on an integer comparison where the integer is
   1.174 +# indexed.
   1.175 +#
   1.176 +set list {}
   1.177 +for {set i 0} {$i<5000} {incr i} {
   1.178 +  set lwr [expr {$i*100}]
   1.179 +  set upr [expr {($i+10)*100}]
   1.180 +  lappend list $lwr $upr
   1.181 +}
   1.182 +set script {
   1.183 +  foreach {lwr upr} $::list {
   1.184 +    db eval {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
   1.185 +  }
   1.186 +}
   1.187 +explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
   1.188 +db eval BEGIN
   1.189 +speed_trial_tcl speed1p-select3 5000 stmt $script
   1.190 +db eval COMMIT
   1.191 +
   1.192 +# 100000 random SELECTs against rowid.
   1.193 +#
   1.194 +set list {}
   1.195 +for {set i 1} {$i<=100000} {incr i} {
   1.196 +  set id [expr {int(rand()*50000)+1}]
   1.197 +  lappend list $id
   1.198 +}
   1.199 +set script {
   1.200 +  foreach id $::list {
   1.201 +    db eval {SELECT c FROM t1 WHERE rowid=$id}
   1.202 +  }
   1.203 +}
   1.204 +explain {SELECT c FROM t1 WHERE rowid=$id}
   1.205 +db eval BEGIN
   1.206 +speed_trial_tcl speed1p-select4 100000 row $script
   1.207 +db eval COMMIT
   1.208 +
   1.209 +# 100000 random SELECTs against a unique indexed column.
   1.210 +#
   1.211 +set list {}
   1.212 +for {set i 1} {$i<=100000} {incr i} {
   1.213 +  set id [expr {int(rand()*50000)+1}]
   1.214 +  lappend list $id
   1.215 +}
   1.216 +set script {
   1.217 +  foreach id $::list {
   1.218 +    db eval {SELECT c FROM t1 WHERE a=$id}
   1.219 +  }
   1.220 +}
   1.221 +explain {SELECT c FROM t1 WHERE a=$id}
   1.222 +db eval BEGIN
   1.223 +speed_trial_tcl speed1p-select5 100000 row $script
   1.224 +db eval COMMIT
   1.225 +
   1.226 +# 50000 random SELECTs against an indexed column text column
   1.227 +#
   1.228 +set list [db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000}]
   1.229 +set script {
   1.230 +  foreach c $::list {
   1.231 +    db eval {SELECT c FROM t1 WHERE c=$c}
   1.232 +  }
   1.233 +}
   1.234 +explain {SELECT c FROM t1 WHERE c=$c}
   1.235 +db eval BEGIN
   1.236 +speed_trial_tcl speed1p-select6 50000 row $script
   1.237 +db eval COMMIT
   1.238 +
   1.239 +
   1.240 +# Vacuum
   1.241 +speed_trial speed1p-vacuum 100000 row VACUUM
   1.242 +
   1.243 +# 5000 updates of ranges where the field being compared is indexed.
   1.244 +#
   1.245 +set list {}
   1.246 +for {set i 0} {$i<5000} {incr i} {
   1.247 +  set lwr [expr {$i*2}]
   1.248 +  set upr [expr {($i+1)*2}]
   1.249 +  lappend list $lwr $upr
   1.250 +}
   1.251 +set script {
   1.252 +  foreach {lwr upr} $::list {
   1.253 +    db eval {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr}
   1.254 +  }
   1.255 +}
   1.256 +explain {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr}
   1.257 +db eval BEGIN
   1.258 +speed_trial_tcl speed1p-update1 5000 stmt $script
   1.259 +db eval COMMIT
   1.260 +
   1.261 +# 50000 single-row updates.  An index is used to find the row quickly.
   1.262 +#
   1.263 +set list {}
   1.264 +for {set i 0} {$i<50000} {incr i} {
   1.265 +  set r [expr {int(rand()*500000)}]
   1.266 +  lappend list $i $r
   1.267 +}
   1.268 +set script {
   1.269 +  foreach {i r} $::list {
   1.270 +    db eval {UPDATE t1 SET b=$r WHERE a=$i}
   1.271 +  }
   1.272 +}
   1.273 +explain {UPDATE t1 SET b=$r WHERE a=$i}
   1.274 +db eval BEGIN
   1.275 +speed_trial_tcl speed1p-update2 50000 row $script
   1.276 +db eval COMMIT
   1.277 +
   1.278 +# 1 big text update that touches every row in the table.
   1.279 +#
   1.280 +explain {UPDATE t1 SET c=a}
   1.281 +speed_trial speed1p-update3 50000 row {
   1.282 +  UPDATE t1 SET c=a;
   1.283 +}
   1.284 +
   1.285 +# Many individual text updates.  Each row in the table is
   1.286 +# touched through an index.
   1.287 +#
   1.288 +set list {}
   1.289 +for {set i 1} {$i<=50000} {incr i} {
   1.290 +  set r [expr {int(rand()*500000)}]
   1.291 +  lappend list $i [number_name $r]
   1.292 +}
   1.293 +set script {
   1.294 +  foreach {i x} $::list {
   1.295 +    db eval {UPDATE t1 SET c=$x WHERE a=$i}
   1.296 +  }
   1.297 +}
   1.298 +explain {UPDATE t1 SET c=$x WHERE a=$i}
   1.299 +db eval BEGIN
   1.300 +speed_trial_tcl speed1p-update4 50000 row $script
   1.301 +db eval COMMIT
   1.302 +
   1.303 +# Delete all content in a table.
   1.304 +#
   1.305 +explain {DELETE FROM t1}
   1.306 +speed_trial speed1p-delete1 50000 row {DELETE FROM t1}
   1.307 +
   1.308 +# Copy one table into another
   1.309 +#
   1.310 +explain {INSERT INTO t1 SELECT * FROM t2}
   1.311 +speed_trial speed1p-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}
   1.312 +
   1.313 +# Delete all content in a table, one row at a time.
   1.314 +#
   1.315 +explain {DELETE FROM t1 WHERE 1}
   1.316 +speed_trial speed1p-delete2 50000 row {DELETE FROM t1 WHERE 1}
   1.317 +
   1.318 +# Refill the table yet again
   1.319 +#
   1.320 +speed_trial speed1p-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}
   1.321 +
   1.322 +# Drop the table and recreate it without its indices.
   1.323 +#
   1.324 +explain {DROP TABLE t1}
   1.325 +explain {CREATE TABLE tX(a INTEGER, b INTEGER, c TEXT)}
   1.326 +db eval BEGIN
   1.327 +speed_trial speed1p-drop1 50000 row {
   1.328 +   DROP TABLE t1;
   1.329 +   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
   1.330 +}
   1.331 +db eval COMMIT
   1.332 +
   1.333 +# Refill the table yet again.  This copy should be faster because
   1.334 +# there are no indices to deal with.
   1.335 +#
   1.336 +speed_trial speed1p-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}
   1.337 +
   1.338 +# Select 20000 rows from the table at random.
   1.339 +#
   1.340 +explain {SELECT rowid FROM t1 ORDER BY random() LIMIT 20000}
   1.341 +speed_trial speed1p-random1 50000 row {
   1.342 +  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000
   1.343 +}
   1.344 +
   1.345 +# Delete 20000 random rows from the table.
   1.346 +#
   1.347 +explain {DELETE FROM t1 WHERE rowid IN
   1.348 +    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)}
   1.349 +speed_trial speed1p-random-del1 20000 row {
   1.350 +  DELETE FROM t1 WHERE rowid IN
   1.351 +    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
   1.352 +}
   1.353 +do_test speed1p-1.1 {
   1.354 +  db one {SELECT count(*) FROM t1}
   1.355 +} 30000
   1.356 +
   1.357 +    
   1.358 +# Delete 20000 more rows at random from the table.
   1.359 +#
   1.360 +speed_trial speed1p-random-del2 20000 row {
   1.361 +  DELETE FROM t1 WHERE rowid IN
   1.362 +    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
   1.363 +}
   1.364 +do_test speed1p-1.2 {
   1.365 +  db one {SELECT count(*) FROM t1}
   1.366 +} 10000
   1.367 +speed_trial_summary speed1
   1.368 +
   1.369 +finish_test