Подтвердить что ты не робот

Для полей автоинкремента: MAX (ID) vs TOP 1 ID ORDER BY ID DESC

Я хочу найти максимальное значение AutoIncremented из поля. (его не извлекают после вставки, где я могу использовать @@SCOPE_IDENTITY и т.д.), Какой из этих двух запросов будет работать быстрее или даст лучшую производительность. Id - это первичный ключ и поле autoincrement для Table1. И это для Sql Server 2005.

SELECT MAX(Id) FROM Table1

SELECT 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

Надеюсь, что это не высокий порядок!

4b9b3361

ОТВЕТЫ

Ответ 1

В теории они будут использовать те же планы и работать почти в то же время.

На практике

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

скорее всего, будет использовать PRIMARY KEY INDEX.

Кроме того, этот вариант более расширяем, если вы решите выбрать столбец else else вместе с id.

Фактический план MAX() гласит:

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

а в плане TOP 1 говорится:

SELECT <- TOP <- CLUSTERED INDEX SCAN

i. е. aggregate опускается.

Агрегат на самом деле ничего не сделает, поскольку есть только одна строка.

Р. S. Как отмечалось @Mehrdad Afshari и @John Sansom, в неиндексированном поле MAX выполняется несколько быстрее (разумеется, не 20 раз, как говорит оптимизатор):

-- 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.

Ответ 2

Если кластеризованный индекс практически не отличается от производительности между двумя запросами.

Это связано с тем, что оба будут выполнять кластерное сканирование индексов, которое будет стоить 100% стоимости запроса.

Выполнение двух запросов в столбце, который не имеет индекса, приводит к тому, что в обоих планах выполнения используются 3 оператора.

В предложении Top используется оператор Sort, а функция Max использует оператор Stream Aggregate.

Если индекс отсутствует, функция MAX() обеспечивает лучшую производительность.

Доказательство концепции можно найти, и полное описание тестового сценария можно найти здесь

Сравнение производительности Top 1 Verses MAX() Funciton

Ответ 3

Никто не упоминает IDENT_CURRENT ('Table1') - удаляет их всех - конечно, он работает только на столбцах идентификации, но это был вопрос...

Ответ 4

Просто сравните планы выполнения, и вы увидите (нажмите Ctrl+M в Management Studio при редактировании запроса). Моя дикая догадка заключается в том, что эти запросы одинаково эффективны, если в столбце Id есть (кластерный) индекс.

Однако это в целом очень плохая идея.

Ответ 5

MAX обычно быстрее.

Оба запроса будут использовать индекс в столбце, если он существует.

Если индекс не существует в столбце, запрос TOP 1 будет использовать оператор Top N Sort для сортировки таблицы вместо агрегации потоков, что делает ее более медленной.

MAX также обеспечивает лучшую читаемость.

Боковое примечание: в то время как MAX будет использовать оператор агрегации потока в плане выполнения в индексированном случае, он не имеет какой-либо конкретной стоимости, поскольку он просто обрабатывает одну строку (Actual Rows = 1). Вы можете сравнивать запросы, запуская их в одной партии и видя относительную стоимость. В индексированном случае оба запроса будут стоить 50%. Я проверил неиндексированный случай на столе с примерно 7000 строк, а TOP обойдется в 65% по сравнению с MAX, который стоит 35%.

Ответ 6

Я только что проверил два предложения SQL, которые вы предоставили, против типичного набора данных:

SELECT MAX(Id) FROM Table1

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

И SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC работает немного быстрее, потому что у него есть последний шаг в плане выполнения. Вот планы выполнения, которые выполняет каждый запрос:

SELECT MAX (Id) FROM Table1

Сканирование с кластеризованным индексом → Наверх → Агрегат потоков → Выберите

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

Кластерное сканирование индексов → Наверх → Выберите

Ответ 7

Да, в этом случае Id - это поле на который я определил сгруппированным индекс. Если индекс равен ID DESC, тогда что.. И да, было бы хорошо знать, как производительность будет если

  • Id - кластерный индекс + первичный ключ.
  • Id - это кластерный индекс, а не первичный ключ.
  • Идентификатор - это некластеризованный индекс ASC + первичный ключ.
  • Id - это некластеризованный индекс ASC, а не первичный ключ.
  • Идентификатор - это некластеризованный индексный первичный ключ DESC +.
  • Id - это некластеризованный индекс DESC, а не первичный ключ.
  • Идентификатор - это просто AutoIncrement

Для случаев 1 и 2 обе будут выполнять кластерное сканирование индекса, которое возвращает одну запись. Между двумя запросами нет разницы IO.

Для случаев 3, 4, 5 и 6 оба будут выполнять сканирование индекса, которое возвращает одну запись. Между двумя запросами нет разницы IO.

Для случая 7 обе будут выполнять сканирование таблицы. Нет никакой разницы в стоимости ввода-вывода.

Резюме: Случай 1-6 сделан из победы! Если вы находитесь в Case 7, то вы уже потеряли с точки зрения ввода-вывода.

Вы можете измерить IO с помощью анализатора SQL Query. Запустите это перед запросом.

SET STATISTICS IO ON