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.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speed1p.explain CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test