Documentation Source Text

Check-in [39b74ae928]
Login

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

Overview
Comment:Add documentation about the threads pragma and the WORKER_THREADS compile-time options. Update the change log to include the latest performance numbers and mention of multi-threaded sorting.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 39b74ae928f3873d8fce5003298f1b055d05f4dd
User & Date: drh 2014-09-01 18:16:24.912
Context
2014-09-01
18:31
Further tweaks to the change log for 3.8.7. (check-in: e3957c53b1 user: drh tags: trunk)
18:16
Add documentation about the threads pragma and the WORKER_THREADS compile-time options. Update the change log to include the latest performance numbers and mention of multi-threaded sorting. (check-in: 39b74ae928 user: drh tags: trunk)
2014-08-27
18:49
Add an initial change log for version 3.8.7. (check-in: d7f4783e95 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
evidence:	tclsh
	./tclsh $(SCANNER) -reset src $(SRC)/src/*.[chy]
	./tclsh $(SCANNER) src $(SRC)/ext/fts3/*.[ch]
	./tclsh $(SCANNER) src $(SRC)/ext/rtree/*.[ch]
	./tclsh $(SCANNER) tcl $(SRC)/test/*.test
	if test '' != '$(TH3)'; then \
	  ./tclsh $(SCANNER) th3 $(TH3)/mkth3.tcl; \
	  ./tclsh $(SCANNER) th3 $(TH3)/core/*.c; \
	  ./tclsh $(SCANNER) th3/req1 $(TH3)/req1/*.test; \
	  ./tclsh $(SCANNER) th3/cov1 $(TH3)/cov1/*.test; \
	  ./tclsh $(SCANNER) th3/stress $(TH3)/stress/*.test; \
	fi
	if test '' != '$(SLT)'; then \
	  ./tclsh $(SCANNER) slt $(SLT)/test/evidence/*.test; \
	fi







|







101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
evidence:	tclsh
	./tclsh $(SCANNER) -reset src $(SRC)/src/*.[chy]
	./tclsh $(SCANNER) src $(SRC)/ext/fts3/*.[ch]
	./tclsh $(SCANNER) src $(SRC)/ext/rtree/*.[ch]
	./tclsh $(SCANNER) tcl $(SRC)/test/*.test
	if test '' != '$(TH3)'; then \
	  ./tclsh $(SCANNER) th3 $(TH3)/mkth3.tcl; \
	  ./tclsh $(SCANNER) th3 $(TH3)/base/*.c; \
	  ./tclsh $(SCANNER) th3/req1 $(TH3)/req1/*.test; \
	  ./tclsh $(SCANNER) th3/cov1 $(TH3)/cov1/*.test; \
	  ./tclsh $(SCANNER) th3/stress $(TH3)/stress/*.test; \
	fi
	if test '' != '$(SLT)'; then \
	  ./tclsh $(SCANNER) slt $(SLT)/test/evidence/*.test; \
	fi
Changes to pages/changes.in.
16
17
18
19
20
21
22
23
24







25
26
27
28
29
30
31
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2014-11-?? (3.8.7)} {
<p><b>Performance Enhancements:</b>
<li>Dozens and dozens of micro-optimizations resulting in having to use 5% fewer CPU cycles
    to do the same amount work.







<li>Enhance the [skip-scan] optimization so that it is able to skip index terms that
    occur in the middle of the index, not just as the left-hand side of the index.
<li>The query planner is now able to do a better job of understanding constants that
    happy to occur inside of [CAST] operators.
<p><b>Bug Fixes:</b>
<li>Fix a bug in the [partial index] implementation that might result in an incorrect
    answer if a partial index is used in a subquery or in a [view].







|
|
>
>
>
>
>
>
>







16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2014-11-?? (3.8.7)} {
<p><b>Performance Enhancements:</b>
<li>Dozens of micro-optimizations result in 6.7% fewer CPU cycles needed
    to do the same amount work.  (Measured using 
    [http://valgrind.org/docs/manual/cg-manual.html|cachegrind] on the
    [http://www.sqlite.org/src/artifact/83f6b3318f7ee|speedtest1.c] workload on
    Ubuntu 10.13 x64 with gcc 4.8.1 and -Os. Your performance may vary.)
<li>Added the ability for the sorter to start and use auxiliary helper threads in order
    to increase real-time response.  This feature is off by default and may be
    enabled using the [PRAGMA threads] command or the [SQLITE_DEFAULT_WORKER_THREADS]
    compile-time option.
<li>Enhance the [skip-scan] optimization so that it is able to skip index terms that
    occur in the middle of the index, not just as the left-hand side of the index.
<li>The query planner is now able to do a better job of understanding constants that
    happy to occur inside of [CAST] operators.
<p><b>Bug Fixes:</b>
<li>Fix a bug in the [partial index] implementation that might result in an incorrect
    answer if a partial index is used in a subquery or in a [view].
Changes to pages/compile.in.
148
149
150
151
152
153
154









155
156
157
158
159
160
161
}

COMPILE_OPTION {SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<i>&lt;pages&gt;</i>} {
  This macro sets the default page count for the [WAL]
  [checkpointing | automatic checkpointing] feature.  If unspecified,
  the default page count is 1000.
}










COMPILE_OPTION {SQLITE_FTS3_MAX_EXPR_DEPTH=<i>N</i>} {
  This macro sets the maximum depth of the search tree that corresponds to
  the right-hand side of the MATCH operator in an [FTS3] or [FTS4] full-text
  index.  The full-text search uses a recursive algorithm, so the depth of
  the tree is limited to prevent using too much stack space.  The default
  limit is 12.  This limit is sufficient for up to 4095 search terms on the







>
>
>
>
>
>
>
>
>







148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
}

COMPILE_OPTION {SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<i>&lt;pages&gt;</i>} {
  This macro sets the default page count for the [WAL]
  [checkpointing | automatic checkpointing] feature.  If unspecified,
  the default page count is 1000.
}

COMPILE_OPTION {SQLITE_DEFAULT_WORKER_THREADS=<i>N</i>} {
  This macro determins the default vallue for
  the [SQLITE_LIMIT_WORKER_THREADS] parameter.  The [SQLITE_LIMIT_WORKER_THREADS]
  parameter determines the maximum number of auxiliary threads that a single
  [prepared statement] will launch to assist it with a query.  If not specified,
  the default maximum is 0.
  The value set here cannot be more then [SQLITE_MAX_WORKER_THREADS].
}

COMPILE_OPTION {SQLITE_FTS3_MAX_EXPR_DEPTH=<i>N</i>} {
  This macro sets the maximum depth of the search tree that corresponds to
  the right-hand side of the MATCH operator in an [FTS3] or [FTS4] full-text
  index.  The full-text search uses a recursive algorithm, so the depth of
  the tree is limited to prevent using too much stack space.  The default
  limit is 12.  This limit is sufficient for up to 4095 search terms on the
191
192
193
194
195
196
197







198
199
200
201
202
203
204
  in that if one thread is constantly changing the schema, another thread
  might spin on reparses and repreparations of a prepared statement and
  never get any real work done.  This parameter prevents an infinite loop
  by forcing the spinning thread to give up after a fixed number of attempts
  at recompiling the prepared statement.  The default setting is 50 which is
  more than adequate for most applications.
}








COMPILE_OPTION {SQLITE_MINIMUM_FILE_DESCRIPTOR=<i>N</i>} {
  The unix [VFS] will never use a file descriptor less than <i>N</i>.  The
  default value of <i>N</i> is 3.
  <p>
  Avoiding the use of low-numbered file descriptors is a defense against
  accidental database corruption.  If a database file was opened using







>
>
>
>
>
>
>







200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
  in that if one thread is constantly changing the schema, another thread
  might spin on reparses and repreparations of a prepared statement and
  never get any real work done.  This parameter prevents an infinite loop
  by forcing the spinning thread to give up after a fixed number of attempts
  at recompiling the prepared statement.  The default setting is 50 which is
  more than adequate for most applications.
}

COMPILE_OPTION {SQLITE_MAX_WORKER_THREADS=<i>N</i>} {
  Set an upper bound on the [sqlite3_limit](db,[SQLITE_LIMIT_WORKER_THREADS],N)
  setting that determines the maximum number of auxiliary threads that a single
  [prepared statement] will use to aid with CPU-intensive computations
  (mostly sorting).  See also the [SQLITE_DEFAULT_WORKER_THREADS] options.
}

COMPILE_OPTION {SQLITE_MINIMUM_FILE_DESCRIPTOR=<i>N</i>} {
  The unix [VFS] will never use a file descriptor less than <i>N</i>.  The
  default value of <i>N</i> is 3.
  <p>
  Avoiding the use of low-numbered file descriptors is a defense against
  accidental database corruption.  If a database file was opened using
Changes to pages/pragma.in.
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
    <p>The default directory for temporary files depends on the OS.  Some
    OS interfaces may choose to ignore this variable and place temporary
    files in some other directory different from the directory specified
    here.  In that sense, this pragma is only advisory.</p>

    DISCLAIMER
}

LegacyPragma data_store_directory {
    <p><b>PRAGMA data_store_directory;
       <br>PRAGMA data_store_directory = '</b><i>directory-name</i><b>';</b></p>
    <p>Query or change the value of the [sqlite3_data_directory] global
    variable, which windows operating-system interface backends use to
    determine where to store database files specified using a relative
    pathname.</p>







<







1047
1048
1049
1050
1051
1052
1053

1054
1055
1056
1057
1058
1059
1060
    <p>The default directory for temporary files depends on the OS.  Some
    OS interfaces may choose to ignore this variable and place temporary
    files in some other directory different from the directory specified
    here.  In that sense, this pragma is only advisory.</p>

    DISCLAIMER
}

LegacyPragma data_store_directory {
    <p><b>PRAGMA data_store_directory;
       <br>PRAGMA data_store_directory = '</b><i>directory-name</i><b>';</b></p>
    <p>Query or change the value of the [sqlite3_data_directory] global
    variable, which windows operating-system interface backends use to
    determine where to store database files specified using a relative
    pathname.</p>
1070
1071
1072
1073
1074
1075
1076

















1077
1078
1079
1080
1081
1082
1083
    <p>This facility is provided for WinRT which does not have an OS
    mechanism for reading or changing the current working directory.
    The use of this pragma in any other context is discouraged and may
    be disallowed in future releases.</p>

    DISCLAIMER
}


















Pragma collation_list {
    <p>^(<b>PRAGMA collation_list;</b></p>
    <p>Return a list of the collating sequences defined for the current
    database connection.</p>)^
}








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







1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
    <p>This facility is provided for WinRT which does not have an OS
    mechanism for reading or changing the current working directory.
    The use of this pragma in any other context is discouraged and may
    be disallowed in future releases.</p>

    DISCLAIMER
}

Pragma threads {
    <p><b>PRAGMA threads;
       <br>PRAGMA threads = </b><i>N</i><b>;</b></p>
    <p>Query or change the value of the 
    [sqlite3_limit](db,[SQLITE_LIMIT_WORKER_THREADS],...) limit for
    the current database connection.  This limit sets an upper bound
    on the number of auxiliary threads that a [prepared statement] is
    allowed to launch to assist with a query.  The default limit is 0
    unless it is changed using the [SQLITE_DEFAULT_WORKER_THREADS]
    compile-time option.  When the limit is zero, that means no
    auxiliary threads will be launched.</p>

    <p>This pragma is a thin wrapper around the
    [sqlite3_limit](db,[SQLITE_LIMIT_WORKER_THREADS],...) interface.
    </p>
}

Pragma collation_list {
    <p>^(<b>PRAGMA collation_list;</b></p>
    <p>Return a list of the collating sequences defined for the current
    database connection.</p>)^
}