SQLite

Check-in [764e7262b9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional tests designed to detect future performance regressions. (CVS 3840)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 764e7262b93a7a5073128ecd4db265b0c728a701
User & Date: drh 2007-04-13 03:23:21.000
Context
2007-04-13
04:01
Fix a bug in autovacuum introduced by (3839). (CVS 3841) (check-in: e39efa195a user: drh tags: trunk)
03:23
Additional tests designed to detect future performance regressions. (CVS 3840) (check-in: 764e7262b9 user: drh tags: trunk)
02:14
Fix multiple performance regressions (ticket #2298 among them) and add tests to make sure they do not come back. (CVS 3839) (check-in: 32bb2d5859 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/pageropt.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# The focus of the tests in this file are to verify that the
# pager optimizations implemented in version 3.3.14 work.
#
# $Id: pageropt.test,v 1.1 2007/04/13 02:14:30 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!pager_pragmas} {
  finish_test
  return







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# The focus of the tests in this file are to verify that the
# pager optimizations implemented in version 3.3.14 work.
#
# $Id: pageropt.test,v 1.2 2007/04/13 03:23:21 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!pager_pragmas} {
  finish_test
  return
129
130
131
132
133
134
135
136






























































137
138
  }
} {3 4 3 0}
do_test pageropt-2.3 {
  pagercount_sql {
    INSERT INTO t1 VALUES(randomblob(1500));
  }
} {0 4 3 0}































































catch {db2 close}
finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
  }
} {3 4 3 0}
do_test pageropt-2.3 {
  pagercount_sql {
    INSERT INTO t1 VALUES(randomblob(1500));
  }
} {0 4 3 0}

# Note the new optimization that when pulling the very last page off of the
# freelist we do not read the content of that page.
#
do_test pageropt-2.4 {
  pagercount_sql {
    INSERT INTO t1 VALUES(randomblob(1500));
  }
} {0 5 3 0}

# Appending a large quantity of data does not involve writing much
# to the journal file.
#
do_test pageropt-3.1 {
  pagercount_sql {
    INSERT INTO t2 SELECT * FROM t1;
  }
} {1 7 2 0}

# Once again, we do not need to read the last page of an overflow chain
# while deleting.
#
do_test pageropt-3.2 {
  pagercount_sql {
    DROP TABLE t2;
  }
} {0 2 3 0}
do_test pageropt-3.3 {
  pagercount_sql {
    DELETE FROM t1;
  }
} {0 3 3 0}

# There are now 11 pages on the freelist.  Move them all into an
# overflow chain by inserting a single large record.  Starting from
# a cold cache, only page 1, the root page of table t1, and the trunk
# of the freelist need to be read (3 pages).  And only those three
# pages need to be journalled.  But 13 pages need to be written:
# page1, the root page of table t1, and an 11 page overflow chain.
#
do_test pageropt-4.1 {
  db close
  sqlite3 db test.db
  pagercount_sql {
    INSERT INTO t1 VALUES(randomblob(11300))
  }
} {3 13 3 0}

# Now we delete that big entries starting from a cold cache and an
# empty freelist.  The first 10 of the 11 pages overflow chain have
# to be read, together with page1 and the root of the t1 table.  12
# reads total.  But only page1, the t1 root, and the trunk of the
# freelist need to be journalled and written back.
#
do_test pageroot-4.2 {
  db close
  sqlite3 db test.db
  pagercount_sql {
    DELETE FROM t1
  }
} {12 3 3 0}


catch {db2 close}
finish_test
Changes to test/tester.tcl.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# 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 some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.77 2007/03/31 22:34:16 drh Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# 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 some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.78 2007/04/13 03:23:21 drh Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
115
116
117
118
119
120
121

122
123
124
125
126
127
128
    puts "\nExpected: \[$expected\]\n     Got: \[$result\]"
    incr nErr
    lappend ::failList $name
    if {$nErr>=$maxErr} {puts "*** Giving up..."; finalize_testing}
  } else {
    puts " Ok"
  }

}

# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {
  puts -nonewline [format {%-21.21s } $name...]







>







115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
    puts "\nExpected: \[$expected\]\n     Got: \[$result\]"
    incr nErr
    lappend ::failList $name
    if {$nErr>=$maxErr} {puts "*** Giving up..."; finalize_testing}
  } else {
    puts " Ok"
  }
  flush stdout
}

# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {
  puts -nonewline [format {%-21.21s } $name...]