Which one is faster of the following two queries?

以下两个查询中哪个更快？

1

SELECT TOP 1 order_date
FROM         orders WITH (NOLOCK)
WHERE customer_id = 9999999
ORDER BY order_date DESC

2

SELECT MAX(order_date)
FROM         orders WITH (NOLOCK)
WHERE customer_id = 9999999

5 个解决方案

#1

With an index on order_date, they are of same performance.

使用order_date上的索引，它们具有相同的性能。

Without an index, MAX is a little bit faster, since it will use Stream Aggregation rather than Top N Sort.

没有索引，MAX会快一点，因为它将使用流聚合而不是前N排序。

#2

ORDER BY is almost always slowest. The table's data must be sorted.

ORDER BY几乎总是最慢的。必须对表的数据进行排序。

Aggregate functions shouldn't slow things down as much as a sort.

聚合函数不应该像减少一样减慢速度。

However, some aggregate functions use a sort as part of their implementation. So a particular set of tables in a particular database product must be tested experimentally to see which is faster -- for that set of data.

但是，某些聚合函数使用排序作为其实现的一部分。因此，必须通过实验测试特定数据库产品中的特定表集，以查看哪个表更快 - 对于该组数据。

#3

I would say the first, because the second requires it to go through an aggregate function.

我会说第一个，因为第二个要求它通过一个聚合函数。

However, as marc_s said, test it.

但是，正如marc_s所说，测试它。

#4

You can't answer that without knowing at least something about your indexes (have you got one on customer_id, on order_date?), the amount of data in the table, the state of your statistics etc.etc.

如果不知道至少有关索引的内容（你有没有关于customer_id，有关order_date吗？），表中的数据量，统计数据等等，你都无法回答这个问题。

#5

it depends on how big your table is. On the second query, i guess there's no need for "TOP 1," since MAX only returns one value. If I were you, I would use the second query.

这取决于你的桌子有多大。在第二个查询中，我猜不需要“TOP 1”，因为MAX只返回一个值。如果我是你，我会使用第二个查询。

#1