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

    Unanswered: removing CTE from query

    Hi, i have a query taking little long time.
    i thought to remove the CTE and write it as a sub select .how to write that and will that improve the performance if i do that way.



    WITH T1 AS
    (
    SELECT tf.T_iden,
    SUM ( od.WEIGHT_QY ) AS TotWt
    FROM dev.order_detail od
    INNER JOIN dev.order o ON o.o_iden = od.o_iden
    INNER JOIN dev.t_order tor ON tor.o_iden = od.o_iden
    INNER JOIN dev.tour_fact tf ON tf.T_iden = tor.T_iden
    GROUP BY tf.T_iden
    )
    SELECT o.SHIPPER_NM,
    sh.CITY_NM AS s_cty,
    sh.STATE_CD AS s_st,
    o.c_nb AS DestNM,
    con.CITY_NM AS DestCity,
    con.STATE_CD AS DestSt,
    od.o_iden,
    tor.T_iden,
    COUNT ( od.o_iden ) AS OrderCt,
    ( tf.TOTAL_REVENUE * od.WEIGHT_QY / gt.TotWt ) AS LaneCost,
    od.WEIGHT_QY AS LaneWt,
    tf.TOTAL_REVENUE AS TotCost
    FROM dev.order_detail od
    INNER JOIN dev.order o ON o.o_iden = od.o_iden
    INNER JOIN dev.t_order tor ON tor.o_iden = od.o_iden
    INNER JOIN dev.shipper_dim sh ON sh.shipper_id = o.SHIPPER_ROLE_ID
    INNER JOIN dev.consignee_dim con ON con.CONSIGNEE_ID = o.CONSIGNEE_ROLE_ID
    INNER JOIN dev.tour_fact tf ON tf.T_iden = tor.T_iden
    INNER JOIN T1 gt ON gt.T_iden = tf.T_iden
    WHERE PROJECT_ID = 11
    AND tf.tour_status_id NOT IN ( 141, 139, 011, 2190, 9442, 7897 )
    AND DATE ( tf.ACTUAL_DEPARTURE_TS ) BETWEEN '2007-10-31' AND '2007-11-31'
    GROUP BY od.o_iden,
    tor.T_iden,
    od.WEIGHT_QY,
    gt.TotWt,
    o.SHIPPER_NM,
    sh.CITY_NM,
    sh.STATE_CD,
    o.c_nb,
    con.CITY_NM,
    con.STATE_CD,
    tf.TOTAL_REVENUE
    ORDER BY con.STATE_CD,
    con.CITY_NM,
    sh.STATE_CD,
    sh.CITY_NM

    The db is db2 V8.1 fp 12 with os as solaris

    any help would be appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I doubt that using a subselect will increase performance. Everyplace you are referencing gt.totwt in the select will need to have the subselect.

    Ways that I can see to improve performance:

    1) remove unnecessary joins (see the CTE section of you query)
    2) make sure that you have appropriate indexes (use the Adviser)

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did you already have a look at the plan to verify that DB2 didn't simply inline the CTE already?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2007
    Posts
    30
    i am trying the differnet ways.so i thought of writing a sub select instead of CTE.so can u plz help me out in writing a sub select instead of cte.
    thanks...

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Just copy the block from the CTE into the FROM clause. Add a bit of decent formatting, and you get this:
    Code:
    SELECT o.SHIPPER_NM, sh.CITY_NM AS s_cty, sh.STATE_CD AS s_st, o.c_nb AS DestNM,
           con.CITY_NM AS DestCity, con.STATE_CD AS DestSt, od.o_iden, tor.T_iden,
           COUNT ( od.o_iden ) AS OrderCt, ( tf.TOTAL_REVENUE * od.WEIGHT_QY / gt.TotWt ) AS LaneCost, od.WEIGHT_QY AS LaneWt, tf.TOTAL_REVENUE AS TotCost
    FROM   dev.order_detail od
              INNER JOIN
           dev.order o ON o.o_iden = od.o_iden
              INNER JOIN
           dev.t_order tor ON tor.o_iden = od.o_iden
              INNER JOIN
           dev.shipper_dim sh ON sh.shipper_id = o.SHIPPER_ROLE_ID
              INNER JOIN
           dev.consignee_dim con ON con.CONSIGNEE_ID = o.CONSIGNEE_ROLE_ID
              INNER JOIN
           dev.tour_fact tf ON tf.T_iden = tor.T_iden
              INNER JOIN
           ( SELECT tf.T_iden, SUM ( od.WEIGHT_QY ) AS TotWt
             FROM   dev.order_detail od
                       INNER JOIN
                    dev.order o ON o.o_iden = od.o_iden
                       INNER JOIN
                    dev.t_order tor ON tor.o_iden = od.o_iden
                       INNER JOIN
                    dev.tour_fact tf ON tf.T_iden = tor.T_iden
             GROUP BY tf.T_iden ) gt ON gt.T_iden = tf.T_iden
    WHERE  PROJECT_ID = 11 AND
           tf.tour_status_id NOT IN ( 141, 139, 011, 2190, 9442, 7897 ) AND
           DATE ( tf.ACTUAL_DEPARTURE_TS ) BETWEEN '2007-10-31' AND '2007-11-31'
    GROUP BY od.o_iden, tor.T_iden, od.WEIGHT_QY, gt.TotWt,
                  o.SHIPPER_NM, sh.CITY_NM, sh.STATE_CD, o.c_nb,
                  con.CITY_NM, con.STATE_CD, tf.TOTAL_REVENUE
    ORDER BY con.STATE_CD, con.CITY_NM, sh.STATE_CD, sh.CITY_NM
    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
  •