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.

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

Did you already have a look at the plan to verify that DB2 didn't simply inline the CTE already?

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...

Just copy the block from the CTE into the FROM clause. Add a bit of decent formatting, and you get this: