os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/speed4p.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
# 2007 October 23
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. More specifically,
sl@0
    13
# the focus is on the speed of:
sl@0
    14
#
sl@0
    15
#   * joins
sl@0
    16
#   * views
sl@0
    17
#   * sub-selects
sl@0
    18
#   * triggers
sl@0
    19
#
sl@0
    20
# $Id: speed4p.explain,v 1.1 2008/04/16 12:57:48 drh Exp $
sl@0
    21
#
sl@0
    22
sl@0
    23
set testdir [file dirname $argv0]
sl@0
    24
source $testdir/tester.tcl
sl@0
    25
speed_trial_init speed1
sl@0
    26
sl@0
    27
# Set a uniform random seed
sl@0
    28
expr srand(0)
sl@0
    29
sl@0
    30
set sqlout [open speed1.txt w]
sl@0
    31
proc tracesql {sql} {
sl@0
    32
  puts $::sqlout $sql\;
sl@0
    33
}
sl@0
    34
#db trace tracesql
sl@0
    35
sl@0
    36
# The number_name procedure below converts its argment (an integer)
sl@0
    37
# into a string which is the English-language name for that number.
sl@0
    38
#
sl@0
    39
# Example:
sl@0
    40
#
sl@0
    41
#     puts [number_name 123]   ->  "one hundred twenty three"
sl@0
    42
#
sl@0
    43
set ones {zero one two three four five six seven eight nine
sl@0
    44
          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
sl@0
    45
          eighteen nineteen}
sl@0
    46
set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
sl@0
    47
proc number_name {n} {
sl@0
    48
  if {$n>=1000} {
sl@0
    49
    set txt "[number_name [expr {$n/1000}]] thousand"
sl@0
    50
    set n [expr {$n%1000}]
sl@0
    51
  } else {
sl@0
    52
    set txt {}
sl@0
    53
  }
sl@0
    54
  if {$n>=100} {
sl@0
    55
    append txt " [lindex $::ones [expr {$n/100}]] hundred"
sl@0
    56
    set n [expr {$n%100}]
sl@0
    57
  }
sl@0
    58
  if {$n>=20} {
sl@0
    59
    append txt " [lindex $::tens [expr {$n/10}]]"
sl@0
    60
    set n [expr {$n%10}]
sl@0
    61
  }
sl@0
    62
  if {$n>0} {
sl@0
    63
    append txt " [lindex $::ones $n]"
sl@0
    64
  }
sl@0
    65
  set txt [string trim $txt]
sl@0
    66
  if {$txt==""} {set txt zero}
sl@0
    67
  return $txt
sl@0
    68
}
sl@0
    69
sl@0
    70
# Summary of tests:
sl@0
    71
#
sl@0
    72
#   speed4p-join1: Join three tables using IPK index.
sl@0
    73
#   speed4p-join2: Join three tables using an index.
sl@0
    74
#   speed4p-join3: Join two tables without an index.
sl@0
    75
#
sl@0
    76
#   speed4p-view1:  Querying a view.
sl@0
    77
#   speed4p-table1: Same queries as in speed4p-view1, but run directly against
sl@0
    78
#                  the tables for comparison purposes.
sl@0
    79
#
sl@0
    80
#   speed4p-subselect1: A SELECT statement that uses many sub-queries..
sl@0
    81
#
sl@0
    82
#   speed4p-trigger1: An INSERT statement that fires a trigger.
sl@0
    83
#   speed4p-trigger2: An UPDATE statement that fires a trigger.
sl@0
    84
#   speed4p-trigger3: A DELETE statement that fires a trigger.
sl@0
    85
#   speed4p-notrigger1: Same operation as trigger1, but without the trigger.
sl@0
    86
#   speed4p-notrigger2:        "          trigger2           "
sl@0
    87
#   speed4p-notrigger3:        "          trigger3           "
sl@0
    88
#
sl@0
    89
sl@0
    90
# Set up the schema. Each of the tables t1, t2 and t3 contain 50,000 rows.
sl@0
    91
# This creates a database of around 16MB.
sl@0
    92
execsql {
sl@0
    93
  PRAGMA page_size=1024;
sl@0
    94
  PRAGMA cache_size=8192;
sl@0
    95
  PRAGMA locking_mode=EXCLUSIVE;
sl@0
    96
  BEGIN;
sl@0
    97
  CREATE TABLE t1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
sl@0
    98
  CREATE TABLE t2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
sl@0
    99
  CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
sl@0
   100
sl@0
   101
  CREATE VIEW v1 AS SELECT rowid, i, t FROM t1;
sl@0
   102
  CREATE VIEW v2 AS SELECT rowid, i, t FROM t2;
sl@0
   103
  CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;
sl@0
   104
}
sl@0
   105
