Annotation of embedaddon/sqlite3/test/speed1.test, revision 1.1
1.1 ! misho 1: # 2006 November 23
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # 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. The
! 12: # 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:
! 17: sqlite3_shutdown
! 18: #sqlite3_config_scratch 29000 1
! 19: set old_lookaside [sqlite3_config_lookaside 1000 300]
! 20: #sqlite3_config_pagecache 1024 10000
! 21: set testdir [file dirname $argv0]
! 22: source $testdir/tester.tcl
! 23: speed_trial_init speed1
! 24:
! 25: # Set a uniform random seed
! 26: expr srand(0)
! 27:
! 28: set sqlout [open speed1.txt w]
! 29: proc tracesql {sql} {
! 30: puts $::sqlout $sql\;
! 31: }
! 32: #db trace tracesql
! 33:
! 34: # The number_name procedure below converts its argment (an integer)
! 35: # into a string which is the English-language name for that number.
! 36: #
! 37: # Example:
! 38: #
! 39: # puts [number_name 123] -> "one hundred twenty three"
! 40: #
! 41: set ones {zero one two three four five six seven eight nine
! 42: ten eleven twelve thirteen fourteen fifteen sixteen seventeen
! 43: eighteen nineteen}
! 44: set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
! 45: proc number_name {n} {
! 46: if {$n>=1000} {
! 47: set txt "[number_name [expr {$n/1000}]] thousand"
! 48: set n [expr {$n%1000}]
! 49: } else {
! 50: set txt {}
! 51: }
! 52: if {$n>=100} {
! 53: append txt " [lindex $::ones [expr {$n/100}]] hundred"
! 54: set n [expr {$n%100}]
! 55: }
! 56: if {$n>=20} {
! 57: append txt " [lindex $::tens [expr {$n/10}]]"
! 58: set n [expr {$n%10}]
! 59: }
! 60: if {$n>0} {
! 61: append txt " [lindex $::ones $n]"
! 62: }
! 63: set txt [string trim $txt]
! 64: if {$txt==""} {set txt zero}
! 65: return $txt
! 66: }
! 67:
! 68: # Create a database schema.
! 69: #
! 70: do_test speed1-1.0 {
! 71: execsql {
! 72: PRAGMA page_size=1024;
! 73: PRAGMA cache_size=8192;
! 74: PRAGMA locking_mode=EXCLUSIVE;
! 75: CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
! 76: CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
! 77: CREATE INDEX i2a ON t2(a);
! 78: CREATE INDEX i2b ON t2(b);
! 79: }
! 80: execsql {
! 81: SELECT name FROM sqlite_master ORDER BY 1;
! 82: }
! 83: } {i2a i2b t1 t2}
! 84:
! 85:
! 86: # 50000 INSERTs on an unindexed table
! 87: #
! 88: set sql {}
! 89: for {set i 1} {$i<=50000} {incr i} {
! 90: set r [expr {int(rand()*500000)}]
! 91: append sql "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');\n"
! 92: }
! 93: db eval BEGIN
! 94: speed_trial speed1-insert1 50000 row $sql
! 95: db eval COMMIT
! 96:
! 97: # 50000 INSERTs on an indexed table
! 98: #
! 99: set sql {}
! 100: for {set i 1} {$i<=50000} {incr i} {
! 101: set r [expr {int(rand()*500000)}]
! 102: append sql "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');\n"
! 103: }
! 104: db eval BEGIN
! 105: speed_trial speed1-insert2 50000 row $sql
! 106: db eval COMMIT
! 107:
! 108:
! 109:
! 110: # 50 SELECTs on an integer comparison. There is no index so
! 111: # a full table scan is required.
! 112: #
! 113: set sql {}
! 114: for {set i 0} {$i<50} {incr i} {
! 115: set lwr [expr {$i*100}]
! 116: set upr [expr {($i+10)*100}]
! 117: append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
! 118: }
! 119: db eval BEGIN
! 120: speed_trial speed1-select1 [expr {50*50000}] row $sql
! 121: db eval COMMIT
! 122:
! 123: # 50 SELECTs on an LIKE comparison. There is no index so a full
! 124: # table scan is required.
! 125: #
! 126: set sql {}
! 127: for {set i 0} {$i<50} {incr i} {
! 128: append sql \
! 129: "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"
! 130: }
! 131: db eval BEGIN
! 132: speed_trial speed1-select2 [expr {50*50000}] row $sql
! 133: db eval COMMIT
! 134:
! 135: # Create indices
! 136: #
! 137: db eval BEGIN
! 138: speed_trial speed1-createidx 150000 row {
! 139: CREATE INDEX i1a ON t1(a);
! 140: CREATE INDEX i1b ON t1(b);
! 141: CREATE INDEX i1c ON t1(c);
! 142: }
! 143: db eval COMMIT
! 144:
! 145: # 5000 SELECTs on an integer comparison where the integer is
! 146: # indexed.
! 147: #
! 148: set sql {}
! 149: for {set i 0} {$i<5000} {incr i} {
! 150: set lwr [expr {$i*100}]
! 151: set upr [expr {($i+10)*100}]
! 152: append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
! 153: }
! 154: db eval BEGIN
! 155: speed_trial speed1-select3 5000 stmt $sql
! 156: db eval COMMIT
! 157:
! 158: # 100000 random SELECTs against rowid.
! 159: #
! 160: set sql {}
! 161: for {set i 1} {$i<=100000} {incr i} {
! 162: set id [expr {int(rand()*50000)+1}]
! 163: append sql "SELECT c FROM t1 WHERE rowid=$id;"
! 164: }
! 165: db eval BEGIN
! 166: speed_trial speed1-select4 100000 row $sql
! 167: db eval COMMIT
! 168:
! 169: # 100000 random SELECTs against a unique indexed column.
! 170: #
! 171: set sql {}
! 172: for {set i 1} {$i<=100000} {incr i} {
! 173: set id [expr {int(rand()*50000)+1}]
! 174: append sql "SELECT c FROM t1 WHERE a=$id;"
! 175: }
! 176: db eval BEGIN
! 177: speed_trial speed1-select5 100000 row $sql
! 178: db eval COMMIT
! 179:
! 180: # 50000 random SELECTs against an indexed column text column
! 181: #
! 182: set sql {}
! 183: db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {
! 184: append sql "SELECT c FROM t1 WHERE c='$c';"
! 185: }
! 186: db eval BEGIN
! 187: speed_trial speed1-select6 50000 row $sql
! 188: db eval COMMIT
! 189:
! 190:
! 191: # Vacuum
! 192: speed_trial speed1-vacuum 100000 row VACUUM
! 193:
! 194: # 5000 updates of ranges where the field being compared is indexed.
! 195: #
! 196: set sql {}
! 197: for {set i 0} {$i<5000} {incr i} {
! 198: set lwr [expr {$i*2}]
! 199: set upr [expr {($i+1)*2}]
! 200: append sql "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
! 201: }
! 202: db eval BEGIN
! 203: speed_trial speed1-update1 5000 stmt $sql
! 204: db eval COMMIT
! 205:
! 206: # 50000 single-row updates. An index is used to find the row quickly.
! 207: #
! 208: set sql {}
! 209: for {set i 0} {$i<50000} {incr i} {
! 210: set r [expr {int(rand()*500000)}]
! 211: append sql "UPDATE t1 SET b=$r WHERE a=$i;"
! 212: }
! 213: db eval BEGIN
! 214: speed_trial speed1-update2 50000 row $sql
! 215: db eval COMMIT
! 216:
! 217: # 1 big text update that touches every row in the table.
! 218: #
! 219: speed_trial speed1-update3 50000 row {
! 220: UPDATE t1 SET c=a;
! 221: }
! 222:
! 223: # Many individual text updates. Each row in the table is
! 224: # touched through an index.
! 225: #
! 226: set sql {}
! 227: for {set i 1} {$i<=50000} {incr i} {
! 228: set r [expr {int(rand()*500000)}]
! 229: append sql "UPDATE t1 SET c='[number_name $r]' WHERE a=$i;"
! 230: }
! 231: db eval BEGIN
! 232: speed_trial speed1-update4 50000 row $sql
! 233: db eval COMMIT
! 234:
! 235: # Delete all content in a table.
! 236: #
! 237: speed_trial speed1-delete1 50000 row {DELETE FROM t1}
! 238:
! 239: # Copy one table into another
! 240: #
! 241: speed_trial speed1-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}
! 242:
! 243: # Delete all content in a table, one row at a time.
! 244: #
! 245: speed_trial speed1-delete2 50000 row {DELETE FROM t1 WHERE 1}
! 246:
! 247: # Refill the table yet again
! 248: #
! 249: speed_trial speed1-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}
! 250:
! 251: # Drop the table and recreate it without its indices.
! 252: #
! 253: db eval BEGIN
! 254: speed_trial speed1-drop1 50000 row {
! 255: DROP TABLE t1;
! 256: CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
! 257: }
! 258: db eval COMMIT
! 259:
! 260: # Refill the table yet again. This copy should be faster because
! 261: # there are no indices to deal with.
! 262: #
! 263: speed_trial speed1-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}
! 264:
! 265: # Select 20000 rows from the table at random.
! 266: #
! 267: speed_trial speed1-random1 50000 row {
! 268: SELECT rowid FROM t1 ORDER BY random() LIMIT 20000
! 269: }
! 270:
! 271: # Delete 20000 random rows from the table.
! 272: #
! 273: speed_trial speed1-random-del1 20000 row {
! 274: DELETE FROM t1 WHERE rowid IN
! 275: (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
! 276: }
! 277: do_test speed1-1.1 {
! 278: db one {SELECT count(*) FROM t1}
! 279: } 30000
! 280:
! 281:
! 282: # Delete 20000 more rows at random from the table.
! 283: #
! 284: speed_trial speed1-random-del2 20000 row {
! 285: DELETE FROM t1 WHERE rowid IN
! 286: (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
! 287: }
! 288: do_test speed1-1.2 {
! 289: db one {SELECT count(*) FROM t1}
! 290: } 10000
! 291: speed_trial_summary speed1
! 292:
! 293: db close
! 294: sqlite3_shutdown
! 295: eval sqlite3_config_lookaside $old_lookaside
! 296: sqlite3_initialize
! 297: autoinstall_test_functions
! 298: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speed1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test