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

    Unanswered: simplyfy the query

    Hi all

    i have a query running on db2 V8.5 on solaris environment.its running against a datawarehouse.

    can any one plz tell me how to make it simple.is there any way that i can break the query into simpler query

    select dr.no_tx,
    o.create_user_cd,
    OD.WEIGHT_TYPE_ID,
    od.weight_qy,
    CASE WHEN WEIGHT_TYPE_ID = 1973
    THEN
    CAST(CAST(weight_qy AS DECIMAL(21,4))* CAST(2.2046 AS DECIMAL (21,4)) AS DECIMAL(21,4))
    ELSE
    WEIGHT_QY
    END as k_t_l_wt
    pord.t_iden,
    pord.order_id,
    TF.TOTAL_REVENUE,
    TF.SCHEDULED_DEPARTURE_TS,
    TF.con_rl_id,
    tf.ACTUAL_CARRIER_ID,
    rtrim(dw.get_attri_value (32, pord.t_iden, 4964)) as tr_md,
    rtrim(dw.get_attri_value(31, rs.st_id, 4934)) as Vendor_Num


    from dpo dr
    INNER JOIN dw.placement_order pord ON dr.ORDER_ID = pord.ORDER_ID
    Inner join dw.placement_fact tf on pord.t_iden = tf.t_iden
    AND Tf.T_st_id NOT IN (5740,8485,1202,0921,9234,1991)

    Inner join dw.order o on pord.order_id = o.order_id
    AND O.STATUS_ID NOT IN (1189, 9901, 3061, 2227, 3106, 4351)

    Inner join dw.placement_detail od on od.order_id = o.order_id

    Left join dw.st_rl rs on tf.s_id = rs.role_id
    and rs.type_id = 8678
    and rs.status_id = 2070
    where tf.t_iden = 8528
    ORDER BY dr.no_tx

    actually its a part of a big query, i want this query to be written in simpler terms.the big query is giving an error msg which is
    SQL0437W Performance of this complex query may be sub-optimal. Reason code: "3". SQLSTATE=01602

    0 Rows selected

    when i am running this part of the query this error is coming.the statements which are executed prior to this query are not giving any problem.



    Thanks!!!

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    can you post the explain. It would help me much to know which part of SQL is not optimal written.
    Please also post primary keys columns, foreign keys columns, indexes etc. More info better.
    Regards,
    Grofaty
    Last edited by grofaty; 12-21-07 at 05:14.

  3. #3
    Join Date
    Dec 2007
    Location
    Amsterdam, NL
    Posts
    2
    dear dareman123

    First: let DB2 calculate its access path from itself, so rewrite "from T1 inner join T2 on clause T1T2" into "from T1, T2 where clause T1T2.
    Secondly: when I read the query I am not sure you really need the left join operation. When you only mean to have rows connected to "rs.type_id = 8678
    and rs.status_id = 2070" you better use an inner join.
    Thirdly: you should have indexes on rs.type_id, together with rs.status_id and on tf.t_iden (which already must be there being Primary Key).

    Regards,
    FdeGrijs

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    you might want to omit pord.t_iden in the SELECT - clause.
    It is a constant value ( 8528 ). I assume, the expression

    rtrim(dw.get_attri_value (32, pord.t_iden, 4964)) as tr_md

    is constant, too

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you are really after improving performance not simplifying, as the query is pretty straight forward. There isn' much can be done as there doesn't seem to be any real limiting criteria in here, unless the TF.T_IDEN limits you to a set number of rows. Can you change the NOT IN clauses to IN clauses? Meaning instead of saying I don't want to see these, say I want to see this othere set of status ids. Otherwise just ensure you have indexes on the join keys and maybe some of the other fields trailing those.

Posting Permissions

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