SQL查询 - 需要提高性能

Question

我有一个数据加载场景，需要创建动态SQL查询来拉取数据并在我们的服务中进行缓存。有一个包含所有产品数据的表：ProductHistory（47列，200,000条记录+且将继续增长）

我需要什么： 通过使用最大ID、最大版本和最大更改ID获取最新的产品。

第一次尝试：

SELECT distinct Product.* FROM ProductHistory product 
WHERE  product.version = 
(SELECT max(version) from ProductHistory p2 where product.Id = p2.Id 
  and product.changeId = 
(SELECT max(changeid) from ProductHistory p3 where p2.changeId = p3.changeId))

这花费了超过2.51分钟。

其他失败尝试：

select distinct product.* from ProductHistory product 
where CAST(CAST(id as nvarchar)+'0'+CAST(Version as nvarchar)+'0'+CAST(changeid as nvarchar) as decimal) = 
(select MAX(CAST(CAST(id as nvarchar)+'0'+CAST(Version as nvarchar)+'0'+CAST(changeid as nvarchar) as decimal)) from ProductHistory p2 
where product.Id = p2.Id)

它基本上使用与订购日期相同的原理，将按相关性排序的数字连接在一起。

For example 11 Jun 2007 = 20070711
And in our case: Id = 4 , version = 127, changeid = 32   => 40127032
The zeros are there not to mix up the 3 different ids

但是这个查询需要3.10分钟！ :(

因此，我基本上需要一种方法来改进我的第一次尝试的查询。我也在想，有这么多数据，这是我应该期望的最佳检索速度吗？

1. 我运行了 sp_helpindex ProductHistory 并发现以下索引：

   PK_ProductHistoryNew-聚集，唯一，主键位于PRIMARY- Id，Version

2. 我将第一个查询包装在SP中，但仍然没有变化。

那么，我们可以通过什么其他方式来提高此操作的性能呢？

谢谢， Mani p.s：我只是在SQL管理工具中运行这些查询以查看时间。

- Everything Matters

---

第一个查询需要2.51分钟还是秒？ - Ben Robinson

谢谢Ben。已编辑以更正时间。它是以分钟为单位的。 - Everything Matters

Answer 1

在Sql Server Management Studio中运行查询并查看查询计划，以确定瓶颈所在。任何地方都可以看到“table scan”或“index scan”，都需要扫描所有数据才能找到所需内容。如果创建适当的索引来执行这些操作，则应该可以提高性能。

- Anders Abel

---

另一件需要注意的事情是，您是否需要在SELECT语句中使用Product表中的所有列。 - Neil Knight

我基本上是一个.NET组件开发人员，对SQL还比较新。如果有更多关于Anders点的解释，将有助于我进一步调试。我已经提到了当前表上的索引。你有什么具体的想法可以帮助我做得更好吗？ - Everything Matters

这很棒，它运行得更快。 - user3497034

Answer 2

我看到的一些问题：

• DISTINCT 是必需的吗？如果你使用 DISTINCT *，它很可能没有任何好处，但会有开销来检查所有字段中的重复项。
• 不要在 WHERE 子句中使用两个子查询，而是连接一个派生表。这应该只处理一次。我怀疑你的 WHERE 子句正在多次处理。

<-- -->

SELECT Product.* 
FROM ProductHistory product 
INNER JOIN ( SELECT P.Id, 
                    MAX(p.version) as [MaxVer], 
                    MAX(p.Changeid) as [MaxChange]
             FROM Product p
             GROUP BY p.ID) SubQ
    ON SubQ.ID = product.ID
    AND SubQ.MaxChange = Product.ChangeID
    AND SubQ.MaxVer = Product.Version

对于这个问题，你还需要在Id, Version, ChangeID上建立一个索引。

- JNK

---

@Mani：我建议你尝试这个查询（同时也把Changeid移除，两行都是：MAX(p.Changeid) as [MaxChange]和AND SubQ.MaxChange = Product.ChangeID）。因为你已经有了一个主键(Id, Version)，所以它会更快。而且可能你并不需要你尝试的复杂方法。 - ypercubeᵀᴹ

Answer 3

嗯，把所有东西都存储在表中不是正确的方法。 更好的方法是将最新版本存储在一个表中，并使用另一个具有相同结构的表来存储历史记录（因为我猜你更关心当前产品而不是旧产品）。概念问题会创建许多解决方法...

此外，不要使用DISTINCT，因为它经常隐藏查询中的问题（通常，如果检索到重复项，则意味着您可以更好地进行优化）。

现在，最好的部分：如何解决您的问题？我想您应该使用分组原则，得到类似这样的结果：

SELECT max(id), max(version), max(changeid) 
  FROM ProductHistory p
  WHERE <filter if necessary for old products or anything else>
  GROUP BY version, changeid
  HAVING version = max(version)
     AND changeid = max(changeid)
     AND id = max(id)

但是，如果我查看您的PK，我会感到惊讶，因为changeid并不相关，您只需要处理id和版本...

我不确定我的请求是否完全正确，因为我无法测试，但我猜您可以进行一些测试。

- William R

---

您提供的查询与 OP 的查询不相等。 - ypercubeᵀᴹ

Answer 4

这有点复杂，但我想分区是否可行：

  SELECT Id
  FROM (
      SELECT Id,
      MAX(version) OVER (PARTITION BY changeId) max_version
      FROM ProductHistory
  ) s
  where version = s.max_version

- Ash Eldritch

Answer 5

我有一种感觉，随着行数的增加，这个查询将需要更长时间，但还是值得一试：

SELECT * FROM 
(
SELECT Col1, Col2, Col3,
ROW_NUMBER() OVER (PARTITION BY ProductHistory.Id ORDER BY Version DESC, ChangeID DESC) AS RowNumber 
FROM ProductHistory
)
WHERE RowNumber = 1

- mohdowais

Answer 6

尝试使用CTE，它应该是最快的选项，而且您可能甚至不需要索引来获得很快的速度：

with mysuperfastcte as (
 select product.*, 
 row_number() over (partition by id order by version desc) as versionorder,
 row_number() over (partition by id order by changeid desc) as changeorder 
 from ProductHistory as product
)
select distinct product.*
from mysuperfastcte
where versionorder = 1
and changeorder = 1;

注意：我认为你的代码在这一点上可能存在错误，请使用我的代码确认并仔细检查您期望的结果：

  and product.changeId =  (SELECT max(changeid) from ProductHistory p3 where p2.changeId = p3.changeId))

• 你正在尝试使用相关子查询获取max(changeid)，但同时你也在连接changeid - 这就相当于获取每一行。你可能并不想这样做吧？

此外 - 显然减少返回的列数，只返回你需要的那些列，然后在运行查询之前运行以下命令，并检查输出的消息：

SET STATISTICS IO ON

查找逻辑读取次数高的表，并确定哪个索引可以帮助你。

提示：如果我的代码对你有用，那么根据你需要的列，你可以执行以下操作：

create index ix1 (id, version desc) include (changeid, .... ) on ProductHistory.

希望这能帮到你！

- Dave Hilditch

Answer 7

我认为你需要在此查询中对 (Id, changeId, version) 建立索引。请提供表定义、现有表上的索引以及查询计划。

- ypercubeᵀᴹ

Answer 8

一般来说，使用 select max() 需要遍历整个表格。而且你需要执行两次

使用 SELECT TOP 1 更快，但你需要确保索引正确并且有正确的 ORDER BY。看看能否调整它。

- GilShalit

---

-1 - 如果存在索引，SELECT MAX() 几乎总是可以使用索引。暗示它需要进行表扫描是不准确的。 - JNK

我并没有说或暗示 select max() 会使用表扫描，只是它会遍历整个表格，或者索引（如果存在的话）。 - GilShalit

SELECT TOP 1 也不会更快，因为你要么依赖于表的未指定顺序，要么你有一个 ORDER BY，这仍然需要在后台进行 SORT。 - JNK