Annotation of embedaddon/sqlite3/tool/speedtest2.tcl, revision 1.1.1.1
1.1 misho 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 ./sqlite240 s2k.db <$sqlfile" 1]
51: set t [expr {[lindex $t 0]/1000000.0}]
52: puts [format $format {SQLite 2.4:} $t]
53: exec sync; after $delay;
54: set t [time "exec ./sqlite240 sns.db <$sqlfile" 1]
55: set t [expr {[lindex $t 0]/1000000.0}]
56: puts [format $format {SQLite 2.4 (nosync):} $t]
57: # set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
58: # set t [expr {[lindex $t 0]/1000000.0}]
59: # puts [format $format {SQLite 2.4 (test):} $t]
60: puts "</table>"
61: }
62:
63: # Initialize the environment
64: #
65: expr srand(1)
66: catch {exec /bin/sh -c {rm -f s*.db}}
67: set fd [open clear.sql w]
68: puts $fd {
69: drop table t1;
70: drop table t2;
71: }
72: close $fd
73: catch {exec psql drh <clear.sql}
74: catch {exec mysql drh <clear.sql}
75: set fd [open 2kinit.sql w]
76: puts $fd {
77: PRAGMA default_cache_size=2000;
78: PRAGMA default_synchronous=on;
79: }
80: close $fd
81: exec ./sqlite240 s2k.db <2kinit.sql
82: exec ./sqlite-t1 st1.db <2kinit.sql
83: set fd [open nosync-init.sql w]
84: puts $fd {
85: PRAGMA default_cache_size=2000;
86: PRAGMA default_synchronous=off;
87: }
88: close $fd
89: exec ./sqlite240 sns.db <nosync-init.sql
90: set ones {zero one two three four five six seven eight nine
91: ten eleven twelve thirteen fourteen fifteen sixteen seventeen
92: eighteen nineteen}
93: set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
94: proc number_name {n} {
95: if {$n>=1000} {
96: set txt "[number_name [expr {$n/1000}]] thousand"
97: set n [expr {$n%1000}]
98: } else {
99: set txt {}
100: }
101: if {$n>=100} {
102: append txt " [lindex $::ones [expr {$n/100}]] hundred"
103: set n [expr {$n%100}]
104: }
105: if {$n>=20} {
106: append txt " [lindex $::tens [expr {$n/10}]]"
107: set n [expr {$n%10}]
108: }
109: if {$n>0} {
110: append txt " [lindex $::ones $n]"
111: }
112: set txt [string trim $txt]
113: if {$txt==""} {set txt zero}
114: return $txt
115: }
116:
117:
118: set fd [open test$cnt.sql w]
119: puts $fd "BEGIN;"
120: puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
121: for {set i 1} {$i<=25000} {incr i} {
122: set r [expr {int(rand()*500000)}]
123: puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
124: }
125: puts $fd "COMMIT;"
126: close $fd
127: runtest {25000 INSERTs in a transaction}
128:
129:
130: set fd [open test$cnt.sql w]
131: puts $fd "DELETE FROM t1;"
132: close $fd
133: runtest {DELETE everything}
134:
135:
136: set fd [open test$cnt.sql w]
137: puts $fd "BEGIN;"
138: for {set i 1} {$i<=25000} {incr i} {
139: set r [expr {int(rand()*500000)}]
140: puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
141: }
142: puts $fd "COMMIT;"
143: close $fd
144: runtest {25000 INSERTs in a transaction}
145:
146:
147: set fd [open test$cnt.sql w]
148: puts $fd "DELETE FROM t1;"
149: close $fd
150: runtest {DELETE everything}
151:
152:
153: set fd [open test$cnt.sql w]
154: puts $fd "BEGIN;"
155: for {set i 1} {$i<=25000} {incr i} {
156: set r [expr {int(rand()*500000)}]
157: puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
158: }
159: puts $fd "COMMIT;"
160: close $fd
161: runtest {25000 INSERTs in a transaction}
162:
163:
164: set fd [open test$cnt.sql w]
165: puts $fd "DELETE FROM t1;"
166: close $fd
167: runtest {DELETE everything}
168:
169:
170: set fd [open test$cnt.sql w]
171: puts $fd "BEGIN;"
172: for {set i 1} {$i<=25000} {incr i} {
173: set r [expr {int(rand()*500000)}]
174: puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
175: }
176: puts $fd "COMMIT;"
177: close $fd
178: runtest {25000 INSERTs in a transaction}
179:
180:
181: set fd [open test$cnt.sql w]
182: puts $fd "DELETE FROM t1;"
183: close $fd
184: runtest {DELETE everything}
185:
186:
187: set fd [open test$cnt.sql w]
188: puts $fd "BEGIN;"
189: for {set i 1} {$i<=25000} {incr i} {
190: set r [expr {int(rand()*500000)}]
191: puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
192: }
193: puts $fd "COMMIT;"
194: close $fd
195: runtest {25000 INSERTs in a transaction}
196:
197:
198: set fd [open test$cnt.sql w]
199: puts $fd "DELETE FROM t1;"
200: close $fd
201: runtest {DELETE everything}
202:
203:
204: set fd [open test$cnt.sql w]
205: puts $fd {DROP TABLE t1;}
206: close $fd
207: runtest {DROP TABLE}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to speedtest2.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