Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Improve Performance on Select Statement

Author  Topic 

mhg1063
Starting Member

27 Posts
Posted - 2004-03-25 : 12:53:10
I have instance of SQL server installed on win 2000 server, dual processers(1.8) 2 gb of memory. I'm running a select statement to calculate some information. I'm trying to pre-populate a table with calculations to speed up reporting. I'll attach the statement. The statement takes 36 seconds to run. I was wondering if their were any suggestions for speeding up the statement.



SELECT

l.loan_id AS id,
l.profile_name AS profile,
segment_name AS segment,
'Lower Rate Scenario' AS rate_scenario,
--rp.as_of_business_day AS as_of_bus_date,
l.instrument_name AS loan_instrument,
l.investor_instrument_name AS investor_instrument,
rlp.ps_group AS ps_group,
rlp.ps_group_description AS ps_group_description,
rlp.ps_sequence AS ps_sequence,
'Locked Pipeline and Mandatory Coverage' AS group_name,
rlp.ps_group_description AS subgroup,
rlp.ps_sequence AS subgroup_sequence,
l.note_rate AS note_rate,
l.pass_thru_rate AS coupon,
NULL AS coupon_slot,
rlp.current_month_code AS current_month_code,
rlp.hedged_month_code AS hedged_month_code,
rlp.settle_month_code AS settle_month_code,
rlp.hedged_delivery_date AS hedged_delivery_date,
rlp.settle_delivery_date AS settle_delivery_date,
l.time_slot AS time_slot,
l.lr_closing_ratio AS closing_ratio,
l.lr_gain AS total_gain,
l.guarantee_fee,
l.slack,
l.buyup_limit,
CONVERT( NUMERIC(11,8),rlp.bubd) AS bubd,
l.buyup_buydn_fee,
CONVERT( NUMERIC(11,8),rlp.bubd_ratio) AS bubd_ratio,
CONVERT( NUMERIC(11,8),rlp.excess_msr) AS excess_msr,
l.msr AS msr_fee,
l.servicing_fee AS required_servicing,
CONVERT( NUMERIC(6,3),rlp.required_servicing_fee) AS required_servicing_fee,
CONVERT( NUMERIC(6,3),rlp.excess_msr_ratio) AS excess_msr_ratio,
CONVERT( NUMERIC(11,8),rlp.excess_msr_ratio * rlp.excess_msr) AS excess_msr_fee,
CONVERT( NUMERIC(11,8),l.price + (l.rate_change * l.lower_rate_beta * l.lr_dpc)) AS rs_market_price,
l.discount_points,
l.cost_basis,
l.price_adjustment,
l.rate_change,
l.rm_class,
l.investor_id,
l.zone_id,
rlp.lr_dpc_bm AS bm_dpc,
l.lr_dpc AS rs_dpc,
lr_bm_factor AS bm_factor,
ABS(lr_bm_factor) AS bm_factor_abs_multiplier,


CONVERT( NUMERIC(12,2),loan_amount) AS nofo_total_loan_vol_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code THEN l.loan_amount
ELSE 0
END) AS nofo_total_loan_vol_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+1 = hedged_month_code THEN l.loan_amount
ELSE 0
END) AS nofo_total_loan_vol_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+2 = hedged_month_code THEN l.loan_amount
ELSE 0
END) AS nofo_total_loan_vol_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+3 <= hedged_month_code THEN l.loan_amount
ELSE 0
END) AS nofo_total_loan_vol_m4,


CONVERT( NUMERIC(12,2),l.loan_amount * l.lr_closing_ratio) AS fo_total_loan_vol_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_total_loan_vol_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_total_loan_vol_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_total_loan_vol_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_total_loan_vol_m4,

l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio) AS loan_gain_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_gain_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_gain_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_gain_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_gain_m4,

CONVERT( NUMERIC(12,2),(msr/100 * l.loan_amount * l.lr_closing_ratio)) AS loan_msr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_msr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_msr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_msr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_msr_m4,


CONVERT( NUMERIC(12,2),(required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)) AS loan_bsmsr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_bsmsr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_bsmsr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_bsmsr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_bsmsr_m4,


