Distributed Query Processing (DQP) performance is improved in SAP Sybase IQ 16.0.
| Performance Improvement | Description |
|---|---|
| Affinity-based work allocation |
For DQP eligible queries, SAP Sybase IQ identifies preferred nodes for scans of specific row ranges and partition IDs. Once a query establishes an affinity for a piece of data, subsequent queries that need the same data use the same node for their work. Use the database options AFFINITY_AUTOEXCLUDE_TIMEOUT and CACHE_AFFINITY_PERCENT to control allocation. |
| Asymmetric sort merge join algorithm |
The new asymmetric sort merge join algorithm improves performance in distributed queries that join and sort high-cardinality data. Advanced DBAs can control algorithm choice using the JOIN_PREFERENCE option. |
| Early aggregation |
An optimization where distincting, grouping, and aggregates are pushed into a sort, rather than sorting all rows first, then performing any aggregation outside the sort as a second step. Advanced DBAs can control algorithm choice using the AGGREGATION_PREFERENCE option. |
| Option to use DQP over the network |
DQP over the network keeps data in memory in the temporary cache instead of in the IQ_SHARED_TEMP dbspace. The option that provides the best performance may vary by query, and depends on the performance of the network relative to the I/O system. Use the DQP_ENABLED logical server policy option to control DQP processing. The DQP_ENABLED and DQP_ENABLED_OVER_NETWORK database options control DQP at the connection level. |