Sybase 12.4.2 manual Network performance, Improving large data transfers, 459

CHAPTER 12 Managing System Resources

•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.

459