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}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speedtest.tcl CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool