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: speed4.test,v 1.2 2008/07/12 14:52:20 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 | # speed4-join1: Join three tables using IPK index. |
||
73 | # speed4-join2: Join three tables using an index. |
||
74 | # speed4-join3: Join two tables without an index. |
||
75 | # |
||
76 | # speed4-view1: Querying a view. |
||
77 | # speed4-table1: Same queries as in speed4-view1, but run directly against |
||
78 | # the tables for comparison purposes. |
||
79 | # |
||
80 | # speed4-subselect1: A SELECT statement that uses many sub-queries.. |
||
81 | # |
||
82 | # speed4-trigger1: An INSERT statement that fires a trigger. |
||
83 | # speed4-trigger2: An UPDATE statement that fires a trigger. |
||
84 | # speed4-trigger3: A DELETE statement that fires a trigger. |
||
85 | # speed4-notrigger1: Same operation as trigger1, but without the trigger. |
||
86 | # speed4-notrigger2: " trigger2 " |
||
87 | # speed4-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 | BEGIN; |
||
94 | CREATE TABLE t1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
95 | CREATE TABLE t2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
96 | CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
97 | |||
98 | CREATE VIEW v1 AS SELECT rowid, i, t FROM t1; |
||
99 | CREATE VIEW v2 AS SELECT rowid, i, t FROM t2; |
||
100 | CREATE VIEW v3 AS SELECT rowid, i, t FROM t3; |
||
101 | } |
||
102 | for {set jj 1} {$jj <= 3} {incr jj} { |
||
103 | set stmt [string map "%T% t$jj" {INSERT INTO %T% VALUES(NULL, $i, $t)}] |
||
104 | for {set ii 0} {$ii < 50000} {incr ii} { |
||
105 | set i [expr {int(rand()*50000)}] |
||
106 | set t [number_name $i] |
||
107 | execsql $stmt |
||
108 | } |
||
109 | } |
||
110 | execsql { |
||
111 | CREATE INDEX i1 ON t1(t); |
||
112 | CREATE INDEX i2 ON t2(t); |
||
113 | CREATE INDEX i3 ON t3(t); |
||
114 | COMMIT; |
||
115 | } |
||
116 | |||
117 | # Before running these tests, disable the compiled statement cache built into |
||
118 | # the Tcl interface. This is because we want to test the speed of SQL |
||
119 | # compilation as well as execution. |
||
120 | # |
||
121 | db cache size 0 |
||
122 | |||
123 | # Join t1, t2, t3 on IPK. |
||
124 | set sql "SELECT * FROM t1, t2, t3 WHERE t1.oid = t2.oid AND t2.oid = t3.oid" |
||
125 | speed_trial speed4-join1 50000 row $sql |
||
126 | |||
127 | # Join t1, t2, t3 on the non-IPK index. |
||
128 | set sql "SELECT * FROM t1, t2, t3 WHERE t1.t = t2.t AND t2.t = t3.t" |
||
129 | speed_trial speed4-join2 50000 row $sql |
||
130 | |||
131 | # Run 10000 simple queries against the views. |
||
132 | set sql "" |
||
133 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
134 | append sql "SELECT * FROM v[expr {($ii%3)+1}] WHERE rowid = [expr {$ii*3}];" |
||
135 | } |
||
136 | speed_trial speed4-view1 10000 stmt $sql |
||
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 sql "" |
||
144 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
145 | append sql "SELECT t FROM t[expr {($ii%3)+1}] WHERE rowid = [expr {$ii*3}];" |
||
146 | } |
||
147 | speed_trial speed4-table1 10000 stmt $sql |
||
148 | |||
149 | # Run a SELECT that uses sub-queries 10000 times. A total of 30000 sub-selects. |
||
150 | # |
||
151 | set sql "" |
||
152 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
153 | append sql " |
||
154 | SELECT (SELECT t FROM t1 WHERE rowid = [expr {$ii*3}]), |
||
155 | (SELECT t FROM t2 WHERE rowid = [expr {$ii*3}]), |
||
156 | (SELECT t FROM t3 WHERE rowid = [expr {$ii*3}]) |
||
157 | ;" |
||
158 | } |
||
159 | speed_trial speed4-subselect1 10000 stmt $sql |
||
160 | |||
161 | # The following block tests the speed of some DML statements that cause |
||
162 | # triggers to fire. |
||
163 | # |
||
164 | execsql { |
||
165 | CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT); |
||
166 | CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
167 | CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN |
||
168 | INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t); |
||
169 | END; |
||
170 | CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN |
||
171 | INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t); |
||
172 | END; |
||
173 | CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN |
||
174 | INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t); |
||
175 | END; |
||
176 | BEGIN; |
||
177 | } |
||
178 | set sql "" |
||
179 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
180 | append sql "INSERT INTO t4 VALUES(NULL, $ii, '[number_name $ii]');" |
||
181 | } |
||
182 | speed_trial speed4-trigger1 10000 stmt $sql |
||
183 | set sql "" |
||
184 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
185 | set ii2 [expr {$ii*2}] |
||
186 | append sql " |
||
187 | UPDATE t4 SET i = $ii2, t = '[number_name $ii2]' WHERE rowid = $ii; |
||
188 | " |
||
189 | } |
||
190 | speed_trial speed4-trigger2 10000 stmt $sql |
||
191 | set sql "" |
||
192 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
193 | append sql "DELETE FROM t4 WHERE rowid = $ii;" |
||
194 | } |
||
195 | speed_trial speed4-trigger3 10000 stmt $sql |
||
196 | execsql {COMMIT} |
||
197 | |||
198 | # The following block contains the same tests as the above block that |
||
199 | # tests triggers, with one crucial difference: no triggers are defined. |
||
200 | # So the difference in speed between these tests and the preceding ones |
||
201 | # is the amount of time taken to compile and execute the trigger programs. |
||
202 | # |
||
203 | execsql { |
||
204 | DROP TABLE t4; |
||
205 | DROP TABLE log; |
||
206 | VACUUM; |
||
207 | CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT); |
||
208 | BEGIN; |
||
209 | } |
||
210 | set sql "" |
||
211 | for {set ii 1} {$ii < 10000} {incr ii} { |
||
212 | append sql "INSERT INTO t4 VALUES(NULL, $ii, '[number_name $ii]');" |
||
213 | } |
||
214 | speed_trial speed4-notrigger1 10000 stmt $sql |
||
215 | set sql "" |
||
216 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
217 | set ii2 [expr {$ii*2}] |
||
218 | append sql " |
||
219 | UPDATE t4 SET i = $ii2, t = '[number_name $ii2]' WHERE rowid = $ii; |
||
220 | " |
||
221 | } |
||
222 | speed_trial speed4-notrigger2 10000 stmt $sql |
||
223 | set sql "" |
||
224 | for {set ii 1} {$ii < 20000} {incr ii 2} { |
||
225 | append sql "DELETE FROM t4 WHERE rowid = $ii;" |
||
226 | } |
||
227 | speed_trial speed4-notrigger3 10000 stmt $sql |
||
228 | execsql {COMMIT} |
||
229 | |||
230 | speed_trial_summary speed4 |
||
231 | finish_test |