/ Check-in [9a23f020]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test cases that compare the performance of the transitive_closure virtual table again common table expressions for walking a tree.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:9a23f020e8ed0e7a1ad227b4ab379fdf5e2de222
User & Date: drh 2014-01-24 14:37:44
Context
2014-01-24
15:42
Add a few more CTE test cases to closure.test. check-in: 1b6405d9 user: drh tags: trunk
14:37
Add test cases that compare the performance of the transitive_closure virtual table again common table expressions for walking a tree. check-in: 9a23f020 user: drh tags: trunk
11:16
Add test cases showing the use of ORDER BY on a recursive query to control depth-first versus breath-first search of a tree. check-in: 83b0b291 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/closure01.test.

    11     11   # 
    12     12   # Test cases for transitive_closure virtual table.
    13     13   
    14     14   set testdir [file dirname $argv0]
    15     15   source $testdir/tester.tcl
    16     16   set testprefix closure01
    17     17   
    18         -ifcapable !vtab { finish_test ; return }
           18  +ifcapable !vtab||!cte { finish_test ; return }
    19     19   
    20     20   load_static_extension db closure
    21     21   
    22     22   do_execsql_test 1.0 {
    23     23     BEGIN;
    24     24     CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);
           25  +  WITH RECURSIVE
           26  +    cnt(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM cnt LIMIT 131072)
           27  +  INSERT INTO t1(x, y) SELECT i, nullif(i,1)/2 FROM cnt;
    25     28     CREATE INDEX t1y ON t1(y);
    26         -  INSERT INTO t1(x) VALUES(1),(2);
    27         -  INSERT INTO t1(x) SELECT x+2 FROM t1;
    28         -  INSERT INTO t1(x) SELECT x+4 FROM t1;
    29         -  INSERT INTO t1(x) SELECT x+8 FROM t1;
    30         -  INSERT INTO t1(x) SELECT x+16 FROM t1;
    31         -  INSERT INTO t1(x) SELECT x+32 FROM t1;
    32         -  INSERT INTO t1(x) SELECT x+64 FROM t1;
    33         -  INSERT INTO t1(x) SELECT x+128 FROM t1;
    34         -  INSERT INTO t1(x) SELECT x+256 FROM t1;
    35         -  INSERT INTO t1(x) SELECT x+512 FROM t1;
    36         -  INSERT INTO t1(x) SELECT x+1024 FROM t1;
    37         -  INSERT INTO t1(x) SELECT x+2048 FROM t1;
    38         -  INSERT INTO t1(x) SELECT x+4096 FROM t1;
    39         -  INSERT INTO t1(x) SELECT x+8192 FROM t1;
    40         -  INSERT INTO t1(x) SELECT x+16384 FROM t1;
    41         -  INSERT INTO t1(x) SELECT x+32768 FROM t1;
    42         -  INSERT INTO t1(x) SELECT x+65536 FROM t1;
    43         -  UPDATE t1 SET y=x/2 WHERE x>1;
    44     29     COMMIT;
    45     30     CREATE VIRTUAL TABLE cx 
    46     31      USING transitive_closure(tablename=t1, idcolumn=x, parentcolumn=y);
    47     32   } {}
    48     33   
    49     34   # The entire table
    50         -do_execsql_test 1.1 {
           35  +do_timed_execsql_test 1.1 {
    51     36     SELECT count(*), depth FROM cx WHERE root=1 GROUP BY depth ORDER BY 1;
    52     37   } {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}
           38  +do_timed_execsql_test 1.1-cte {
           39  +  WITH RECURSIVE
           40  +    below(id,depth) AS (
           41  +      VALUES(1,0)
           42  +       UNION ALL
           43  +      SELECT t1.x, below.depth+1
           44  +        FROM t1 JOIN below on t1.y=below.id
           45  +    )
           46  +  SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
           47  +} {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}
    53     48   
    54     49   # descendents of 32768
    55     50   do_execsql_test 1.2 {
    56     51     SELECT * FROM cx WHERE root=32768 ORDER BY id;
    57     52   } {32768 0 65536 1 65537 1 131072 2}
    58     53   
    59     54   # descendents of 16384
    60         -do_execsql_test 1.3 {
           55  +do_timed_execsql_test 1.3 {
    61     56     SELECT * FROM cx WHERE root=16384 AND depth<=2 ORDER BY id;
    62     57   } {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}
           58  +do_timed_execsql_test 1.3-cte {
           59  +  WITH RECURSIVE
           60  +    below(id,depth) AS (
           61  +      VALUES(16384,0)
           62  +       UNION ALL
           63  +      SELECT t1.x, below.depth+1
           64  +        FROM t1 JOIN below on t1.y=below.id
           65  +       WHERE below.depth<2
           66  +    )
           67  +  SELECT id, depth FROM below ORDER BY id;
           68  +} {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}
    63     69   
    64     70   # children of 16384
    65     71   do_execsql_test 1.4 {
    66     72     SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
    67     73      WHERE root=16384
    68     74        AND depth=1
    69     75      ORDER BY id;

Changes to test/tester.tcl.

    55     55   #      do_ioerr_test          TESTNAME ARGS...
    56     56   #      crashsql               ARGS...
    57     57   #      integrity_check        TESTNAME ?DB?
    58     58   #      verify_ex_errcode      TESTNAME EXPECTED ?DB?
    59     59   #      do_test                TESTNAME SCRIPT EXPECTED
    60     60   #      do_execsql_test        TESTNAME SQL EXPECTED
    61     61   #      do_catchsql_test       TESTNAME SQL EXPECTED
           62  +#      do_timed_execsql_test  TESTNAME SQL EXPECTED
    62     63   #
    63     64   # Commands providing a lower level interface to the global test counters:
    64     65   #
    65     66   #      set_test_counter       COUNTER ?VALUE?
    66     67   #      omit_test              TESTNAME REASON ?APPEND?
    67     68   #      fail_test              TESTNAME
    68     69   #      incr_ntest
................................................................................
   713    714     fix_testname testname
   714    715     uplevel do_test [list $testname] [list "execsql {$sql}"] [list [list {*}$result]]
   715    716   }
   716    717   proc do_catchsql_test {testname sql result} {
   717    718     fix_testname testname
   718    719     uplevel do_test [list $testname] [list "catchsql {$sql}"] [list $result]
   719    720   }
          721  +proc do_timed_execsql_test {testname sql {result {}}} {
          722  +  fix_testname testname
          723  +  uplevel do_test [list $testname] [list "execsql_timed {$sql}"]\
          724  +                                   [list [list {*}$result]]
          725  +}
   720    726   proc do_eqp_test {name sql res} {
   721    727     uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
   722    728   }
   723    729   
   724    730   #-------------------------------------------------------------------------
   725    731   #   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
   726    732   #
................................................................................
  1008   1014   }
  1009   1015   
  1010   1016   # A procedure to execute SQL
  1011   1017   #
  1012   1018   proc execsql {sql {db db}} {
  1013   1019     # puts "SQL = $sql"
  1014   1020     uplevel [list $db eval $sql]
         1021  +}
         1022  +proc execsql_timed {sql {db db}} {
         1023  +  set tm [time {
         1024  +    set x [uplevel [list $db eval $sql]]
         1025  +  } 1]
         1026  +  set tm [lindex $tm 0]
         1027  +  puts -nonewline " ([expr {$tm*0.001}]ms) "
         1028  +  set x
  1015   1029   }
  1016   1030   
  1017   1031   # Execute SQL and catch exceptions.
  1018   1032   #
  1019   1033   proc catchsql {sql {db db}} {
  1020   1034     # puts "SQL = $sql"
  1021   1035     set r [catch [list uplevel [list $db eval $sql]] msg]