Annotation of embedaddon/sqlite3/test/speed3.test, revision 1.1
1.1 ! misho 1: # 2007 May 17
! 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 testing that the overflow-page related
! 13: # enhancements added after version 3.3.17 speed things up.
! 14: #
! 15: # $Id: speed3.test,v 1.6 2009/07/09 02:48:24 shane Exp $
! 16: #
! 17:
! 18: #---------------------------------------------------------------------
! 19: # Test plan:
! 20: #
! 21: # If auto-vacuum is enabled for the database, the following cases
! 22: # should show performance improvement with respect to 3.3.17.
! 23: #
! 24: # + When deleting rows that span overflow pages. This is faster
! 25: # because the overflow pages no longer need to be read before
! 26: # they can be moved to the free list (test cases speed3-1.X).
! 27: #
! 28: # + When reading a column value stored on an overflow page that
! 29: # is not the first overflow page for the row. The improvement
! 30: # in this case is because the overflow pages between the tree
! 31: # page and the overflow page containing the value do not have
! 32: # to be read (test cases speed3-2.X).
! 33: #
! 34:
! 35: set testdir [file dirname $argv0]
! 36: source $testdir/tester.tcl
! 37:
! 38: ifcapable !tclvar||!attach {
! 39: finish_test
! 40: return
! 41: }
! 42:
! 43: speed_trial_init speed1
! 44:
! 45: # Set a uniform random seed
! 46: expr srand(0)
! 47:
! 48: set ::NROW 1000
! 49:
! 50: # The number_name procedure below converts its argment (an integer)
! 51: # into a string which is the English-language name for that number.
! 52: #
! 53: # Example:
! 54: #
! 55: # puts [number_name 123] -> "one hundred twenty three"
! 56: #
! 57: set ones {zero one two three four five six seven eight nine
! 58: ten eleven twelve thirteen fourteen fifteen sixteen seventeen
! 59: eighteen nineteen}
! 60: set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
! 61: proc number_name {n} {
! 62: if {$n>=1000} {
! 63: set txt "[number_name [expr {$n/1000}]] thousand"
! 64: set n [expr {$n%1000}]
! 65: } else {
! 66: set txt {}
! 67: }
! 68: if {$n>=100} {
! 69: append txt " [lindex $::ones [expr {$n/100}]] hundred"
! 70: set n [expr {$n%100}]
! 71: }
! 72: if {$n>=20} {
! 73: append txt " [lindex $::tens [expr {$n/10}]]"
! 74: set n [expr {$n%10}]
! 75: }
! 76: if {$n>0} {
! 77: append txt " [lindex $::ones $n]"
! 78: }
! 79: set txt [string trim $txt]
! 80: if {$txt==""} {set txt zero}
! 81: return $txt
! 82: }
! 83:
! 84: proc populate_t1 {db} {
! 85: $db transaction {
! 86: for {set ii 0} {$ii < $::NROW} {incr ii} {
! 87: set N [number_name $ii]
! 88: set repeats [expr {(10000/[string length $N])+1}]
! 89: set text [string range [string repeat $N $repeats] 0 10000]
! 90: $db eval {INSERT INTO main.t1 VALUES($ii, $text, $ii)}
! 91: }
! 92: $db eval {INSERT INTO aux.t1 SELECT * FROM main.t1}
! 93: }
! 94: }
! 95:
! 96:
! 97: proc io_log {db} {
! 98: db_enter db
! 99: array set stats1 [btree_pager_stats [btree_from_db db]]
! 100: array set stats2 [btree_pager_stats [btree_from_db db 2]]
! 101: db_leave db
! 102: # puts "1: [array get stats1]"
! 103: # puts "2: [array get stats2]"
! 104: puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"
! 105: puts "Normal : Read $stats2(read), wrote $stats2(write)"
! 106: }
! 107:
! 108: proc reset_db {} {
! 109: db close
! 110: sqlite3 db test.db
! 111: db eval {
! 112: PRAGMA main.cache_size = 200000;
! 113: PRAGMA main.auto_vacuum = 'incremental';
! 114: ATTACH 'test2.db' AS 'aux';
! 115: PRAGMA aux.auto_vacuum = 'none';
! 116: }
! 117: }
! 118:
! 119: forcedelete test2.db test2.db-journal
! 120: reset_db
! 121:
! 122: # Set up a database in auto-vacuum mode and create a database schema.
! 123: #
! 124: do_test speed3-0.1 {
! 125: execsql {
! 126: CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
! 127: }
! 128: execsql {
! 129: SELECT name FROM sqlite_master ORDER BY 1;
! 130: }
! 131: } {t1}
! 132: do_test speed3-0.2 {
! 133: execsql {
! 134: CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
! 135: }
! 136: execsql {
! 137: SELECT name FROM aux.sqlite_master ORDER BY 1;
! 138: }
! 139: } {t1}
! 140: do_test speed3-0.3 {
! 141: populate_t1 db
! 142: execsql {
! 143: SELECT count(*) FROM main.t1;
! 144: SELECT count(*) FROM aux.t1;
! 145: }
! 146: } "$::NROW $::NROW"
! 147: do_test speed3-0.4 {
! 148: execsql {
! 149: PRAGMA main.auto_vacuum;
! 150: PRAGMA aux.auto_vacuum;
! 151: }
! 152: } {2 0}
! 153:
! 154: # Delete all content in a table, one row at a time.
! 155: #
! 156: #io_log db
! 157: reset_db
! 158: speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
! 159: speed_trial speed3-1.normal $::NROW row {DELETE FROM aux.t1 WHERE 1}
! 160: io_log db
! 161:
! 162: # Select the "C" column (located at the far end of the overflow
! 163: # chain) from each table row.
! 164: #
! 165: #db eval {PRAGMA incremental_vacuum(500000)}
! 166: populate_t1 db
! 167: reset_db
! 168: speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
! 169: speed_trial speed3-2.normal $::NROW row {SELECT c FROM aux.t1}
! 170: io_log db
! 171:
! 172: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speed3.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