SQLite

Check-in [27f56c2051]
Login

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

Overview
Comment:Improvements to the new performance tests. (CVS 3526)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 27f56c20514030e009fc3aa7e060d2e6276ddb83
User & Date: drh 2006-11-30 13:06:00.000
Context
2006-11-30
13:06
Change the table_info pragma so that it returns NULL for the default value if there is no default value. Ticket #2078. (CVS 3527) (check-in: 5f21c3a5f0 user: drh tags: trunk)
13:06
Improvements to the new performance tests. (CVS 3526) (check-in: 27f56c2051 user: drh tags: trunk)
13:05
Fix indentation typo in btree.c. (CVS 3525) (check-in: 5d61486f0f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/select6.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 this file is testing SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.25 2006/11/23 09:39:16 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_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 this file is testing SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.26 2006/11/30 13:06:00 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
    SELECT q, p, r, b.[min(x)+y]
    FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
         (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b
    WHERE q=s ORDER BY s
  }
} {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21}

do_speed_test select6-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
    SELECT DISTINCT b FROM t2 ORDER BY b;
  }
} {1 2 3 4 5}
do_test select6-2.1 {







|







97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
    SELECT q, p, r, b.[min(x)+y]
    FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
         (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b
    WHERE q=s ORDER BY s
  }
} {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21}

do_test select6-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
    SELECT DISTINCT b FROM t2 ORDER BY b;
  }
} {1 2 3 4 5}
do_test select6-2.1 {
Changes to test/speed1.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18






19
20
21
22
23
24
25
# 2006 November 23
#
# 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 regression tests for SQLite library.  The
# focus of this script is measuring executing speed.
#
# $Id: speed1.test,v 1.1 2006/11/29 20:53:00 drh Exp $
#

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







# The number_name procedure below converts its argment (an integer)
# into a string which is the English-language name for that number.
#
# Example:
#
#     puts [number_name 123]   ->  "one hundred twenty three"













|




>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 2006 November 23
#
# 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 regression tests for SQLite library.  The
# focus of this script is measuring executing speed.
#
# $Id: speed1.test,v 1.2 2006/11/30 13:06:00 drh Exp $
#

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

set sqlout [open speed1.txt w]
proc tracesql {sql} {
  puts $::sqlout $sql\;
}
db trace tracesql

# The number_name procedure below converts its argment (an integer)
# into a string which is the English-language name for that number.
#
# Example:
#
#     puts [number_name 123]   ->  "one hundred twenty three"
51
52
53
54
55
56
57

58
59
60
61
62
63
64
  return $txt
}

# Create a database schema.
#
do_test speed1-1.0 {
  execsql {

    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
    CREATE INDEX i2a ON t2(a);
    CREATE INDEX i2b ON t2(b);
    SELECT name FROM sqlite_master ORDER BY 1;
  }
} {i2a i2b t1 t2}







>







57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
  return $txt
}

# Create a database schema.
#
do_test speed1-1.0 {
  execsql {
pragma page_size=4096;
    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
    CREATE INDEX i2a ON t2(a);
    CREATE INDEX i2b ON t2(b);
    SELECT name FROM sqlite_master ORDER BY 1;
  }
} {i2a i2b t1 t2}
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
  set upr [expr {($i+10)*100}]
  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
}
db eval BEGIN
speed_trial speed1-select3 5000 stmt $sql
db eval COMMIT

# 20000 random SELECTs against rowid.
#
set sql {}
for {set i 1} {$i<=20000} {incr i} {
  set id [expr {int(rand()*50000)+1}]
  append sql "SELECT c FROM t1 WHERE rowid=$id;"
}
db eval BEGIN
speed_trial speed1-select4 20000 row $sql
db eval COMMIT

# 20000 random SELECTs against a unique indexed column.
#
set sql {}
for {set i 1} {$i<=20000} {incr i} {
  set id [expr {int(rand()*50000)+1}]
  append sql "SELECT c FROM t1 WHERE a=$id;"
}
db eval BEGIN
speed_trial speed1-select5 20000 row $sql
db eval COMMIT

# 20000 random SELECTs against an indexed column text column
#
set sql {}
db eval {SELECT c FROM t1 ORDER BY random() LIMIT 20000} {
  append sql "SELECT c FROM t1 WHERE c='$c';"
}
db eval BEGIN
speed_trial speed1-select6 20000 row $sql
db eval COMMIT


# Vacuum
speed_trial speed1-vacuum 100000 row VACUUM

# 5000 updates of ranges where the field being compared is indexed.







|


|




|


|


|




|


|


|



|







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
  set upr [expr {($i+10)*100}]
  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
}
db eval BEGIN
speed_trial speed1-select3 5000 stmt $sql
db eval COMMIT

# 100000 random SELECTs against rowid.
#
set sql {}
for {set i 1} {$i<=100000} {incr i} {
  set id [expr {int(rand()*50000)+1}]
  append sql "SELECT c FROM t1 WHERE rowid=$id;"
}
db eval BEGIN
speed_trial speed1-select4 100000 row $sql
db eval COMMIT

# 100000 random SELECTs against a unique indexed column.
#
set sql {}
for {set i 1} {$i<=100000} {incr i} {
  set id [expr {int(rand()*50000)+1}]
  append sql "SELECT c FROM t1 WHERE a=$id;"
}
db eval BEGIN
speed_trial speed1-select5 100000 row $sql
db eval COMMIT

# 50000 random SELECTs against an indexed column text column
#
set sql {}
db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {
  append sql "SELECT c FROM t1 WHERE c='$c';"
}
db eval BEGIN
speed_trial speed1-select6 50000 row $sql
db eval COMMIT


# Vacuum
speed_trial speed1-vacuum 100000 row VACUUM

# 5000 updates of ranges where the field being compared is indexed.