diff options
Diffstat (limited to 'libraries/sqlite/unix/sqlite-3.5.1/tool/speedtest.tcl')
-rw-r--r-- | libraries/sqlite/unix/sqlite-3.5.1/tool/speedtest.tcl | 275 |
1 files changed, 275 insertions, 0 deletions
diff --git a/libraries/sqlite/unix/sqlite-3.5.1/tool/speedtest.tcl b/libraries/sqlite/unix/sqlite-3.5.1/tool/speedtest.tcl new file mode 100644 index 0000000..ef39dc5 --- /dev/null +++ b/libraries/sqlite/unix/sqlite-3.5.1/tool/speedtest.tcl | |||
@@ -0,0 +1,275 @@ | |||
1 | #!/usr/bin/tclsh | ||
2 | # | ||
3 | # Run this script using TCLSH to do a speed comparison between | ||
4 | # various versions of SQLite and PostgreSQL and MySQL | ||
5 | # | ||
6 | |||
7 | # Run a test | ||
8 | # | ||
9 | set cnt 1 | ||
10 | proc runtest {title} { | ||
11 | global cnt | ||
12 | set sqlfile test$cnt.sql | ||
13 | puts "<h2>Test $cnt: $title</h2>" | ||
14 | incr cnt | ||
15 | set fd [open $sqlfile r] | ||
16 | set sql [string trim [read $fd [file size $sqlfile]]] | ||
17 | close $fd | ||
18 | set sx [split $sql \n] | ||
19 | set n [llength $sx] | ||
20 | if {$n>8} { | ||
21 | set sql {} | ||
22 | for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n} | ||
23 | append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n" | ||
24 | for {set i [expr {$n-3}]} {$i<$n} {incr i} { | ||
25 | append sql [lindex $sx $i]<br>\n | ||
26 | } | ||
27 | } else { | ||
28 | regsub -all \n [string trim $sql] <br> sql | ||
29 | } | ||
30 | puts "<blockquote>" | ||
31 | puts "$sql" | ||
32 | puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>" | ||
33 | set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} | ||
34 | set delay 1000 | ||
35 | # exec sync; after $delay; | ||
36 | # set t [time "exec psql drh <$sqlfile" 1] | ||
37 | # set t [expr {[lindex $t 0]/1000000.0}] | ||
38 | # puts [format $format PostgreSQL: $t] | ||
39 | exec sync; after $delay; | ||
40 | set t [time "exec mysql -f drh <$sqlfile" 1] | ||
41 | set t [expr {[lindex $t 0]/1000000.0}] | ||
42 | puts [format $format MySQL: $t] | ||
43 | # set t [time "exec ./sqlite232 s232.db <$sqlfile" 1] | ||
44 | # set t [expr {[lindex $t 0]/1000000.0}] | ||
45 | # puts [format $format {SQLite 2.3.2:} $t] | ||
46 | # set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1] | ||
47 | # set t [expr {[lindex $t 0]/1000000.0}] | ||
48 | # puts [format $format {SQLite 2.4 (cache=100):} $t] | ||
49 | exec sync; after $delay; | ||
50 | set t [time "exec ./sqlite248 s2k.db <$sqlfile" 1] | ||
51 | set t [expr {[lindex $t 0]/1000000.0}] | ||
52 | puts [format $format {SQLite 2.4.8:} $t] | ||
53 | exec sync; after $delay; | ||
54 | set t [time "exec ./sqlite248 sns.db <$sqlfile" 1] | ||
55 | set t [expr {[lindex $t 0]/1000000.0}] | ||
56 | puts [format $format {SQLite 2.4.8 (nosync):} $t] | ||
57 | exec sync; after $delay; | ||
58 | set t [time "exec ./sqlite2412 s2kb.db <$sqlfile" 1] | ||
59 | set t [expr {[lindex $t 0]/1000000.0}] | ||
60 | puts [format $format {SQLite 2.4.12:} $t] | ||
61 | exec sync; after $delay; | ||
62 | set t [time "exec ./sqlite2412 snsb.db <$sqlfile" 1] | ||
63 | set t [expr {[lindex $t 0]/1000000.0}] | ||
64 | puts [format $format {SQLite 2.4.12 (nosync):} $t] | ||
65 | # set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1] | ||
66 | # set t [expr {[lindex $t 0]/1000000.0}] | ||
67 | # puts [format $format {SQLite 2.4 (test):} $t] | ||
68 | puts "</table>" | ||
69 | } | ||
70 | |||
71 | # Initialize the environment | ||
72 | # | ||
73 | expr srand(1) | ||
74 | catch {exec /bin/sh -c {rm -f s*.db}} | ||
75 | set fd [open clear.sql w] | ||
76 | puts $fd { | ||
77 | drop table t1; | ||
78 | drop table t2; | ||
79 | } | ||
80 | close $fd | ||
81 | catch {exec psql drh <clear.sql} | ||
82 | catch {exec mysql drh <clear.sql} | ||
83 | set fd [open 2kinit.sql w] | ||
84 | puts $fd { | ||
85 | PRAGMA default_cache_size=2000; | ||
86 | PRAGMA default_synchronous=on; | ||
87 | } | ||
88 | close $fd | ||
89 | exec ./sqlite248 s2k.db <2kinit.sql | ||
90 | exec ./sqlite2412 s2kb.db <2kinit.sql | ||
91 | set fd [open nosync-init.sql w] | ||
92 | puts $fd { | ||
93 | PRAGMA default_cache_size=2000; | ||
94 | PRAGMA default_synchronous=off; | ||
95 | } | ||
96 | close $fd | ||
97 | exec ./sqlite248 sns.db <nosync-init.sql | ||
98 | exec ./sqlite2412 snsb.db <nosync-init.sql | ||
99 | set ones {zero one two three four five six seven eight nine | ||
100 | ten eleven twelve thirteen fourteen fifteen sixteen seventeen | ||
101 | eighteen nineteen} | ||
102 | set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} | ||
103 | proc number_name {n} { | ||
104 | if {$n>=1000} { | ||
105 | set txt "[number_name [expr {$n/1000}]] thousand" | ||
106 | set n [expr {$n%1000}] | ||
107 | } else { | ||
108 | set txt {} | ||
109 | } | ||
110 | if {$n>=100} { | ||
111 | append txt " [lindex $::ones [expr {$n/100}]] hundred" | ||
112 | set n [expr {$n%100}] | ||
113 | } | ||
114 | if {$n>=20} { | ||
115 | append txt " [lindex $::tens [expr {$n/10}]]" | ||
116 | set n [expr {$n%10}] | ||
117 | } | ||
118 | if {$n>0} { | ||
119 | append txt " [lindex $::ones $n]" | ||
120 | } | ||
121 | set txt [string trim $txt] | ||
122 | if {$txt==""} {set txt zero} | ||
123 | return $txt | ||
124 | } | ||
125 | |||
126 | |||
127 | |||
128 | set fd [open test$cnt.sql w] | ||
129 | puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" | ||
130 | for {set i 1} {$i<=1000} {incr i} { | ||
131 | set r [expr {int(rand()*100000)}] | ||
132 | puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" | ||
133 | } | ||
134 | close $fd | ||
135 | runtest {1000 INSERTs} | ||
136 | |||
137 | |||
138 | |||
139 | set fd [open test$cnt.sql w] | ||
140 | puts $fd "BEGIN;" | ||
141 | puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));" | ||
142 | for {set i 1} {$i<=25000} {incr i} { | ||
143 | set r [expr {int(rand()*500000)}] | ||
144 | puts $fd "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');" | ||
145 | } | ||
146 | puts $fd "COMMIT;" | ||
147 | close $fd | ||
148 | runtest {25000 INSERTs in a transaction} | ||
149 | |||
150 | |||
151 | |||
152 | set fd [open test$cnt.sql w] | ||
153 | for {set i 0} {$i<100} {incr i} { | ||
154 | set lwr [expr {$i*100}] | ||
155 | set upr [expr {($i+10)*100}] | ||
156 | puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;" | ||
157 | } | ||
158 | close $fd | ||
159 | runtest {100 SELECTs without an index} | ||
160 | |||
161 | |||
162 | |||
163 | set fd [open test$cnt.sql w] | ||
164 | for {set i 1} {$i<=100} {incr i} { | ||
165 | puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';" | ||
166 | } | ||
167 | close $fd | ||
168 | runtest {100 SELECTs on a string comparison} | ||
169 | |||
170 | |||
171 | |||
172 | set fd [open test$cnt.sql w] | ||
173 | puts $fd {CREATE INDEX i2a ON t2(a);} | ||
174 | puts $fd {CREATE INDEX i2b ON t2(b);} | ||
175 | close $fd | ||
176 | runtest {Creating an index} | ||
177 | |||
178 | |||
179 | |||
180 | set fd [open test$cnt.sql w] | ||
181 | for {set i 0} {$i<5000} {incr i} { | ||
182 | set lwr [expr {$i*100}] | ||
183 | set upr [expr {($i+1)*100}] | ||
184 | puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;" | ||
185 | } | ||
186 | close $fd | ||
187 | runtest {5000 SELECTs with an index} | ||
188 | |||
189 | |||
190 | |||
191 | set fd [open test$cnt.sql w] | ||
192 | puts $fd "BEGIN;" | ||
193 | for {set i 0} {$i<1000} {incr i} { | ||
194 | set lwr [expr {$i*10}] | ||
195 | set upr [expr {($i+1)*10}] | ||
196 | puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;" | ||
197 | } | ||
198 | puts $fd "COMMIT;" | ||
199 | close $fd | ||
200 | runtest {1000 UPDATEs without an index} | ||
201 | |||
202 | |||
203 | |||
204 | set fd [open test$cnt.sql w] | ||
205 | puts $fd "BEGIN;" | ||
206 | for {set i 1} {$i<=25000} {incr i} { | ||
207 | set r [expr {int(rand()*500000)}] | ||
208 | puts $fd "UPDATE t2 SET b=$r WHERE a=$i;" | ||
209 | } | ||
210 | puts $fd "COMMIT;" | ||
211 | close $fd | ||
212 | runtest {25000 UPDATEs with an index} | ||
213 | |||
214 | |||
215 | set fd [open test$cnt.sql w] | ||
216 | puts $fd "BEGIN;" | ||
217 | for {set i 1} {$i<=25000} {incr i} { | ||
218 | set r [expr {int(rand()*500000)}] | ||
219 | puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;" | ||
220 | } | ||
221 | puts $fd "COMMIT;" | ||
222 | close $fd | ||
223 | runtest {25000 text UPDATEs with an index} | ||
224 | |||
225 | |||
226 | |||
227 | set fd [open test$cnt.sql w] | ||
228 | puts $fd "BEGIN;" | ||
229 | puts $fd "INSERT INTO t1 SELECT * FROM t2;" | ||
230 | puts $fd "INSERT INTO t2 SELECT * FROM t1;" | ||
231 | puts $fd "COMMIT;" | ||
232 | close $fd | ||
233 | runtest {INSERTs from a SELECT} | ||
234 | |||
235 | |||
236 | |||
237 | set fd [open test$cnt.sql w] | ||
238 | puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';} | ||
239 | close $fd | ||
240 | runtest {DELETE without an index} | ||
241 | |||
242 | |||
243 | |||
244 | set fd [open test$cnt.sql w] | ||
245 | puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;} | ||
246 | close $fd | ||
247 | runtest {DELETE with an index} | ||
248 | |||
249 | |||
250 | |||
251 | set fd [open test$cnt.sql w] | ||
252 | puts $fd {INSERT INTO t2 SELECT * FROM t1;} | ||
253 | close $fd | ||
254 | runtest {A big INSERT after a big DELETE} | ||
255 | |||
256 | |||
257 | |||
258 | set fd [open test$cnt.sql w] | ||
259 | puts $fd {BEGIN;} | ||
260 | puts $fd {DELETE FROM t1;} | ||
261 | for {set i 1} {$i<=3000} {incr i} { | ||
262 | set r [expr {int(rand()*100000)}] | ||
263 | puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" | ||
264 | } | ||
265 | puts $fd {COMMIT;} | ||
266 | close $fd | ||
267 | runtest {A big DELETE followed by many small INSERTs} | ||
268 | |||
269 | |||
270 | |||
271 | set fd [open test$cnt.sql w] | ||
272 | puts $fd {DROP TABLE t1;} | ||
273 | puts $fd {DROP TABLE t2;} | ||
274 | close $fd | ||
275 | runtest {DROP TABLE} | ||