SQL почему SELECT COUNT (*), MIN (col), MAX (col) быстрее, чем SELECT MIN (col), MAX (col)

Мы видим огромную разницу между этими запросами.

Медленный запрос

SELECT MIN(col) AS Firstdate, MAX(col) AS Lastdate 
FROM table WHERE status = 'OK' AND fk = 4193

Таблица 'table'. Число сканирования 2, логическое чтение 2458969, физическое считывание 0, чтение вперед 0, логическое чтение логических чисел 0, физическое чтение lob 0, чтение с чтением lob 0.

Время выполнения SQL Server: Время CPU = 1966 мс, прошедшее время = 1955 мс.

Быстрый запрос

SELECT count(*), MIN(col) AS Firstdate, MAX(col) AS Lastdate 
FROM table WHERE status = 'OK' AND fk = 4193

Таблица 'table'. Число сканирования 1, логическое считывание 5803, физическое чтение 0, чтение вперед 0, логическое чтение логических чисел 0, физическое чтение lob 0, чтение с чтением lob 0.

Время выполнения SQL Server: Время CPU = 0 мс, прошедшее время = 9 мс.

Вопрос

В чем причина огромной разницы в производительности между запросами?

Обновление Небольшое обновление, основанное на вопросах, заданных в качестве комментариев:

Порядок выполнения или повторное выполнение ничего не меняет. Нет дополнительных параметров, и (тестовая) база данных ничего не делает во время выполнения.

Медленный запрос

|--Nested Loops(Inner Join)
 |--Stream Aggregate(DEFINE:([Expr1003]=MIN([DBTest].[dbo].[table].[startdate])))
   |    |--Top(TOP EXPRESSION:((1)))
   |         |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1008]) WITH ORDERED PREFETCH)
   |              |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED FORWARD)
   |              |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)
   |--Stream Aggregate(DEFINE:([Expr1004]=MAX([DBTest].[dbo].[table].[startdate])))
        |--Top(TOP EXPRESSION:((1)))
             |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1009]) WITH ORDERED PREFETCH)
                  |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED BACKWARD)
                  |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)

Быстрый запрос

 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1012],0)))
   |--Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1004]=MIN([DBTest].[dbo].[table].[startdate]), [Expr1005]=MAX([DBTest].[dbo].[table].[startdate])))
        |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1011]) WITH UNORDERED PREFETCH)
             |--Index Seek(OBJECT:([DBTest].[dbo].[table].[FK]), SEEK:([DBTest].[dbo].[table].[FK]=(5806)) ORDERED FORWARD)
             |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[status]<'A' OR [DBTest].[dbo].[table].[status]>'A') LOOKUP ORDERED FORWARD)

The execution plan from SSMS

Ответ

Ответ, приведенный ниже, Мартин Смит, кажется, объясняет проблему. Супер короткая версия заключается в том, что анализатор запросов MS-SQL ошибочно использует план запроса в медленном запросе, который вызывает полное сканирование таблицы.

Добавление графа (*), подсказка запроса с (FORCESCAN) или комбинированный индекс в столбцах startdate, FK и status устраняет проблему с производительностью.

Ответы

Ответ 1

Средство оценки мощности SQL Server делает различные предположения моделирования, такие как

  • Независимость: распределения данных по разным столбцам независимы, если не доступна информация о корреляции.
  • Однородность: на каждом этапе гистограммы объекта статистики разные значения равномерно распределяются, и каждое значение имеет одинаковую частоту.

Источник

В таблице содержится 810 064 строки.

У вас есть запрос

SELECT COUNT(*),
       MIN(startdate) AS Firstdate,
       MAX(startdate) AS Lastdate
FROM   table
WHERE  status <> 'A'
       AND fk = 4193

1,893 (0,23%) строки соответствуют предикату fk = 4193, а из этих двух не соответствуют части status <> 'A', поэтому общее совпадение составляет 1,891 и должно быть агрегировано.

У вас также есть два индекса, ни один из которых не охватывает весь запрос.

Для вашего быстрого запроса он использует индекс на fk, чтобы напрямую находить строки, где fk = 4193, тогда необходимо выполнить 1,893 ключевые поисковые запросы до найдите каждую строку в кластерном индексе, чтобы проверить предикат status и получить startdate для агрегации.

При удалении COUNT(*) из списка SELECT SQL Server больше не нужно обрабатывать каждую квалификационную строку. В результате он рассматривает другой вариант.

У вас есть индекс на startdate, чтобы он мог начать сканировать это с самого начала, выполняя ключевые поиски обратно в базовую таблицу и как только обнаруживает, что первая совпадающая строка останавливается, поскольку она нашла MIN(startdate), Аналогично MAX можно найти с другим сканированием, начиная с другого конца индекса и работать в обратном направлении.

SQL Server оценивает, что каждое из этих проверок закончит обработку 590 строк, прежде чем они нажмут на один, который соответствует предикату. Предоставление 1,180 общих запросов против 1,893, поэтому он выбирает этот план.

Рисунок 590 просто table_size / estimated_number_of_rows_that_match. то есть оценщик мощности предполагает, что соответствующие строки будут равномерно распределены по всей таблице.

К сожалению, 1 891 строк, которые соответствуют предикату, не распределены случайным образом относительно startdate. На самом деле все они сконденсированы в один сегмент из 8205 строк в конце индекса, означающий, что сканирование, чтобы добраться до MIN(startdate), заканчивает выполнение 801,859 ключевых поисков, прежде чем оно может остановиться.

Это можно воспроизвести ниже.

CREATE TABLE T
(
id int identity(1,1) primary key,
startdate datetime,
fk int,
[status] char(1),
Filler char(2000)
)

CREATE NONCLUSTERED INDEX ix ON T(startdate)

INSERT INTO T
SELECT TOP 810064 Getdate() - 1,
                  4192,
                  'B',
                  ''
FROM   sys.all_columns c1,
       sys.all_columns c2  


UPDATE T 
SET fk = 4193, startdate = GETDATE()
WHERE id BETWEEN 801859 and 803748 or id = 810064

UPDATE T 
SET  startdate = GETDATE() + 1
WHERE id > 810064


/*Both queries give the same plan. 
UPDATE STATISTICS T WITH FULLSCAN
makes no difference*/

SELECT MIN(startdate) AS Firstdate, 
       MAX(startdate) AS Lastdate 
FROM T
WHERE status <> 'A' AND fk = 4192


SELECT MIN(startdate) AS Firstdate, 
       MAX(startdate) AS Lastdate 
FROM T
WHERE status <> 'A' AND fk = 4193

Вы можете использовать подсказки для запроса, чтобы заставить план использовать индекс на fk, а не startdate, или добавить предлагаемый отсутствующий индекс, выделенный в плане выполнения на (fk,status) INCLUDE (startdate), чтобы избежать этой проблемы.