Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    30

    Unanswered: query optimization

    hi,
    i ahve query taking long time to execute probably 4 mins.is there any way to improce this SQL.it using the indexes and there are no tabe scans going on. so i wonder whether or not this query could be modified to perform better.
    any thoughts?..
    db2 V8.2fp5 solaris

    with t1 as
    ( SELECT TF.journ_ID,
    C.NAME AS Carrier,
    TS.DESCRIPTION AS journ_Status,
    TF.design_DEPARTURE_TS,
    TF.design_ARRIVAL_TS,
    TF.ACTUAL_ARRIVAL_TS,
    BR.br_id,
    rtrim (udb.get_value (32, TF.journ_ID, 4964)) AS journ_Load_Type
    FROM udb.journ_FACT TF
    INNER JOIN udb.CARRIER_Dim C
    ON TF.ACTUAL_CARRIER_ID = C.CARRIER_ID
    JOIN udb.journ_STATUS_DIM TS
    ON TF.journ_STATUS_ID = TS.journ_STATUS_ID
    JOIN udb.cd_dt CD
    ON CD.cal_DATE_KEY = design_DEPARTURE_id
    AND (CD.cal_DT between '2008-01-01' And '2008-04-05')
    Inner Join udb.BILL_TO_ROLE_Dim BR On TF.br_id = BR.br_id
    WHERE TF.PROJECT_ID = 58
    AND TF.journ_STATUS_ID NOT IN (1785630, 1241356, 7961312, 93722943)
    And BR.br_id <> 15894 and
    Coalesce(rtrim(udb.get_value(86, TF.journ_ID, 9119)),'mni') not in ('25')
    )select * from t1

    thanks in advance

  2. #2
    Join Date
    Jan 2008
    Posts
    45
    Can you post your explain plan for that query here...db2exfmt output would be better..Please attach that if you have..

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Any reason why you put the query in a common table expression and then do a "SELECT *" from the CTE? You could just use the CTE as select statement - and "SELECT *" is bad practice anyway.
    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
  •