SQL Where 子句大大增加了查询时间

Question

我有一个表，我对其进行了一些连接和操作。该表有大约 150,000 行，如果我全选并运行它，它会在大约 10 秒内返回。如果我将查询创建到它自己的表中，并过滤掉特定字段为空的所有行，那么现在查询需要 10 分钟才能运行。它应该是这样的还是有什么办法可以解决它？这是查询。

SELECT *
FROM
(
    Select 
     I.Date_Created
    ,I.Company_Code
    ,I.Division_Code
    ,I.Invoice_Number
    ,Sh.CUST_PO
    ,I.Total_Quantity
    ,ID.Total
    ,SH.Ship_City City
    ,CASE WHEN SH.Ship_Cntry <> 'US' THEN 'INT' ELSE SH.Ship_prov END State
    ,SH.Ship_Zip Zip
    ,SH.Ship_Cntry Country
    ,S.CustomerEmail
    from [JMNYC-AMTDB].[AMTPLUS].[dbo].Invoices I (nolock)
    LEFT JOIN (SELECT 
                    ID.Company_Code
                    ,ID.Division_Code
                    ,ID.Invoice_Number
                    ,SUM (ID.Price* ID.Quantity) Total
                    FROM [JMNYC-AMTDB].[AMTPLUS].[dbo].Invoices_Detail ID (nolock)
                    GROUP BY ID.Company_Code, ID.Division_Code, ID.Invoice_Number) ID 
            ON I.Company_Code = ID.Company_Code
            AND I.Division_Code = ID.Division_Code
            AND I.Invoice_Number = ID.Invoice_Number
    LEFT JOIN 
        [JMDNJ-ACCELSQL].[A1WAREHOUSE].[dbo].SHIPHIST SH (nolock) ON I.Pickticket_Number = SH.Packslip
    LEFT JOIN 
        [JMDNJ-ACCELSQL].[A1WAREHOUSE].[dbo].[MagentoCustomerEmailData] S on SH.CUST_PO = S.InvoiceNumber

    Where I.Company_Code ='09' AND I.Division_Code = '001'
    AND I.Customer_Number = 'ECOM2X'
    )T
    Where T.CustomerEmail IS NOT NULL -- This is the problematic line
Order By T.Date_Created desc 

Answer 1

如果您知道 Index Considerations并且你确定了问题点，那么你可以使用它来改进它:

USE A1WAREHOUSE;
GO

CREATE NONCLUSTERED INDEX IX_MagentoCustomerEmailData_CustomerEmail
ON [dbo].[MagentoCustomerEmailData] (CustomerEmail ASC);

GO

总的来说，您需要在 ORDER BY、WHERE、GROUP BY、ON 中使用的列添加索引> 等部分。在添加索引之前，请确保您了解后果。

阅读更多关于索引的信息:

1. https://www.mssqltips.com/sqlservertutorial/9133/sql-server-nonclustered-indexes/
2. https://www.itprotoday.com/sql-server/indexing-dos-and-don-ts