如何在查询中避免使用NOT IN [英] How to avoid NOT IN in query

问题描述

SELECT * FROM MFS_CM_VISUAL_PAYMENT vp
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')))

i如上查询

i将其剪切为简单查询



此查询中的NOT IN需要很长时间才能执行

任何建议为了更好的性能？

i have query as above
i cut it to make simple query

NOT IN in this query makes long time to be executed
any advises for better performance?

推荐答案

虽然我不了解你的结构和业务我尝试过优化你的SQL。请尝试以下

While having no idea about your structure and business i have tried optimizing your SQL. Try below

SELECT * 
FROM MFS_CM_VISUAL_PAYMENT vp
	LEFT OUTER JOIN 
	(
		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'
     ) D1 ON vp.PURCHASE_ORDER_ID = D1.Expr1
     LEFT OUTER JOIN 
     (
		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'
     ) D2 ON VP.PURCHASE_ORDER_ID = D2.Expr1
WHERE vp.APPROVAL_STATUS = 'A'
AND	 D1.EXPR1 IS NULL
AND  D2.EXPR1 IS NULL

我想级联嵌套SELECT比罪恶更多的罪魁祸首。



不知道你的基地是什么，他们之间有什么关系，给你提供更好的建议会很复杂。

I guess the cascading nesting SELECT is more culprit than the NOT.

Without an idea of what is in your bases and what are the relations between them, it will be complicated to give you better advice.

我认为在你的查询中第一个别名应该是 a 而不是 VP 。如果这是正确的，查询看起来像

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')))

所以优化。通常关键不在于您如何编写查询，而是数据的基础访问路径是什么。您没有提供有关索引的任何信息，因此根据查询我尝试添加以下索引（如果尚未存在）：

• MFS_CM_VISUAL_PAYMENT_LINE上的索引：
  
  列（ Document_Id，Document_Type，Visual_Payment_Id ）
• MFS_CM_VISUAL_PAYMENT上的索引：
  
  列（ Visual_Payment_Id，Is_Void，状态）
• MFS_INV_GOOD_RECEIPT上的索引：
  
  列（ Source_Doc_No，Void_Ind，Source_Type，Status，Receipt_Id ）
• MFS_INV_GOOD_RECEIPT_ITEM上的索引：
  
  列（ Receipt_ID ）
• MFS_CM_VISUAL_PAYMENT上的索引：
  
  列（ APPROVAL_STATUS ）如果状态A的数量与所有行的数量


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

SELECT * 
FROM MFS_CM_VISUAL_PAYMENT a
WHERE a.APPROVAL_STATUS    = 'A' 
AND   a.PURCHASE_ORDER_ID  <> 0 -- Can be removed if 0 cannot exist in the data
AND   a.PURCHASE_ORDER_NBR <> 0 -- Can be removed if 0 cannot exist in the data
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)



如上所述，性能将高度依赖于基数，索引和访问计划SQL Server选择这些是你可以尝试的建议。


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.




这篇关于如何在查询中避免使用NOT IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！