Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dwalker79
Yak Posting Veteran
54 Posts |
 Posted - 2012-06-19 : 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-19 : 15:31:32
|
| first explain us what you're trying to do giving some sample data. then we might be able to suggest an alternative.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
dwalker79
Yak Posting Veteran
54 Posts |
Posted - 2012-06-19 : 15:40:56
|
| Here is an example of the data I'm trying to return.BUSINESS_UNIT VENDOR_ID NAME1 DESCR INVOICE_ID VOUCHER_ID (No column name) MONETARY_AMOUNT GROSS_AMT QTY_VCHR UNIT_PRICE ACCOUNT SETID VENDOR_IDCONC1 0100500065 AUTO LOCK UNLIMITED INC M32840 19695 00079134 2011-08-30 35.000 35.000 0.0000 0.00000 519500 SHARE 0100500065 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Help with sub-select