ext/rtree/rtree_perf.tcl

   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"