xref: /sqlite-3.40.0/test/speed3.test (revision cd7274ce) 
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.5 2007/10/09 08:29:33 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
37
38ifcapable !tclvar||!attach {
39  finish_test
40  return
41}
42
43speed_trial_init speed1
44
45# Set a uniform random seed
46expr srand(0)
47
48set ::NROW 1000
49
50# The number_name procedure below converts its argment (an integer)
51# into a string which is the English-language name for that number.
52#
53# Example:
54#
55#     puts [number_name 123]   ->  "one hundred twenty three"
56#
57set ones {zero one two three four five six seven eight nine
58          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
59          eighteen nineteen}
60set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
61proc number_name {n} {
62  if {$n>=1000} {
63    set txt "[number_name [expr {$n/1000}]] thousand"
64    set n [expr {$n%1000}]
65  } else {
66    set txt {}
67  }
68  if {$n>=100} {
69    append txt " [lindex $::ones [expr {$n/100}]] hundred"
70    set n [expr {$n%100}]
71  }
72  if {$n>=20} {
73    append txt " [lindex $::tens [expr {$n/10}]]"
74    set n [expr {$n%10}]
75  }
76  if {$n>0} {
77    append txt " [lindex $::ones $n]"
78  }
79  set txt [string trim $txt]
80  if {$txt==""} {set txt zero}
81  return $txt
82}
83
84proc populate_t1 {db} {
85  $db transaction {
86    for {set ii 0} {$ii < $::NROW} {incr ii} {
87      set N [number_name $ii]
88      set repeats [expr {(10000/[string length $N])+1}]
89      set text [string range [string repeat $N $repeats] 0 10000]
90      $db eval {INSERT INTO main.t1 VALUES($ii, $text, $ii)}
91    }
92    $db eval {INSERT INTO aux.t1 SELECT * FROM main.t1}
93  }
94}
95
96
97proc io_log {db} {
98  db_enter db
99  array set stats1 [btree_pager_stats [btree_from_db db]]
100  array set stats2 [btree_pager_stats [btree_from_db db 2]]
101  db_leave db
102# puts "1: [array get stats1]"
103# puts "2: [array get stats2]"
104  puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"
105  puts "Normal    : Read $stats2(read), wrote $stats2(write)"
106}
107
108proc overflow_report {db} {
109  set bt [btree_from_db db]
110  set csr [btree_cursor $bt 3 0]
111
112  for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
113    puts "[btree_ovfl_info $bt $csr]"
114  }
115
116  btree_close_cursor $csr
117
118}
119
120proc reset_db {} {
121  db close
122  sqlite3 db test.db
123  db eval {
124    PRAGMA main.cache_size = 200000;
125    PRAGMA main.auto_vacuum = 'incremental';
126    ATTACH 'test2.db' AS 'aux';
127    PRAGMA aux.auto_vacuum = 'none';
128  }
129}
130
131file delete -force test2.db test2.db-journal
132reset_db
133
134# Set up a database in auto-vacuum mode and create a database schema.
135#
136do_test speed3-0.1 {
137  execsql {
138    CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
139  }
140  execsql {
141    SELECT name FROM sqlite_master ORDER BY 1;
142  }
143} {t1}
144do_test speed3-0.2 {
145  execsql {
146    CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
147  }
148  execsql {
149    SELECT name FROM aux.sqlite_master ORDER BY 1;
150  }
151} {t1}
152do_test speed3-0.3 {
153  populate_t1 db
154  execsql {
155    SELECT count(*) FROM main.t1;
156    SELECT count(*) FROM aux.t1;
157  }
158} "$::NROW $::NROW"
159do_test speed3-0.4 {
160  execsql {
161    PRAGMA main.auto_vacuum;
162    PRAGMA aux.auto_vacuum;
163  }
164} {2 0}
165
166# Delete all content in a table, one row at a time.
167#
168#io_log db
169#overflow_report db
170reset_db
171speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
172speed_trial speed3-1.normal     $::NROW row {DELETE FROM aux.t1 WHERE 1}
173io_log db
174
175# Select the "C" column (located at the far end of the overflow
176# chain) from each table row.
177#
178#db eval {PRAGMA incremental_vacuum(500000)}
179populate_t1 db
180#overflow_report db
181reset_db
182speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
183speed_trial speed3-2.normal     $::NROW row {SELECT c FROM aux.t1}
184io_log db
185
186finish_test
187