os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/speed1p.explain
author sl@SLION-WIN7.fritz.box
Fri, 15 Jun 2012 03:10:57 +0200
changeset 0 bde4ae8d615e
permissions -rw-r--r--
First public contribution.
sl@0
     1
# 2008 March 21
sl@0
     2
#
sl@0
     3
# The author disclaims copyright to this source code.  In place of
sl@0
     4
# a legal notice, here is a blessing:
sl@0
     5
#
sl@0
     6
#    May you do good and not evil.
sl@0
     7
#    May you find forgiveness for yourself and forgive others.
sl@0
     8
#    May you share freely, never taking more than you give.
sl@0
     9
#
sl@0
    10
#*************************************************************************
sl@0
    11
# This file implements regression tests for SQLite library.  The
sl@0
    12
# focus of this script is measuring executing speed.  
sl@0
    13
#
sl@0
    14
# This is a copy of speed1.test modified to user prepared statements.
sl@0
    15
#
sl@0
    16
# $Id: speed1p.explain,v 1.1 2008/04/16 12:57:48 drh Exp $
sl@0
    17
#
sl@0
    18
sl@0
    19
set testdir [file dirname $argv0]
sl@0
    20
source $testdir/tester.tcl
sl@0
    21
speed_trial_init speed1
sl@0
    22
sl@0
    23
# Set a uniform random seed
sl@0
    24
expr srand(0)
sl@0
    25
sl@0
    26
set sqlout [open speed1.txt w]
sl@0
    27
proc tracesql {sql} {
sl@0
    28
  puts $::sqlout $sql\;
sl@0
    29
}
sl@0
    30
#db trace tracesql
sl@0
    31
sl@0
    32
# The number_name procedure below converts its argment (an integer)
sl@0
    33
# into a string which is the English-language name for that number.
sl@0
    34
#
sl@0
    35
# Example:
sl@0
    36
#
sl@0
    37
#     puts [number_name 123]   ->  "one hundred twenty three"
sl@0
    38
#
sl@0
    39
set ones {zero one two three four five six seven eight nine
sl@0
    40
          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
sl@0
    41
          eighteen nineteen}
sl@0
    42
set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
sl@0
    43
proc number_name {n} {
sl@0
    44
  if {$n>=1000} {
sl@0
    45
    set txt "[number_name [expr {$n/1000}]] thousand"
sl@0
    46
    set n [expr {$n%1000}]
sl@0
    47
  } else {
sl@0
    48
    set txt {}
sl@0
    49
  }
sl@0
    50
  if {$n>=100} {
sl@0
    51
    append txt " [lindex $::ones [expr {$n/100}]] hundred"
sl@0
    52
    set n [expr {$n%100}]
sl@0
    53
  }
sl@0
    54
  if {$n>=20} {
sl@0
    55
    append txt " [lindex $::tens [expr {$n/10}]]"
sl@0
    56
    set n [expr {$n%10}]
sl@0
    57
  }
sl@0
    58
  if {$n>0} {
sl@0
    59
    append txt " [lindex $::ones $n]"
sl@0
    60
  }
sl@0
    61
  set txt [string trim $txt]
sl@0
    62
  if {$txt==""} {set txt zero}
sl@0
    63
  return $txt
sl@0
    64
}
sl@0
    65
sl@0
    66
# Create a database schema.
sl@0
    67
#
sl@0
    68
do_test speed1p-1.0 {
sl@0
    69
  execsql {
sl@0
    70
    PRAGMA page_size=1024;
sl@0
    71
    PRAGMA cache_size=8192;
sl@0
    72
    PRAGMA locking_mode=EXCLUSIVE;
sl@0
    73
    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
sl@0
    74
    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
sl@0
    75
    CREATE INDEX i2a ON t2(a);
sl@0
    76
    CREATE INDEX i2b ON t2(b);
sl@0
    77
  }
sl@0
    78
  execsql {
sl@0
    79
    SELECT name FROM sqlite_master ORDER BY 1;
sl@0
    80
  }
sl@0
    81
} {i2a i2b t1 t2}
sl@0
    82
sl@0
    83
sl@0
    84
# 50000 INSERTs on an unindexed table
sl@0
    85
#
sl@0
    86
set list {}
sl@0
    87
for {set i 1} {$i<=50000} {incr i} {
sl@0
    88
  set r [expr {int(rand()*500000)}]
sl@0
    89
  set x [number_name $r]
sl@0
    90
  lappend list $i $r $x
sl@0
    91
}
sl@0
    92
set script {
sl@0
    93
  foreach {i r x} $::list {
sl@0
    94
    db eval {INSERT INTO t1 VALUES($i,$r,$x)}
sl@0
    95
  }
sl@0
    96
}
sl@0
    97
explain {INSERT INTO t1 VALUES($i,$r,$x)}
sl@0
    98
db eval BEGIN
sl@0
    99
speed_trial_tcl speed1p-insert1 50000 row $script
sl@0
   100
db eval COMMIT
sl@0
   101
sl@0
   102
# 50000 INSERTs on an indexed table
sl@0
   103
#
sl@0
   104
set list {}
sl@0
   105
for {set i 1} {$i<=50000} {incr i} {
sl@0
   106
  set r [expr {int(rand()*500000)}]
sl@0
   107
  set x [number_name $r]
sl@0
   108
  lappend list $i $r $x
sl@0
   109
}
sl@0
   110
set script {
sl@0
   111
  foreach {i r x} $::list {
sl@0
   112
    db eval {INSERT INTO t2 VALUES($i,$r,$x)}
sl@0
   113
  }
sl@0
   114
}
sl@0
   115
explain {INSERT INTO t2 VALUES($i,$r,$x)}
sl@0
   116
db eval BEGIN
sl@0
   117
speed_trial_tcl speed1p-insert2 50000 row $script
sl@0
   118
db eval COMMIT
sl@0
   119
sl@0
   120
sl@0
   121
sl@0
   122
# 50 SELECTs on an integer comparison.  There is no index so
sl@0
   123
# a full table scan is required.
sl@0
   124
#
sl@0
   125
set list {}
sl@0
   126
for {set i 0} {$i<50} {incr i} {
sl@0
   127
  set lwr [expr {$i*100}]
sl@0
   128
  set upr [expr {($i+10)*100}]
sl@0
   129
  lappend list $lwr $upr
sl@0
   130
}
sl@0
   131
