|
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.5 2007/10/09 08:29:33 danielk1977 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 overflow_report {db} { |
|
109 set bt [btree_from_db db] |
|
110 set csr [btree_cursor $bt 3 0] |
|
111 |
|
112 for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} { |
|
113 puts "[btree_ovfl_info $bt $csr]" |
|
114 } |
|
115 |
|
116 btree_close_cursor $csr |
|
117 |
|
118 } |
|
119 |
|
120 proc reset_db {} { |
|
121 db close |
|
122 sqlite3 db test.db |
|
123 db eval { |
|
124 PRAGMA main.cache_size = 200000; |
|
125 PRAGMA main.auto_vacuum = 'incremental'; |
|
126 ATTACH 'test2.db' AS 'aux'; |
|
127 PRAGMA aux.auto_vacuum = 'none'; |
|
128 } |
|
129 } |
|
130 |
|
131 file delete -force test2.db test2.db-journal |
|
132 reset_db |
|
133 |
|
134 # Set up a database in auto-vacuum mode and create a database schema. |
|
135 # |
|
136 do_test speed3-0.1 { |
|
137 execsql { |
|
138 CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER); |
|
139 } |
|
140 execsql { |
|
141 SELECT name FROM sqlite_master ORDER BY 1; |
|
142 } |
|
143 } {t1} |
|
144 do_test speed3-0.2 { |
|
145 execsql { |
|
146 CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER); |
|
147 } |
|
148 execsql { |
|
149 SELECT name FROM aux.sqlite_master ORDER BY 1; |
|
150 } |
|
151 } {t1} |
|
152 do_test speed3-0.3 { |
|
153 populate_t1 db |
|
154 execsql { |
|
155 SELECT count(*) FROM main.t1; |
|
156 SELECT count(*) FROM aux.t1; |
|
157 } |
|
158 } "$::NROW $::NROW" |
|
159 do_test speed3-0.4 { |
|
160 execsql { |
|
161 PRAGMA main.auto_vacuum; |
|
162 PRAGMA aux.auto_vacuum; |
|
163 } |
|
164 } {2 0} |
|
165 |
|
166 # Delete all content in a table, one row at a time. |
|
167 # |
|
168 #io_log db |
|
169 #overflow_report db |
|
170 reset_db |
|
171 speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1} |
|
172 speed_trial speed3-1.normal $::NROW row {DELETE FROM aux.t1 WHERE 1} |
|
173 io_log db |
|
174 |
|
175 # Select the "C" column (located at the far end of the overflow |
|
176 # chain) from each table row. |
|
177 # |
|
178 #db eval {PRAGMA incremental_vacuum(500000)} |
|
179 populate_t1 db |
|
180 #overflow_report db |
|
181 reset_db |
|
182 speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1} |
|
183 speed_trial speed3-2.normal $::NROW row {SELECT c FROM aux.t1} |
|
184 io_log db |
|
185 |
|
186 finish_test |