OLD | NEW |
| (Empty) |
1 # 2008 March 21 | |
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. | |
13 # | |
14 # This is a copy of speed1.test modified to user prepared statements. | |
15 # | |
16 # $Id: speed1p.explain,v 1.1 2008/04/16 12:57:48 drh Exp $ | |
17 # | |
18 | |
19 set testdir [file dirname $argv0] | |
20 source $testdir/tester.tcl | |
21 speed_trial_init speed1 | |
22 | |
23 # Set a uniform random seed | |
24 expr srand(0) | |
25 | |
26 set sqlout [open speed1.txt w] | |
27 proc tracesql {sql} { | |
28 puts $::sqlout $sql\; | |
29 } | |
30 #db trace tracesql | |
31 | |
32 # The number_name procedure below converts its argment (an integer) | |
33 # into a string which is the English-language name for that number. | |
34 # | |
35 # Example: | |
36 # | |
37 # puts [number_name 123] -> "one hundred twenty three" | |
38 # | |
39 set ones {zero one two three four five six seven eight nine | |
40 ten eleven twelve thirteen fourteen fifteen sixteen seventeen | |
41 eighteen nineteen} | |
42 set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} | |
43 proc number_name {n} { | |
44 if {$n>=1000} { | |
45 set txt "[number_name [expr {$n/1000}]] thousand" | |
46 set n [expr {$n%1000}] | |
47 } else { | |
48 set txt {} | |
49 } | |
50 if {$n>=100} { | |
51 append txt " [lindex $::ones [expr {$n/100}]] hundred" | |
52 set n [expr {$n%100}] | |
53 } | |
54 if {$n>=20} { | |
55 append txt " [lindex $::tens [expr {$n/10}]]" | |
56 set n [expr {$n%10}] | |
57 } | |
58 if {$n>0} { | |
59 append txt " [lindex $::ones $n]" | |
60 } | |
61 set txt [string trim $txt] | |
62 if {$txt==""} {set txt zero} | |
63 return $txt | |
64 } | |
65 | |
66 # Create a database schema. | |
67 # | |
68 do_test speed1p-1.0 { | |
69 execsql { | |
70 PRAGMA page_size=1024; | |
71 PRAGMA cache_size=8192; | |
72 PRAGMA locking_mode=EXCLUSIVE; | |
73 CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT); | |
74 CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT); | |
75 CREATE INDEX i2a ON t2(a); | |
76 CREATE INDEX i2b ON t2(b); | |
77 } | |
78 execsql { | |
79 SELECT name FROM sqlite_master ORDER BY 1; | |
80 } | |
81 } {i2a i2b t1 t2} | |
82 | |
83 | |
84 # 50000 INSERTs on an unindexed table | |
85 # | |
86 set list {} | |
87 for {set i 1} {$i<=50000} {incr i} { | |
88 set r [expr {int(rand()*500000)}] | |
89 set x [number_name $r] | |
90 lappend list $i $r $x | |
91 } | |
92 set script { | |
93 foreach {i r x} $::list { | |
94 db eval {INSERT INTO t1 VALUES($i,$r,$x)} | |
95 } | |
96 } | |
97 explain {INSERT INTO t1 VALUES($i,$r,$x)} | |
98 db eval BEGIN | |
99 speed_trial_tcl speed1p-insert1 50000 row $script | |
100 db eval COMMIT | |
101 | |
102 # 50000 INSERTs on an indexed table | |
103 # | |
104 set list {} | |
105 for {set i 1} {$i<=50000} {incr i} { | |
106 set r [expr {int(rand()*500000)}] | |
107 set x [number_name $r] | |
108 lappend list $i $r $x | |
109 } | |
110 set script { | |
111 foreach {i r x} $::list { | |
112 db eval {INSERT INTO t2 VALUES($i,$r,$x)} | |
113 } | |
114 } | |
115 explain {INSERT INTO t2 VALUES($i,$r,$x)} | |
116 db eval BEGIN | |
117 speed_trial_tcl speed1p-insert2 50000 row $script | |
118 db eval COMMIT | |
119 | |
120 | |
121 | |
122 # 50 SELECTs on an integer comparison. There is no index so | |
123 # a full table scan is required. | |
124 # | |
125 set list {} | |
126 for {set i 0} {$i<50} {incr i} { | |
127 set lwr [expr {$i*100}] | |
128 set upr [expr {($i+10)*100}] | |
129 lappend list $lwr $upr | |
130 } | |
131 set script { | |
132 foreach {lwr upr} $::list { | |
133 db eval {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr} | |
134 } | |
135 } | |
136 explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr} | |
137 db eval BEGIN | |
138 speed_trial_tcl speed1p-select1 [expr {50*50000}] row $script | |
139 db eval COMMIT | |
140 | |
141 # 50 SELECTs on an LIKE comparison. There is no index so a full | |
142 # table scan is required. | |
143 # | |
144 set list {} | |
145 for {set i 0} {$i<50} {incr i} { | |
146 lappend list "%[number_name $i]%" | |
147 } | |
148 set script { | |
149 foreach pattern $::list { | |
150 db eval {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern} | |
151 } | |
152 } | |
153 explain {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern} | |
154 db eval BEGIN | |
155 speed_trial_tcl speed1p-select2 [expr {50*50000}] row $script | |
156 db eval COMMIT | |
157 | |
158 # Create indices | |
159 # | |
160 explain {CREATE INDEX i1a ON t1(a)} | |
161 explain {CREATE INDEX i1b ON t1(b)} | |
162 db eval BEGIN | |
163 speed_trial speed1p-createidx 150000 row { | |
164 CREATE INDEX i1a ON t1(a); | |
165 CREATE INDEX i1b ON t1(b); | |
166 CREATE INDEX i1c ON t1(c); | |
167 } | |
168 db eval COMMIT | |
169 | |
170 # 5000 SELECTs on an integer comparison where the integer is | |
171 # indexed. | |
172 # | |
173 set list {} | |
174 for {set i 0} {$i<5000} {incr i} { | |
175 set lwr [expr {$i*100}] | |
176 set upr [expr {($i+10)*100}] | |
177 lappend list $lwr $upr | |
178 } | |
179 set script { | |
180 foreach {lwr upr} $::list { | |
181 db eval {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr} | |
182 } | |
183 } | |
184 explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr} | |
185 db eval BEGIN | |
186 speed_trial_tcl speed1p-select3 5000 stmt $script | |
187 db eval COMMIT | |
188 | |
189 # 100000 random SELECTs against rowid. | |
190 # | |
191 set list {} | |
192 for {set i 1} {$i<=100000} {incr i} { | |
193 set id [expr {int(rand()*50000)+1}] | |
194 lappend list $id | |
195 } | |
196 set script { | |
197 foreach id $::list { | |
198 db eval {SELECT c FROM t1 WHERE rowid=$id} | |
199 } | |
200 } | |
201 explain {SELECT c FROM t1 WHERE rowid=$id} | |
202 db eval BEGIN | |
203 speed_trial_tcl speed1p-select4 100000 row $script | |
204 db eval COMMIT | |
205 | |
206 # 100000 random SELECTs against a unique indexed column. | |
207 # | |
208 set list {} | |
209 for {set i 1} {$i<=100000} {incr i} { | |
210 set id [expr {int(rand()*50000)+1}] | |
211 lappend list $id | |
212 } | |
213 set script { | |
214 foreach id $::list { | |
215 db eval {SELECT c FROM t1 WHERE a=$id} | |
216 } | |
217 } | |
218 explain {SELECT c FROM t1 WHERE a=$id} | |
219 db eval BEGIN | |
220 speed_trial_tcl speed1p-select5 100000 row $script | |
221 db eval COMMIT | |
222 | |
223 # 50000 random SELECTs against an indexed column text column | |
224 # | |
225 set list [db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000}] | |
226 set script { | |
227 foreach c $::list { | |
228 db eval {SELECT c FROM t1 WHERE c=$c} | |
229 } | |
230 } | |
231 explain {SELECT c FROM t1 WHERE c=$c} | |
232 db eval BEGIN | |
233 speed_trial_tcl speed1p-select6 50000 row $script | |
234 db eval COMMIT | |
235 | |
236 | |
237 # Vacuum | |
238 speed_trial speed1p-vacuum 100000 row VACUUM | |
239 | |
240 # 5000 updates of ranges where the field being compared is indexed. | |
241 # | |
242 set list {} | |
243 for {set i 0} {$i<5000} {incr i} { | |
244 set lwr [expr {$i*2}] | |
245 set upr [expr {($i+1)*2}] | |
246 lappend list $lwr $upr | |
247 } | |
248 set script { | |
249 foreach {lwr upr} $::list { | |
250 db eval {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr} | |
251 } | |
252 } | |
253 explain {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr} | |
254 db eval BEGIN | |
255 speed_trial_tcl speed1p-update1 5000 stmt $script | |
256 db eval COMMIT | |
257 | |
258 # 50000 single-row updates. An index is used to find the row quickly. | |
259 # | |
260 set list {} | |
261 for {set i 0} {$i<50000} {incr i} { | |
262 set r [expr {int(rand()*500000)}] | |
263 lappend list $i $r | |
264 } | |
265 set script { | |
266 foreach {i r} $::list { | |
267 db eval {UPDATE t1 SET b=$r WHERE a=$i} | |
268 } | |
269 } | |
270 explain {UPDATE t1 SET b=$r WHERE a=$i} | |
271 db eval BEGIN | |
272 speed_trial_tcl speed1p-update2 50000 row $script | |
273 db eval COMMIT | |
274 | |
275 # 1 big text update that touches every row in the table. | |
276 # | |
277 explain {UPDATE t1 SET c=a} | |
278 speed_trial speed1p-update3 50000 row { | |
279 UPDATE t1 SET c=a; | |
280 } | |
281 | |
282 # Many individual text updates. Each row in the table is | |
283 # touched through an index. | |
284 # | |
285 set list {} | |
286 for {set i 1} {$i<=50000} {incr i} { | |
287 set r [expr {int(rand()*500000)}] | |
288 lappend list $i [number_name $r] | |
289 } | |
290 set script { | |
291 foreach {i x} $::list { | |
292 db eval {UPDATE t1 SET c=$x WHERE a=$i} | |
293 } | |
294 } | |
295 explain {UPDATE t1 SET c=$x WHERE a=$i} | |
296 db eval BEGIN | |
297 speed_trial_tcl speed1p-update4 50000 row $script | |
298 db eval COMMIT | |
299 | |
300 # Delete all content in a table. | |
301 # | |
302 explain {DELETE FROM t1} | |
303 speed_trial speed1p-delete1 50000 row {DELETE FROM t1} | |
304 | |
305 # Copy one table into another | |
306 # | |
307 explain {INSERT INTO t1 SELECT * FROM t2} | |
308 speed_trial speed1p-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2} | |
309 | |
310 # Delete all content in a table, one row at a time. | |
311 # | |
312 explain {DELETE FROM t1 WHERE 1} | |
313 speed_trial speed1p-delete2 50000 row {DELETE FROM t1 WHERE 1} | |
314 | |
315 # Refill the table yet again | |
316 # | |
317 speed_trial speed1p-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2} | |
318 | |
319 # Drop the table and recreate it without its indices. | |
320 # | |
321 explain {DROP TABLE t1} | |
322 explain {CREATE TABLE tX(a INTEGER, b INTEGER, c TEXT)} | |
323 db eval BEGIN | |
324 speed_trial speed1p-drop1 50000 row { | |
325 DROP TABLE t1; | |
326 CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT); | |
327 } | |
328 db eval COMMIT | |
329 | |
330 # Refill the table yet again. This copy should be faster because | |
331 # there are no indices to deal with. | |
332 # | |
333 speed_trial speed1p-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2} | |
334 | |
335 # Select 20000 rows from the table at random. | |
336 # | |
337 explain {SELECT rowid FROM t1 ORDER BY random() LIMIT 20000} | |
338 speed_trial speed1p-random1 50000 row { | |
339 SELECT rowid FROM t1 ORDER BY random() LIMIT 20000 | |
340 } | |
341 | |
342 # Delete 20000 random rows from the table. | |
343 # | |
344 explain {DELETE FROM t1 WHERE rowid IN | |
345 (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)} | |
346 speed_trial speed1p-random-del1 20000 row { | |
347 DELETE FROM t1 WHERE rowid IN | |
348 (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000) | |
349 } | |
350 do_test speed1p-1.1 { | |
351 db one {SELECT count(*) FROM t1} | |
352 } 30000 | |
353 | |
354 | |
355 # Delete 20000 more rows at random from the table. | |
356 # | |
357 speed_trial speed1p-random-del2 20000 row { | |
358 DELETE FROM t1 WHERE rowid IN | |
359 (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000) | |
360 } | |
361 do_test speed1p-1.2 { | |
362 db one {SELECT count(*) FROM t1} | |
363 } 10000 | |
364 speed_trial_summary speed1 | |
365 | |
366 finish_test | |
OLD | NEW |