wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2007 October 23 |
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 measuring executing speed. More specifically, |
||
13 | # the focus is on the speed of: |
||
14 | # |
||
15 | # * joins |
||
16 | # * views |
||
17 | # * sub-selects |
||
18 | # * triggers |
||
19 | # |
||
20 | # $Id: speed4p.test,v 1.4 2008/04/10 13:32:37 drh Exp $ |
||
21 | # |
||
22 | |||
23 | set testdir [file dirname $argv0] |
||
24 | source $testdir/tester.tcl |
||
25 | speed_trial_init speed1 |
||
26 | |||
27 | # Set a uniform random seed |
||
28 | expr srand(0) |
||
29 | |||
30 | set sqlout [open speed1.txt w] |
||
31 | proc tracesql {sql} { |
||
32 | puts $::sqlout $sql\; |
||
33 | } |
||
34 | #db trace tracesql |
||
35 | |||
36 | # The number_name procedure below converts its argment (an integer) |
||
37 | # into a string which is the English-language name for that number. |
||
38 | # |
||
39 | # Example: |
||
40 | # |
||
41 | # puts [number_name 123] -> "one hundred twenty three" |
||
42 | # |
||
43 | set ones {zero one two three four five six seven eight nine |
||
44 | ten eleven twelve thirteen fourteen fifteen sixteen seventeen |
||
45 | eighteen nineteen} |
||
46 | set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} |
||
47 | proc number_name {n} { |
||
48 | if {$n>=1000} { |
||
49 | set txt "[number_name [expr {$n/1000}]] thousand" |
||
50 | set n [expr {$n%1000}] |
||
51 | } else { |
||
52 | set txt {} |
||
53 | } |
||
54 | if {$n>=100} { |
||
55 | append txt " [lindex $::ones [expr {$n/100}]] hundred" |
||
56 | set n [expr {$n%100}] |
||
57 | } |
||
58 | if {$n>=20} { |
||
59 | append txt " [lindex $::tens [expr {$n/10}]]" |
||
60 | set n [expr {$n%10}] |
||
61 | } |
||
62 | if {$n>0} { |
||
63 | append txt " [lindex $::ones $n]" |
||
64 | } |
||
65 | set txt [string trim $txt] |
||
66 | if {$txt==""} {set txt zero} |
||
67 | return $txt |
||
68 | } |
||
69 | |||
70 | # Summary of tests: |
||
71 | # |
||
72 | # speed4p-join1: Join three tables using IPK index. |
||
73 | # speed4p-join2: Join three tables using an index. |
||
74 | # speed4p-join3: Join two tables without an index. |
||
75 | # |
||
76 | # speed4p-view1: Querying a view. |
||
77 | # speed4p-table1: Same queries as in speed4p-view1, but run directly against |
||
78 | # the tables for comparison purposes. |
||
79 | # |
||
80 | # speed4p-subselect1: A SELECT statement that uses many sub-queries.. |
||
81 | # |
||
82 | # speed4p-trigger1: An INSERT statement that fires a trigger. |
||
83 | # speed4p-trigger2: An UPDATE statement that fires a trigger. |
||
84 | # speed4p-trigger3: A DELETE statement that fires a trigger. |
||
85 | # speed4p-notrigger1: Same operation as trigger1, but without the trigger. |
||
86 | # speed4p-notrigger2: " trigger2 " |
||
87 | # speed4p-notrigger3: " trigger3 " |
||
88 | # |
||
89 | |||
90 | # Set up the schema. Each of the tables t1, t2 and t3 contain 50,000 rows. |
||
91 | # This creates a database of around 16MB. |
||
92 | execsql { |
||
93 | PRAGMA page_size=1024; |
||
94 | PRAGMA cache_size=8192; |
||
95 | PRAGMA locking_mode=EXCLUSIVE; |
||
96 | BEGIN; |
||
97 | CREATE TABLE t1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
98 | CREATE TABLE t2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
99 | CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
100 | |||
101 | CREATE VIEW v1 AS SELECT rowid, i, t FROM t1; |
||
102 | CREATE VIEW v2 AS SELECT rowid, i, t FROM t2; |
||
103 | CREATE VIEW v3 AS SELECT rowid, i, t FROM t3; |
||
104 | } |
||
105 | for {set jj 1} {$jj <= 3} {incr jj} { |
||
106 | set stmt [string map "%T% t$jj" {INSERT INTO %T% VALUES(NULL, $i, $t)}] |
||
107 | for {set ii 0} {$ii < 50000} {incr ii} { |
||
108 | set i [expr {int(rand()*50000)}] |
||
109 | set t [number_name $i] |
||
110 | execsql $stmt |
||
111 | } |
||
112 | } |
||
113 | execsql { |
||
114 | CREATE INDEX i1 ON t1(t); |
||
115 | CREATE INDEX i2 ON t2(t); |
||
116 | CREATE INDEX i3 ON t3(t); |
||
117 | COMMIT; |
||
118 | } |
||
119 | |||
120 | # Join t1, t2, t3 on IPK. |
||
121 | set sql "SELECT * FROM t1, t2, t3 WHERE t1.oid = t2.oid AND t2.oid = t3.oid" |
||
122 | speed_trial speed4p-join1 50000 row $sql |
||
123 | |||
124 | # Join t1, t2, t3 on the non-IPK index. |
||
125 | set sql "SELECT * FROM t1, t2, t3 WHERE t1.t = t2.t AND t2.t = t3.t" |
||
126 | speed_trial speed4p-join2 50000 row $sql |
||
127 | |||
128 | # Run 10000 simple queries against the views. |
||
129 | set script { |
||
130 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
131 | set v [expr {$ii*3}] |
||
132 | set t [expr {$ii%3+1}] |
||
133 | db eval "SELECT * FROM v$t WHERE rowid = \$v" |
||
134 | } |
||
135 | } |
||
136 | speed_trial_tcl speed4p-view1 10000 stmt $script |
||
137 | |||
138 | # Run the same 10000 simple queries as in the previous test case against |
||
139 | # the underlying tables. The compiled vdbe programs should be identical, so |
||
140 | # the only difference in running time is the extra time taken to compile |
||
141 | # the view definitions. |
||
142 | # |
||
143 | set script { |
||
144 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
145 | set v [expr {$ii*3}] |
||
146 | set t [expr {$ii%3+1}] |
||
147 | db eval "SELECT t FROM t$t WHERE rowid = \$v" |
||
148 | } |
||
149 | } |
||
150 | speed_trial_tcl speed4p-table1 10000 stmt $script |
||
151 | |||
152 | # Run a SELECT that uses sub-queries 10000 times. A total of 30000 sub-selects. |
||
153 | # |
||
154 | set script { |
||
155 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
156 | set v [expr {$ii*3}] |
||
157 | db eval { |
||
158 | SELECT (SELECT t FROM t1 WHERE rowid = $v), |
||
159 | (SELECT t FROM t2 WHERE rowid = $v), |
||
160 | (SELECT t FROM t3 WHERE rowid = $v) |
||
161 | } |
||
162 | } |
||
163 | } |
||
164 | speed_trial_tcl speed4p-subselect1 10000 stmt $script |
||
165 | |||
166 | # Single-row updates performance. |
||
167 | # |
||
168 | set script { |
||
169 | db eval BEGIN |
||
170 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
171 | set v [expr {$ii*3}] |
||
172 | db eval {UPDATE t1 SET i=i+1 WHERE rowid=$ii} |
||
173 | } |
||
174 | db eval COMMIT |
||
175 | } |
||
176 | speed_trial_tcl speed4p-rowid-update 10000 stmt $script |
||
177 | |||
178 | |||
179 | db eval { |
||
180 | CREATE TABLE t5(t TEXT PRIMARY KEY, i INTEGER); |
||
181 | } |
||
182 | speed_trial speed4p-insert-ignore 50000 row { |
||
183 | INSERT OR IGNORE INTO t5 SELECT t, i FROM t1; |
||
184 | } |
||
185 | |||
186 | set list [db eval {SELECT t FROM t5}] |
||
187 | set script { |
||
188 | db eval BEGIN |
||
189 | foreach t $::list { |
||
190 | db eval {UPDATE t5 SET i=i+1 WHERE t=$t} |
||
191 | } |
||
192 | db eval COMMIT |
||
193 | } |
||
194 | speed_trial_tcl speed4p-unique-update [llength $list] stmt $script |
||
195 | |||
196 | # The following block tests the speed of some DML statements that cause |
||
197 | # triggers to fire. |
||
198 | # |
||
199 | execsql { |
||
200 | CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT); |
||
201 | CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
202 | CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN |
||
203 | INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t); |
||
204 | END; |
||
205 | CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN |
||
206 | INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t); |
||
207 | END; |
||
208 | CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN |
||
209 | INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t); |
||
210 | END; |
||
211 | BEGIN; |
||
212 | } |
||
213 | set list {} |
||
214 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
215 | lappend list $ii [number_name $ii] |
||
216 | } |
||
217 | set script { |
||
218 | foreach {ii name} $::list { |
||
219 | db eval {INSERT INTO t4 VALUES(NULL, $ii, $name)} |
||
220 | } |
||
221 | } |
||
222 | speed_trial_tcl speed4p-trigger1 10000 stmt $script |
||
223 | |||
224 | set list {} |
||
225 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
226 | set ii2 [expr {$ii*2}] |
||
227 | lappend list $ii $ii2 [number_name $ii2] |
||
228 | } |
||
229 | set script { |
||
230 | foreach {ii ii2 name} $::list { |
||
231 | db eval { |
||
232 | UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii; |
||
233 | } |
||
234 | } |
||
235 | } |
||
236 | speed_trial_tcl speed4p-trigger2 10000 stmt $script |
||
237 | |||
238 | set script { |
||
239 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
240 | db eval {DELETE FROM t4 WHERE rowid = $ii} |
||
241 | } |
||
242 | } |
||
243 | speed_trial_tcl speed4p-trigger3 10000 stmt $script |
||
244 | execsql {COMMIT} |
||
245 | |||
246 | # The following block contains the same tests as the above block that |
||
247 | # tests triggers, with one crucial difference: no triggers are defined. |
||
248 | # So the difference in speed between these tests and the preceding ones |
||
249 | # is the amount of time taken to compile and execute the trigger programs. |
||
250 | # |
||
251 | execsql { |
||
252 | DROP TABLE t4; |
||
253 | DROP TABLE log; |
||
254 | VACUUM; |
||
255 | CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
256 | BEGIN; |
||
257 | } |
||
258 | set list {} |
||
259 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
260 | lappend list $ii [number_name $ii] |
||
261 | } |
||
262 | set script { |
||
263 | foreach {ii name} $::list { |
||
264 | db eval {INSERT INTO t4 VALUES(NULL, $ii, $name);} |
||
265 | } |
||
266 | } |
||
267 | speed_trial_tcl speed4p-notrigger1 10000 stmt $script |
||
268 | |||
269 | set list {} |
||
270 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
271 | set ii2 [expr {$ii*2}] |
||
272 | lappend list $ii $ii2 [number_name $ii2] |
||
273 | } |
||
274 | set script { |
||
275 | foreach {ii ii2 name} $::list { |
||
276 | db eval { |
||
277 | UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii; |
||
278 | } |
||
279 | } |
||
280 | } |
||
281 | speed_trial_tcl speed4p-notrigger2 10000 stmt $script |
||
282 | |||
283 | set script { |
||
284 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
285 | db eval {DELETE FROM t4 WHERE rowid = $ii} |
||
286 | } |
||
287 | } |
||
288 | speed_trial_tcl speed4p-notrigger3 10000 stmt $script |
||
289 | execsql {COMMIT} |
||
290 | |||
291 | speed_trial_summary speed4 |
||
292 | finish_test |