AryaWu/sqlite

1# 2008 March 212#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#*************************************************************************11# This file implements regression tests for SQLite library.  The12# focus of this script is measuring executing speed.  13#14# This is a copy of speed1.test modified to user prepared statements.15#16# $Id: speed1p.explain,v 1.1 2008/04/16 12:57:48 drh Exp $17#18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21speed_trial_init speed122 23# Set a uniform random seed24expr srand(0)25 26set sqlout [open speed1.txt w]27proc tracesql {sql} {28  puts $::sqlout $sql\;29}30#db trace tracesql31 32# The number_name procedure below converts its argment (an integer)33# into a string which is the English-language name for that number.34#35# Example:36#37#     puts [number_name 123]   ->  "one hundred twenty three"38#39set ones {zero one two three four five six seven eight nine40          ten eleven twelve thirteen fourteen fifteen sixteen seventeen41          eighteen nineteen}42set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}43proc number_name {n} {44  if {$n>=1000} {45    set txt "[number_name [expr {$n/1000}]] thousand"46    set n [expr {$n%1000}]47  } else {48    set txt {}49  }50  if {$n>=100} {51    append txt " [lindex $::ones [expr {$n/100}]] hundred"52    set n [expr {$n%100}]53  }54  if {$n>=20} {55    append txt " [lindex $::tens [expr {$n/10}]]"56    set n [expr {$n%10}]57  }58  if {$n>0} {59    append txt " [lindex $::ones $n]"60  }61  set txt [string trim $txt]62  if {$txt==""} {set txt zero}63  return $txt64}65 66# Create a database schema.67#68do_test speed1p-1.0 {69  execsql {70    PRAGMA page_size=1024;71    PRAGMA cache_size=8192;72    PRAGMA locking_mode=EXCLUSIVE;73    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);74    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);75    CREATE INDEX i2a ON t2(a);76    CREATE INDEX i2b ON t2(b);77  }78  execsql {79    SELECT name FROM sqlite_master ORDER BY 1;80  }81} {i2a i2b t1 t2}82 83 84# 50000 INSERTs on an unindexed table85#86set list {}87for {set i 1} {$i<=50000} {incr i} {88  set r [expr {int(rand()*500000)}]89  set x [number_name $r]90  lappend list $i $r $x91}92set script {93  foreach {i r x} $::list {94    db eval {INSERT INTO t1 VALUES($i,$r,$x)}95  }96}97explain {INSERT INTO t1 VALUES($i,$r,$x)}98db eval BEGIN99speed_trial_tcl speed1p-insert1 50000 row $script100db eval COMMIT101 102# 50000 INSERTs on an indexed table103#104set list {}105for {set i 1} {$i<=50000} {incr i} {106  set r [expr {int(rand()*500000)}]107  set x [number_name $r]108  lappend list $i $r $x109}110set script {111  foreach {i r x} $::list {112    db eval {INSERT INTO t2 VALUES($i,$r,$x)}113  }114}115explain {INSERT INTO t2 VALUES($i,$r,$x)}116db eval BEGIN117speed_trial_tcl speed1p-insert2 50000 row $script118db eval COMMIT119 120 121 122# 50 SELECTs on an integer comparison.  There is no index so123# a full table scan is required.124#125set list {}126for {set i 0} {$i<50} {incr i} {127  set lwr [expr {$i*100}]128  set upr [expr {($i+10)*100}]129  lappend list $lwr $upr130}131set script {132  foreach {lwr upr} $::list {133    db eval  {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}134  }135}136explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}137db eval BEGIN138speed_trial_tcl speed1p-select1 [expr {50*50000}] row $script139db eval COMMIT140 141# 50 SELECTs on an LIKE comparison.  There is no index so a full142# table scan is required.143#144set list {}145for {set i 0} {$i<50} {incr i} {146  lappend list "%[number_name $i]%"147}148set script {149  foreach pattern $::list {150    db eval {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern}151  }152}153explain {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern}154db eval BEGIN155speed_trial_tcl speed1p-select2 [expr {50*50000}] row $script156db eval COMMIT157 158# Create indices159#160explain {CREATE INDEX i1a ON t1(a)}161explain {CREATE INDEX i1b ON t1(b)}162db eval BEGIN163speed_trial speed1p-createidx 150000 row {164  CREATE INDEX i1a ON t1(a);165  CREATE INDEX i1b ON t1(b);166  CREATE INDEX i1c ON t1(c);167}168db eval COMMIT169 170# 5000 SELECTs on an integer comparison where the integer is171# indexed.172#173set list {}174for {set i 0} {$i<5000} {incr i} {175  set lwr [expr {$i*100}]176  set upr [expr {($i+10)*100}]177  lappend list $lwr $upr178}179set script {180  foreach {lwr upr} $::list {181    db eval {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}182  }183}184explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}185db eval BEGIN186speed_trial_tcl speed1p-select3 5000 stmt $script187db eval COMMIT188 189# 100000 random SELECTs against rowid.190#191set list {}192for {set i 1} {$i<=100000} {incr i} {193  set id [expr {int(rand()*50000)+1}]194  lappend list $id195}196set script {197  foreach id $::list {198    db eval {SELECT c FROM t1 WHERE rowid=$id}199  }200}201explain {SELECT c FROM t1 WHERE rowid=$id}202db eval BEGIN203speed_trial_tcl speed1p-select4 100000 row $script204db eval COMMIT205 206# 100000 random SELECTs against a unique indexed column.207#208set list {}209for {set i 1} {$i<=100000} {incr i} {210  set id [expr {int(rand()*50000)+1}]211  lappend list $id212}213set script {214  foreach id $::list {215    db eval {SELECT c FROM t1 WHERE a=$id}216  }217}218explain {SELECT c FROM t1 WHERE a=$id}219db eval BEGIN220speed_trial_tcl speed1p-select5 100000 row $script221db eval COMMIT222 223# 50000 random SELECTs against an indexed column text column224#225set list [db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000}]226set script {227  foreach c $::list {228    db eval {SELECT c FROM t1 WHERE c=$c}229  }230}231explain {SELECT c FROM t1 WHERE c=$c}232db eval BEGIN233speed_trial_tcl speed1p-select6 50000 row $script234db eval COMMIT235 236 237# Vacuum238speed_trial speed1p-vacuum 100000 row VACUUM239 240# 5000 updates of ranges where the field being compared is indexed.241#242set list {}243for {set i 0} {$i<5000} {incr i} {244  set lwr [expr {$i*2}]245  set upr [expr {($i+1)*2}]246  lappend list $lwr $upr247}248set script {249  foreach {lwr upr} $::list {250    db eval {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr}251  }252}253explain {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr}254db eval BEGIN255speed_trial_tcl speed1p-update1 5000 stmt $script256db eval COMMIT257 258# 50000 single-row updates.  An index is used to find the row quickly.259#260set list {}261for {set i 0} {$i<50000} {incr i} {262  set r [expr {int(rand()*500000)}]263  lappend list $i $r264}265set script {266  foreach {i r} $::list {267    db eval {UPDATE t1 SET b=$r WHERE a=$i}268  }269}270explain {UPDATE t1 SET b=$r WHERE a=$i}271db eval BEGIN272speed_trial_tcl speed1p-update2 50000 row $script273db eval COMMIT274 275# 1 big text update that touches every row in the table.276#277explain {UPDATE t1 SET c=a}278speed_trial speed1p-update3 50000 row {279  UPDATE t1 SET c=a;280}281 282# Many individual text updates.  Each row in the table is283# touched through an index.284#285set list {}286for {set i 1} {$i<=50000} {incr i} {287  set r [expr {int(rand()*500000)}]288  lappend list $i [number_name $r]289}290set script {291  foreach {i x} $::list {292    db eval {UPDATE t1 SET c=$x WHERE a=$i}293  }294}295explain {UPDATE t1 SET c=$x WHERE a=$i}296db eval BEGIN297speed_trial_tcl speed1p-update4 50000 row $script298db eval COMMIT299 300# Delete all content in a table.301#302explain {DELETE FROM t1}303speed_trial speed1p-delete1 50000 row {DELETE FROM t1}304 305# Copy one table into another306#307explain {INSERT INTO t1 SELECT * FROM t2}308speed_trial speed1p-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}309 310# Delete all content in a table, one row at a time.311#312explain {DELETE FROM t1 WHERE 1}313speed_trial speed1p-delete2 50000 row {DELETE FROM t1 WHERE 1}314 315# Refill the table yet again316#317speed_trial speed1p-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}318 319# Drop the table and recreate it without its indices.320#321explain {DROP TABLE t1}322explain {CREATE TABLE tX(a INTEGER, b INTEGER, c TEXT)}323db eval BEGIN324speed_trial speed1p-drop1 50000 row {325   DROP TABLE t1;326   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);327}328db eval COMMIT329 330# Refill the table yet again.  This copy should be faster because331# there are no indices to deal with.332#333speed_trial speed1p-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}334 335# Select 20000 rows from the table at random.336#337explain {SELECT rowid FROM t1 ORDER BY random() LIMIT 20000}338speed_trial speed1p-random1 50000 row {339  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000340}341 342# Delete 20000 random rows from the table.343#344explain {DELETE FROM t1 WHERE rowid IN345    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)}346speed_trial speed1p-random-del1 20000 row {347  DELETE FROM t1 WHERE rowid IN348    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)349}350do_test speed1p-1.1 {351  db one {SELECT count(*) FROM t1}352} 30000353 354    355# Delete 20000 more rows at random from the table.356#357speed_trial speed1p-random-del2 20000 row {358  DELETE FROM t1 WHERE rowid IN359    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)360}361do_test speed1p-1.2 {362  db one {SELECT count(*) FROM t1}363} 10000364speed_trial_summary speed1365 366finish_test367