set script {
sl@0
   132
  foreach {lwr upr} $::list {
sl@0
   133
    db eval  {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
sl@0
   134
  }
sl@0
   135
}
sl@0
   136
explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
sl@0
   137
db eval BEGIN
sl@0
   138
speed_trial_tcl speed1p-select1 [expr {50*50000}] row $script
sl@0
   139
db eval COMMIT
sl@0
   140
sl@0
   141
# 50 SELECTs on an LIKE comparison.  There is no index so a full
sl@0
   142
# table scan is required.
sl@0
   143
#
sl@0
   144
set list {}
sl@0
   145
for {set i 0} {$i<50} {incr i} {
sl@0
   146
  lappend list "%[number_name $i]%"
sl@0
   147
}
sl@0
   148
set script {
sl@0
   149
  foreach pattern $::list {
sl@0
   150
    db eval {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern}
sl@0
   151
  }
sl@0
   152
}
sl@0
   153
explain {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern}
sl@0
   154
db eval BEGIN
sl@0
   155
speed_trial_tcl speed1p-select2 [expr {50*50000}] row $script
sl@0
   156
db eval COMMIT
sl@0
   157
sl@0
   158
# Create indices
sl@0
   159
#
sl@0
   160
explain {CREATE INDEX i1a ON t1(a)}
sl@0
   161
explain {CREATE INDEX i1b ON t1(b)}
sl@0
   162
db eval BEGIN
sl@0
   163
speed_trial speed1p-createidx 150000 row {
sl@0
   164
  CREATE INDEX i1a ON t1(a);
sl@0
   165
  CREATE INDEX i1b ON t1(b);
sl@0
   166
  CREATE INDEX i1c ON t1(c);
sl@0
   167
}
sl@0
   168
db eval COMMIT
sl@0
   169
sl@0
   170
# 5000 SELECTs on an integer comparison where the integer is
sl@0
   171
# indexed.
sl@0
   172
#
sl@0
   173
set list {}
sl@0
   174
for {set i 0} {$i<5000} {incr i} {
sl@0
   175
  set lwr [expr {$i*100}]
sl@0
   176
  set upr [expr {($i+10)*100}]
sl@0
   177
  lappend list $lwr $upr
sl@0
   178
}
sl@0
   179
set script {
sl@0
   180
  foreach {lwr upr} $::list {
sl@0
   181
    db eval {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
sl@0
   182
  }
sl@0
   183
}
sl@0
   184
explain {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}
sl@0
   185
db eval BEGIN
sl@0
   186
speed_trial_tcl speed1p-select3 5000 stmt $script
sl@0
   187
db eval COMMIT
sl@0
   188
sl@0
   189
# 100000 random SELECTs against rowid.
sl@0
   190
#
sl@0
   191
set list {}
sl@0
   192
for {set i 1} {$i<=100000} {incr i} {
sl@0
   193
  set id [expr {int(rand()*50000)+1}]
sl@0
   194
  lappend list $id
sl@0
   195
}
sl@0
   196
set script {
sl@0
   197
  foreach id $::list {
sl@0
   198
    db eval {SELECT c FROM t1 WHERE rowid=$id}
sl@0
   199
  }
sl@0
   200
}
sl@0
   201
explain {SELECT c FROM t1 WHERE rowid=$id}
sl@0
   202
db eval BEGIN
sl@0
   203
speed_trial_tcl speed1p-select4 100000 row $script
sl@0
   204
db eval COMMIT
sl@0
   205
sl@0
   206
# 100000 random SELECTs against a unique indexed column.
sl@0
   207
#
sl@0
   208
set list {}
sl@0
   209
for {set i 1} {$i<=100000} {incr i} {
sl@0
   210
  set id [expr {int(rand()*50000)+1}]
sl@0
   211
  lappend list $id
sl@0
   212
}
sl@0
   213
set script {
sl@0
   214
  foreach id $::list {
sl@0
   215
    db eval {SELECT c FROM t1 WHERE a=$id}
sl@0
   216
  }
sl@0
   217
}
sl@0
   218
explain {SELECT c FROM t1 WHERE a=$id}
sl@0
   219
db eval BEGIN
sl@0
   220
speed_trial_tcl speed1p-select5 100000 row $script
sl@0
   221
db eval COMMIT
sl@0
   222
sl@0
   223
# 50000 random SELECTs against an indexed column text column
sl@0
   224
#
sl@0
   225
set list [db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000}]
sl@0
   226
set script {
sl@0
   227
  foreach c $::list {
sl@0
   228
    db eval {SELECT c FROM t1 WHERE c=$c}
sl@0
   229
  }
sl@0
   230
}
sl@0
   231
explain {SELECT c FROM t1 WHERE c=$c}
sl@0
   232
db eval BEGIN
sl@0
   233
speed_trial_tcl speed1p-select6 50000 row $script
sl@0
   234
db eval COMMIT
sl@0
   235
sl@0
   236
sl@0
   237
# Vacuum
sl@0
   238
speed_trial speed1p-vacuum 100000 row VACUUM
sl@0
   239
sl@0
   240
# 5000 updates of ranges where the field being compared is indexed.
sl@0
   241
#
sl@0
   242
set list {}
sl@0
   243
for {set i 0} {$i<5000} {incr i} {
sl@0
   244
  set lwr [expr {$i*2}]
sl@0
   245
  set upr [expr {($i+1)*2}]
sl@0
   246
  lappend list $lwr $upr
sl@0
   247
}
sl@0
   248
set script {
sl@0
   249
  foreach {lwr upr} $::list {
sl@0
   250
    db eval {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr}
sl@0
   251
  }
sl@0
   252
}
sl@0
   253
explain {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr}
sl@0
   254
db eval BEGIN
sl@0
   255
speed_trial_tcl speed1p-update1 5000 stmt $script
sl@0
   256
db eval COMMIT
sl@0
   257
sl@0
   258
# 50000 single-row updates.  An index is used to find the row quickly.
sl@0
   259
#
sl@0
   260
set list {}
sl@0
   261
for {set i 0} {$i<50000} {incr i} {
sl@0
   262
  set r [expr {int(rand()*500000)}]
sl@0
   263
  lappend list $i $r
sl@0
   264
}
sl@0
   265
set script {
sl@0
   266
  foreach {i r} $::list {
sl@0
   267
    db eval {UPDATE t1 SET b=$r WHERE a=$i}
sl@0
   268
  }
sl@0
   269
}
sl@0
   270
explain {UPDATE t1 SET b=$r WHERE a=$i}
sl@0
   271
db eval BEGIN
sl@0
   272
speed_trial_tcl speed1p-update2 50000 row $script
sl@0
   273
db eval COMMIT
sl@0
   274
sl@0
   275
# 1 big text update that touches every row in the table.
sl@0
   276
#
sl@0
   277
explain {UPDATE t1 SET c=a}
sl@0
   278
speed_trial speed1p-update3 50000 row {
sl@0
   279
  UPDATE t1 SET c=a;
sl@0
   280
}
sl@0
   281
sl@0
   282
# Many individual text updates.  Each row in the table is
sl@0
   283
# touched through an index.
sl@0
   284
#
sl@0
   285
set list {}
sl@0
   286
for {set i 1} {$i<=50000} {incr i} {
sl@0
   287
  set r [expr {int(rand()*500000)}]
sl@0
   288
  lappend list $i [number_name $r]
sl@0
   289
}
sl@0
   290
set script {
sl@0
   291
  foreach {i x} $::list {
sl@0
   292
    db eval {UPDATE t1 SET c=$x WHERE a=$i}
sl@0
   293
  }
sl@0
   294
}
sl@0
   295
explain {UPDATE t1 SET c=$x WHERE a=$i}
sl@0
   296
db eval BEGIN
sl@0
   297
speed_trial_tcl speed1p-update4 50000 row $script
sl@0
   298
db eval COMMIT
sl@0
   299
sl@0
   300
# Delete all content in a table.
sl@0
   301
#
sl@0
   302
explain {DELETE FROM t1}
sl@0
   303
speed_trial speed1p-delete1 50000 row {DELETE FROM t1}
sl@0
   304
sl@0
   305
# Copy one table into another
sl@0
   306
#
sl@0
   307
explain {INSERT INTO t1 SELECT * FROM t2}
sl@0
   308
speed_trial speed1p-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}
sl@0
   309
