I take it that in your query the first alias should be
a
instead of
vp
. If that's correct the query looks like
SELECT * FROM MFS_CM_VISUAL_PAYMENT a
WHERE a.APPROVAL_STATUS = 'A'
AND (a.PURCHASE_ORDER_ID NOT IN
(SELECT ISNULL(VPL.DOCUMENT_ID, 0) AS Expr1
FROM dbo.MFS_CM_VISUAL_PAYMENT AS VP INNER JOIN
dbo.MFS_CM_VISUAL_PAYMENT_LINE AS VPL ON VP.VISUAL_PAYMENT_ID = VPL.VISUAL_PAYMENT_ID
WHERE (VP.STATUS <> 'R') AND (VP.IS_VOID = 'N') AND (VPL.DOCUMENT_TYPE = 'PO'))
)
AND (a.PURCHASE_ORDER_NBR NOT IN
(SELECT ISNULL(GRN.SOURCE_DOC_NO, 0) AS Expr1
FROM dbo.MFS_INV_GOOD_RECEIPT AS GRN INNER JOIN
dbo.MFS_INV_GOOD_RECEIPT_ITEM AS GRNI ON GRN.RECEIPT_ID = GRNI.RECEIPT_ID
WHERE (GRN.STATUS <> 'R') AND (GRN.VOID_IND = 0) AND (GRN.SOURCE_TYPE = 'SUP')))
So to the optimization. Very often the key thing isn't how you write the query but what are the underlying access paths to the data. You didn't provide any info on the indexes so based on the query I'd try adding the following indexes if not already present:
- Index on MFS_CM_VISUAL_PAYMENT_LINE:
Columns (Document_Id, Document_Type, Visual_Payment_Id
) - Index on MFS_CM_VISUAL_PAYMENT:
Columns (Visual_Payment_Id, Is_Void, Status
) - Index on MFS_INV_GOOD_RECEIPT:
Columns (Source_Doc_No, Void_Ind, Source_Type, Status, Receipt_Id
) - Index on MFS_INV_GOOD_RECEIPT_ITEM:
Columns (Receipt_ID
) - Indexes on MFS_CM_VISUAL_PAYMENT:
Columns (APPROVAL_STATUS
) if the amount of statuses A is very small compared to the number of all rows
Depending on the cardinality of each column the order may need to be changed.
Now what comes to the query, if both of the sub queries can have multiple rows with same returned key I would use NOT EXISTS instead of IN. Also I would eliminate all rows having NULL in the returned keys since you replace all of those with static value 0.
Based on those the query could look something like:
SELECT *
FROM MFS_CM_VISUAL_PAYMENT a
WHERE a.APPROVAL_STATUS = 'A'
AND a.PURCHASE_ORDER_ID <> 0
AND a.PURCHASE_ORDER_NBR <> 0
AND NOT EXISTS ( SELECT 1
FROM dbo.MFS_CM_VISUAL_PAYMENT AS VP
INNER JOIN dbo.MFS_CM_VISUAL_PAYMENT_LINE AS VPL
ON VP.VISUAL_PAYMENT_ID = VPL.VISUAL_PAYMENT_ID
WHERE VP.STATUS <> 'R'
AND VP.IS_VOID = 'N'
AND VPL.DOCUMENT_TYPE = 'PO'
AND VPL.DOCUMENT_ID IS NOT NULL
AND VPL.DOCUMENT_ID = a.PURCHASE_ORDER_ID)
AND NOT EXISTS ( SELECT 1
FROM dbo.MFS_INV_GOOD_RECEIPT AS GRN
INNER JOIN dbo.MFS_INV_GOOD_RECEIPT_ITEM AS GRNI
ON GRN.RECEIPT_ID = GRNI.RECEIPT_ID
WHERE GRN.STATUS <> 'R'
AND GRN.VOID_IND = 0
AND GRN.SOURCE_TYPE = 'SUP'
AND GRN.SOURCE_DOC_NO IS NOT NULL
AND GRN.SOURCE_DOC_NO = a.PURCHASE_ORDER_NBR)
As said the performance will depend highly on the cardinalities, indexes and the access plan SQL server chooses so these are suggestions you can try.