Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    1

    Question Unanswered: Teradata - Help Optimize SQL

    Hi everybody,

    Need the help of a Teradata expert on how the SQL below can be optimized. It's currently consuming a lot of CPU resources and using a lot of spool space.

    Can anybody help?
    SELECT tb1.store_id
    ,tb1.upc_id
    ,'10/10/2006' (date, format 'mm/dd/yyyy')
    ,(SUM(tb1.base_qty) - MIN(tb1.base_qty) - MAX(tb1.base_qty)) / (COUNT(tb1.txn_dt) - 2)
    ,(SUM(tb1.base_amt) - MIN(tb1.base_amt) - MAX(tb1.base_amt)) / (COUNT(tb1.txn_dt) - 2)
    FROM orca_db.txn_dtl tb1
    GROUP BY 1,2,3
    HAVING COUNT(tb1.txn_dt) >= 6;

    Thanks
    Rick

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    without knowing the full details of what you're wanting to do - I suggest you run the query a number of times, removing one line at a time.
    Narrow down the line(s) that are causing the issue then perhaps we can optomize those.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2011
    Posts
    1

    Urgent help in teradata

    can u please help me in optimizing the query?

    SELECT (V001.BTN_NPA_CD||V001.BTN_NXX_CD||V001.BTN_LINE_N BR) BTN,MAX(V001.DW_LOAD_DT) DW_LOAD_DT FROM

    VBTF001_TELCO_REVENUE B,VCTD700 A
    WHERE A.BTN_NPA_CD = B.BTN_NPA_CD

    AND A.BTN_NXX_CD = B.BTN_NXX_CD

    AND A.BTN_LINE_NBR = B.BTN_LINE_NBR

    AND A.REPTD_SERV = ''

    AND A.RPT_CAT = 'CR'

    AND A.DISP LIKE ANY ('03%','04%','05%','07%',

    '08%','09%','11%','12%') group by 1

    As B table has huge records, so it was taking more than allocate space

Posting Permissions

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