SQL performance question

[supermega]

I have a view that is running as slow as hell with the addition of one line ("and catastrophe_key = 1005") is included in the view, but if I do a select of the view and put the "catastrophe_key = 1005" in the where ie: select * from the_view where catastrophe_key = 1005
it runs very fast, but if that is in the view its slow, any ideas? the view is included below.

Thanks in advance,
SM



SELECT
PRODUCTION.PARAMETERS.GET_NUMBER_1() SELECTED_MONTH,
PRODUCTION.PARAMETERS.GET_NUMBER_2() v_REIN_CUST_KEY,
--PRODUCTION.PARAMETERS.GET_NUMBER_3() v_REINSURER_KEY,
--WRITTEN,
LOB,CONTRACT_STYLE,REIN_CUSTOMER_KEY,REINSURER_KEY,REIN_START_DATE,REIN_END_DATE,FRONT_NAME
,REIN_NAME,CLAIM_KEY,CATASTROPHE_KEY,CLAIMANT_NAME,INITIAL_LOSS_DATE,
DIAGNOSIS,IS_CLOSED,ORIG_CONTRACT_TYPE_KEY,CONTRACT_NUMBER,CONTRACT_NAME,
EFFECTIVE_DATE,TYPE_NAME, CLIENT_NAME,
SUM(PMT_ASSUMED_LOSS),
SUM(PMT_ASSUMED_LOSS_ADJ_EXP),
SUM(SUB_ASSUMED_RESERVE),SUM(SUB_ASSUMED_LOSS),
SUM(SUB_ASSUMED_LOSS_EXP),
SUM(SUB_ASSUMED_AUDIT_SAVINGS),
SUM((PMT_ASSUMED_LOSS+PMT_ASSUMED_LOSS_ADJ_EXP)) TOTAL_PAID,
SUM(SUB_ASSUMED_RESERVE) TOTAL_RES_BAL,
SUM((SUB_ASSUMED_RESERVE+SUB_ASSUMED_LOSS+SUB_ASSUMED_LOSS_EXP+SUB_ASSUMED_AUDIT_SAVINGS-PMT_ASSUMED_LOSS-PMT_ASSUMED_LOSS_ADJ_EXP)) OSLR,
SUM((SUB_ASSUMED_RESERVE+SUB_ASSUMED_LOSS+SUB_ASSUMED_LOSS_EXP+SUB_ASSUMED_AUDIT_SAVINGS)) TOTAL_INCURRED,
SUM((SUB_ASSUMED_LOSS+SUB_ASSUMED_LOSS_EXP+SUB_ASSUMED_AUDIT_SAVINGS-PMT_ASSUMED_LOSS-PMT_ASSUMED_LOSS_ADJ_EXP)) TOTAL_CLAIM_BAL,

SUM(DECODE(WRITTEN, PRODUCTION.PARAMETERS.GET_NUMBER_1(), (PMT_ASSUMED_LOSS+PMT_ASSUMED_LOSS_ADJ_EXP),0)) TOTAL_PAID_CURRENT_PERIOD,
SUM(DECODE(WRITTEN, PRODUCTION.PARAMETERS.GET_NUMBER_1(), (SUB_ASSUMED_RESERVE),0)) TOTAL_RES_BAL_CURRENT_PERIOD,
SUM(DECODE(WRITTEN, PRODUCTION.PARAMETERS.GET_NUMBER_1(), (SUB_ASSUMED_RESERVE+SUB_ASSUMED_LOSS+SUB_ASSUMED_LOSS_EXP+SUB_ASSUMED_AUDIT_SAVINGS-PMT_ASSUMED_LOSS-PMT_ASSUMED_LOSS_ADJ_EXP),0)) OSLR_CURRENT_PERIOD,
SUM(DECODE(WRITTEN, PRODUCTION.PARAMETERS.GET_NUMBER_1(), (SUB_ASSUMED_RESERVE+SUB_ASSUMED_LOSS+SUB_ASSUMED_LOSS_EXP+SUB_ASSUMED_AUDIT_SAVINGS),0)) TOTAL_INCURRED_CURRENT_PERIOD

FROM
VIEW_CLM_LOSS_BORD_SR
WHERE
CONTRACT_NUMBER <>'45843'
AND WRITTEN <= PRODUCTION.PARAMETERS.GET_NUMBER_1() --Jan 2003 (6083)
AND REIN_CUSTOMER_KEY = PRODUCTION.PARAMETERS.GET_NUMBER_2() --5346 -- MaxRe
--AND REINSURER_KEY = PRODUCTION.PARAMETERS.GET_NUMBER_3() --3606 -- 7/1/2001 to 7/1/2002\
--AND CATASTROPHE_KEY = 1005
/*
and crf.customer_key = 5346
*/
GROUP BY
PRODUCTION.PARAMETERS.GET_NUMBER_1(),
PRODUCTION.PARAMETERS.GET_NUMBER_2(),
LOB,CONTRACT_STYLE,REIN_CUSTOMER_KEY,REINSURER_KEY,REIN_START_DATE,REIN_END_DATE,
FRONT_NAME,REIN_NAME,
CLAIM_KEY,CATASTROPHE_KEY,CLAIMANT_NAME,INITIAL_LOSS_DATE,
DIAGNOSIS,IS_CLOSED,ORIG_CONTRACT_TYPE_KEY,CONTRACT_NUMBER,CONTRACT_NAME,
EFFECTIVE_DATE,TYPE_NAME, CLIENT_NAME

[slimdave]

Presumably a different execution plan is being used for the two versions -- can you post them?

[supermega]

well thanks SLimDave,
but for some reason they both run fast now...
im thinking it was a poltergiest
thanks anyway
SM

[marist89]

That's why slimdave gets the big bucks; he waves his hands, says some magic incantations, and poof, performance problems gone!

[supermega]

thanks slimdave, gotta show me how to do that magic hand wave, s it jedi based?

SM

[slimdave]

if everyone could do it, there'd be no big bucks.

It's a ten year course at the SOHK, anyway.

[stecal]

I love it when you do the voodoo you do.