对于自动增量字段:MAX(ID) vs TOP 1 ID ORDER BY ID DESC [英] For autoincrement fields: MAX(ID) vs TOP 1 ID ORDER BY ID DESC

问题描述

我想从一个字段中找到最高的 AutoIncremented 值.(在我可以使用 @@SCOPE_IDENTITY 等的插入后，它不会被提取)这两个查询中的哪一个会运行得更快或提供更好的性能.Id 是Table1 的主键和autoincrement 字段.这是针对 Sql Server 2005 的.

SELECT MAX(Id) FROM Table1SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

是的，在这种情况下 Id 是我定义聚集索引的字段.
如果索引是 ID DESC 那么什么..
是的，很高兴知道如果
1.Id是聚集索引+主键.
2.Id是聚集索引，不是主键.
3.Id为非聚集索引ASC+主键.
4.Id是非聚集索引ASC，不是主键.
5.Id为非聚集索引DESC+主键.
6.Id是非聚集索引DESC，不是主键.
7.Id 就是 AutoIncrement

希望这不是一项艰巨的任务！

解决方案

理论上，他们将使用相同的计划并运行几乎相同的时间.

在实践中，

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

更有可能使用PRIMARY KEY INDEX.

此外，如果您决定与 id 一起选择其他列，则此列更具可扩展性.

MAX() 的实际计划说:

SELECT <- AGGREGATE <- TOP <- Clustered INDEX SCAN

，而 TOP 1 的计划说:

SELECT <- TOP <- 集群索引扫描

，我.e.aggregate 被省略.

聚合在这里实际上不会做任何事情，因为只有一行.

P.S. 正如 @Mehrdad Afshari 和 @John Sansom 所指出的，在非索引字段上 MAX 稍微快一点(当然不是优化器所说的 20 次):

<前>-- 18,874,368 行设置语言英语设置统计时间设置统计 IO打印最大"SELECT MAX(id) FROM master打印前 1"SELECT TOP 1 id FROM master ORDER BY id DESC打印最大"SELECT MAX(id) FROM master打印前 1"SELECT TOP 1 id FROM master ORDER BY id DESC打印最大"SELECT MAX(id) FROM master打印前 1"SELECT TOP 1 id FROM master ORDER BY id DESC打印最大"SELECT MAX(id) FROM master打印前 1"SELECT TOP 1 id FROM master ORDER BY id DESC打印最大"SELECT MAX(id) FROM master打印前 1"SELECT TOP 1 id FROM master ORDER BY id DESC将语言设置更改为 us_english.SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.最大限度SQL Server 执行时间:CPU 时间 = 0 ms，经过时间 = 20 ms.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读取 32655，物理读取 0，预读读取 447，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.SQL Server 执行时间:CPU 时间 = 5452 毫秒，已用时间 = 2766 毫秒.前 1SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读 32655，物理读 0，预读 2，lob 逻辑读 0，lob 物理读 0，lob 预读 0.SQL Server 执行时间:CPU 时间 = 6813 毫秒，已用时间 = 3449 毫秒.最大限度SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读 32655，物理读 0，预读 44，lob 逻辑读 0，lob 物理读 0，lob 预读 0.SQL Server 执行时间:CPU 时间 = 5359 毫秒，已用时间 = 2714 毫秒.前 1SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读取 32655，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.SQL Server 执行时间:CPU 时间 = 6766 毫秒，已用时间 = 3379 毫秒.最大限度SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读取 32655，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.SQL Server 执行时间:CPU 时间 = 5406 毫秒，已用时间 = 2726 毫秒.前 1SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读取 32655，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.SQL Server 执行时间:CPU 时间 = 6780 毫秒，已用时间 = 3415 毫秒.最大限度SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读 32655，物理读 0，预读 85，lob 逻辑读 0，lob 物理读 0，lob 预读 0.SQL Server 执行时间:CPU 时间 = 5392 毫秒，已用时间 = 2709 毫秒.前 1SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读取 32655，物理读取 0，预读读取 10，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.SQL Server 执行时间:CPU 时间 = 6766 毫秒，已用时间 = 3387 毫秒.最大限度SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读取 32655，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.SQL Server 执行时间:CPU 时间 = 5374 毫秒，已用时间 = 2708 毫秒.前 1SQL Server 执行时间:CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.(строк обработано: 1)表'主人'.扫描计数 3，逻辑读取 32655，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.SQL Server 执行时间:CPU 时间 = 6797 毫秒，已用时间 = 3494 毫秒.

I want to find the highest AutoIncremented value from a field. (its not being fetched after an insert where I can use @@SCOPE_IDENTITY etc) Which of these two queries would run faster or gives better performance. Id is the primary key and autoincrement field for Table1. And this is for Sql Server 2005.

SELECT MAX(Id) FROM Table1

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

[Edit]
Yes in this case Id is the field on which I have defined the clustered index.
If the index is ID DESC then what..
And yes it would be nice to know how the performance would be affected if
1. Id is a clustered index + primary key.
2. Id is a clustered index and not primary key.
3. Id is a non clustered index ASC + primary key.
4. Id is a non clustered index ASC and not primary key.
5. Id is a non clustered index DESC + primary key.
6. Id is a non clustered index DESC and not primary key.
7. Id is just AutoIncrement

Hope its not a tall order!

解决方案

In theory, they will use same plans and run almost same time.

In practice,

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

will more probably use a PRIMARY KEY INDEX.

Also, this one is more extendable if you will decide to select some else column along with id.

An actual plan on MAX() says:

SELECT <- AGGREGATE <- TOP <- CLUSTERED INDEX SCAN

, while plan for TOP 1 says:

SELECT <- TOP <- CLUSTERED INDEX SCAN

, i. e. aggregate is omitted.

Aggregate actually won't do anything here, as there is but one row.

P. S. As @Mehrdad Afshari and @John Sansom noted, on a non-indexed field MAX is slightly faster (of course not 20 times as optimizer says):

-- 18,874,368 rows

SET LANGUAGE ENGLISH
SET STATISTICS TIME ON
SET STATISTICS IO ON
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC

Changed language setting to us_english.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 20 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 447, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5452 ms,  elapsed time = 2766 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6813 ms,  elapsed time = 3449 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 44, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5359 ms,  elapsed time = 2714 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6766 ms,  elapsed time = 3379 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5406 ms,  elapsed time = 2726 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6780 ms,  elapsed time = 3415 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5392 ms,  elapsed time = 2709 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6766 ms,  elapsed time = 3387 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5374 ms,  elapsed time = 2708 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6797 ms,  elapsed time = 3494 ms.

这篇关于对于自动增量字段:MAX(ID) vs TOP 1 ID ORDER BY ID DESC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！