for {set jj 1} {$jj <= 3} {incr jj} {
sl@0
   106
  set stmt [string map "%T% t$jj" {INSERT INTO %T% VALUES(NULL, $i, $t)}]
sl@0
   107
  for {set ii 0} {$ii < 50000} {incr ii} {
sl@0
   108
    set i [expr {int(rand()*50000)}]
sl@0
   109
    set t [number_name $i]
sl@0
   110
    execsql $stmt
sl@0
   111
  }
sl@0
   112
}
sl@0
   113
execsql {
sl@0
   114
  CREATE INDEX i1 ON t1(t);
sl@0
   115
  CREATE INDEX i2 ON t2(t);
sl@0
   116
  CREATE INDEX i3 ON t3(t);
sl@0
   117
  COMMIT;
sl@0
   118
}
sl@0
   119
sl@0
   120
# Before running these tests, disable the compiled statement cache built into
sl@0
   121
# the Tcl interface. This is because we want to test the speed of SQL
sl@0
   122
# compilation as well as execution.
sl@0
   123
#
sl@0
   124
db cache size 0
sl@0
   125
sl@0
   126
# Join t1, t2, t3 on IPK.
sl@0
   127
set sql "SELECT * FROM t1, t2, t3 WHERE t1.oid = t2.oid AND t2.oid = t3.oid"
sl@0
   128
explain $sql
sl@0
   129
speed_trial speed4p-join1 50000 row $sql
sl@0
   130
sl@0
   131
# Join t1, t2, t3 on the non-IPK index.
sl@0
   132
set sql "SELECT * FROM t1, t2, t3 WHERE t1.t = t2.t AND t2.t = t3.t"
sl@0
   133
explain $sql
sl@0
   134
speed_trial speed4p-join2 50000 row $sql
sl@0
   135
sl@0
   136
# Run 10000 simple queries against the views.
sl@0
   137
set script {
sl@0
   138
  for {set ii 1} {$ii < 10000} {incr ii} {
sl@0
   139
    set v [expr {$ii*3}]
sl@0
   140
    set t [expr {$ii%3+1}]
sl@0
   141
    db eval "SELECT * FROM v$t WHERE rowid = \$v"
sl@0
   142
  }
sl@0
   143
}
sl@0
   144
explain {SELECT * FROm v1 WHERE rowid=$v}
sl@0
   145
speed_trial_tcl speed4p-view1 10000 stmt $script
sl@0
   146
sl@0
   147
# Run the same 10000 simple queries as in the previous test case against
sl@0
   148
# the underlying tables. The compiled vdbe programs should be identical, so
sl@0
   149
# the only difference in running time is the extra time taken to compile
sl@0
   150
# the view definitions.
sl@0
   151
#
sl@0
   152
set script {
sl@0
   153
  for {set ii 1} {$ii < 10000} {incr ii} {
sl@0
   154
    set v [expr {$ii*3}]
sl@0
   155
    set t [expr {$ii%3+1}]
sl@0
   156
    db eval "SELECT t FROM t$t WHERE rowid = \$v"
sl@0
   157
  }
sl@0
   158
}
sl@0
   159
explain {SELECT * FROM t1 WHERE rowid=$v}
sl@0
   160
speed_trial_tcl speed4p-table1 10000 stmt $script
sl@0
   161
sl@0
   162
# Run a SELECT that uses sub-queries 10000 times. A total of 30000 sub-selects.
sl@0
   163
#
sl@0
   164
set script {
sl@0
   165
  for {set ii 1} {$ii < 10000} {incr ii} {
sl@0
   166
    set v [expr {$ii*3}]
sl@0
   167
    db eval {
sl@0
   168
      SELECT (SELECT t FROM t1 WHERE rowid = $v), 
sl@0
   169
             (SELECT t FROM t2 WHERE rowid = $v), 
sl@0
   170
             (SELECT t FROM t3 WHERE rowid = $v)
sl@0
   171
    }
sl@0
   172
  }
sl@0
   173
}
sl@0
   174
explain {
sl@0
   175
      SELECT (SELECT t FROM t1 WHERE rowid = $v), 
sl@0
   176
             (SELECT t FROM t2 WHERE rowid = $v), 
sl@0
   177
             (SELECT t FROM t3 WHERE rowid = $v)
sl@0
   178
}
sl@0
   179
speed_trial_tcl speed4p-subselect1 10000 stmt $script
sl@0
   180
sl@0
   181
# The following block tests the speed of some DML statements that cause
sl@0
   182
# triggers to fire.
sl@0
   183
#
sl@0
   184
execsql {
sl@0
   185
  CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);
sl@0
   186
  CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
sl@0
   187
  CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN
sl@0
   188
    INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);
sl@0
   189
  END;
sl@0
   190
  CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN
sl@0
   191
    INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);
sl@0
   192
  END;
sl@0
   193
  CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN
sl@0
   194
    INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);
sl@0
   195
  END;
sl@0
   196
  BEGIN;
sl@0
   197
}
sl@0
   198
set list {}
sl@0
   199
for {set ii 1} {$ii < 10000} {incr ii} {
sl@0
   200
  lappend list $ii [number_name $ii]
sl@0
   201
}
sl@0
   202
set script {
sl@0
   203
  foreach {ii name} $::list {
sl@0
   204
    db eval {INSERT INTO t4 VALUES(NULL, $ii, $name)}
sl@0
   205
  }
sl@0
   206
}
sl@0
   207
explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
sl@0
   208
speed_trial_tcl speed4p-trigger1 10000 stmt $script
sl@0
   209
sl@0
   210
set list {}
sl@0
   211
for {set ii 1} {$ii < 20000} {incr ii 2} {
sl@0
   212
  set ii2 [expr {$ii*2}]
sl@0
   213
  lappend list $ii $ii2 [number_name $ii2]
sl@0
   214
}
sl@0
   215
set script {
sl@0
   216
  foreach {ii ii2 name} $::list {
sl@0
   217
    db eval {
sl@0
   218
      UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
sl@0
   219
    }
sl@0
   220
  }
sl@0
   221
}
sl@0
   222
explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
sl@0
   223
speed_trial_tcl speed4p-trigger2 10000 stmt $script
sl@0
   224
sl@0
   225
set script {
sl@0
   226
  for {set ii 1} {$ii < 20000} {incr ii 2} {
sl@0
   227
    db eval {DELETE FROM t4 WHERE rowid = $ii}
sl@0
   228
  }
sl@0
   229
}
sl@0
   230
explain {DELETE FROM t4 WHERE rowid = $ii}
sl@0
   231
speed_trial_tcl speed4p-trigger3 10000 stmt $script
sl@0
   232
execsql {COMMIT}
sl@0
   233
sl@0
   234
# The following block contains the same tests as the above block that
sl@0
   235
# tests triggers, with one crucial difference: no triggers are defined.
sl@0
   236
# So the difference in speed between these tests and the preceding ones
sl@0
   237
# is the amount of time taken to compile and execute the trigger programs.
sl@0
   238
#
sl@0
   239
execsql {
sl@0
   240
  DROP TABLE t4;
sl@0
   241
  DROP TABLE log;
sl@0
   242
  VACUUM;
sl@0
   243
  CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);
sl@0
   244
  BEGIN;
sl@0
   245
}
sl@0
   246
set list {}
sl@0
   247
for {set ii 1} {$ii < 10000} {incr ii} {
sl@0
   248
  lappend list $ii [number_name $ii]
sl@0
   249
}
sl@0
   250
set script {
sl@0
   251
  foreach {ii name} $::list {
sl@0
   252
    db eval {INSERT INTO t4 VALUES(NULL, $ii, $name);}
sl@0
   253
  }
sl@0
   254
}
sl@0
   255
explain {INSERT INTO t4 VALUES(NULL, $ii, $name)}
sl@0
   256
speed_trial_tcl speed4p-notrigger1 10000 stmt $script
sl@0
   257
sl@0
   258
set list {}
sl@0
   259
for {set ii 1} {$ii < 20000} {incr ii 2} {
sl@0
   260
  set ii2 [expr {$ii*2}]
sl@0
   261
  lappend list $ii $ii2 [number_name $ii2]
sl@0
   262
}
sl@0
   263
set script {
sl@0
   264
  foreach {ii ii2 name} $::list {
sl@0
   265
    db eval {
sl@0
   266
      UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii;
sl@0
   267
    }
sl@0
   268
  }
sl@0
   269
}
sl@0
   270
explain {UPDATE t4 SET i = $ii2, t = $name WHERE rowid = $ii}
sl@0
   271
speed_trial_tcl speed4p-notrigger2 10000 stmt $script
sl@0
   272
sl@0
   273
set script {
sl@0
   274
  for {set ii 1} {$ii < 20000} {incr ii 2} {
sl@0
   275
    db eval {DELETE FROM t4 WHERE rowid = $ii}
sl@0
   276
  }
sl@0
   277
}
sl@0
   278
explain {DELETE FROM t4 WHERE rowid = $ii}
sl@0
   279
speed_trial_tcl speed4p-notrigger3 10000 stmt $script
sl@0
   280
execsql {COMMIT}
sl@0
   281
sl@0
   282
speed_trial_summary speed4
sl@0
   283
finish_test