[
https://issues.apache.org/jira/browse/DERBY-6305?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14177915#comment-14177915
]
Myrna van Lunteren commented on DERBY-6305:
-------------------------------------------
Here is some ancient cloudscape documentation re bulkFetch:
bulkFetch
Function
The bulkFetch optimizer property overrides the default number of rows that
Cloudscape fetches at a time when reading a conglomerate (table or index). When
this property is set to 1, Cloudscape fetches rows one at a time. Setting this
property to a greater number allows Cloudscape to fetch more than one row at a
time and reduces system overhead.
Setting the bulkFetch property uses memory. Cloudscape constructs a temporary
cache for storing fetched rows of a size specified in the property. This cache
uses memory based on the sum of the columns referenced by the scan, not the
size of the entire row in the target conglomerate. Cloudscape fills the cache
with the number of qualifying rows, up to the number specified with the
property. Cloudscape applies simple predicates (such as WHERE Region =
'Europe') before fetching. When the scan completes, the row cache is freed.
Setting the bulkFetch property to a large value (up to the number of rows in a
typical page) enhances performance at the expense of the amount of memory
needed for the row cache. There is no performance gain in setting the bulkFetch
size larger than the number of qualifying rows from the target table. For
example, if the query will return only 5 rows, there is no performance benefit
in setting this property to 30.
The default bulk fetch size is determined by the
cloudscape.language.bulkFetchDefault system property; the default value for
that property is 16. That value is optimal for most situations. For more
information, see “cloudscape.language.bulkFetchDefault”.
Syntax
bulkFetch=size
Default Value
16.
Minimum Value
1.
Maximum Value
java.lang.Integer.MAX_INTEGER.
Example
SELECT *
FROM FlightAvailability
PROPERTIES bulkFetch=256
Restrictions
Bulk fetch is not used in the following cases:
with a joinStrategy=hash clause, since the hash join strategy always causes
the entire table to be fetched at once
with updatable cursors, since a positioned update or delete requires the
current scan position to locate a single target row
with nonmaterialized subqueries
if the table in question is the inner table of an equijoin on a unique key
When Useful
Bulk fetch is best suited for situations in which many rows are returned.
For example, it is useful for table scans or index range scans:
SELECT *
FROM Flights
WHERE orig_airport > 'AAB'
AND orig_airport < 'XYZ'
SELECT *
FROM Flights
SELECT *
FROM Flights
WHERE segment_number = 1
Scope
Table optimizer-override property.
I do not know if this override is actually working, nor do I know if there is
(still) an equivalent to the mentioned property.
> Tuning Derby information on optimizer overrides needs improvement
> -----------------------------------------------------------------
>
> Key: DERBY-6305
> URL: https://issues.apache.org/jira/browse/DERBY-6305
> Project: Derby
> Issue Type: Bug
> Components: Documentation
> Affects Versions: 10.10.1.1
> Reporter: Kim Haase
> Attachments: DERBY-6305.diff, DerbyDashProperties.html,
> ctundepthoptover.html
>
>
> The topic "Optimizer overrides" in the Tuning Derby manual talks about using
> the "-- DERBY-PROPERTIES" clause to specify overrides but doesn't include a
> link to the topic later in the manual, "Overriding the default optimizer
> behavior", that explains how to use the clause. This undoubtedly results in
> user confusion.
> The first topic is
> http://db.apache.org/derby/docs/10.10/tuning/ctundepthoptover.html; the
> second is
> http://db.apache.org/derby/docs/10.10/tuning/ctunoptimzoverride.html.
> I'll add the link, and I welcome additional suggestions on how to improve
> these topics.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
