--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/persistentstorage/sqlite3api/TEST/TclScript/speed3.test Fri Jan 22 11:06:30 2010 +0200
@@ -0,0 +1,186 @@
+# 2007 May 17
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#*************************************************************************
+# This file implements regression tests for SQLite library. The
+# focus of this script is testing that the overflow-page related
+# enhancements added after version 3.3.17 speed things up.
+#
+# $Id: speed3.test,v 1.5 2007/10/09 08:29:33 danielk1977 Exp $
+#
+
+#---------------------------------------------------------------------
+# Test plan:
+#
+# If auto-vacuum is enabled for the database, the following cases
+# should show performance improvement with respect to 3.3.17.
+#
+# + When deleting rows that span overflow pages. This is faster
+# because the overflow pages no longer need to be read before
+# they can be moved to the free list (test cases speed3-1.X).
+#
+# + When reading a column value stored on an overflow page that
+# is not the first overflow page for the row. The improvement
+# in this case is because the overflow pages between the tree
+# page and the overflow page containing the value do not have
+# to be read (test cases speed3-2.X).
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !tclvar||!attach {
+ finish_test
+ return
+}
+
+speed_trial_init speed1
+
+# Set a uniform random seed
+expr srand(0)
+
+set ::NROW 1000
+
+# The number_name procedure below converts its argment (an integer)
+# into a string which is the English-language name for that number.
+#
+# Example:
+#
+# puts [number_name 123] -> "one hundred twenty three"
+#
+set ones {zero one two three four five six seven eight nine
+ ten eleven twelve thirteen fourteen fifteen sixteen seventeen
+ eighteen nineteen}
+set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
+proc number_name {n} {
+ if {$n>=1000} {
+ set txt "[number_name [expr {$n/1000}]] thousand"
+ set n [expr {$n%1000}]
+ } else {
+ set txt {}
+ }
+ if {$n>=100} {
+ append txt " [lindex $::ones [expr {$n/100}]] hundred"
+ set n [expr {$n%100}]
+ }
+ if {$n>=20} {
+ append txt " [lindex $::tens [expr {$n/10}]]"
+ set n [expr {$n%10}]
+ }
+ if {$n>0} {
+ append txt " [lindex $::ones $n]"
+ }
+ set txt [string trim $txt]
+ if {$txt==""} {set txt zero}
+ return $txt
+}
+
+proc populate_t1 {db} {
+ $db transaction {
+ for {set ii 0} {$ii < $::NROW} {incr ii} {
+ set N [number_name $ii]
+ set repeats [expr {(10000/[string length $N])+1}]
+ set text [string range [string repeat $N $repeats] 0 10000]
+ $db eval {INSERT INTO main.t1 VALUES($ii, $text, $ii)}
+ }
+ $db eval {INSERT INTO aux.t1 SELECT * FROM main.t1}
+ }
+}
+
+
+proc io_log {db} {
+ db_enter db
+ array set stats1 [btree_pager_stats [btree_from_db db]]
+ array set stats2 [btree_pager_stats [btree_from_db db 2]]
+ db_leave db
+# puts "1: [array get stats1]"
+# puts "2: [array get stats2]"
+ puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"
+ puts "Normal : Read $stats2(read), wrote $stats2(write)"
+}
+
+proc overflow_report {db} {
+ set bt [btree_from_db db]
+ set csr [btree_cursor $bt 3 0]
+
+ for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
+ puts "[btree_ovfl_info $bt $csr]"
+ }
+
+ btree_close_cursor $csr
+
+}
+
+proc reset_db {} {
+ db close
+ sqlite3 db test.db
+ db eval {
+ PRAGMA main.cache_size = 200000;
+ PRAGMA main.auto_vacuum = 'incremental';
+ ATTACH 'test2.db' AS 'aux';
+ PRAGMA aux.auto_vacuum = 'none';
+ }
+}
+
+file delete -force test2.db test2.db-journal
+reset_db
+
+# Set up a database in auto-vacuum mode and create a database schema.
+#
+do_test speed3-0.1 {
+ execsql {
+ CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
+ }
+ execsql {
+ SELECT name FROM sqlite_master ORDER BY 1;
+ }
+} {t1}
+do_test speed3-0.2 {
+ execsql {
+ CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
+ }
+ execsql {
+ SELECT name FROM aux.sqlite_master ORDER BY 1;
+ }
+} {t1}
+do_test speed3-0.3 {
+ populate_t1 db
+ execsql {
+ SELECT count(*) FROM main.t1;
+ SELECT count(*) FROM aux.t1;
+ }
+} "$::NROW $::NROW"
+do_test speed3-0.4 {
+ execsql {
+ PRAGMA main.auto_vacuum;
+ PRAGMA aux.auto_vacuum;
+ }
+} {2 0}
+
+# Delete all content in a table, one row at a time.
+#
+#io_log db
+#overflow_report db
+reset_db
+speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
+speed_trial speed3-1.normal $::NROW row {DELETE FROM aux.t1 WHERE 1}
+io_log db
+
+# Select the "C" column (located at the far end of the overflow
+# chain) from each table row.
+#
+#db eval {PRAGMA incremental_vacuum(500000)}
+populate_t1 db
+#overflow_report db
+reset_db
+speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
+speed_trial speed3-2.normal $::NROW row {SELECT c FROM aux.t1}
+io_log db
+
+finish_test