SQL performance question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL performance question

  1. #1
    Join Date
    Oct 2000
    Posts
    103

    Question SQL performance question

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Presumably a different execution plan is being used for the two versions -- can you post them?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2000
    Posts
    103
    well thanks SLimDave,
    but for some reason they both run fast now...
    im thinking it was a poltergiest
    thanks anyway
    SM

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    That's why slimdave gets the big bucks; he waves his hands, says some magic incantations, and poof, performance problems gone!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2000
    Posts
    103
    thanks slimdave, gotta show me how to do that magic hand wave, s it jedi based?

    SM

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    if everyone could do it, there'd be no big bucks.

    It's a ten year course at the SOHK, anyway.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    I love it when you do the voodoo you do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width