/ Check-in [27f56c20]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:27f56c20514030e009fc3aa7e060d2e6276ddb83
User & Date: drh 2006-11-30 13:06:00
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: 5f21c3a5 user: drh tags: trunk
13:06
Improvements to the new performance tests. (CVS 3526) check-in: 27f56c20 user: drh tags: trunk
13:05
Fix indentation typo in btree.c. (CVS 3525) check-in: 5d61486f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/select6.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#    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
................................................................................
    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 {







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#    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
................................................................................
    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.

7
8
9
10
11
12
13
14
15
16
17
18






19
20
21
22
23
24
25
..
51
52
53
54
55
56
57

58
59
60
61
62
63
64
...
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
#    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"
................................................................................
  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}
................................................................................
  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.







|




>
>
>
>
>
>







 







>







 







|


|




|


|


|




|


|


|



|







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
..
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
...
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
#    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"
................................................................................
  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}
................................................................................
  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.