Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Unanswered: Query is Taking too much of time around 30 mins... plz help me out folks :-(

    select distinct
    a.status,
    BILLING_TELEPHONE_NUMBER,
    LINE_OF_BUSINESS_CODE,
    ACCOUNT_CUSTOMER_CODE,
    CUSTOMER_ACCOUNT_NUMBER,
    SERVICE_NAME,
    a.SERVICE_ORDER_ID,
    STATUS,
    ORIGINAL_ORDER_TYPE,
    SUBID,
    ORDER_NUMBER,
    REGION_CODE,
    ORDER_BATCHID,
    CREATE_DATE,
    ORIGINAL_CREATE_DATE,
    COMPLETION_DATE,
    SENT_TO_BILL_DATE,
    ORIGINAL_SENT_TO_BILL_DATE,
    DUE_DATE_SUFFIX,
    RELATED_ORIGINAL_ORDER_TYPE,
    RELATED_ORDER_NUMBER,
    BIR,
    b.LINE_ITEM_SEQ_NUM,
    COMPENSABLE_PRODUCT_ID,
    WORKING_TELEPHONE_NUMBER,
    PRODUCT_CODE,
    ACTION,
    QUANTITY,
    RATE,
    CONTRACT_EFFECTIVE_DATE,
    CONTRACT_USOC_TERM,
    CONTRACT_EXPIRATION_DATE,
    ORIGINAL_PROCESS_FLAG,
    LAST_PROCESS_FLAG,
    MCC_CD,
    ISSUANCE_SENT_TO_BILL_FLAG,
    QTNUM, ltrim(SALESID),
    ISSUANCE_SHARE_PERCENT
    from acct c, service_order a, so_line_item b, so_salesid f
    where c.account_id=a.account_id and a.service_order_id=b.service_order_id and f.service_order_id=a.service_order_id
    and a.create_date >= '1/1/2011'
    and b.change_flag<>'D'
    and char(a.cancel_order_flag)<>'Y'
    and a.SENT_TO_BILL_DATE is not null
    and b.issuance_sent_to_bill_flag<>'S'
    and ltrim(rtrim(b.product_code))<>'TNC' with ur

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You will probably need to post the table DDL, including indexes, for someone to know how to help you with the performance of the SQL. However, there are a few things to consider:

    • Avoid using functions in predicates. DB2 is not Oracle (POS) and DB2 will compare 'Y' and 'Y ' as equals (unlike Oracle). Not sure why you are using the CHAR and LTRIM/RTRIM functions, but they can in some circumstances prevent indexes on these columns from being used (if indexes exist on those columns).
    • It may not improve performance in this case, but always use ISO date formats when possible ('2011-01-01'). This avoids confusion if the client is not US territory code.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    In Addition. to marcus_a's comments: Do you really need the SELECT DISTINCT? Is it any quicker if you remove it?

    If you get duplicates I would check your joins as you shouldn't need 'distinct' in a properly normalised DB.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have you current statistics on the tables?
    Have you indexes on the join columns?
    Have you indexes on the columns on which predicates are applied?
    Have you a sound configuration of the database system? (memory pool sizes etc.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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