tool/mkspeedsql.tcl

   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;}