Optimization criteria

An optimization goal is a collection of “on/off” settings for a series of properties known as “optimization criteria.” Optimization criteria allow or disallow the optimizer to consider a particular algorithm for access methods, joins, grouping, sorting, and so on.

For example, to enable hash joins, use the optimization criterion:

set hash_join on 

Or, to disable the “store_index” algorithm (reformatting), use the optimization criterion:

set store_index off

The optimizer may decide to ignore a given criteria or goal for semantic reasons. For example, if a user disables all join operators, the new optimizer enables “nested loop” automatically.

NoteSybase recommends that you use optimization goals, instead of explicit settings for optimization criteria, unless advised to do otherwise by Sybase Technical Support.

Parallel query processing in Adaptive Server 15.0

Since version 11.5, Adaptive Server has supported parallelism within queries, whereby a single query is processed by multiple worker processes. You can use parallelism to improve response times for DSS-type queries, where a large number of rows are accessed, but only a small result set is returned.

Since the query processing features in Adaptive Server 15.0 offer potential performance benefits for DSS-type queries, Sybase recommends that, when you upgrade to Adaptive Server 15.0, you do not initially use parallel processing.

Serial processing is more resource-efficient than parallel processing although parallel processing allows you to deliver better overall performance with the same hardware. Also, Adaptive Server 15.0 in serial mode runs queries faster than earlier versions of Adaptive Server with parallelism.

However, parallelism may deliver better response times than serial processing for queries that use semantic table partitioning in Adaptive Server 15.0, or for DDL commands such as create index.