CONVERT( NUMERIC(12,2),((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)) AS loan_xsmsr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_xsmsr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_xsmsr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_xsmsr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS loan_xsmsr_m4,




CONVERT( NUMERIC(12,2),CASE WHEN rm_class <> 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_vol_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class <> 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_vol_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code AND rm_class <> 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_vol_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code AND rm_class <> 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_vol_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code AND rm_class <> 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_vol_m4,

CONVERT( NUMERIC(12,2),CASE
WHEN rm_class <> 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_gain_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class <> 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_gain_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code AND rm_class <> 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_gain_m2,
CASE
WHEN current_month_code +2 = hedged_month_code AND rm_class <> 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END AS cl_gain_m3,
CASE
WHEN current_month_code +3 <= hedged_month_code AND rm_class <> 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END AS cl_gain_m4,

CONVERT( NUMERIC(12,2),CASE
WHEN rm_class <> 'a' THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_msr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class <> 'a' THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_msr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+1 = hedged_month_code AND rm_class <> 'a' THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_msr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+2 = hedged_month_code AND rm_class <> 'a' THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_msr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+3 <= hedged_month_code AND rm_class <> 'a' THEN (msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_msr_m4,


CONVERT( NUMERIC(12,2),CASE
WHEN rm_class <> 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_bsmsr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class <> 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_bsmsr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+1 = hedged_month_code AND rm_class <> 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_bsmsr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+2 = hedged_month_code AND rm_class <> 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_bsmsr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+3 <= hedged_month_code AND rm_class <> 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_bsmsr_m4,


CONVERT( NUMERIC(12,2),CASE
WHEN rm_class <> 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_xsmsr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class <> 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_xsmsr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+1 = hedged_month_code AND rm_class <> 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_xsmsr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+2 = hedged_month_code AND rm_class <> 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_xsmsr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+3 <= hedged_month_code AND rm_class <> 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS cl_xsmsr_m4,



CONVERT( NUMERIC(12,2),CASE
WHEN rm_class = 'a' THEN l.loan_amount
ELSE 0
END) AS nofo_rl_vol_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class = 'a' THEN l.loan_amount
ELSE 0
END) AS nofo_rl_vol_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+1 = hedged_month_code AND rm_class = 'a' THEN l.loan_amount
ELSE 0
END) AS nofo_rl_vol_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+2 = hedged_month_code AND rm_class = 'a' THEN l.loan_amount
ELSE 0
END) AS nofo_rl_vol_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+3 <= hedged_month_code AND rm_class = 'a' THEN l.loan_amount
ELSE 0
END) AS nofo_rl_vol_m4,

CONVERT( NUMERIC(12,2),CASE
WHEN rm_class = 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_rl_vol_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class = 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_rl_vol_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+1 = hedged_month_code AND rm_class = 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_rl_vol_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+2 = hedged_month_code AND rm_class = 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_rl_vol_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code+3 <= hedged_month_code AND rm_class = 'a' THEN (l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS fo_rl_vol_m4,

CONVERT( NUMERIC(12,2),CASE
WHEN rm_class = 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_gain_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND rm_class = 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_gain_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code AND rm_class = 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_gain_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code AND rm_class = 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_gain_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code AND rm_class = 'a' THEN l.lr_gain - (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_gain_m4,


CONVERT( NUMERIC(12,2),CASE
WHEN l.rm_class = 'a' THEN (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_msr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND l.rm_class = 'a' THEN (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_msr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code AND l.rm_class = 'a' THEN (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_msr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code AND l.rm_class = 'a' THEN (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_msr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code AND l.rm_class = 'a' THEN (l.msr/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_msr_m4,


CONVERT( NUMERIC(12,2),CASE
WHEN l.rm_class = 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_bsmsr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND l.rm_class = 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_bsmsr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code AND l.rm_class = 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_bsmsr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code AND l.rm_class = 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_bsmsr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code AND l.rm_class = 'a' THEN (required_servicing_fee/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_bsmsr_m4,


CONVERT( NUMERIC(12,2),CASE
WHEN l.rm_class = 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_xsmsr_total,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code >= hedged_month_code AND l.rm_class = 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_xsmsr_m1,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +1 = hedged_month_code AND l.rm_class = 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_xsmsr_m2,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +2 = hedged_month_code AND l.rm_class = 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_xsmsr_m3,
CONVERT( NUMERIC(12,2),CASE
WHEN current_month_code +3 <= hedged_month_code AND l.rm_class = 'a' THEN ((excess_msr_ratio * excess_msr)/100 * l.loan_amount * l.lr_closing_ratio)
ELSE 0
END) AS rl_xsmsr_m4,

CONVERT( NUMERIC(12,2), 0) AS mbs_vol_total,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS mbs_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_total,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS mbs_gain_m4a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_total,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_total,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS wl_m_gain_m4a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_total,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_total,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS wl_be_gain_m4a,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_total,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS fc_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_total,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS fc_gain_m4a,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_total,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS ts_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_total,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS ts_gain_m4a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_total,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_total,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS o_ts_gain_m4a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_total,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_total,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS o_mbs_gain_m4a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_total,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m1,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m2,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m3,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m4,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m1a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m2a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m3a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_vol_m4a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_total,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m1,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m2,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m3,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m4,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m1a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m2a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m3a,
CONVERT( NUMERIC(12,2), 0) AS o_fc_gain_m4a

FROM rmcat_loan l

INNER JOIN rmrep_loan_prep rlp
ON l.loan_id = rlp.loan_id AND
l.profile_name = rlp.profile_name



tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-25 : 12:57:57
Have you run SQL Profiler while executing this statement? If so, did you send the trace results through the index tuning wizard?

You have posted a very large query. Due to its size, it's almost impossible for us to read and understand it let alone help make it perform better. So with that said, are your tables properly indexed to support the query?

Tara
Go to Top of Page

mhg1063
Starting Member

27 Posts
Posted - 2004-03-25 : 13:51:28
Tables are properly indexed. Tuning wizard ran and stated if I make certain changes I would receive 59% imporovement. I made the modifications. Results were the same. The query is only on two tables. There are just alot of calculations.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2004-03-25 : 13:51:41
Holy super wide result set Batman!

Why is there no where clause??? Do you need every record?
With all of these calulations, maybe you can do the calulations into a semi-temp table and query your infomation out of this table.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-25 : 13:56:53
Are your indexes fragmented (DBCC SHOWCONTIG)? If so, have you run DBCC DBREINDEX? Are your statistics updated?

What indexes do you have on rmcat_loan and rmrep_loan_prep?

Tara
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2004-03-25 : 14:35:39
Well, I think 36 seconds for this query is pretty fast. Do you run this thing alot? It looks like it dumps stuff into a de-normalized table for reporting, so I don't think you would run it often.
If you don't run it a ton, I'd go with what you have.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-25 : 14:37:09
I was thinking 36 seconds was pretty fast too considering the amount of work it has to do.

Tara
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2004-03-25 : 14:38:53
Once this becomes an INSERT INTO...SELECT it's going to be a good bit slower methinks.

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

mhg1063
Starting Member

27 Posts
Posted - 2004-03-25 : 14:39:27
No fragments, Stats are updated nightly. rmcat_loan and loan_prep both have primary keys(clustered) (profile_name, loan_id) Tuning wizard told me to drop the index I had on instrument_name on rmcat_loan, although the table is used in other queries where the join is instument_name.
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2004-03-25 : 14:39:44
there is no WHERE clause to be found, so this query wouldn't use any indexes anyway - except for the join.

The question is, does this reporting table actually speed up your reporting?


-ec
Go to Top of Page

mhg1063
Starting Member

27 Posts
Posted - 2004-03-25 : 14:44:30
Thats what I was looking for confirmation about the time, considering what the query does. One day I hope the app populates this data since it calculates it anyway. We just discard the information, thus this monstrosity to regenerate the information. And your right the actual query gets run once per day to populate a reports table. Although user run reports against the table multiple times.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-25 : 14:45:49
quote:
Originally posted by eyechart

there is no WHERE clause to be found, so this query wouldn't use any indexes anyway - except for the join.


That's why I asked what indexes there were. But he's got the ones to support the JOIN already, so no other indexes are going to help.

Tara
Go to Top of Page

mhg1063
Starting Member

27 Posts
Posted - 2004-03-25 : 14:47:38
A where clause is not need. This is a result set I'm working with. Users need more information on the result set.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site