sl@0
   310
# Delete all content in a table, one row at a time.
sl@0
   311
#
sl@0
   312
explain {DELETE FROM t1 WHERE 1}
sl@0
   313
speed_trial speed1p-delete2 50000 row {DELETE FROM t1 WHERE 1}
sl@0
   314
sl@0
   315
# Refill the table yet again
sl@0
   316
#
sl@0
   317
speed_trial speed1p-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}
sl@0
   318
sl@0
   319
# Drop the table and recreate it without its indices.
sl@0
   320
#
sl@0
   321
explain {DROP TABLE t1}
sl@0
   322
explain {CREATE TABLE tX(a INTEGER, b INTEGER, c TEXT)}
sl@0
   323
db eval BEGIN
sl@0
   324
speed_trial speed1p-drop1 50000 row {
sl@0
   325
   DROP TABLE t1;
sl@0
   326
   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
sl@0
   327
}
sl@0
   328
db eval COMMIT
sl@0
   329
sl@0
   330
# Refill the table yet again.  This copy should be faster because
sl@0
   331
# there are no indices to deal with.
sl@0
   332
#
sl@0
   333
speed_trial speed1p-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}
sl@0
   334
sl@0
   335
# Select 20000 rows from the table at random.
sl@0
   336
#
sl@0
   337
explain {SELECT rowid FROM t1 ORDER BY random() LIMIT 20000}
sl@0
   338
speed_trial speed1p-random1 50000 row {
sl@0
   339
  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000
sl@0
   340
}
sl@0
   341
sl@0
   342
# Delete 20000 random rows from the table.
sl@0
   343
#
sl@0
   344
explain {DELETE FROM t1 WHERE rowid IN
sl@0
   345
    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)}
sl@0
   346
speed_trial speed1p-random-del1 20000 row {
sl@0
   347
  DELETE FROM t1 WHERE rowid IN
sl@0
   348
    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
sl@0
   349
}
sl@0
   350
do_test speed1p-1.1 {
sl@0
   351
  db one {SELECT count(*) FROM t1}
sl@0
   352
} 30000
sl@0
   353
sl@0
   354
    
sl@0
   355
# Delete 20000 more rows at random from the table.
sl@0
   356
#
sl@0
   357
speed_trial speed1p-random-del2 20000 row {
sl@0
   358
  DELETE FROM t1 WHERE rowid IN
sl@0
   359
    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
sl@0
   360
}
sl@0
   361
do_test speed1p-1.2 {
sl@0
   362
  db one {SELECT count(*) FROM t1}
sl@0
   363
} 10000
sl@0
   364
speed_trial_summary speed1
sl@0
   365
sl@0
   366
finish_test