Need help tuning joins

[Nowell]

Hey folks. I am re-visiting some very old stored procs. One in
particular has been giving me problems. Joins are my weak point, and I
hope someone can give me some suggestions on improving performance.
Below is the stored proc. The dbcc traceon(3604, 302, 310) and
showplan output is rather large (close to 100M). Most of the tables
are in the 1MM range.

Again, any suggestions would be helpful, thanks!

Nowell

Usage: To pick up all corporate bonds which are not deleted and
matured

create proc p_corpbonds
as

SELECT DISTINCT
aa.am_imnt_outst As AmountOutstanding,
e.id_typ_imnt AS AssetType,
a.id_ctry_issuer AS CountryOfIssue,
ir.rt_cpn AS Coupon,
ir.id_freq_cpn AS CouponFrequency,
sis.id_typ_sched AS CouponType,
a.id_ccy_main AS Currency,
aidp.id_imnt_alt AS CUSIP,
ir.dt_cpn_lst AS FirstCouponDate,
isec.tx_sector AS IndustrySector,
aid.id_imnt_alt AS ISIN,
a.am_issue AS IssueAmount,
a.dt_issue AS IssueDate,
p.nm_party AS IssuerName,
aa.nm_underwrt AS LeadUnderwriter,
einst.id_mkt AS MarketOfIssue,
a.dt_expy_imnt AS MaturityDate,
optc.dt_start_optn AS NextCallDate,
optp.dt_start_optn AS NextPutDate,
ir.id_series AS Series,
aidt.id_imnt_alt AS Ticker,
mdr.id_curr_rtg AS MoodyRating,
sp.id_rtg_mat AS SPRating,
fitch.id_issue_lt AS FitchRating,
a.am_trd_min AS MinimumDenomination,
NULL AS DenominationIncrement
FROM (((((((((((((((((
INSTRUMENT a
INNER JOIN IR_INSTRUMENT ir
ON ir.id_imnt = a.id_imnt AND ir.id_del_grd = '0')
INNER JOIN LISTED_INSTRUMENT aa
ON a.id_imnt = aa.id_imnt AND aa.id_del_grd='0' AND
aa.id_valid='Y')
INNER JOIN EXCHANGE_IMNT_ALTERNATE_ID aid
ON a.id_imnt = aid.id_imnt AND aid.id_del_grd='0' AND
rtrim(aid.id_typ_alt_imnt) = 'I')
INNER JOIN EXCHANGE_IMNT_ALTERNATE_ID aidp
ON a.id_imnt = aidp.id_imnt AND aidp.id_del_grd='0' AND
rtrim(aidp.id_typ_alt_imnt) = 'P')
INNER JOIN EXCHANGE_IMNT_ALTERNATE_ID aidt
ON a.id_imnt = aidt.id_imnt AND aidt.id_del_grd='0' AND
rtrim(aidt.id_typ_alt_imnt) = 'BLT')
INNER JOIN IMNT_TYPE_ASSIGN e
ON a.id_imnt = e.id_imnt)
INNER JOIN IMNT_TYPE f
ON e.id_typ_imnt = f.id_typ_imnt AND rtrim(f.id_purp_typ_imnt) =
'TRTP')
INNER JOIN PARTY p
ON a.id_issuer = p.id_party)
INNER JOIN INSTRUMENT_SCHEDULE isch
ON isch.id_imnt = a.id_imnt AND isch.id_del_grd = '0')
INNER JOIN STREAM_IR_SCHEDULE sis
ON isch.id_sched = sis.id_sched_ir AND sis.id_del_grd = '0')
LEFT OUTER JOIN (
SELECT ids.tx_sector, is1.id_imnt FROM INSTRUMENT_SECTOR is1,
INDUSTRY_SECTOR ids
WHERE is1.id_sector = ids.id_sector
AND ids.id_purp_sector = 'ISSR-IND'
AND ids.id_src_sector = 'BBG'
AND is1.id_del_grd='0'
AND ids.id_del_grd='0'
) isec
ON isec.id_imnt = a.id_imnt)
LEFT OUTER JOIN EXCHANGE_INSTRUMENT einst
ON a.id_imnt = einst.id_imnt AND einst.id_del_grd = '0')
LEFT OUTER JOIN MD_RATING mdr
ON aid.id_imnt_alt = mdr.id_isin and mdr.id_del_grd = '0')
LEFT OUTER JOIN SP_ISSUE_MATURITY sp
ON aid.id_imnt_alt = sp.id_isin AND sp.id_del_grd = '0')
LEFT OUTER JOIN FITCH_ISSUE_RATING fitch
ON aid.id_imnt_alt = fitch.id_isin)
LEFT OUTER JOIN
(SELECT MIN(dt_start_optn) as dt_start_optn, id_imnt FROM
EMBEDDED_OPTION
WHERE id_del_grd='0' AND id_typ_optn = 'BCAL' AND
dt_start_optn>current_date()
GROUP BY id_imnt) optc
ON optc.id_imnt = a.id_imnt)
LEFT OUTER JOIN
(SELECT MIN(dt_start_optn) as dt_start_optn, id_imnt FROM
EMBEDDED_OPTION
WHERE id_del_grd='0' AND id_typ_optn = 'BPUT' AND
dt_start_optn>current_date()
GROUP BY id_imnt) optp
ON optp.id_imnt = a.id_imnt)
WHERE a.id_typ_imnt in ('BON', 'CBL')
AND a.id_del_grd='0'
AND a.id_ccy_main = 'USD'
AND a.dt_expy_imnt >= current_date()
AND e.id_typ_imnt = 'CO'

go

[bslade]

Wow, that's a really big query. Rather than give an answer, I'll ask a series of questions:

- ASE version?

- Given the search args on the main INSTRUMENT table, is it (and should it be) scanning the whole INSTRUMENT table or using an index? Ie., if the search args are supposed to use an index to only return a small percentage of the whole table, is that happening?

- Are any other big tables being scanned? (if so, and if useful/selective indexes are available, use index hints)

- Are there any worktables being created? (ASE 15.x can sometimes go overboard on work tables in order to do merge joins)

- How many lines is the showplan output?

Ben