wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
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