xref: /sqlite-3.40.0/test/speed3.test (revision 63c64f3b) 
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.1 2007/05/17 14:45:13 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 reset_db {} {
101  db close
102  sqlite3 db test.db
103  db eval {
104    PRAGMA main.cache_size = 200000;
105    PRAGMA main.auto_vacuum = 'incremental';
106    ATTACH 'test2.db' AS 'aux';
107    PRAGMA aux.auto_vacuum = 'none';
108  }
109}
110
111file delete -force test2.db test2.db-journal
112reset_db
113
114# Set up a database in auto-vacuum mode and create a database schema.
115#
116do_test speed3-0.1 {
117  execsql {
118    CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
119  }
120  execsql {
121    SELECT name FROM sqlite_master ORDER BY 1;
122  }
123} {t1}
124do_test speed3-0.2 {
125  execsql {
126    CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
127  }
128  execsql {
129    SELECT name FROM aux.sqlite_master ORDER BY 1;
130  }
131} {t1}
132do_test speed3-0.3 {
133  populate_t1 db
134  execsql {
135    SELECT count(*) FROM main.t1;
136    SELECT count(*) FROM aux.t1;
137  }
138} "$::NROW $::NROW"
139do_test speed3-0.4 {
140  execsql {
141    PRAGMA main.auto_vacuum;
142    PRAGMA aux.auto_vacuum;
143  }
144} {2 0}
145
146# Delete all content in a table, one row at a time.
147#
148#io_log db
149reset_db
150speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
151speed_trial speed3-1.normal     $::NROW row {DELETE FROM aux.t1 WHERE 1}
152io_log db
153
154# Select the "C" column (located at the far end of the overflow
155# chain) from each table row.
156#
157db eval {PRAGMA incremental_vacuum(500000)}
158populate_t1 db
159reset_db
160speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
161speed_trial speed3-2.normal     $::NROW row {SELECT c FROM aux.t1}
162io_log db
163
164finish_test
165
166