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