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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speed4p.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