Annotation of embedaddon/sqlite3/test/speed3.test, revision 1.1.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