sql - 加速 sql JOIN

Question

首先，一些背景。

我们有一个订单处理系统，工作人员在应用程序中输入有关订单的账单数据，并将其存储在 sql server 2000 数据库中。这个数据库不是真正的计费系统:它只是一个保存位置，以便记录可以通过每晚的批处理过程运行到大型机系统中。

此批处理过程是由外部供应商提供的 jar 装第三方包。它应该做的部分工作是为任何被拒绝的记录提供一份报告。拒绝报告是手动处理的。

不幸的是，第三方软件并不能捕捉到所有错误。我们有单独的进程将数据从大型机拉回数据库中的另一个表，并将被拒绝的费用加载到另一个表中。

然后运行一个审计流程，以确保员工最初输入的所有内容都可以在某个地方得到说明。此审计采用我们运行的 sql 查询的形式，它看起来像这样:

SELECT *
FROM [StaffEntry] s with (nolock)
LEFT JOIN [MainFrame] m with (nolock)
    ON m.ItemNumber = s.ItemNumber 
        AND m.Customer=s.Customer 
        AND m.CustomerPO = s.CustomerPO -- purchase order
        AND m.CustPORev = s.CustPORev  -- PO revision number
LEFT JOIN [Rejected] r with (nolock) ON r.OrderID = s.OrderID
WHERE s.EntryDate BETWEEN @StartDate AND @EndDate
    AND r.OrderID IS NULL AND m.MainFrameOrderID IS NULL

当然，那是经过大量修改的，但我相信重要的部分都得到了体现。问题是这个查询开始运行的时间太长了，我正在想办法加快它的速度。

我很确定问题是从 StaffEntry 表到 MainFrame 表的 JOIN。由于两者都保存自时间开始(在本系统中为 2003 年)以来每个订单的数据，因此它们往往有点大。 StaffEntry 表中使用的 OrderID 和 EntryDate 值在导入到大型机时不会保留，这就是连接稍微复杂一点的原因.最后，由于我在 MainFrame 表中查找不存在的记录，在执行 JOIN 之后，我们在 where 子句中有那个丑陋的 IS NULL。

StaffEntry 表由 EntryDate(集群)索引，并在 Customer/PO/rev 上单独索引。 MainFrame 由客户和大型机费用编号(集群，其他系统需要)和单独的客户/PO/Rev 索引。 Rejected 根本没有索引，但它很小，测试表明这不是问题所在。

所以，我想知道是否有另一种(希望更快)的方式来表达这种关系？

Answer 1

首先，您可以去掉第二个 LEFT JOIN。

无论如何，您的 WHERE 正在删除任何匹配项...例如，如果 S.OrderID 为 1 并且存在值为 1 的 R.OrderID，则 WHERE 中的 IS NULL 强制执行将不允许这样做。所以它只会返回 s.OrderID 为 NULL 的记录，如果我没看错的话......

其次，如果您要处理大量数据，添加 NOLOCK 表提示通常不会有什么坏处。假设您不介意这里或那里脏读的可能性 :-P 不过，通常值得冒这个风险。

SELECT *
FROM [StaffEntry] s (nolock)
LEFT JOIN [MainFrame] m (nolock) ON m.ItemNumber = s.ItemNumber 
    AND m.Customer=s.Customer 
    AND m.CustomerPO = s.CustomerPO -- purchase order
    AND m.CustPORev = s.CustPORev  -- PO revision number
WHERE s.EntryDate BETWEEN @StartDate AND @EndDate
    AND s.OrderID IS NULL

最后，您的问题中有一部分我不太清楚...

"since I'm looking for records in the MainFrame table that don't exist, after doing the JOIN we have that ugly IS NULL in the where clause."

好的...但是您是否试图将其限制在那些 MainFrame 表记录不存在的地方？如果是这样，您也会希望在 WHERE 中表达它，对吗？所以像这样...

SELECT *
FROM [StaffEntry] s (nolock)
LEFT JOIN [MainFrame] m (nolock) ON m.ItemNumber = s.ItemNumber 
    AND m.Customer=s.Customer 
    AND m.CustomerPO = s.CustomerPO -- purchase order
    AND m.CustPORev = s.CustPORev  -- PO revision number
WHERE s.EntryDate BETWEEN @StartDate AND @EndDate
    AND s.OrderID IS NULL AND m.ItemNumber IS NULL

如果这就是您对原始语句的打算，也许您可​​以去掉 s.OrderID IS NULL 检查？