Условная производительность агрегации

Я хочу подсчитать количество строк, количество строк прошлого года и число последних десяти лет. Это может быть достигнуто с помощью запроса условной агрегирования или использования подзапросов следующим образом

Если вы выполняете запросы и просматриваете планы запросов, вы видите что-то вроде

Очевидно, что первое решение имеет гораздо более приятный план запроса, оценку затрат и даже команду SQL выглядит более кратким и фантастическим. Однако, если вы измеряете время процессора запроса с помощью SET STATISTICS TIME ON, я получаю следующие результаты (я несколько раз измерял примерно одинаковые результаты)

Следовательно, второе решение имеет немного лучшую (или такую же) производительность, чем решение, использующее условную агрегацию. Разница становится более очевидной, если мы создаем индекс на атрибуте datesent.

Затем второе решение начинает использовать Index Seek вместо Table Scan, а его производительность процессора по времени запроса падает до 16 мс на моем компьютере.

Мои вопросы - два: (1) почему решение условной агрегации не превосходит решение подзапроса, по крайней мере, в случае без индекса, (2) можно создать "индекс" для решения условной агрегации (или переписать запрос условной агрегирования), чтобы избежать проверки, или условная агрегация вообще непригодна, если нас беспокоит производительность?

Sidenote: Я могу сказать, что этот сценарий довольно оптимистичен для условной агрегации, так как мы выбираем число всех строк, которое всегда приводит к решению с помощью сканирования. Если количество всех строк не требуется, то индексированное решение с подзапросами не имеет сканирования, тогда как решение с условной агрегацией должно выполнить сканирование в любом случае.

Владимир Баранов в основном ответил на первый вопрос (большое спасибо). Однако остается второй вопрос. В ответах StackOverflow я вижу, что решения условной агрегирования довольно отягощены, и они привлекают большое внимание, которое считается самым элегантным и понятным решением (а иногда и предлагается как наиболее эффективное решение). Поэтому я немного обобщу вопрос:

Не могли бы вы привести мне пример, где условная агрегация заметно превосходит решение подзапроса?

Для простоты предположим, что физических доступов нет (данные находятся в кеше буфера), так как сегодняшние серверы баз данных остаются в большинстве своих данных в памяти.

Ответы

Ответ 1

Краткое описание

Производительность метода подзапросов зависит от распределения данных.
Производительность условной агрегации не зависит от распределения данных.

Метод подзапросов может быть быстрее или медленнее, чем условная агрегация, это зависит от распределения данных.

Естественно, если таблица имеет подходящий индекс, то подзапросы, вероятно, извлекут выгоду из нее, потому что индекс позволит сканировать только соответствующую часть таблицы вместо полного сканирования. Наличие подходящего индекса вряд ли принесет существенное влияние на метод условной агрегирования, поскольку он будет сканировать полный индекс в любом случае. Единственное преимущество было бы в том, что индекс будет уже, чем таблица, и движок должен будет считывать меньше страниц в памяти.

Зная это, вы можете решить, какой метод выбрать.

Первый тест

Я сделал большую тестовую таблицу с 5М строками. На столе не было указателей. Я измерил статистику ввода-вывода и процессора, используя SQL Sentry Plan Explorer. Я использовал SQL Server 2014 SP1-CU7 (12.0.4459.0) Express 64-бит для этих тестов.

Действительно, ваши первоначальные запросы вели себя так, как вы описали, т.е. подзапросы были быстрее, хотя чтения были в 3 раза выше.

После нескольких попыток таблицы без индекса я переписал ваш условный агрегат и добавленные переменные для хранения значений выражений DATEADD.

Общее время стало значительно быстрее.

Затем я заменил SUM на COUNT, и он стал немного быстрее.

В конце концов, условная агрегация стала такой же быстрой, как и подзапросы.

Теплый кеш (CPU = 375)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

Подзапросы (CPU = 1031)

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-1,GETDATE())
) last_year_cnt,
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-10,GETDATE())
) last_ten_year_cnt
OPTION (RECOMPILE);

Исходная условная агрегация (CPU = 1641)

SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Условная агрегация с переменными (CPU = 1078)

DECLARE @VarYear1 datetime = DATEADD(year,-1,GETDATE());
DECLARE @VarYear10 datetime = DATEADD(year,-10,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Условная агрегация с переменными и COUNT вместо SUM (CPU = 1062)

SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE NULL END) AS last_year_cnt,
    COUNT(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

На основе этих результатов я предполагаю, что CASE вызвал DATEADD для каждой строки, а WHERE был достаточно умен, чтобы вычислить его один раз. Плюс COUNT является чуть более эффективным, чем SUM.

В конце концов, условная агрегация только немного медленнее, чем подзапросы (1062 против 1031), возможно, потому что WHERE немного эффективнее, чем CASE сама по себе, и кроме того, WHERE отфильтровывает довольно много строк, поэтому COUNT должен обрабатывать меньше строк.

На практике я бы использовал условную агрегацию, потому что я думаю, что число чтений более важно. Если ваша таблица невелика, и она остается в пуле буферов, то любой запрос будет быстрым для конечного пользователя. Но, если таблица больше доступной памяти, я ожидаю, что чтение с диска значительно замедлит подзапросы.

Второй тест

С другой стороны, важно также фильтровать строки как можно раньше.

Вот небольшая вариация теста, которая демонстрирует это. Здесь я устанавливаю порог как GETDATE() + 100 лет, чтобы убедиться, что никакие строки не удовлетворяют критериям фильтра.

Теплый кеш (CPU = 344)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

Подзапросы (ЦП = 500)

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,100,GETDATE())
) last_year_cnt
OPTION (RECOMPILE);

Исходная условная агрегация (CPU = 937)

SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,100,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Условная агрегация с переменными (CPU = 750)

DECLARE @VarYear100 datetime = DATEADD(year,100,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Условная агрегация с переменными и COUNT вместо SUM (CPU = 750)

SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Ниже приведен план с подзапросами. Вы можете видеть, что 0 строк вошли в Агрегат потока во втором подзапросе, все они были отфильтрованы на этапе сканирования таблицы.

В результате подзапросы снова быстрее.

Третий тест

Здесь я изменил критерии фильтрации предыдущего теста: все > были заменены на <. В результате условный COUNT подсчитал все строки вместо none. Сюрприз Сюрприз! Условный запрос агрегации занял одинаковые 750 мс, тогда как подзапросы стали 813 вместо 500.

Вот план подзапросов:

Не могли бы вы привести мне пример, где условная агрегация заметно превосходит решение подзапроса?

Вот он. Производительность метода подзапросов зависит от распределения данных. Производительность условной агрегации не зависит от распределения данных.

Зная это, вы можете решить, какой метод выбрать.

Информация о бонусах

Если вы наведите указатель мыши на оператор Table Scan, вы можете увидеть Actual Data Size в разных вариантах.

Простой COUNT(*):

Условная агрегация:

Подзапрос в тесте 2:

Подзапрос в тесте 3:

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

В случае простого COUNT(*) нет Output list (значения столбца не нужны), а размер данных наименьший (43 МБ).

В случае условной агрегации эта сумма не изменяется между тестами 2 и 3, она всегда равна 72 МБ. Output list имеет один столбец datesent.

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