xref: /titanic_41/usr/src/lib/libsqlite/tool/speedtest2.tcl (revision e4b86885570d77af552e9cf94f142f4d744fb8c8) 
1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
4#!/usr/bin/tclsh
5#
6# Run this script using TCLSH to do a speed comparison between
7# various versions of SQLite and PostgreSQL and MySQL
8#
9
10# Run a test
11#
12set cnt 1
13proc runtest {title} {
14  global cnt
15  set sqlfile test$cnt.sql
16  puts "<h2>Test $cnt: $title</h2>"
17  incr cnt
18  set fd [open $sqlfile r]
19  set sql [string trim [read $fd [file size $sqlfile]]]
20  close $fd
21  set sx [split $sql \n]
22  set n [llength $sx]
23  if {$n>8} {
24    set sql {}
25    for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n}
26    append sql  "<i>... [expr {$n-6}] lines omitted</i><br>\n"
27    for {set i [expr {$n-3}]} {$i<$n} {incr i} {
28      append sql [lindex $sx $i]<br>\n
29    }
30  } else {
31    regsub -all \n [string trim $sql] <br> sql
32  }
33  puts "<blockquote>"
34  puts "$sql"
35  puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>"
36  set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
37  set delay 1000
38  exec sync; after $delay;
39  set t [time "exec psql drh <$sqlfile" 1]
40  set t [expr {[lindex $t 0]/1000000.0}]
41  puts [format $format PostgreSQL: $t]
42  exec sync; after $delay;
43  set t [time "exec mysql -f drh <$sqlfile" 1]
44  set t [expr {[lindex $t 0]/1000000.0}]
45  puts [format $format MySQL: $t]
46#  set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
47#  set t [expr {[lindex $t 0]/1000000.0}]
48#  puts [format $format {SQLite 2.3.2:} $t]
49#  set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
50#  set t [expr {[lindex $t 0]/1000000.0}]
51#  puts [format $format {SQLite 2.4 (cache=100):} $t]
52  exec sync; after $delay;
53  set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1]
54  set t [expr {[lindex $t 0]/1000000.0}]
55  puts [format $format {SQLite 2.4:} $t]
56  exec sync; after $delay;
57  set t [time "exec ./sqlite240 sns.db <$sqlfile" 1]
58  set t [expr {[lindex $t 0]/1000000.0}]
59  puts [format $format {SQLite 2.4 (nosync):} $t]
60#  set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
61#  set t [expr {[lindex $t 0]/1000000.0}]
62#  puts [format $format {SQLite 2.4 (test):} $t]
63  puts "</table>"
64}
65
66# Initialize the environment
67#
68expr srand(1)
69catch {exec /bin/sh -c {rm -f s*.db}}
70set fd [open clear.sql w]
71puts $fd {
72  drop table t1;
73  drop table t2;
74}
75close $fd
76catch {exec psql drh <clear.sql}
77catch {exec mysql drh <clear.sql}
78set fd [open 2kinit.sql w]
79puts $fd {
80  PRAGMA default_cache_size=2000;
81  PRAGMA default_synchronous=on;
82}
83close $fd
84exec ./sqlite240 s2k.db <2kinit.sql
85exec ./sqlite-t1 st1.db <2kinit.sql
86set fd [open nosync-init.sql w]
87puts $fd {
88  PRAGMA default_cache_size=2000;
89  PRAGMA default_synchronous=off;
90}
91close $fd
92exec ./sqlite240 sns.db <nosync-init.sql
93set ones {zero one two three four five six seven eight nine
94          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
95          eighteen nineteen}
96set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
97proc number_name {n} {
98  if {$n>=1000} {
99    set txt "[number_name [expr {$n/1000}]] thousand"
100    set n [expr {$n%1000}]
101  } else {
102    set txt {}
103  }
104  if {$n>=100} {
105    append txt " [lindex $::ones [expr {$n/100}]] hundred"
106    set n [expr {$n%100}]
107  }
108  if {$n>=20} {
109    append txt " [lindex $::tens [expr {$n/10}]]"
110    set n [expr {$n%10}]
111  }
112  if {$n>0} {
113    append txt " [lindex $::ones $n]"
114  }
115  set txt [string trim $txt]
116  if {$txt==""} {set txt zero}
117  return $txt
118}
119
120
121set fd [open test$cnt.sql w]
122puts $fd "BEGIN;"
123puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
124for {set i 1} {$i<=25000} {incr i} {
125  set r [expr {int(rand()*500000)}]
126  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
127}
128puts $fd "COMMIT;"
129close $fd
130runtest {25000 INSERTs in a transaction}
131
132
133set fd [open test$cnt.sql w]
134puts $fd "DELETE FROM t1;"
135close $fd
136runtest {DELETE everything}
137
138
139set fd [open test$cnt.sql w]
140puts $fd "BEGIN;"
141for {set i 1} {$i<=25000} {incr i} {
142  set r [expr {int(rand()*500000)}]
143  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
144}
145puts $fd "COMMIT;"
146close $fd
147runtest {25000 INSERTs in a transaction}
148
149
150set fd [open test$cnt.sql w]
151puts $fd "DELETE FROM t1;"
152close $fd
153runtest {DELETE everything}
154
155
156set fd [open test$cnt.sql w]
157puts $fd "BEGIN;"
158for {set i 1} {$i<=25000} {incr i} {
159  set r [expr {int(rand()*500000)}]
160  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
161}
162puts $fd "COMMIT;"
163close $fd
164runtest {25000 INSERTs in a transaction}
165
166
167set fd [open test$cnt.sql w]
168puts $fd "DELETE FROM t1;"
169close $fd
170runtest {DELETE everything}
171
172
173set fd [open test$cnt.sql w]
174puts $fd "BEGIN;"
175for {set i 1} {$i<=25000} {incr i} {
176  set r [expr {int(rand()*500000)}]
177  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
178}
179puts $fd "COMMIT;"
180close $fd
181runtest {25000 INSERTs in a transaction}
182
183
184set fd [open test$cnt.sql w]
185puts $fd "DELETE FROM t1;"
186close $fd
187runtest {DELETE everything}
188
189
190set fd [open test$cnt.sql w]
191puts $fd "BEGIN;"
192for {set i 1} {$i<=25000} {incr i} {
193  set r [expr {int(rand()*500000)}]
194  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
195}
196puts $fd "COMMIT;"
197close $fd
198runtest {25000 INSERTs in a transaction}
199
200
201set fd [open test$cnt.sql w]
202puts $fd "DELETE FROM t1;"
203close $fd
204runtest {DELETE everything}
205
206
207set fd [open test$cnt.sql w]
208puts $fd {DROP TABLE t1;}
209close $fd
210runtest {DROP TABLE}
211