ASE : ASE 15 Hints: Query Plan Optimization

ASE 15 Set Plan Commands

These commands allow you to set an optimization goal which fits your database environment.


DSS / Reporting Environments may benefit by giving the optimizer more time to compile a query plan. Sample command
-- set time to 999 ms
set plan opttimeoutlimit 999


Using the "hash" join operator introduced in ASE 15
Hash Joins have been shown to improve queries performance by as much as 500%

This example illustrates a forced hash join.

select t1.invoice_id, t1.total, t1.status_cd, t1.fiscal_qtr, t1.margin
from invoice_master t1, client_master d
where d.region_id = 2001
and t1.invoice_id = d.invoice_id
and t1.fiscal_qtr between 20031 and 20044
order by t1.invoice_id, t1.fiscal_qtr
plan " (h_join (scan t1) (scan d))"
go


Join Queries: Choosing an optimization goal

--Examples: session level

set plan optgoal allrows_mix
go
set plan optgoal allrows_oltp
go
set plan optgoal allrows_dss
go

--Example: server level
sp_configure "optimization goal", 0, "allrows_oltp"
go

Optimization Goal Details

allrows_oltp [nested loop join]
Nested-loop joins provide efficient access when tables are indexed on join columns.

allrows_mix [merge joins + allrows_oltp]
A merge join can use multiple worker processes to perform:. The scan that selects rows into a worktable, note a merge join may requires a sort.

allrows_dss [hash joins + allrows_mix]
The hash join algorithm builds an in-memory hash table of the smaller of its targets.