xref: /sqlite-3.40.0/test/speed3.test (revision a408adc5) 
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.2 2007/05/17 18:28:11 danielk1977 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  array set stats1 [btree_pager_stats [btree_from_db db]]
93  array set stats2 [btree_pager_stats [btree_from_db db 2]]
94# puts "1: [array get stats1]"
95# puts "2: [array get stats2]"
96  puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"
97  puts "Normal    : Read $stats2(read), wrote $stats2(write)"
98}
99
100proc overflow_report {db} {
101  set bt [btree_from_db db]
102  set csr [btree_cursor $bt 3 0]
103
104  for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
105    puts "[btree_ovfl_info $bt $csr]"
106  }
107
108  btree_close_cursor $csr
109
110}
111
112proc reset_db {} {
113  db close
114  sqlite3 db test.db
115  db eval {
116    PRAGMA main.cache_size = 200000;
117    PRAGMA main.auto_vacuum = 'incremental';
118    ATTACH 'test2.db' AS 'aux';
119    PRAGMA aux.auto_vacuum = 'none';
120  }
121}
122
123file delete -force test2.db test2.db-journal
124reset_db
125
126# Set up a database in auto-vacuum mode and create a database schema.
127#
128do_test speed3-0.1 {
129  execsql {
130    CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
131  }
132  execsql {
133    SELECT name FROM sqlite_master ORDER BY 1;
134  }
135} {t1}
136do_test speed3-0.2 {
137  execsql {
138    CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
139  }
140  execsql {
141    SELECT name FROM aux.sqlite_master ORDER BY 1;
142  }
143} {t1}
144do_test speed3-0.3 {
145  populate_t1 db
146  execsql {
147    SELECT count(*) FROM main.t1;
148    SELECT count(*) FROM aux.t1;
149  }
150} "$::NROW $::NROW"
151do_test speed3-0.4 {
152  execsql {
153    PRAGMA main.auto_vacuum;
154    PRAGMA aux.auto_vacuum;
155  }
156} {2 0}
157
158# Delete all content in a table, one row at a time.
159#
160#io_log db
161#overflow_report db
162reset_db
163speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
164speed_trial speed3-1.normal     $::NROW row {DELETE FROM aux.t1 WHERE 1}
165io_log db
166
167# Select the "C" column (located at the far end of the overflow
168# chain) from each table row.
169#
170#db eval {PRAGMA incremental_vacuum(500000)}
171populate_t1 db
172#overflow_report db
173reset_db
174speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
175speed_trial speed3-2.normal     $::NROW row {SELECT c FROM aux.t1}
176io_log db
177
178finish_test
179
180