性能：这两个查询有什么区别？

Question

我最初写的如下：

SELECT t1.TransactionNumber
FROM t1
    JOIN
    (
          SELECT MAX(id) id
          FROM t1
          WHERE Period BETWEEN '01-11-2013' and '01-12-2014'
          GROUP BY AccountNumber
    ) t2
        on t1.id= t2.id

但是速度太慢了。它需要大约20秒的时间，因此我进行了以下更改作为测试

 SELECT MAX(id) AS id
 INTO #t2
 FROM t1
 WHERE Period BETWEEN '01-11-2013' and '01-12-2014'
 GROUP BY AccountNumber

 SELECT t1.id 
 FROM t1
    JOIN #t2 t2
        ON t1.id= t2.id

第二个查询只花费了1秒钟的时间。第二个查询使用PK键进行索引查找，而第一个查询则进行扫描。

注意：id是t1表上聚集的主键。

- user172839

---

你是否有这两个查询的执行计划？ - slavoo

你是想在表t1的主键列上进行自连接，还是涉及到其他表？ - Mikael Eriksson

还有其他表，但它们不相关。在第一个查询中，如果我只运行子查询本身，它大约需要1秒钟。但是如果运行整个查询，则需要超过20秒钟。我原以为它应该不超过2秒钟，因为它是基于主键连接的。子查询返回的记录行数也不到2000行。 - user172839

请注意可能会涉及到其他表。我已经多次遇到这种情况。一个包含子查询的大查询运行非常缓慢，一旦像你所做的那样将子查询分离出来，它就会运行得更快。执行计划会告诉你很多信息。同时，使用statistics io on也会有帮助。 - John Jin

Answer 1

这只是一个猜测，但可能是因为主键的统计信息对你没有起作用。如果查询优化器认为你只会从内部连接返回10条记录，但实际上你返回了100条记录，则会溢出内存缓冲区，最终不得不将子查询的结果写入磁盘。如果您发布查询执行计划结果，应该很明显。

- attila

Answer 2

这两个查询之间的主要区别在于第二个查询严格按照索引进行查询！！！

SELECT t1.TransactionNumber
FROM t1
JOIN
(
      SELECT MAX(id) id
      FROM t1
      WHERE Period BETWEEN '01-11-2013' and '01-12-2014'
      GROUP BY AccountNumber
) t2
    on t1.id= t2.id

查询transactionNumber列，因此无法使用您在表上的索引，第二个查询仅使用ID。这将使所有差异。

- Talspaugh27

---

抱歉，我实际上没有正确地在这里编写查询。两个查询的选择列应该是相同的。 - user172839

Answer 3

通常来说，为了提高性能（尽管需要写更多的代码），最好明确声明列和数据类型，而不是使用SELECT..INTO。这样可能会更快：

CREATE TABLE #t2
    (
    id INT
    );
INSERT INTO #t2(id)
VALUES
    (
    SELECT MAX(id)
    FROM t1
    WHERE Period <= '01-11-2013' 
    AND Period > '01-12-2014'
    GROUP BY AccountNumber
    );
 SELECT t1.id 
 FROM t1
    JOIN #t2 t2
        ON t1.id= t2.id

- Phrancis

Answer 4

你不能把所有的条件放在ON部分吗？

SELECT t1.id
FROM t1
    JOIN
    (
          SELECT id
          FROM t1
          WHERE <condition>
    ) t2
        on t1.id = t2.id;

被转换成

SELECT t1.id
FROM t1
     JOIN t1 as t2
        ON t1.id = t2.id AND <condition>

更新：

检索每个组中的最后一条记录 该链接展示了如何检索组中的最后一条记录。SQL语句如下：

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

你可以使用这个而不是 group by

- StanislavL

---

该表存储交易记录。我正在获取每个账户的最大交易额。 - user172839

谢谢，但我更感兴趣的是为什么将内部查询移动到临时表中，然后加入临时表后，查询时间从20秒+变为1秒。这只是出于我的好奇心。 - user172839

三角连接不太可能比“GROUP BY”更快。 - RBarryYoung

@user172839 如果你想满足好奇心，最好查看这两个查询的执行计划，就像其他评论中提到的那样。如果你正在使用MS SQL Server管理工具，只需键入查询并从工具栏中点击“显示预估执行计划”即可。 - Esko Piirainen

Answer 5

试一下这个：

SELECT t1.TransactionNumber t1 WHERE t1.id = (SELECT MAX(id) id FROM t1 WHERE Period BETWEEN '01-11-2013' and '01-12-2014' GROUP BY AccountNumber)

- Mohammed Saqib Rajput

Answer 6

不同之处在于，在第一个查询中，引擎不知道t2中的结果数量（我假设与t1计数相比是相对较小的数字，但SQL服务器事先不知道）。因此，执行计划从t1开始（循环大量行）。然而，在第二个查询中，t2已经有X条记录，引擎在执行查询的第二部分之前就已经知道了。因此，在该查询中，SQL引擎将使用t2开始执行（扫描t2，因为它很小），并且对于t2中的每个键，它将在t1中执行索引查找。

- Raymond Fekry

Answer 7

连接操作消耗大量资源，计算结果需要再次与表进行连接，因此需要较长时间。相反，当您使用临时表时，结果已经存储在临时表中，因此连接条件比子查询运行更快。

- user2238022