1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/speed3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,186 @@
1.4 +# 2007 May 17
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#*************************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is testing that the overflow-page related
1.16 +# enhancements added after version 3.3.17 speed things up.
1.17 +#
1.18 +# $Id: speed3.test,v 1.5 2007/10/09 08:29:33 danielk1977 Exp $
1.19 +#
1.20 +
1.21 +#---------------------------------------------------------------------
1.22 +# Test plan:
1.23 +#
1.24 +# If auto-vacuum is enabled for the database, the following cases
1.25 +# should show performance improvement with respect to 3.3.17.
1.26 +#
1.27 +# + When deleting rows that span overflow pages. This is faster
1.28 +# because the overflow pages no longer need to be read before
1.29 +# they can be moved to the free list (test cases speed3-1.X).
1.30 +#
1.31 +# + When reading a column value stored on an overflow page that
1.32 +# is not the first overflow page for the row. The improvement
1.33 +# in this case is because the overflow pages between the tree
1.34 +# page and the overflow page containing the value do not have
1.35 +# to be read (test cases speed3-2.X).
1.36 +#
1.37 +
1.38 +set testdir [file dirname $argv0]
1.39 +source $testdir/tester.tcl
1.40 +
1.41 +ifcapable !tclvar||!attach {
1.42 + finish_test
1.43 + return
1.44 +}
1.45 +
1.46 +speed_trial_init speed1
1.47 +
1.48 +# Set a uniform random seed
1.49 +expr srand(0)
1.50 +
1.51 +set ::NROW 1000
1.52 +
1.53 +# The number_name procedure below converts its argment (an integer)
1.54 +# into a string which is the English-language name for that number.
1.55 +#
1.56 +# Example:
1.57 +#
1.58 +# puts [number_name 123] -> "one hundred twenty three"
1.59 +#
1.60 +set ones {zero one two three four five six seven eight nine
1.61 + ten eleven twelve thirteen fourteen fifteen sixteen seventeen
1.62 + eighteen nineteen}
1.63 +set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
1.64 +proc number_name {n} {
1.65 + if {$n>=1000} {
1.66 + set txt "[number_name [expr {$n/1000}]] thousand"
1.67 + set n [expr {$n%1000}]
1.68 + } else {
1.69 + set txt {}
1.70 + }
1.71 + if {$n>=100} {
1.72 + append txt " [lindex $::ones [expr {$n/100}]] hundred"
1.73 + set n [expr {$n%100}]
1.74 + }
1.75 + if {$n>=20} {
1.76 + append txt " [lindex $::tens [expr {$n/10}]]"
1.77 + set n [expr {$n%10}]
1.78 + }
1.79 + if {$n>0} {
1.80 + append txt " [lindex $::ones $n]"
1.81 + }
1.82 + set txt [string trim $txt]
1.83 + if {$txt==""} {set txt zero}
1.84 + return $txt
1.85 +}
1.86 +
1.87 +proc populate_t1 {db} {
1.88 + $db transaction {
1.89 + for {set ii 0} {$ii < $::NROW} {incr ii} {
1.90 + set N [number_name $ii]
1.91 + set repeats [expr {(10000/[string length $N])+1}]
1.92 + set text [string range [string repeat $N $repeats] 0 10000]
1.93 + $db eval {INSERT INTO main.t1 VALUES($ii, $text, $ii)}
1.94 + }
1.95 + $db eval {INSERT INTO aux.t1 SELECT * FROM main.t1}
1.96 + }
1.97 +}
1.98 +
1.99 +
1.100 +proc io_log {db} {
1.101 + db_enter db
1.102 + array set stats1 [btree_pager_stats [btree_from_db db]]
1.103 + array set stats2 [btree_pager_stats [btree_from_db db 2]]
1.104 + db_leave db
1.105 +# puts "1: [array get stats1]"
1.106 +# puts "2: [array get stats2]"
1.107 + puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"
1.108 + puts "Normal : Read $stats2(read), wrote $stats2(write)"
1.109 +}
1.110 +
1.111 +proc overflow_report {db} {
1.112 + set bt [btree_from_db db]
1.113 + set csr [btree_cursor $bt 3 0]
1.114 +
1.115 + for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
1.116 + puts "[btree_ovfl_info $bt $csr]"
1.117 + }
1.118 +
1.119 + btree_close_cursor $csr
1.120 +
1.121 +}
1.122 +
1.123 +proc reset_db {} {
1.124 + db close
1.125 + sqlite3 db test.db
1.126 + db eval {
1.127 + PRAGMA main.cache_size = 200000;
1.128 + PRAGMA main.auto_vacuum = 'incremental';
1.129 + ATTACH 'test2.db' AS 'aux';
1.130 + PRAGMA aux.auto_vacuum = 'none';
1.131 + }
1.132 +}
1.133 +
1.134 +file delete -force test2.db test2.db-journal
1.135 +reset_db
1.136 +
1.137 +# Set up a database in auto-vacuum mode and create a database schema.
1.138 +#
1.139 +do_test speed3-0.1 {
1.140 + execsql {
1.141 + CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
1.142 + }
1.143 + execsql {
1.144 + SELECT name FROM sqlite_master ORDER BY 1;
1.145 + }
1.146 +} {t1}
1.147 +do_test speed3-0.2 {
1.148 + execsql {
1.149 + CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
1.150 + }
1.151 + execsql {
1.152 + SELECT name FROM aux.sqlite_master ORDER BY 1;
1.153 + }
1.154 +} {t1}
1.155 +do_test speed3-0.3 {
1.156 + populate_t1 db
1.157 + execsql {
1.158 + SELECT count(*) FROM main.t1;
1.159 + SELECT count(*) FROM aux.t1;
1.160 + }
1.161 +} "$::NROW $::NROW"
1.162 +do_test speed3-0.4 {
1.163 + execsql {
1.164 + PRAGMA main.auto_vacuum;
1.165 + PRAGMA aux.auto_vacuum;
1.166 + }
1.167 +} {2 0}
1.168 +
1.169 +# Delete all content in a table, one row at a time.
1.170 +#
1.171 +#io_log db
1.172 +#overflow_report db
1.173 +reset_db
1.174 +speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
1.175 +speed_trial speed3-1.normal $::NROW row {DELETE FROM aux.t1 WHERE 1}
1.176 +io_log db
1.177 +
1.178 +# Select the "C" column (located at the far end of the overflow
1.179 +# chain) from each table row.
1.180 +#
1.181 +#db eval {PRAGMA incremental_vacuum(500000)}
1.182 +populate_t1 db
1.183 +#overflow_report db
1.184 +reset_db
1.185 +speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
1.186 +speed_trial speed3-2.normal $::NROW row {SELECT c FROM aux.t1}
1.187 +io_log db
1.188 +
1.189 +finish_test