代码之家 › 专栏 › 技术社区 › Joel Coehoorn

加快sql连接

sql-server-2000 join performance sql-server sql

Joel Coehoorn · 技术社区 · 16 年前

首先,一些背景。

我们有一个订单处理系统,员工在一个应用程序中输入订单的账单数据,该应用程序将订单数据存储在一个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 表。由于这两个系统都保存了自2003年以来每个订单的数据,所以它们往往都有点大。这个 OrderID 和 EntryDate 中使用的值 人员录入 表在导入到大型机时不被保留,这就是为什么连接稍微复杂一点的原因。最后,因为我在 主机 不存在的表,在做了连接之后,我们有了那个丑陋的 IS NULL 在where子句中。

这个 人员录入 表按entrydate(集群)编制索引,并分别在customer/po/rev上编制索引。 主机 按客户和主机收费编号(集群,这是其他系统所需要的)编制索引,并按客户/po/rev单独编制索引。 Rejected 完全没有索引,但它很小,测试表明这不是问题所在。

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

7 回复 | 直到 16 年前

Kevin Fairchild 16 年前

首先,你可以去掉第二个左连接。

你在哪里把火柴拿走了,不管怎样…例如,如果s.orderid为1,并且存在值为1的r.orderid,则where中的is null强制不允许它。所以它只会返回s.orderid为空的记录,如果我读得正确的话…

其次,如果处理大量数据,添加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

最后,你的问题有一部分我不太清楚…

“既然我在找在主机表中记录不存在,加入后我们把那个丑陋的东西条款。

好啊。。。但是,您是否试图将其限制在那些大型机表记录不存在的地方?如果是这样的话,你也希望在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空检查?

kasperjj 16 年前

在开始更改查询之前,应该确保所有表都有一个聚集索引,该索引对该查询和所有其他重要查询都有意义。在您的表上有聚集索引在sql server中是确保正确性能的关键。

Cade Roux 16 年前

这没有道理:

SELECT *
FROM [StaffEntry] s
LEFT JOIN [MainFrame] m 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 ON r.OrderID = s.OrderID
WHERE s.EntryDate BETWEEN @StartDate AND @EndDate
    AND r.OrderID IS NULL AND s.OrderID IS NULL

如果 s.OrderID IS NULL 然后 r.OrderID = s.OrderID 不会是真的,所以 [Rejected] 因此,如前所述,它相当于:

SELECT *
FROM [StaffEntry] s
LEFT JOIN [MainFrame] m 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

你确定你发布的代码是正确的吗?

Frank V 16 年前

除了kasperjj所建议的(我确实同意应该是第一个),您还可以考虑使用临时表来限制数据量。现在,我知道了,我知道每个人都说离临时工桌远点。而我通常但有时,值得尝试一下,因为您可以使用此方法大幅缩减连接的数据量;这会使整个查询更快。(当然,这取决于可以缩小多少结果集。)

我的最后一个想法是,有时您只需要尝试使用不同的方法组合查询。这里可能有太多的变量,任何人都无法给出答案……另一方面,这里的人很聪明,所以我可能错了。

祝你好运!

当做, 弗兰克

ps:我忘了提到,如果您想尝试这个临时表方法,还需要在临时表上尝试不同的索引和主键。根据数据量的不同,索引和pks可以提供帮助。

Cade Roux 16 年前

索引所有的表将是重要的。如果对连接中使用的[Mainframe]列的索引做不了什么,也可以通过指定日期范围预先限制要在[Mainframe]中搜索的行(和[Rejected],尽管它看起来已经有一个主键),如果日期窗口应该大致相似。这可以减少右手边的连接。

我还将查看执行计划,并对您的 JOIN S真的是最贵的- m 或 r ,只使用一个或另一个基准测试查询。我想是的 米 因为多个列和缺少有用的索引。

您可以在几天或几个月内使用m.entrydate。但是,如果大型机上已经有了索引,那么问题是为什么不使用索引,或者如果正在使用索引,那么性能为什么会如此缓慢。

Mladen Prajdic 16 年前

尝试改变在r.orderid=s.orderid上使用(nolock)的左联接[拒绝的]r 进入右合并联接:

SELECT ...
FROM [Rejected] r
     RIGHT MERGE JOIN [StaffEntry] s with (nolock) ON r.OrderID = s.OrderID
     LEFT JOIN [MainFrame] m with (nolock) ON....

Joel Coehoorn 16 年前

更新:
如果这还不明显,我在代码中对原来的问题犯了个错误。现在已经解决了,但不幸的是,这意味着一些更好的回应实际上是完全错误的。

我还有一些统计更新:通过严格限制与 StaffEntry.EntryDate 。不幸的是,我只能这样做,因为在运行了很长的路,一旦我知道确切的日期我关心。我通常事先不知道。

原始运行的执行计划显示在 StaffEntry 表,以及索引查找的11%成本 MainFrame 表,然后连接本身的0%开销。使用窄日期范围运行它,对于 人员录入 ,1%用于“大型机”的索引查找,93%用于 Rejected . 这些是“实际”计划,不是估计的。