Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 479 / 536

CHAPTER 12 Managing System Resources
459
•
JOIN_MAX_HASH_ROWS
Sets the maximum estimated number of
rows the query optimizer will consider for a hash algorithm. The default is
125,000 rows. For example, if there is a join between two tables, and the
estimated number of rows entering the join from both tables exceeds this
option value, the optimizer will not consider a hash join. On systems with
more than 50MB per user of
TEMP_CACHE_MEMORY_MB
, you may want
to consider a higher value for this option.
•
MAX_CARTESIAN_RESULT
Limits the number of result rows from a
query containing a cartesian join (usually the result of missing one or more
join conditions when creating the query). If Adaptive Server IQ cannot
find a query plan for the cartesian join with an estimated result under this
limit, it rejects the query and returns an error. The default is 100,000,000
rows.
•
ROW_COUNTS
Specifies whether the database will always count the
number of rows in a query when it is opened. Default is
OFF
. Turning on
this option guarantees an accurate count, but can slow the start of query
processing.
Network performance
The following sections offer suggestions for solving some network
performance issues.
Improving large data transfers
Large data transfers simultaneously decrease overall throughput and increase
the average response time. Here are some suggestions to improve performance
during these transfers:
• Perform large transfers during off-hour periods, if possible.
• Limit the number of concurrent queries during large transfers.
• Do not run queries and insertions concurrently during large transfers.
• Use stored procedures to reduce total traffic.
• Use row buffering to move large batches through the network.