sl@0
|
1 |
# 2007 October 23
|
sl@0
|
2 |
#
|
sl@0
|
3 |
# The author disclaims copyright to this source code. In place of
|
sl@0
|
4 |
# a legal notice, here is a blessing:
|
sl@0
|
5 |
#
|
sl@0
|
6 |
# May you do good and not evil.
|
sl@0
|
7 |
# May you find forgiveness for yourself and forgive others.
|
sl@0
|
8 |
# May you share freely, never taking more than you give.
|
sl@0
|
9 |
#
|
sl@0
|
10 |
#*************************************************************************
|
sl@0
|
11 |
# This file implements regression tests for SQLite library. The
|
sl@0
|
12 |
# focus of this script is measuring executing speed. More specifically,
|
sl@0
|
13 |
# the focus is on the speed of:
|
sl@0
|
14 |
#
|
sl@0
|
15 |
# * joins
|
sl@0
|
16 |
# * views
|
sl@0
|
17 |
# * sub-selects
|
sl@0
|
18 |
# * triggers
|
sl@0
|
19 |
#
|
sl@0
|
20 |
# $Id: speed4p.explain,v 1.1 2008/04/16 12:57:48 drh Exp $
|
sl@0
|
21 |
#
|
sl@0
|
22 |
|
sl@0
|
23 |
set testdir [file dirname $argv0]
|
sl@0
|
24 |
source $testdir/tester.tcl
|
sl@0
|
25 |
speed_trial_init speed1
|
sl@0
|
26 |
|
sl@0
|
27 |
# Set a uniform random seed
|
sl@0
|
28 |
expr srand(0)
|
sl@0
|
29 |
|
sl@0
|
30 |
set sqlout [open speed1.txt w]
|
sl@0
|
31 |
proc tracesql {sql} {
|
sl@0
|
32 |
puts $::sqlout $sql\;
|
sl@0
|
33 |
}
|
sl@0
|
34 |
#db trace tracesql
|
sl@0
|
35 |
|
sl@0
|
36 |
# The number_name procedure below converts its argment (an integer)
|
sl@0
|
37 |
# into a string which is the English-language name for that number.
|
sl@0
|
38 |
#
|
sl@0
|
39 |
# Example:
|
sl@0
|
40 |
#
|
sl@0
|
41 |
# puts [number_name 123] -> "one hundred twenty three"
|
sl@0
|
42 |
#
|
sl@0
|
43 |
set ones {zero one two three four five six seven eight nine
|
sl@0
|
44 |
ten eleven twelve thirteen fourteen fifteen sixteen seventeen
|
sl@0
|
45 |
eighteen nineteen}
|
sl@0
|
46 |
set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
|
sl@0
|
47 |
proc number_name {n} {
|
sl@0
|
48 |
if {$n>=1000} {
|
sl@0
|
49 |
set txt "[number_name [expr {$n/1000}]] thousand"
|
sl@0
|
50 |
set n [expr {$n%1000}]
|
sl@0
|
51 |
} else {
|
sl@0
|
52 |
set txt {}
|
sl@0
|
53 |
}
|
sl@0
|
54 |
if {$n>=100} {
|
sl@0
|
55 |
append txt " [lindex $::ones [expr {$n/100}]] hundred"
|
sl@0
|
56 |
set n [expr {$n%100}]
|
sl@0
|
57 |
}
|
sl@0
|
58 |
if {$n>=20} {
|
sl@0
|
59 |
append txt " [lindex $::tens [expr {$n/10}]]"
|
sl@0
|
60 |
set n [expr {$n%10}]
|
sl@0
|
61 |
}
|
sl@0
|
62 |
if {$n>0} {
|
sl@0
|
63 |
append txt " [lindex $::ones $n]"
|
sl@0
|
64 |
}
|
sl@0
|
65 |
set txt [string trim $txt]
|
sl@0
|
66 |
if {$txt==""} {set txt zero}
|
sl@0
|
67 |
return $txt
|
sl@0
|
68 |
}
|
sl@0
|
69 |
|
sl@0
|
70 |
# Summary of tests:
|
sl@0
|
71 |
#
|
sl@0
|
72 |
# speed4p-join1: Join three tables using IPK index.
|
sl@0
|
73 |
# speed4p-join2: Join three tables using an index.
|
sl@0
|
74 |
# speed4p-join3: Join two tables without an index.
|
sl@0
|
75 |
#
|
sl@0
|
76 |
# speed4p-view1: Querying a view.
|
sl@0
|
77 |
# speed4p-table1: Same queries as in speed4p-view1, but run directly against
|
sl@0
|
78 |
# the tables for comparison purposes.
|
sl@0
|
79 |
#
|
sl@0
|
80 |
# speed4p-subselect1: A SELECT statement that uses many sub-queries..
|
sl@0
|
81 |
#
|
sl@0
|
82 |
# speed4p-trigger1: An INSERT statement that fires a trigger.
|
sl@0
|
83 |
# speed4p-trigger2: An UPDATE statement that fires a trigger.
|
sl@0
|
84 |
# speed4p-trigger3: A DELETE statement that fires a trigger.
|
sl@0
|
85 |
# speed4p-notrigger1: Same operation as trigger1, but without the trigger.
|
sl@0
|
86 |
# speed4p-notrigger2: " trigger2 "
|
sl@0
|
87 |
# speed4p-notrigger3: " trigger3 "
|
sl@0
|
88 |
#
|
sl@0
|
89 |
|
sl@0
|
90 |
# Set up the schema. Each of the tables t1, t2 and t3 contain 50,000 rows.
|
sl@0
|
91 |
# This creates a database of around 16MB.
|
sl@0
|
92 |
execsql {
|
sl@0
|
93 |
PRAGMA page_size=1024;
|
sl@0
|
94 |
PRAGMA cache_size=8192;
|
sl@0
|
95 |
PRAGMA locking_mode=EXCLUSIVE;
|
sl@0
|
96 |
BEGIN;
|
sl@0
|
97 |
CREATE TABLE t1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
|
sl@0
|
98 |
CREATE TABLE t2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
|
sl@0
|
99 |
CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
|
sl@0
|
100 |
|
sl@0
|
101 |
CREATE VIEW v1 AS SELECT rowid, i, t FROM t1;
|
sl@0
|
102 |
CREATE VIEW v2 AS SELECT rowid, i, t FROM t2;
|
sl@0
|
103 |
CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;
|
sl@0
|
104 |
}
|
sl@0
|
105 |
for {set jj 1} {$jj <= 3} {incr jj} {
|
sl@0
|
106 |
set stmt [string map "%T% t$jj" {INSERT INTO %T% VALUES(NULL, $i, $t)}]
|
sl@0
|
107 |
for {set ii 0} {$ii < 50000} {incr ii} {
|
sl@0
|
108 |
set i [expr {int(rand()*50000)}]
|
sl@0
|
109 |
set t [number_name $i]
|
sl@0
|
110 |
execsql $stmt
|
sl@0
|
111 |
}
|
sl@0
|
112 |
}
|
sl@0
|
113 |
execsql {
|
sl@0
|
114 |
CREATE INDEX i1 ON t1(t);
|
sl@0
|
115 |
CREATE INDEX i2 ON t2(t);
|
sl@0
|
116 |
CREATE INDEX i3 ON t3(t);
|
sl@0
|
117 |
COMMIT;
|
sl@0
|
118 |
}
|
sl@0
|
119 |
|
sl@0
|
120 |
# Before running these tests, disable the compiled statement cache built into
|
sl@0
|
121 |
# the Tcl interface. This is because we want to test the speed of SQL
|
sl@0
|
122 |
# compilation as well as execution.
|
sl@0
|
123 |
#
|
sl@0
|
124 |
db cache size 0
|
sl@0
|
125 |
|
sl@0
|
126 |
# Join t1, t2, t3 on IPK.
|
sl@0
|
127 |
set sql "SELECT * FROM t1, t2, t3 WHERE t1.oid = t2.oid AND t2.oid = t3.oid"
|
sl@0
|
128 |
explain $sql
|
sl@0
|
129 |
speed_trial speed4p-join1 50000 row $sql
|
sl@0
|
130 |
|
sl@0
|
131 |
# Join t1, t2, t3 on the non-IPK index.
|
sl@0
|
132 |
set sql "SELECT * FROM t1, t2, t3 WHERE t1.t = t2.t AND t2.t = t3.t"
|
sl@0
|
133 |
explain $sql
|
sl@0
|
134 |
speed_trial speed4p-join2 50000 row $sql
|
sl@0
|
135 |
|
sl@0
|
136 |
# Run 10000 simple queries against the views.
|
sl@0
|
137 |
set script {
|
sl@0
|
138 |
for {set ii 1} {$ii < 10000} {incr ii} {
|
sl@0
|
139 |
set v [expr {$ii*3}]
|
sl@0
|
140 |
set t [expr {$ii%3+1}]
|
sl@0
|
141 |
db eval "SELECT * FROM v$t WHERE rowid = \$v"
|
sl@0
|
142 |
}
|
sl@0
|
143 |
}
|
sl@0
|
144 |
explain {SELECT * FROm v1 WHERE rowid=$v}
|
sl@0
|
145 |
speed_trial_tcl speed4p-view1 10000 stmt $script
|
sl@0
|
146 |
|
sl@0
|
147 |
# Run the same 10000 simple queries as in the previous test case against
|
sl@0
|
148 |
# the underlying tables. The compiled vdbe programs should be identical, so
|
sl@0
|
149 |
# the only difference in running time is the extra time taken to compile
|
sl@0
|
150 |
# the view definitions.
|
sl@0
|
151 |
#
|
sl@0
|
152 |
set script {
|
sl@0
|
153 |
for {set ii 1} {$ii < 10000} {incr ii} {
|
sl@0
|
154 |
set v [expr {$ii*3}]
|
sl@0
|
155 |
set t [expr {$ii%3+1}]
|
sl@0
|
156 |
db eval "SELECT t FROM t$t WHERE rowid = \$v"
|
sl@0
|
157 |
}
|
sl@0
|
158 |
}
|
sl@0
|
159 |
explain {SELECT * FROM t1 WHERE rowid=$v}
|
sl@0
|
160 |
speed_trial_tcl speed4p-table1 10000 stmt $script
|
sl@0
|
161 |
|
sl@0
|
162 |
# Run a SELECT that uses sub-queries 10000 times. A total of 30000 sub-selects.
|
sl@0
|
163 |
#
|
sl@0
|
164 |
set script {
|
sl@0
|
165 |
for {set ii 1} {$ii < 10000} {incr ii} {
|
sl@0
|
166 |
set v [expr {$ii*3}]
|
sl@0
|
167 |
db eval {
|
sl@0
|
168 |
SELECT (SELECT t FROM t1 WHERE rowid = $v),
|
sl@0
|
169 |
(SELECT t FROM t2 WHERE rowid = $v),
|
sl@0
|
170 |
(SELECT t FROM t3 WHERE rowid = $v)
|
sl@0
|
171 |
}
|
sl@0
|
172 |
}
|
sl@0
|
173 |
}
|
sl@0
|
174 |
explain {
|
sl@0
|
175 |
SELECT (SELECT t FROM t1 WHERE rowid = $v),
|
sl@0
|
176 |
(SELECT t FROM t2 WHERE rowid = $v),
|
sl@0
|
177 |
(SELECT t FROM t3 WHERE rowid = $v)
|
sl@0
|
178 |
}
|
sl@0
|
179 |
speed_trial_tcl speed4p-subselect1 10000 stmt $script
|
sl@0
|
180 |
|
sl@0
|
181 |
# The following block tests the speed of some DML statements that cause
|
sl@0
|
182 |
# triggers to fire.
|
sl@0
|
183 |
#
|
sl@0
|
184 |
execsql {
|
sl@0
|
185 |
CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);
|
sl@0
|
186 |
CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
|
sl@0
|
187 |
CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN
|
sl@0
|
188 |
INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);
|
sl@0
|
189 |
END;
|
sl@0
|
190 |
CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN
|
sl@0
|
191 |
INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);
|
sl@0
|
192 |
END;
|
sl@0
|
193 |
CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN
|
sl@0
|
194 |
INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);
|
sl@0
|
195 |
END;
|
sl@0
|
196 |
BEGIN;
|
sl@0
|
197 |
}
|
sl@0
|
198 |
set list {}
|
sl@0
|
199 |
for {set ii 1} {$ii < 10000} {incr ii} {
|
sl@0
|
200 |
lappend list $ii [number_name $ii]
|
sl@0
|
201 |
}
|
sl@0
|
202 |
set script {
|
sl@0
|
203 |
foreach {ii name} $::list {
|
sl@0
|
204 |
db eval {INSERT INTO t4 VALUES(NULL, $ii, $name)}
|
sl@0
|
205 |
}
|
sl@0
|
206 |
}
|
sl@0
|
207 |
explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
|
sl@0
|
208 |
speed_trial_tcl speed4p-trigger1 10000 stmt $script
|
sl@0
|
209 |
|
sl@0
|
210 |
set list {}
|
sl@0
|
211 |
for {set ii 1} {$ii < 20000} {incr ii 2} {
|
sl@0
|
212 |
set ii2 [expr {$ii*2}]
|
sl@0
|
213 |
lappend list $ii $ii2 [number_name $ii2]
|
sl@0
|
214 |
}
|
sl@0
|
215 |
set script {
|
sl@0
|
216 |
foreach {ii ii2 name} $::list {
|
sl@0
|
217 |
db eval {
|
sl@0
|
218 |
UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
|
sl@0
|
219 |
}
|
sl@0
|
220 |
}
|
sl@0
|
221 |
}
|
sl@0
|
222 |
explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
|
sl@0
|
223 |
speed_trial_tcl speed4p-trigger2 10000 stmt $script
|
sl@0
|
224 |
|
sl@0
|
225 |
set script {
|
sl@0
|
226 |
for {set ii 1} {$ii < 20000} {incr ii 2} {
|
sl@0
|
227 |
db eval {DELETE FROM t4 WHERE rowid = $ii}
|
sl@0
|
228 |
}
|
sl@0
|
229 |
}
|
sl@0
|
230 |
explain {DELETE FROM t4 WHERE rowid = $ii}
|
sl@0
|
231 |
speed_trial_tcl speed4p-trigger3 10000 stmt $script
|
sl@0
|
232 |
execsql {COMMIT}
|
sl@0
|
233 |
|
sl@0
|
234 |
# The following block contains the same tests as the above block that
|
sl@0
|
235 |
# tests triggers, with one crucial difference: no triggers are defined.
|
sl@0
|
236 |
# So the difference in speed between these tests and the preceding ones
|
sl@0
|
237 |
# is the amount of time taken to compile and execute the trigger programs.
|
sl@0
|
238 |
#
|
sl@0
|
239 |
execsql {
|
sl@0
|
240 |
DROP TABLE t4;
|
sl@0
|
241 |
DROP TABLE log;
|
sl@0
|
242 |
VACUUM;
|
sl@0
|
243 |
CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
|
sl@0
|
244 |
BEGIN;
|
sl@0
|
245 |
}
|
sl@0
|
246 |
set list {}
|
sl@0
|
247 |
for {set ii 1} {$ii < 10000} {incr ii} {
|
sl@0
|
248 |
lappend list $ii [number_name $ii]
|
sl@0
|
249 |
}
|
sl@0
|
250 |
set script {
|
sl@0
|
251 |
foreach {ii name} $::list {
|
sl@0
|
252 |
db eval {INSERT INTO t4 VALUES(NULL, $ii, $name);}
|
sl@0
|
253 |
}
|
sl@0
|
254 |
}
|
sl@0
|
255 |
explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
|
sl@0
|
256 |
speed_trial_tcl speed4p-notrigger1 10000 stmt $script
|
sl@0
|
257 |
|
sl@0
|
258 |
set list {}
|
sl@0
|
259 |
for {set ii 1} {$ii < 20000} {incr ii 2} {
|
sl@0
|
260 |
set ii2 [expr {$ii*2}]
|
sl@0
|
261 |
lappend list $ii $ii2 [number_name $ii2]
|
sl@0
|
262 |
}
|
sl@0
|
263 |
set script {
|
sl@0
|
264 |
foreach {ii ii2 name} $::list {
|
sl@0
|
265 |
db eval {
|
sl@0
|
266 |
UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
|
sl@0
|
267 |
}
|
sl@0
|
268 |
}
|
sl@0
|
269 |
}
|
sl@0
|
270 |
explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
|
sl@0
|
271 |
speed_trial_tcl speed4p-notrigger2 10000 stmt $script
|
sl@0
|
272 |
|
sl@0
|
273 |
set script {
|
sl@0
|
274 |
for {set ii 1} {$ii < 20000} {incr ii 2} {
|
sl@0
|
275 |
db eval {DELETE FROM t4 WHERE rowid = $ii}
|
sl@0
|
276 |
}
|
sl@0
|
277 |
}
|
sl@0
|
278 |
explain {DELETE FROM t4 WHERE rowid = $ii}
|
sl@0
|
279 |
speed_trial_tcl speed4p-notrigger3 10000 stmt $script
|
sl@0
|
280 |
execsql {COMMIT}
|
sl@0
|
281 |
|
sl@0
|
282 |
speed_trial_summary speed4
|
sl@0
|
283 |
finish_test
|