Имеем следующие данные
IF OBJECT_ID('dbo.LogTable', 'U') IS NOT NULL DROP TABLE dbo.LogTable
SELECT TOP 100000 DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0) datesent
INTO [LogTable]
FROM sys.sysobjects
CROSS JOIN sys.all_columns
Я хочу подсчитать количество строк, количество строк прошлого года и число последних десяти лет. Это может быть достигнуто с помощью запроса условной агрегирования или использования подзапросов следующим образом
-- conditional aggregation query
SELECT
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
-- subqueries
SELECT
(
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
Если вы выполняете запросы и просматриваете планы запросов, вы видите что-то вроде
Очевидно, что первое решение имеет гораздо более приятный план запроса, оценку затрат и даже команду SQL выглядит более кратким и фантастическим. Однако, если вы измеряете время процессора запроса с помощью SET STATISTICS TIME ON, я получаю следующие результаты (я несколько раз измерял примерно одинаковые результаты)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 41 ms.
(1 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Следовательно, второе решение имеет немного лучшую (или такую же) производительность, чем решение, использующее условную агрегацию. Разница становится более очевидной, если мы создаем индекс на атрибуте datesent.
CREATE INDEX ix_logtable_datesent ON dbo.LogTable(DateSent)
Затем второе решение начинает использовать Index Seek вместо Table Scan, а его производительность процессора по времени запроса падает до 16 мс на моем компьютере.
Мои вопросы - два: (1) почему решение условной агрегации не превосходит решение подзапроса, по крайней мере, в случае без индекса, (2) можно создать "индекс" для решения условной агрегации (или переписать запрос условной агрегирования), чтобы избежать проверки, или условная агрегация вообще непригодна, если нас беспокоит производительность?
Sidenote: Я могу сказать, что этот сценарий довольно оптимистичен для условной агрегации, так как мы выбираем число всех строк, которое всегда приводит к решению с помощью сканирования. Если количество всех строк не требуется, то индексированное решение с подзапросами не имеет сканирования, тогда как решение с условной агрегацией должно выполнить сканирование в любом случае.
ИЗМЕНИТЬ
Владимир Баранов в основном ответил на первый вопрос (большое спасибо). Однако остается второй вопрос. В ответах StackOverflow я вижу, что решения условной агрегирования довольно отягощены, и они привлекают большое внимание, которое считается самым элегантным и понятным решением (а иногда и предлагается как наиболее эффективное решение). Поэтому я немного обобщу вопрос:
Не могли бы вы привести мне пример, где условная агрегация заметно превосходит решение подзапроса?
Для простоты предположим, что физических доступов нет (данные находятся в кеше буфера), так как сегодняшние серверы баз данных остаются в большинстве своих данных в памяти.
