This discussion is archived
3 Replies Latest reply: Sep 24, 2013 11:41 AM by Hoek RSS

please help me to tune this query

ramya_162 Newbie
Currently Being Moderated

Hi Experts,

 

 

The below query is taking 10 minutes to give output.

Please help me to tune this query.

 

 

WITH MARGIN AS (

SELECT sid, bid,

    SUM(qnty* prc) quantity,

    SUM(qty*cst) profit,

    SUM(qty*stc)  volume

FROM sales_dtls

GROUP BY  sid, bid)

SELECT DISTINCT

    sup.sid,

    csh.cname,

    sls.sd_cost,

    mrg.mgn_cost,

    cst.doc,

    rps.rr_rate,

    csc.ccr_cost,

    cdc.ccr_rate,

    prt.prn_loc,

    mag.manu_stat,

    pfts.prn_stat,

    mgss.date_time_mgt,

    hst.hnr_no,

    MARGIN.quantity,

    MARGIN.profit,

    MARGIN.volume

FROM sup_dtls sup

    LEFT OUTER JOIN cash_dtls csh

    ON sup.sid = csh.sid

    LEFT OUTER JOIN sales_dtls sls

    ON (sls.bid = csh.bid OR csh.bid IS NULL) AND sup.sid = sls.sid AND sls.s_no = 1

    INNER JOIN margin ON margin.sid=sup.sid AND margin.bid = sls.bid

    LEFT OUTER JOIN marg_dtls mrg ON sup.per_id = mrg.p_id

    LEFT OUTER JOIN cstr_dtls cst ON sup.cbs_id = cst.c_id

    LEFT OUTER JOIN reps_dtls rps ON rps.res_id = cst.r_id AND cst.pre = 2

    LEFT OUTER JOIN cstr_dtls csc ON sup.esc_id = csc.e_id

    LEFT OUTER JOIN cstr_dtls cdc ON sup.drd_id = cdc.d_id

    LEFT OUTER JOIN prft_dtls prt ON sup.ord_id = prt.o_id AND prt.pre = 1

    LEFT OUTER JOIN marg_dtls mag ON prt.san_id = mrg.s_id

    LEFT OUTER JOIN prft_dtls pfts ON sup.ord_id = pfts.o_id AND pfts.pre = 2

    LEFT OUTER JOIN marg_dtls mgss ON pfts.sal_id = mgss.s_id

    LEFT OUTER JOIN hist_dtls hst ON sup.ord_id = hst.o_id

    WHERE sup.date_time_supply >(SELECT MIN(last_dt) FROM dates_data)

 

 

Number of records in each table.

 

 

SELECT COUNT(*) FROM sup_dtls sup --36949

 

 

SELECT COUNT(*) FROM  cash_dtls csh--59741

 

 

SELECT COUNT(*) FROM sales_dtls sls --723168

 

 

SELECT COUNT(*) FROM  marg_dtls mrg --6425

 

 

SELECT COUNT(*) FROM  cstr_dtls cst --79760

   

SELECT COUNT(*) FROM reps_dtls rps--80264

   

SELECT COUNT(*) FROM prft_dtls prt --25452

   

SELECT COUNT(*) FROM hist_dtls hst --18276510

 

 

Please help me.

 

 

Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points