Return to speed4.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

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