wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 | file delete -force 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 |