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: speed1.test,v 1.11 2009/04/09 01:23:49 drh Exp $15#16 17catch {db close}18sqlite3_shutdown19#sqlite3_config_scratch 29000 120set old_lookaside [sqlite3_config_lookaside 1000 300]21#sqlite3_config_pagecache 1024 1000022set testdir [file dirname $argv0]23source $testdir/tester.tcl24reset_db25speed_trial_init speed126 27# Set a uniform random seed28expr srand(0)29 30set sqlout [open speed1.txt w]31proc tracesql {sql} {32  puts $::sqlout $sql\;33}34#db trace tracesql35 36# The number_name procedure below converts its argment (an integer)37# into a string which is the English-language name for that number.38#39# Example:40#41#     puts [number_name 123]   ->  "one hundred twenty three"42#43set ones {zero one two three four five six seven eight nine44          ten eleven twelve thirteen fourteen fifteen sixteen seventeen45          eighteen nineteen}46set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}47proc number_name {n} {48  if {$n>=1000} {49    set txt "[number_name [expr {$n/1000}]] thousand"50    set n [expr {$n%1000}]51  } else {52    set txt {}53  }54  if {$n>=100} {55    append txt " [lindex $::ones [expr {$n/100}]] hundred"56    set n [expr {$n%100}]57  }58  if {$n>=20} {59    append txt " [lindex $::tens [expr {$n/10}]]"60    set n [expr {$n%10}]61  }62  if {$n>0} {63    append txt " [lindex $::ones $n]"64  }65  set txt [string trim $txt]66  if {$txt==""} {set txt zero}67  return $txt68}69 70# Create a database schema.71#72do_test speed1-1.0 {73  execsql {74    PRAGMA page_size=1024;75    PRAGMA cache_size=8192;76    PRAGMA locking_mode=EXCLUSIVE;77    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);78    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);79    CREATE INDEX i2a ON t2(a);80    CREATE INDEX i2b ON t2(b);81  }82  execsql {83    SELECT name FROM sqlite_master ORDER BY 1;84  }85} {i2a i2b t1 t2}86 87 88# 50000 INSERTs on an unindexed table89#90set sql {}91for {set i 1} {$i<=50000} {incr i} {92  set r [expr {int(rand()*500000)}]93  append sql "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');\n"94}95db eval BEGIN96speed_trial speed1-insert1 50000 row $sql97db eval COMMIT98 99# 50000 INSERTs on an indexed table100#101set sql {}102for {set i 1} {$i<=50000} {incr i} {103  set r [expr {int(rand()*500000)}]104  append sql "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');\n"105}106db eval BEGIN107speed_trial speed1-insert2 50000 row $sql108db eval COMMIT109 110 111 112# 50 SELECTs on an integer comparison.  There is no index so113# a full table scan is required.114#115set sql {}116for {set i 0} {$i<50} {incr i} {117  set lwr [expr {$i*100}]118  set upr [expr {($i+10)*100}]119  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"120}121db eval BEGIN122speed_trial speed1-select1 [expr {50*50000}] row $sql123db eval COMMIT124 125# 50 SELECTs on an LIKE comparison.  There is no index so a full126# table scan is required.127#128set sql {}129for {set i 0} {$i<50} {incr i} {130  append sql \131    "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"132}133db eval BEGIN134speed_trial speed1-select2 [expr {50*50000}] row $sql135db eval COMMIT136 137# Create indices138#139db eval BEGIN140speed_trial speed1-createidx 150000 row {141  CREATE INDEX i1a ON t1(a);142  CREATE INDEX i1b ON t1(b);143  CREATE INDEX i1c ON t1(c);144}145db eval COMMIT146 147# 5000 SELECTs on an integer comparison where the integer is148# indexed.149#150set sql {}151for {set i 0} {$i<5000} {incr i} {152  set lwr [expr {$i*100}]153  set upr [expr {($i+10)*100}]154  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"155}156db eval BEGIN157speed_trial speed1-select3 5000 stmt $sql158db eval COMMIT159 160# 100000 random SELECTs against rowid.161#162set sql {}163for {set i 1} {$i<=100000} {incr i} {164  set id [expr {int(rand()*50000)+1}]165  append sql "SELECT c FROM t1 WHERE rowid=$id;"166}167db eval BEGIN168speed_trial speed1-select4 100000 row $sql169db eval COMMIT170 171# 100000 random SELECTs against a unique indexed column.172#173set sql {}174for {set i 1} {$i<=100000} {incr i} {175  set id [expr {int(rand()*50000)+1}]176  append sql "SELECT c FROM t1 WHERE a=$id;"177}178db eval BEGIN179speed_trial speed1-select5 100000 row $sql180db eval COMMIT181 182# 50000 random SELECTs against an indexed column text column183#184set sql {}185db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {186  append sql "SELECT c FROM t1 WHERE c='$c';"187}188db eval BEGIN189speed_trial speed1-select6 50000 row $sql190db eval COMMIT191 192 193# Vacuum194speed_trial speed1-vacuum 100000 row VACUUM195 196# 5000 updates of ranges where the field being compared is indexed.197#198set sql {}199for {set i 0} {$i<5000} {incr i} {200  set lwr [expr {$i*2}]201  set upr [expr {($i+1)*2}]202  append sql "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"203}204db eval BEGIN205speed_trial speed1-update1 5000 stmt $sql206db eval COMMIT207 208# 50000 single-row updates.  An index is used to find the row quickly.209#210set sql {}211for {set i 0} {$i<50000} {incr i} {212  set r [expr {int(rand()*500000)}]213  append sql "UPDATE t1 SET b=$r WHERE a=$i;"214}215db eval BEGIN216speed_trial speed1-update2 50000 row $sql217db eval COMMIT218 219# 1 big text update that touches every row in the table.220#221speed_trial speed1-update3 50000 row {222  UPDATE t1 SET c=a;223}224 225# Many individual text updates.  Each row in the table is226# touched through an index.227#228set sql {}229for {set i 1} {$i<=50000} {incr i} {230  set r [expr {int(rand()*500000)}]231  append sql "UPDATE t1 SET c='[number_name $r]' WHERE a=$i;"232}233db eval BEGIN234speed_trial speed1-update4 50000 row $sql235db eval COMMIT236 237# Delete all content in a table.238#239speed_trial speed1-delete1 50000 row {DELETE FROM t1}240 241# Copy one table into another242#243speed_trial speed1-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}244 245# Delete all content in a table, one row at a time.246#247speed_trial speed1-delete2 50000 row {DELETE FROM t1 WHERE 1}248 249# Refill the table yet again250#251speed_trial speed1-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}252 253# Drop the table and recreate it without its indices.254#255db eval BEGIN256speed_trial speed1-drop1 50000 row {257   DROP TABLE t1;258   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);259}260db eval COMMIT261 262# Refill the table yet again.  This copy should be faster because263# there are no indices to deal with.264#265speed_trial speed1-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}266 267# Select 20000 rows from the table at random.268#269speed_trial speed1-random1 50000 row {270  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000271}272 273# Delete 20000 random rows from the table.274#275speed_trial speed1-random-del1 20000 row {276  DELETE FROM t1 WHERE rowid IN277    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)278}279do_test speed1-1.1 {280  db one {SELECT count(*) FROM t1}281} 30000282 283    284# Delete 20000 more rows at random from the table.285#286speed_trial speed1-random-del2 20000 row {287  DELETE FROM t1 WHERE rowid IN288    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)289}290do_test speed1-1.2 {291  db one {SELECT count(*) FROM t1}292} 10000293speed_trial_summary speed1294 295db close296sqlite3_shutdown297eval sqlite3_config_lookaside $old_lookaside298sqlite3_initialize299autoinstall_test_functions300finish_test301