|
dwalker79
Yak Posting Veteran
USA
54 Posts |
 Posted - 06/19/2012 : 15:23:42
|
Can someone help me optimize this query and specifically the sub-select. It runs in over 20 minutes and should only be taking seconds to run. Thank you very much!!
SELECT A.BUSINESS_UNIT, A.VENDOR_ID, D.NAME1, C.DESCR, A.INVOICE_ID, A.VOUCHER_ID, (CONVERT(CHAR(10),A.INVOICE_DT,121)), C.MONETARY_AMOUNT, A.GROSS_AMT, C.QTY_VCHR, B.UNIT_PRICE, C.ACCOUNT,D.SETID,D.VENDOR_ID
FROM PS_VOUCHER A
JOIN PS_VOUCHER_LINE B
ON B.VOUCHER_ID = A.VOUCHER_ID
AND B.BUSINESS_UNIT= A.BUSINESS_UNIT
JOIN PS_DISTRIB_LINE C
ON C.BUSINESS_UNIT = B.BUSINESS_UNIT
AND C.VOUCHER_ID = B.VOUCHER_ID
AND C.VOUCHER_LINE_NUM = B.VOUCHER_LINE_NUM
JOIN PS_VENDOR D
ON D.VENDOR_ID = A.VENDOR_ID
WHERE C.DESCR <> ' '
AND A.BUSINESS_UNIT = 'CONC1'
AND A.INVOICE_DT BETWEEN '2011-06-12' AND '2012-06-12'
AND A.ENTRY_STATUS <> 'X'
AND A.CLOSE_STATUS <> 'C'
AND EXISTS (SELECT E.VOUCHER_ID
FROM PS_VOUCHER E
JOIN PS_DISTRIB_LINE F
ON E.BUSINESS_UNIT = F.BUSINESS_UNIT
AND E.VOUCHER_ID = F.VOUCHER_ID
WHERE E.BUSINESS_UNIT = A.BUSINESS_UNIT
AND E.VENDOR_ID = A.VENDOR_ID
AND F.REFERENCE_NUMBER = C.REFERENCE_NUMBER
AND E.VOUCHER_ID <> A.VOUCHER_ID)
ORDER BY A.BUSINESS_UNIT, A.VENDOR_ID
|
|
Help with sub-select
