加速sql JOIN

首先,一些背景.

我们有一个订单处理系统,员工在应用程序中输入有关订单的帐单数据,该应用程序将其存储在sql server 2000数据库中.这个数据库不是真正的计费系统:它只是一个保留位置,因此记录可以通过夜间批处理进入大型机系统.

此批处理过程是由外部供应商提供的固定第三方软件包.它应该做的部分工作是为任何被拒绝的记录提供报告.拒绝报告是手动完成的.

不幸的是,事实证明第三方软件没有捕获所有错误.我们有单独的进程将数据从大型机拉回到数据库中的另一个表中,并将被拒绝的费用加载到另一个表中.

然后运行审计流程以确保工作人员最初输入的所有内容都可以在某处进行说明.此审计采用我们运行的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年)开始保存每个订单的数据,因此它们往往有点大.导入到大型机时,表中使用的OrderID和EntryDate值StaffEntry不会被保留,这就是为什么该连接稍微复杂一些.最后,因为我在MainFrame表中查找不存在的记录,所以在执行JOIN之后我们IS NULL在where子句中有那么难看.

该StaffEntry表由EntryDate(群集)索引,并在Customer/PO/rev上单独编制索引. MainFrame由客户和主机收费编号(集群,其他系统需要)和客户/ PO/Rev分别编制索引. Rejected根本没有索引,但它很小,测试显示它不是问题.

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

首先,你可以摆脱第二次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

最后,你的一部分问题对我来说不太清楚......

"因为我在MainFrame表中查找不存在的记录,所以在执行JOIN之后,我们在where子句中有了那个丑陋的IS NULL."

好的......但是你试图将它限制在那些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检查？