Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Sybase » T-SQL » Performance » Joins

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.










Sybase : Related Topics


Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.