AryaWu/sqlite

1# 2006 November 232#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# $Id: speed2.test,v 1.7 2007/04/16 15:02:20 drh Exp $15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19speed_trial_init speed220 21# Set a uniform random seed22expr srand(0)23 24set sqlout [open speed2.txt w]25proc tracesql {sql} {26  puts $::sqlout $sql\;27}28#db trace tracesql29 30# The number_name procedure below converts its argment (an integer)31# into a string which is the English-language name for that number.32#33# Example:34#35#     puts [number_name 123]   ->  "one hundred twenty three"36#37set ones {zero one two three four five six seven eight nine38          ten eleven twelve thirteen fourteen fifteen sixteen seventeen39          eighteen nineteen}40set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}41proc number_name {n} {42  if {$n>=1000} {43    set txt "[number_name [expr {$n/1000}]] thousand"44    set n [expr {$n%1000}]45  } else {46    set txt {}47  }48  if {$n>=100} {49    append txt " [lindex $::ones [expr {$n/100}]] hundred"50    set n [expr {$n%100}]51  }52  if {$n>=20} {53    append txt " [lindex $::tens [expr {$n/10}]]"54    set n [expr {$n%10}]55  }56  if {$n>0} {57    append txt " [lindex $::ones $n]"58  }59  set txt [string trim $txt]60  if {$txt==""} {set txt zero}61  return $txt62}63 64# Create a database schema.65#66do_test speed2-1.0 {67  execsql {68    PRAGMA page_size=1024;69    PRAGMA cache_size=8192;70    PRAGMA locking_mode=EXCLUSIVE;71    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);72    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);73    CREATE INDEX i2a ON t2(a);74    CREATE INDEX i2b ON t2(b);75  }76  execsql {77    SELECT name FROM sqlite_master ORDER BY 1;78  }79} {i2a i2b t1 t2}80 81 82# 50000 INSERTs on an unindexed table83#84set sql {}85for {set i 1} {$i<=50000} {incr i} {86  set r [expr {int(rand()*500000)}]87  append sql "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');\n"88}89db eval BEGIN90speed_trial speed2-insert1 50000 row $sql91db eval COMMIT92 93# 50000 INSERTs on an indexed table94#95set sql {}96for {set i 1} {$i<=50000} {incr i} {97  set r [expr {int(rand()*500000)}]98  append sql "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');\n"99}100db eval BEGIN101speed_trial speed2-insert2 50000 row $sql102db eval COMMIT103 104 105 106# 50 SELECTs on an integer comparison.  There is no index so107# a full table scan is required.108#109set sql {}110for {set i 0} {$i<50} {incr i} {111  set lwr [expr {$i*100}]112  set upr [expr {($i+10)*100}]113  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"114}115speed_trial speed2-select1a [expr {50*50000}] row $sql116 117# 50 SELECTs on an LIKE comparison.  There is no index so a full118# table scan is required.119#120set sql {}121for {set i 0} {$i<50} {incr i} {122  append sql \123    "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"124}125speed_trial speed2-select2a [expr {50*50000}] row $sql126 127# Vacuum128speed_trial speed2-vacuum1 100000 row VACUUM129 130# 50 SELECTs on an integer comparison.  There is no index so131# a full table scan is required.132#133set sql {}134for {set i 0} {$i<50} {incr i} {135  set lwr [expr {$i*100}]136  set upr [expr {($i+10)*100}]137  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"138}139speed_trial speed2-select1b [expr {50*50000}] row $sql140 141# 50 SELECTs on an LIKE comparison.  There is no index so a full142# table scan is required.143#144set sql {}145for {set i 0} {$i<50} {incr i} {146  append sql \147    "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"148}149speed_trial speed2-select2b [expr {50*50000}] row $sql150 151# Create indices152#153db eval BEGIN154speed_trial speed2-createidx 150000 row {155  CREATE INDEX i1a ON t1(a);156  CREATE INDEX i1b ON t1(b);157  CREATE INDEX i1c ON t1(c);158}159db eval COMMIT160 161# 5000 SELECTs on an integer comparison where the integer is162# indexed.163#164set sql {}165for {set i 0} {$i<5000} {incr i} {166  set lwr [expr {$i*100}]167  set upr [expr {($i+10)*100}]168  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"169}170speed_trial speed2-select3a 5000 stmt $sql171 172# 100000 random SELECTs against rowid.173#174set sql {}175for {set i 1} {$i<=100000} {incr i} {176  set id [expr {int(rand()*50000)+1}]177  append sql "SELECT c=='hi' FROM t1 WHERE rowid=$id;\n"178}179speed_trial speed2-select4a 100000 row $sql180 181# 100000 random SELECTs against a unique indexed column.182#183set sql {}184for {set i 1} {$i<=100000} {incr i} {185  set id [expr {int(rand()*50000)+1}]186  append sql "SELECT c FROM t1 WHERE a=$id;"187}188speed_trial speed2-select5a 100000 row $sql189 190# 50000 random SELECTs against an indexed column text column191#192set sql {}193db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {194  append sql "SELECT c FROM t1 WHERE c='$c';"195}196speed_trial speed2-select6a 50000 row $sql197 198# Vacuum199speed_trial speed2-vacuum2 100000 row VACUUM200 201 202# 5000 SELECTs on an integer comparison where the integer is203# indexed.204#205set sql {}206for {set i 0} {$i<5000} {incr i} {207  set lwr [expr {$i*100}]208  set upr [expr {($i+10)*100}]209  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"210}211speed_trial speed2-select3b 5000 stmt $sql212 213# 100000 random SELECTs against rowid.214#215set sql {}216for {set i 1} {$i<=100000} {incr i} {217  set id [expr {int(rand()*50000)+1}]218  append sql "SELECT c=='hi' FROM t1 WHERE rowid=$id;\n"219}220speed_trial speed2-select4b 100000 row $sql221 222# 100000 random SELECTs against a unique indexed column.223#224set sql {}225for {set i 1} {$i<=100000} {incr i} {226  set id [expr {int(rand()*50000)+1}]227  append sql "SELECT c FROM t1 WHERE a=$id;"228}229speed_trial speed2-select5b 100000 row $sql230 231# 50000 random SELECTs against an indexed column text column232#233set sql {}234db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {235  append sql "SELECT c FROM t1 WHERE c='$c';"236}237speed_trial speed2-select6b 50000 row $sql238 239# 5000 updates of ranges where the field being compared is indexed.240#241set sql {}242for {set i 0} {$i<5000} {incr i} {243  set lwr [expr {$i*2}]244  set upr [expr {($i+1)*2}]245  append sql "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"246}247db eval BEGIN248speed_trial speed2-update1 5000 stmt $sql249db eval COMMIT250 251# 50000 single-row updates.  An index is used to find the row quickly.252#253set sql {}254for {set i 0} {$i<50000} {incr i} {255  set r [expr {int(rand()*500000)}]256  append sql "UPDATE t1 SET b=$r WHERE a=$i;"257}258db eval BEGIN259speed_trial speed2-update2 50000 row $sql260db eval COMMIT261 262# 1 big text update that touches every row in the table.263#264speed_trial speed2-update3 50000 row {265  UPDATE t1 SET c=a;266}267 268# Many individual text updates.  Each row in the table is269# touched through an index.270#271set sql {}272for {set i 1} {$i<=50000} {incr i} {273  set r [expr {int(rand()*500000)}]274  append sql "UPDATE t1 SET c='[number_name $r]' WHERE a=$i;"275}276db eval BEGIN277speed_trial speed2-update4 50000 row $sql278db eval COMMIT279 280# Delete all content in a table.281#282speed_trial speed2-delete1 50000 row {DELETE FROM t1}283 284# Copy one table into another285#286speed_trial speed2-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}287 288# Delete all content in a table, one row at a time.289#290speed_trial speed2-delete2 50000 row {DELETE FROM t1 WHERE 1}291 292# Refill the table yet again293#294speed_trial speed2-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}295 296# Drop the table and recreate it without its indices.297#298db eval BEGIN299speed_trial speed2-drop1 50000 row {300   DROP TABLE t1;301   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);302}303db eval COMMIT304 305# Refill the table yet again.  This copy should be faster because306# there are no indices to deal with.307#308speed_trial speed2-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}309 310# Select 20000 rows from the table at random.311#312speed_trial speed2-random1 50000 row {313  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000314}315 316# Delete 20000 random rows from the table.317#318speed_trial speed2-random-del1 20000 row {319  DELETE FROM t1 WHERE rowid IN320    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)321}322do_test speed2-1.1 {323  db one {SELECT count(*) FROM t1}324} 30000325 326    327# Delete 20000 more rows at random from the table.328#329speed_trial speed2-random-del2 20000 row {330  DELETE FROM t1 WHERE rowid IN331    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)332}333do_test speed2-1.2 {334  db one {SELECT count(*) FROM t1}335} 10000336speed_trial_summary speed2337 338 339finish_test340