xref: /sqlite-3.40.0/test/speed3.test (revision 999cc5d7) 
1# 2007 May 17
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#*************************************************************************
11# This file implements regression tests for SQLite library. The
12# focus of this script is testing that the overflow-page related
13# enhancements added after version 3.3.17 speed things up.
14#
15# $Id: speed3.test,v 1.3 2007/08/22 02:56:44 drh Exp $
16#
17
18#---------------------------------------------------------------------
19# Test plan:
20#
21# If auto-vacuum is enabled for the database, the following cases
22# should show performance improvement with respect to 3.3.17.
23#
24#   + When deleting rows that span overflow pages. This is faster
25#     because the overflow pages no longer need to be read before
26#     they can be moved to the free list (test cases speed3-1.X).
27#
28#   + When reading a column value stored on an overflow page that
29#     is not the first overflow page for the row. The improvement
30#     in this case is because the overflow pages between the tree
31#     page and the overflow page containing the value do not have
32#     to be read (test cases speed3-2.X).
33#
34
35set testdir [file dirname $argv0]
36source $testdir/tester.tcl
37speed_trial_init speed1
38
39# Set a uniform random seed
40expr srand(0)
41
42set ::NROW 1000
43
44# The number_name procedure below converts its argment (an integer)
45# into a string which is the English-language name for that number.
46#
47# Example:
48#
49#     puts [number_name 123]   ->  "one hundred twenty three"
50#
51set ones {zero one two three four five six seven eight nine
52          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
53          eighteen nineteen}
54set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
55proc number_name {n} {
56  if {$n>=1000} {
57    set txt "[number_name [expr {$n/1000}]] thousand"
58    set n [expr {$n%1000}]
59  } else {
60    set txt {}
61  }
62  if {$n>=100} {
63    append txt " [lindex $::ones [expr {$n/100}]] hundred"
64    set n [expr {$n%100}]
65  }
66  if {$n>=20} {
67    append txt " [lindex $::tens [expr {$n/10}]]"
68    set n [expr {$n%10}]
69  }
70  if {$n>0} {
71    append txt " [lindex $::ones $n]"
72  }
73  set txt [string trim $txt]
74  if {$txt==""} {set txt zero}
75  return $txt
76}
77
78proc populate_t1 {db} {
79  $db transaction {
80    for {set ii 0} {$ii < $::NROW} {incr ii} {
81      set N [number_name $ii]
82      set repeats [expr {(10000/[string length $N])+1}]
83      set text [string range [string repeat $N $repeats] 0 10000]
84      $db eval {INSERT INTO main.t1 VALUES($ii, $text, $ii)}
85    }
86    $db eval {INSERT INTO aux.t1 SELECT * FROM main.t1}
87  }
88}
89
90
91proc io_log {db} {
92  db_enter db
93  array set stats1 [btree_pager_stats [btree_from_db db]]
94  array set stats2 [btree_pager_stats [btree_from_db db 2]]
95  db_leave db
96# puts "1: [array get stats1]"
97# puts "2: [array get stats2]"
98  puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"
99  puts "Normal    : Read $stats2(read), wrote $stats2(write)"
100}
101
102proc overflow_report {db} {
103  set bt [btree_from_db db]
104  set csr [btree_cursor $bt 3 0]
105
106  for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
107    puts "[btree_ovfl_info $bt $csr]"
108  }
109
110  btree_close_cursor $csr
111
112}
113
114proc reset_db {} {
115  db close
116  sqlite3 db test.db
117  db eval {
118    PRAGMA main.cache_size = 200000;
119    PRAGMA main.auto_vacuum = 'incremental';
120    ATTACH 'test2.db' AS 'aux';
121    PRAGMA aux.auto_vacuum = 'none';
122  }
123}
124
125file delete -force test2.db test2.db-journal
126reset_db
127
128# Set up a database in auto-vacuum mode and create a database schema.
129#
130do_test speed3-0.1 {
131  execsql {
132    CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
133  }
134  execsql {
135    SELECT name FROM sqlite_master ORDER BY 1;
136  }
137} {t1}
138do_test speed3-0.2 {
139  execsql {
140    CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
141  }
142  execsql {
143    SELECT name FROM aux.sqlite_master ORDER BY 1;
144  }
145} {t1}
146do_test speed3-0.3 {
147  populate_t1 db
148  execsql {
149    SELECT count(*) FROM main.t1;
150    SELECT count(*) FROM aux.t1;
151  }
152} "$::NROW $::NROW"
153do_test speed3-0.4 {
154  execsql {
155    PRAGMA main.auto_vacuum;
156    PRAGMA aux.auto_vacuum;
157  }
158} {2 0}
159
160# Delete all content in a table, one row at a time.
161#
162#io_log db
163#overflow_report db
164reset_db
165speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
166speed_trial speed3-1.normal     $::NROW row {DELETE FROM aux.t1 WHERE 1}
167io_log db
168
169# Select the "C" column (located at the far end of the overflow
170# chain) from each table row.
171#
172#db eval {PRAGMA incremental_vacuum(500000)}
173populate_t1 db
174#overflow_report db
175reset_db
176speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
177speed_trial speed3-2.normal     $::NROW row {SELECT c FROM aux.t1}
178io_log db
179
180finish_test
181