主页 / dba / 问题 / 163238
Accepted
M.Ali
Asked: 2017-02-05 15:24:54 +0800 CST

连接两个具有 MAX 值的表的最有效方法

  • 772

我正在优化一个查询，我必须在其中连接两个表，比如说Product和TransactionHistory表，并从两个表中返回多个列作为表中每个产品的表中的最后TransactionDate一个TransactionHistory列Product。

TransactionHistory表有大约 1300 万行，Product表有近 200 万行。

听起来很容易，不是吗？但是对于不同的场景，不同的查询执行不同。

我在这里没有使用我的实际表名，以显示我尝试过的不同查询语法，我使用的是 Adam Machanic 的 BigProduct 和 BigSalesHistory 表。

查询最初是这样写的......

SELECT p.ProductID
     , p.Name
     , h.Quantity
     , h.TransactionDate
FROM [dbo].[bigProduct] p
INNER JOIN [dbo].[bigTransactionHistory] h ON p.ProductID = h.ProductID
WHERE h.TransactionDate = ( SELECT MAX(TransactionDate)
                            FROM [dbo].[bigTransactionHistory]
                            WHERE ProductID = h.ProductID )
AND p.ProductID > 1317
AND p.ProductID < 1416
GO

这导致了大约 30 次扫描计数和近 300,000 次逻辑读取。

作为窗口函数的忠实粉丝，我使用 编写了以下查询ROW_NUMBER()，但都适得其反：

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
INNER JOIN ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID 
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
            ) c
            ON p.ProductID = c.ProductID AND rn = 1
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

和

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID 
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = ProductID
            ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
  AND rn = 1
GO

Scan 计数达到 100 秒，逻辑读取数跃升至 700,000 秒。

对窗口函数的性能感到非常失望，我使用重写了查询CROSS APPLY 

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT TOP 1  h.ProductID
                          , h.Quantity
                          , h.TransactionDate
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = h.ProductID
              ORDER BY h.TransactionDate 
             ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

现在这个查询似乎在上面提到的所有内容中表现最好，但是一旦 OUTER Query 的 Range forProductID增加，这个查询就会出现可怕的错误，并且开始表现比上述两个更差。

此外，即使它将逻辑读取次数减少到 27000，但 CPU 时间和经过的时间却增加了 8 倍。

有没有更有效的方法来编写这样的查询，记住 ProductID 的外部查询范围变化很​​大？

非常感谢任何正确方向的建议或指示。谢谢你。

sql-server sql-server-2014

1 个回答

  1. Best Answer

    如果您有一个基于纸张的系统，您需要考虑如何解决这个问题。

    喜欢 - 你想一个一个地使用每个 ProductID 并找到最新的吗？然后，在（ProductID，交易日期 DESC）的索引上使用 TOP 1 调用 Seek 的嵌套循环将是最好的。

    您想搜索所有交易并按 ProductID 分组并获取最大值吗？那么这种方法可能是最好的。

    但我建议你在现实生活中做的是对数据的子集（例如最近一个月）执行第二种方法，然后对这些数据进行逐产品搜索那没有用。尝试对按 ProductID 分组并按 TransactionDate 过滤为“相当近期”的子查询进行外部联接。然后执行 Outer Apply 以执行 Top 1，但在其中包含一个谓词，例如where m.ProductID IS NULL，以便它只对第一轮错过的产品执行那些 Seeks。您应该看到一个带有 Startup Expression Predicate 的过滤器，并注意到 Index Seek 的执行次数要少得多。

    我在http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/展示了这种东西- 寻找我展示的计划，在示例中包含两个外部应用位。

    • 2

相关问题