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.explain,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: # Before running these tests, disable the compiled statement cache built into
121: # the Tcl interface. This is because we want to test the speed of SQL
122: # compilation as well as execution.
123: #
124: db cache size 0
125:
126: # Join t1, t2, t3 on IPK.
127: set sql "SELECT * FROM t1, t2, t3 WHERE t1.oid = t2.oid AND t2.oid = t3.oid"
128: explain $sql
129: speed_trial speed4p-join1 50000 row $sql
130:
131: # Join t1, t2, t3 on the non-IPK index.
132: set sql "SELECT * FROM t1, t2, t3 WHERE t1.t = t2.t AND t2.t = t3.t"
133: explain $sql
134: speed_trial speed4p-join2 50000 row $sql
135:
136: # Run 10000 simple queries against the views.
137: set script {
138: for {set ii 1} {$ii < 10000} {incr ii} {
139: set v [expr {$ii*3}]
140: set t [expr {$ii%3+1}]
141: db eval "SELECT * FROM v$t WHERE rowid = \$v"
142: }
143: }
144: explain {SELECT * FROm v1 WHERE rowid=$v}
145: speed_trial_tcl speed4p-view1 10000 stmt $script
146:
147: # Run the same 10000 simple queries as in the previous test case against
148: # the underlying tables. The compiled vdbe programs should be identical, so
149: # the only difference in running time is the extra time taken to compile
150: # the view definitions.
151: #
152: set script {
153: for {set ii 1} {$ii < 10000} {incr ii} {
154: set v [expr {$ii*3}]
155: set t [expr {$ii%3+1}]
156: db eval "SELECT t FROM t$t WHERE rowid = \$v"
157: }
158: }
159: explain {SELECT * FROM t1 WHERE rowid=$v}
160: speed_trial_tcl speed4p-table1 10000 stmt $script
161:
162: # Run a SELECT that uses sub-queries 10000 times. A total of 30000 sub-selects.
163: #
164: set script {
165: for {set ii 1} {$ii < 10000} {incr ii} {
166: set v [expr {$ii*3}]
167: db eval {
168: SELECT (SELECT t FROM t1 WHERE rowid = $v),
169: (SELECT t FROM t2 WHERE rowid = $v),
170: (SELECT t FROM t3 WHERE rowid = $v)
171: }
172: }
173: }
174: explain {
175: SELECT (SELECT t FROM t1 WHERE rowid = $v),
176: (SELECT t FROM t2 WHERE rowid = $v),
177: (SELECT t FROM t3 WHERE rowid = $v)
178: }
179: speed_trial_tcl speed4p-subselect1 10000 stmt $script
180:
181: # The following block tests the speed of some DML statements that cause
182: # triggers to fire.
183: #
184: execsql {
185: CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);
186: CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
187: CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN
188: INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);
189: END;
190: CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN
191: INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);
192: END;
193: CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN
194: INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);
195: END;
196: BEGIN;
197: }
198: set list {}
199: for {set ii 1} {$ii < 10000} {incr ii} {
200: lappend list $ii [number_name $ii]
201: }
202: set script {
203: foreach {ii name} $::list {
204: db eval {INSERT INTO t4 VALUES(NULL, $ii, $name)}
205: }
206: }
207: explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
208: speed_trial_tcl speed4p-trigger1 10000 stmt $script
209:
210: set list {}
211: for {set ii 1} {$ii < 20000} {incr ii 2} {
212: set ii2 [expr {$ii*2}]
213: lappend list $ii $ii2 [number_name $ii2]
214: }
215: set script {
216: foreach {ii ii2 name} $::list {
217: db eval {
218: UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
219: }
220: }
221: }
222: explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
223: speed_trial_tcl speed4p-trigger2 10000 stmt $script
224:
225: set script {
226: for {set ii 1} {$ii < 20000} {incr ii 2} {
227: db eval {DELETE FROM t4 WHERE rowid = $ii}
228: }
229: }
230: explain {DELETE FROM t4 WHERE rowid = $ii}
231: speed_trial_tcl speed4p-trigger3 10000 stmt $script
232: execsql {COMMIT}
233:
234: # The following block contains the same tests as the above block that
235: # tests triggers, with one crucial difference: no triggers are defined.
236: # So the difference in speed between these tests and the preceding ones
237: # is the amount of time taken to compile and execute the trigger programs.
238: #
239: execsql {
240: DROP TABLE t4;
241: DROP TABLE log;
242: VACUUM;
243: CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
244: BEGIN;
245: }
246: set list {}
247: for {set ii 1} {$ii < 10000} {incr ii} {
248: lappend list $ii [number_name $ii]
249: }
250: set script {
251: foreach {ii name} $::list {
252: db eval {INSERT INTO t4 VALUES(NULL, $ii, $name);}
253: }
254: }
255: explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
256: speed_trial_tcl speed4p-notrigger1 10000 stmt $script
257:
258: set list {}
259: for {set ii 1} {$ii < 20000} {incr ii 2} {
260: set ii2 [expr {$ii*2}]
261: lappend list $ii $ii2 [number_name $ii2]
262: }
263: set script {
264: foreach {ii ii2 name} $::list {
265: db eval {
266: UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
267: }
268: }
269: }
270: explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
271: speed_trial_tcl speed4p-notrigger2 10000 stmt $script
272:
273: set script {
274: for {set ii 1} {$ii < 20000} {incr ii 2} {
275: db eval {DELETE FROM t4 WHERE rowid = $ii}
276: }
277: }
278: explain {DELETE FROM t4 WHERE rowid = $ii}
279: speed_trial_tcl speed4p-notrigger3 10000 stmt $script
280: execsql {COMMIT}
281:
282: speed_trial_summary speed4
283: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speed4p.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