1 2 set testdir [file join [file dirname $argv0] .. .. test] 3 source $testdir/tester.tcl 4 5 ifcapable !rtree { 6 finish_test 7 return 8 } 9 10 set NROW 10000 11 set NQUERY 500 12 13 puts "Generating $NROW rows of data..." 14 set data [list] 15 for {set ii 0} {$ii < $NROW} {incr ii} { 16 set x1 [expr {rand()*1000}] 17 set x2 [expr {$x1+rand()*50}] 18 set y1 [expr {rand()*1000}] 19 set y2 [expr {$y1+rand()*50}] 20 lappend data $x1 $x2 $y1 $y2 21 } 22 puts "Finished generating data" 23 24 25 set sql1 {CREATE TABLE btree(ii INTEGER PRIMARY KEY, x1, x2, y1, y2)} 26 set sql2 {CREATE VIRTUAL TABLE rtree USING rtree(ii, x1, x2, y1, y2)} 27 puts "Creating tables:" 28 puts " $sql1" 29 puts " $sql2" 30 db eval $sql1 31 db eval $sql2 32 33 db eval "pragma cache_size=100" 34 35 puts -nonewline "Inserting into btree... " 36 flush stdout 37 set btree_time [time {db transaction { 38 set ii 1 39 foreach {x1 x2 y1 y2} $data { 40 db eval {INSERT INTO btree VALUES($ii, $x1, $x2, $y1, $y2)} 41 incr ii 42 } 43 }}] 44 puts "$btree_time" 45 46 puts -nonewline "Inserting into rtree... " 47 flush stdout 48 set rtree_time [time {db transaction { 49 set ii 1 50 foreach {x1 x2 y1 y2} $data { 51 incr ii 52 db eval {INSERT INTO rtree VALUES($ii, $x1, $x2, $y1, $y2)} 53 } 54 }}] 55 puts "$rtree_time" 56 57 58 puts -nonewline "Selecting from btree... " 59 flush stdout 60 set btree_select_time [time { 61 foreach {x1 x2 y1 y2} [lrange $data 0 [expr $NQUERY*4-1]] { 62 db eval {SELECT * FROM btree WHERE x1<$x1 AND x2>$x2 AND y1<$y1 AND y2>$y2} 63 } 64 }] 65 puts "$btree_select_time" 66 67 puts -nonewline "Selecting from rtree... " 68 flush stdout 69 set rtree_select_time [time { 70 foreach {x1 x2 y1 y2} [lrange $data 0 [expr $NQUERY*4-1]] { 71 db eval {SELECT * FROM rtree WHERE x1<$x1 AND x2>$x2 AND y1<$y1 AND y2>$y2} 72 } 73 }] 74 puts "$rtree_select_time" 75