MMdist

We wrote the sql query below to pull non-stock item usage but it takes a long time to run. We added the sql to a Crystal Report and published it to our LBI dashboard for our Purchasing department to use. Is there a way to speed up this query?

 

 

select

pli.company,

pli.po_number,

pli.po_release,

pli.po_code,

pli.vendor,

pli.line_nbr as line_nbr, 

pcr.tot_order_amt,

pcr.dropship_fl,

pcr.open_po_amt,

pcr.term_code,

mmd.acct_unit,

mmd.account,

mmd.sub_acct,

mmd.dist_company,

mmd.activity,

mmd.acct_category,

mmd.system_cd,

mmd.doc_type,

mmd.line_seq,

mmd.dist_percent,

mmd.dist_qty,

mmd.dist_amount,

src.source_doc_n,

gln.acct_unit,

gln.description,

pcr.dflt_src_doc,

pcr.po_date,

pcr.location,

pli.line_nbr, pli.item,

pli.description,

pli.manuf_code,

pli.manuf_division,

pli.manuf_nbr,

pli.ent_unit_cst,

pli.ent_buy_uom,

pli.quantity,

ven.vendor_vname,

pli.item_type,

pli.location,

pli.closed_fl

from lawprod.poline pli

               left join lawprod.mmdist mmd on  (pli.company = mmd.company

                                       and 'PO' = mmd.system_cd

                                       and pli.location = mmd.location

                                       and 'PT' = mmd.doc_type

                                       and pli.po_number = mmd.doc_number

                                        and pli.po_release = mmd.doc_nbr_num

                                        and pli.po_code = mmd.po_code

                                        and pli.line_nbr = mmd.line_nbr)     

    join lawprod.purchorder pcr on (pli.company = pcr.company

          and pli.po_number = pcr.po_number

          and pli.po_release = pcr.po_release

          and pli.po_code = pcr.po_code)

    left join lawprod.polinesrc src

       on (pli.company = src.company

         and pli.po_number = src.po_number

         and pli.po_release = src.po_release

         and pli.po_code = src.po_code

         and pli.line_nbr = src.line_nbr)

left join lawprod.apvenmast ven

     on (pli.vendor = ven.vendor)

left join lawprod.glnames gln

               on (mmd.acct_unit = gln.acct_unit

                              and mmd.company = gln.company)         

           where pli.item_type <> 'I'

            and po_date between {?begin_date} and {?end_date}