1 # 2006 November 23

     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 implements regression tests for SQLite library.  The

    12 # focus of this script is measuring executing speed.

    13 #

    14 # $Id: speed2.test,v 1.7 2007/04/16 15:02:20 drh Exp $

    15 #

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 speed_trial_init speed2

    20 

    21 # Set a uniform random seed

    22 expr srand(0)

    23 

    24 set sqlout [open speed2.txt w]

    25 proc tracesql {sql} {

    26   puts $::sqlout $sql\;

    27 }

    28 #db trace tracesql

    29 

    30 # The number_name procedure below converts its argment (an integer)

    31 # into a string which is the English-language name for that number.

    32 #

    33 # Example:

    34 #

    35 #     puts [number_name 123]   ->  "one hundred twenty three"

    36 #

    37 set ones {zero one two three four five six seven eight nine

    38           ten eleven twelve thirteen fourteen fifteen sixteen seventeen

    39           eighteen nineteen}

    40 set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}

    41 proc number_name {n} {

    42   if {$n>=1000} {

    43     set txt "[number_name [expr {$n/1000}]] thousand"

    44     set n [expr {$n%1000}]

    45   } else {

    46     set txt {}

    47   }

    48   if {$n>=100} {

    49     append txt " [lindex $::ones [expr {$n/100}]] hundred"

    50     set n [expr {$n%100}]

    51   }

    52   if {$n>=20} {

    53     append txt " [lindex $::tens [expr {$n/10}]]"

    54     set n [expr {$n%10}]

    55   }

    56   if {$n>0} {

    57     append txt " [lindex $::ones $n]"

    58   }

    59   set txt [string trim $txt]

    60   if {$txt==""} {set txt zero}

    61   return $txt

    62 }

    63 

    64 # Create a database schema.

    65 #

    66 do_test speed2-1.0 {

    67   execsql {

    68     PRAGMA page_size=1024;

    69     PRAGMA cache_size=8192;

    70     PRAGMA locking_mode=EXCLUSIVE;

    71     CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);

    72     CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);

    73     CREATE INDEX i2a ON t2(a);

    74     CREATE INDEX i2b ON t2(b);

    75   }

    76   execsql {

    77     SELECT name FROM sqlite_master ORDER BY 1;

    78   }

    79 } {i2a i2b t1 t2}

    80 

    81 

    82 # 50000 INSERTs on an unindexed table

    83 #

    84 set sql {}

    85 for {set i 1} {$i<=50000} {incr i} {

    86   set r [expr {int(rand()*500000)}]

    87   append sql "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');\n"

    88 }

    89 db eval BEGIN

    90 speed_trial speed2-insert1 50000 row $sql

    91 db eval COMMIT

    92 

    93 # 50000 INSERTs on an indexed table

    94 #

    95 set sql {}

    96 for {set i 1} {$i<=50000} {incr i} {

    97   set r [expr {int(rand()*500000)}]

    98   append sql "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');\n"

    99 }

   100 db eval BEGIN

   101 speed_trial speed2-insert2 50000 row $sql

   102 db eval COMMIT

   103 

   104 

   105 

   106 # 50 SELECTs on an integer comparison.  There is no index so

   107 # a full table scan is required.

   108 #

   109 set sql {}

   110 for {set i 0} {$i<50} {incr i} {

   111   set lwr [expr {$i*100}]

   112   set upr [expr {($i+10)*100}]

   113   append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"

   114 }

   115 speed_trial speed2-select1a [expr {50*50000}] row $sql

   116 

   117 # 50 SELECTs on an LIKE comparison.  There is no index so a full

   118 # table scan is required.

   119 #

   120 set sql {}

   121 for {set i 0} {$i<50} {incr i} {

   122   append sql \

   123     "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"

   124 }

   125 speed_trial speed2-select2a [expr {50*50000}] row $sql

   126 

   127 # Vacuum

   128 speed_trial speed2-vacuum1 100000 row VACUUM

   129 

   130 # 50 SELECTs on an integer comparison.  There is no index so

   131 # a full table scan is required.

   132 #

   133 set sql {}

   134 for {set i 0} {$i<50} {incr i} {

   135   set lwr [expr {$i*100}]

   136   set upr [expr {($i+10)*100}]

   137   append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"

   138 }

   139 speed_trial speed2-select1b [expr {50*50000}] row $sql

   140 

   141 # 50 SELECTs on an LIKE comparison.  There is no index so a full

   142 # table scan is required.

   143 #

   144 set sql {}

   145 for {set i 0} {$i<50} {incr i} {

   146   append sql \

   147     "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"

   148 }

   149 speed_trial speed2-select2b [expr {50*50000}] row $sql

   150 

   151 # Create indices

   152 #

   153 db eval BEGIN

   154 speed_trial speed2-createidx 150000 row {

   155   CREATE INDEX i1a ON t1(a);

   156   CREATE INDEX i1b ON t1(b);

   157   CREATE INDEX i1c ON t1(c);

   158 }

   159 db eval COMMIT

   160 

   161 # 5000 SELECTs on an integer comparison where the integer is

   162 # indexed.

   163 #

   164 set sql {}

   165 for {set i 0} {$i<5000} {incr i} {

   166   set lwr [expr {$i*100}]

   167   set upr [expr {($i+10)*100}]

   168   append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"

   169 }

   170 speed_trial speed2-select3a 5000 stmt $sql

   171 

   172 # 100000 random SELECTs against rowid.

   173 #

   174 set sql {}

   175 for {set i 1} {$i<=100000} {incr i} {

   176   set id [expr {int(rand()*50000)+1}]

   177   append sql "SELECT c=='hi' FROM t1 WHERE rowid=$id;\n"

   178 }

   179 speed_trial speed2-select4a 100000 row $sql

   180 

   181 # 100000 random SELECTs against a unique indexed column.

   182 #

   183 set sql {}

   184 for {set i 1} {$i<=100000} {incr i} {

   185   set id [expr {int(rand()*50000)+1}]

   186   append sql "SELECT c FROM t1 WHERE a=$id;"

   187 }

   188 speed_trial speed2-select5a 100000 row $sql

   189 

   190 # 50000 random SELECTs against an indexed column text column

   191 #

   192 set sql {}

   193 db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {

   194   append sql "SELECT c FROM t1 WHERE c='$c';"

   195 }

   196 speed_trial speed2-select6a 50000 row $sql

   197 

   198 # Vacuum

   199 speed_trial speed2-vacuum2 100000 row VACUUM

   200 

   201 

   202 # 5000 SELECTs on an integer comparison where the integer is

   203 # indexed.

   204 #

   205 set sql {}

   206 for {set i 0} {$i<5000} {incr i} {

   207   set lwr [expr {$i*100}]

   208   set upr [expr {($i+10)*100}]

   209   append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"

   210 }

   211 speed_trial speed2-select3b 5000 stmt $sql

   212 

   213 # 100000 random SELECTs against rowid.

   214 #

   215 set sql {}

   216 for {set i 1} {$i<=100000} {incr i} {

   217   set id [expr {int(rand()*50000)+1}]

   218   append sql "SELECT c=='hi' FROM t1 WHERE rowid=$id;\n"

   219 }

   220 speed_trial speed2-select4b 100000 row $sql

   221 

   222 # 100000 random SELECTs against a unique indexed column.

   223 #

   224 set sql {}

   225 for {set i 1} {$i<=100000} {incr i} {

   226   set id [expr {int(rand()*50000)+1}]

   227   append sql "SELECT c FROM t1 WHERE a=$id;"

   228 }

   229 speed_trial speed2-select5b 100000 row $sql

   230 

   231 # 50000 random SELECTs against an indexed column text column

   232 #

   233 set sql {}

   234 db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {

   235   append sql "SELECT c FROM t1 WHERE c='$c';"

   236 }

   237 speed_trial speed2-select6b 50000 row $sql

   238 

   239 # 5000 updates of ranges where the field being compared is indexed.

   240 #

   241 set sql {}

   242 for {set i 0} {$i<5000} {incr i} {

   243   set lwr [expr {$i*2}]

   244   set upr [expr {($i+1)*2}]

   245   append sql "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"

   246 }

   247 db eval BEGIN

   248 speed_trial speed2-update1 5000 stmt $sql

   249 db eval COMMIT

   250 

   251 # 50000 single-row updates.  An index is used to find the row quickly.

   252 #

   253 set sql {}

   254 for {set i 0} {$i<50000} {incr i} {

   255   set r [expr {int(rand()*500000)}]

   256   append sql "UPDATE t1 SET b=$r WHERE a=$i;"

   257 }

   258 db eval BEGIN

   259 speed_trial speed2-update2 50000 row $sql

   260 db eval COMMIT

   261 

   262 # 1 big text update that touches every row in the table.

   263 #

   264 speed_trial speed2-update3 50000 row {

   265   UPDATE t1 SET c=a;

   266 }

   267 

   268 # Many individual text updates.  Each row in the table is

   269 # touched through an index.

   270 #

   271 set sql {}

   272 for {set i 1} {$i<=50000} {incr i} {

   273   set r [expr {int(rand()*500000)}]

   274   append sql "UPDATE t1 SET c='[number_name $r]' WHERE a=$i;"

   275 }

   276 db eval BEGIN

   277 speed_trial speed2-update4 50000 row $sql

   278 db eval COMMIT

   279 

   280 # Delete all content in a table.

   281 #

   282 speed_trial speed2-delete1 50000 row {DELETE FROM t1}

   283 

   284 # Copy one table into another

   285 #

   286 speed_trial speed2-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}

   287 

   288 # Delete all content in a table, one row at a time.

   289 #

   290 speed_trial speed2-delete2 50000 row {DELETE FROM t1 WHERE 1}

   291 

   292 # Refill the table yet again

   293 #

   294 speed_trial speed2-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}

   295 

   296 # Drop the table and recreate it without its indices.

   297 #

   298 db eval BEGIN

   299 speed_trial speed2-drop1 50000 row {

   300    DROP TABLE t1;

   301    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);

   302 }

   303 db eval COMMIT

   304 

   305 # Refill the table yet again.  This copy should be faster because

   306 # there are no indices to deal with.

   307 #

   308 speed_trial speed2-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}

   309 

   310 # Select 20000 rows from the table at random.

   311 #

   312 speed_trial speed2-random1 50000 row {

   313   SELECT rowid FROM t1 ORDER BY random() LIMIT 20000

   314 }

   315 

   316 # Delete 20000 random rows from the table.

   317 #

   318 speed_trial speed2-random-del1 20000 row {

   319   DELETE FROM t1 WHERE rowid IN

   320     (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)

   321 }

   322 do_test speed2-1.1 {

   323   db one {SELECT count(*) FROM t1}

   324 } 30000

   325 

   326     

   327 # Delete 20000 more rows at random from the table.

   328 #

   329 speed_trial speed2-random-del2 20000 row {

   330   DELETE FROM t1 WHERE rowid IN

   331     (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)

   332 }

   333 do_test speed2-1.2 {

   334   db one {SELECT count(*) FROM t1}

   335 } 10000

   336 speed_trial_summary speed2

   337 

   338 

   339 finish_test