Annotation of embedaddon/sqlite3/tool/mkspeedsql.tcl, revision 1.1.1.1
1.1 misho 1: # 2008 October 9
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #*************************************************************************
11: # This file generates SQL text used for performance testing.
12: #
13: # $Id: mkspeedsql.tcl,v 1.1 2008/10/09 17:57:34 drh Exp $
14: #
15:
16: # Set a uniform random seed
17: expr srand(0)
18:
19: # The number_name procedure below converts its argment (an integer)
20: # into a string which is the English-language name for that number.
21: #
22: # Example:
23: #
24: # puts [number_name 123] -> "one hundred twenty three"
25: #
26: set ones {zero one two three four five six seven eight nine
27: ten eleven twelve thirteen fourteen fifteen sixteen seventeen
28: eighteen nineteen}
29: set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
30: proc number_name {n} {
31: if {$n>=1000} {
32: set txt "[number_name [expr {$n/1000}]] thousand"
33: set n [expr {$n%1000}]
34: } else {
35: set txt {}
36: }
37: if {$n>=100} {
38: append txt " [lindex $::ones [expr {$n/100}]] hundred"
39: set n [expr {$n%100}]
40: }
41: if {$n>=20} {
42: append txt " [lindex $::tens [expr {$n/10}]]"
43: set n [expr {$n%10}]
44: }
45: if {$n>0} {
46: append txt " [lindex $::ones $n]"
47: }
48: set txt [string trim $txt]
49: if {$txt==""} {set txt zero}
50: return $txt
51: }
52:
53: # Create a database schema.
54: #
55: puts {
56: PRAGMA page_size=1024;
57: PRAGMA cache_size=8192;
58: PRAGMA locking_mode=EXCLUSIVE;
59: CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
60: CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
61: CREATE INDEX i2a ON t2(a);
62: CREATE INDEX i2b ON t2(b);
63: SELECT name FROM sqlite_master ORDER BY 1;
64: }
65:
66:
67: # 50000 INSERTs on an unindexed table
68: #
69: set t1c_list {}
70: puts {BEGIN;}
71: for {set i 1} {$i<=50000} {incr i} {
72: set r [expr {int(rand()*500000)}]
73: set x [number_name $r]
74: lappend t1c_list $x
75: puts "INSERT INTO t1 VALUES($i,$r,'$x');"
76: }
77: puts {COMMIT;}
78:
79: # 50000 INSERTs on an indexed table
80: #
81: puts {BEGIN;}
82: for {set i 1} {$i<=50000} {incr i} {
83: set r [expr {int(rand()*500000)}]
84: puts "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');"
85: }
86: puts {COMMIT;}
87:
88:
89: # 50 SELECTs on an integer comparison. There is no index so
90: # a full table scan is required.
91: #
92: for {set i 0} {$i<50} {incr i} {
93: set lwr [expr {$i*100}]
94: set upr [expr {($i+10)*100}]
95: puts "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
96: }
97:
98: # 50 SELECTs on an LIKE comparison. There is no index so a full
99: # table scan is required.
100: #
101: for {set i 0} {$i<50} {incr i} {
102: puts "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"
103: }
104:
105: # Create indices
106: #
107: puts {BEGIN;}
108: puts {
109: CREATE INDEX i1a ON t1(a);
110: CREATE INDEX i1b ON t1(b);
111: CREATE INDEX i1c ON t1(c);
112: }
113: puts {COMMIT;}
114:
115: # 5000 SELECTs on an integer comparison where the integer is
116: # indexed.
117: #
118: set sql {}
119: for {set i 0} {$i<5000} {incr i} {
120: set lwr [expr {$i*100}]
121: set upr [expr {($i+10)*100}]
122: puts "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
123: }
124:
125: # 100000 random SELECTs against rowid.
126: #
127: for {set i 1} {$i<=100000} {incr i} {
128: set id [expr {int(rand()*50000)+1}]
129: puts "SELECT c FROM t1 WHERE rowid=$id;"
130: }
131:
132: # 100000 random SELECTs against a unique indexed column.
133: #
134: for {set i 1} {$i<=100000} {incr i} {
135: set id [expr {int(rand()*50000)+1}]
136: puts "SELECT c FROM t1 WHERE a=$id;"
137: }
138:
139: # 50000 random SELECTs against an indexed column text column
140: #
141: set nt1c [llength $t1c_list]
142: for {set i 0} {$i<50000} {incr i} {
143: set r [expr {int(rand()*$nt1c)}]
144: set c [lindex $t1c_list $i]
145: puts "SELECT c FROM t1 WHERE c='$c';"
146: }
147:
148:
149: # Vacuum
150: puts {VACUUM;}
151:
152: # 5000 updates of ranges where the field being compared is indexed.
153: #
154: puts {BEGIN;}
155: for {set i 0} {$i<5000} {incr i} {
156: set lwr [expr {$i*2}]
157: set upr [expr {($i+1)*2}]
158: puts "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
159: }
160: puts {COMMIT;}
161:
162: # 50000 single-row updates. An index is used to find the row quickly.
163: #
164: puts {BEGIN;}
165: for {set i 0} {$i<50000} {incr i} {
166: set r [expr {int(rand()*500000)}]
167: puts "UPDATE t1 SET b=$r WHERE a=$i;"
168: }
169: puts {COMMIT;}
170:
171: # 1 big text update that touches every row in the table.
172: #
173: puts {
174: UPDATE t1 SET c=a;
175: }
176:
177: # Many individual text updates. Each row in the table is
178: # touched through an index.
179: #
180: puts {BEGIN;}
181: for {set i 1} {$i<=50000} {incr i} {
182: set r [expr {int(rand()*500000)}]
183: puts "UPDATE t1 SET c='[number_name $r]' WHERE a=$i;"
184: }
185: puts {COMMIT;}
186:
187: # Delete all content in a table.
188: #
189: puts {DELETE FROM t1;}
190:
191: # Copy one table into another
192: #
193: puts {INSERT INTO t1 SELECT * FROM t2;}
194:
195: # Delete all content in a table, one row at a time.
196: #
197: puts {DELETE FROM t1 WHERE 1;}
198:
199: # Refill the table yet again
200: #
201: puts {INSERT INTO t1 SELECT * FROM t2;}
202:
203: # Drop the table and recreate it without its indices.
204: #
205: puts {BEGIN;}
206: puts {
207: DROP TABLE t1;
208: CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
209: }
210: puts {COMMIT;}
211:
212: # Refill the table yet again. This copy should be faster because
213: # there are no indices to deal with.
214: #
215: puts {INSERT INTO t1 SELECT * FROM t2;}
216:
217: # Select 20000 rows from the table at random.
218: #
219: puts {
220: SELECT rowid FROM t1 ORDER BY random() LIMIT 20000;
221: }
222:
223: # Delete 20000 random rows from the table.
224: #
225: puts {
226: DELETE FROM t1 WHERE rowid IN
227: (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000);
228: }
229: puts {SELECT count(*) FROM t1;}
230:
231: # Delete 20000 more rows at random from the table.
232: #
233: puts {
234: DELETE FROM t1 WHERE rowid IN
235: (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000);
236: }
237: puts {SELECT count(*) FROM t1;}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to mkspeedsql.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