aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/libraries/sqlite/unix/sqlite-3.5.1/tool/speedtest.tcl
diff options
context:
space:
mode:
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.tcl275
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#
9set cnt 1
10proc 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">&nbsp;&nbsp;&nbsp;%.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#
73expr srand(1)
74catch {exec /bin/sh -c {rm -f s*.db}}
75set fd [open clear.sql w]
76puts $fd {
77 drop table t1;
78 drop table t2;
79}
80close $fd
81catch {exec psql drh <clear.sql}
82catch {exec mysql drh <clear.sql}
83set fd [open 2kinit.sql w]
84puts $fd {
85 PRAGMA default_cache_size=2000;
86 PRAGMA default_synchronous=on;
87}
88close $fd
89exec ./sqlite248 s2k.db <2kinit.sql
90exec ./sqlite2412 s2kb.db <2kinit.sql
91set fd [open nosync-init.sql w]
92puts $fd {
93 PRAGMA default_cache_size=2000;
94 PRAGMA default_synchronous=off;
95}
96close $fd
97exec ./sqlite248 sns.db <nosync-init.sql
98exec ./sqlite2412 snsb.db <nosync-init.sql
99set ones {zero one two three four five six seven eight nine
100 ten eleven twelve thirteen fourteen fifteen sixteen seventeen
101 eighteen nineteen}
102set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
103proc 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
128set fd [open test$cnt.sql w]
129puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
130for {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}
134close $fd
135runtest {1000 INSERTs}
136
137
138
139set fd [open test$cnt.sql w]
140puts $fd "BEGIN;"
141puts $fd "CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));"
142for {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}
146puts $fd "COMMIT;"
147close $fd
148runtest {25000 INSERTs in a transaction}
149
150
151
152set fd [open test$cnt.sql w]
153for {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}
158close $fd
159runtest {100 SELECTs without an index}
160
161
162
163set fd [open test$cnt.sql w]
164for {set i 1} {$i<=100} {incr i} {
165 puts $fd "SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%[number_name $i]%';"
166}
167close $fd
168runtest {100 SELECTs on a string comparison}
169
170
171
172set fd [open test$cnt.sql w]
173puts $fd {CREATE INDEX i2a ON t2(a);}
174puts $fd {CREATE INDEX i2b ON t2(b);}
175close $fd
176runtest {Creating an index}
177
178
179
180set fd [open test$cnt.sql w]
181for {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}
186close $fd
187runtest {5000 SELECTs with an index}
188
189
190
191set fd [open test$cnt.sql w]
192puts $fd "BEGIN;"
193for {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}
198puts $fd "COMMIT;"
199close $fd
200runtest {1000 UPDATEs without an index}
201
202
203
204set fd [open test$cnt.sql w]
205puts $fd "BEGIN;"
206for {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}
210puts $fd "COMMIT;"
211close $fd
212runtest {25000 UPDATEs with an index}
213
214
215set fd [open test$cnt.sql w]
216puts $fd "BEGIN;"
217for {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}
221puts $fd "COMMIT;"
222close $fd
223runtest {25000 text UPDATEs with an index}
224
225
226
227set fd [open test$cnt.sql w]
228puts $fd "BEGIN;"
229puts $fd "INSERT INTO t1 SELECT * FROM t2;"
230puts $fd "INSERT INTO t2 SELECT * FROM t1;"
231puts $fd "COMMIT;"
232close $fd
233runtest {INSERTs from a SELECT}
234
235
236
237set fd [open test$cnt.sql w]
238puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';}
239close $fd
240runtest {DELETE without an index}
241
242
243
244set fd [open test$cnt.sql w]
245puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;}
246close $fd
247runtest {DELETE with an index}
248
249
250
251set fd [open test$cnt.sql w]
252puts $fd {INSERT INTO t2 SELECT * FROM t1;}
253close $fd
254runtest {A big INSERT after a big DELETE}
255
256
257
258set fd [open test$cnt.sql w]
259puts $fd {BEGIN;}
260puts $fd {DELETE FROM t1;}
261for {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}
265puts $fd {COMMIT;}
266close $fd
267runtest {A big DELETE followed by many small INSERTs}
268
269
270
271set fd [open test$cnt.sql w]
272puts $fd {DROP TABLE t1;}
273puts $fd {DROP TABLE t2;}
274close $fd
275runtest {DROP TABLE}