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