| 1 |
|
| 2 |
|
| 3 |
#-------------------------------------------------------------------------- |
| 4 |
# This script contains several sub-programs used to test FTS3/FTS4 |
| 5 |
# performance. It does not run the queries directly, but generates SQL |
| 6 |
# scripts that can be run using the shell tool. |
| 7 |
# |
| 8 |
# The following cases are tested: |
| 9 |
# |
| 10 |
# 1. Inserting documents into an FTS3 table. |
| 11 |
# 2. Optimizing an FTS3 table (i.e. "INSERT INTO t1 VALUES('optimize')"). |
| 12 |
# 3. Deleting documents from an FTS3 table. |
| 13 |
# 4. Querying FTS3 tables. |
| 14 |
# |
| 15 |
|
| 16 |
# Number of tokens in vocabulary. And number of tokens in each document. |
| 17 |
# |
| 18 |
set VOCAB_SIZE 2000 |
| 19 |
set DOC_SIZE 100 |
| 20 |
|
| 21 |
set NUM_INSERTS 100000 |
| 22 |
set NUM_SELECTS 1000 |
| 23 |
|
| 24 |
# Force everything in this script to be deterministic. |
| 25 |
# |
| 26 |
expr {srand(0)} |
| 27 |
|
| 28 |
proc usage {} { |
| 29 |
puts stderr "Usage: $::argv0 <rows> <selects>" |
| 30 |
exit -1 |
| 31 |
} |
| 32 |
|
| 33 |
proc sql {sql} { |
| 34 |
puts $::fd $sql |
| 35 |
} |
| 36 |
|
| 37 |
|
| 38 |
# Return a list of $nWord randomly generated tokens each between 2 and 10 |
| 39 |
# characters in length. |
| 40 |
# |
| 41 |
proc build_vocab {nWord} { |
| 42 |
set ret [list] |
| 43 |
set chars [list a b c d e f g h i j k l m n o p q r s t u v w x y z] |
| 44 |
for {set i 0} {$i<$nWord} {incr i} { |
| 45 |
set len [expr {int((rand()*9.0)+2)}] |
| 46 |
set term "" |
| 47 |
for {set j 0} {$j<$len} {incr j} { |
| 48 |
append term [lindex $chars [expr {int(rand()*[llength $chars])}]] |
| 49 |
} |
| 50 |
lappend ret $term |
| 51 |
} |
| 52 |
set ret |
| 53 |
} |
| 54 |
|
| 55 |
proc select_term {} { |
| 56 |
set n [llength $::vocab] |
| 57 |
set t [expr int(rand()*$n*3)] |
| 58 |
if {$t>=2*$n} { set t [expr {($t-2*$n)/100}] } |
| 59 |
if {$t>=$n} { set t [expr {($t-$n)/10}] } |
| 60 |
lindex $::vocab $t |
| 61 |
} |
| 62 |
|
| 63 |
proc select_doc {nTerm} { |
| 64 |
set ret [list] |
| 65 |
for {set i 0} {$i<$nTerm} {incr i} { |
| 66 |
lappend ret [select_term] |
| 67 |
} |
| 68 |
set ret |
| 69 |
} |
| 70 |
|
| 71 |
proc test_1 {nInsert} { |
| 72 |
sql "PRAGMA synchronous = OFF;" |
| 73 |
sql "DROP TABLE IF EXISTS t1;" |
| 74 |
sql "CREATE VIRTUAL TABLE t1 USING fts4;" |
| 75 |
for {set i 0} {$i < $nInsert} {incr i} { |
| 76 |
set doc [select_doc $::DOC_SIZE] |
| 77 |
sql "INSERT INTO t1 VALUES('$doc');" |
| 78 |
} |
| 79 |
} |
| 80 |
|
| 81 |
proc test_2 {} { |
| 82 |
sql "INSERT INTO t1(t1) VALUES('optimize');" |
| 83 |
} |
| 84 |
|
| 85 |
proc test_3 {nSelect} { |
| 86 |
for {set i 0} {$i < $nSelect} {incr i} { |
| 87 |
sql "SELECT count(*) FROM t1 WHERE t1 MATCH '[select_term]';" |
| 88 |
} |
| 89 |
} |
| 90 |
|
| 91 |
proc test_4 {nSelect} { |
| 92 |
for {set i 0} {$i < $nSelect} {incr i} { |
| 93 |
sql "SELECT count(*) FROM t1 WHERE t1 MATCH '[select_term] [select_term]';" |
| 94 |
} |
| 95 |
} |
| 96 |
|
| 97 |
if {[llength $argv]!=0} usage |
| 98 |
|
| 99 |
set ::vocab [build_vocab $::VOCAB_SIZE] |
| 100 |
|
| 101 |
set ::fd [open fts3speed_insert.sql w] |
| 102 |
test_1 $NUM_INSERTS |
| 103 |
close $::fd |
| 104 |
|
| 105 |
set ::fd [open fts3speed_select.sql w] |
| 106 |
test_3 $NUM_SELECTS |
| 107 |
close $::fd |
| 108 |
|
| 109 |
set ::fd [open fts3speed_select2.sql w] |
| 110 |
test_4 $NUM_SELECTS |
| 111 |
close $::fd |
| 112 |
|
| 113 |
set ::fd [open fts3speed_optimize.sql w] |
| 114 |
test_2 |
| 115 |
close $::fd |
| 116 |
|
| 117 |
puts "Success. Created files:" |
| 118 |
puts " fts3speed_insert.sql" |
| 119 |
puts " fts3speed_select.sql" |
| 120 |
puts " fts3speed_select2.sql" |
| 121 |
puts " fts3speed_optimize.sql" |
| 122 |
|