Подзапрос против производительности соединений

Я бы, вероятно, написал этот запрос, используя соединения:

SELECT
    s.siteid,
    COALESCE(si.CountUniquePermissions, 0) AS CountUniquePermissions,
    COALESCE(si.CountNotModified30Days, 0) AS CountNotModified30Days
FROM sites s
LEFT JOIN
(
    SELECT siteid,
           COUNT(CASE WHEN CountUniqueRoleAssignments > 0 THEN 1 END)
               AS CountUniquePermissions,
           COUNT(CASE WHEN Modified < DATEADD (day, -30, GETDATE()) THEN 1 END)
               AS CountNotModified30Days
    FROM ScannedItems
    GROUP BY siteid
) si
    ON si.siteid = s.siteid
ORDER BY
    s.siteid;

В приведенном выше запросе нет предложений WHERE или HAVING, поэтому я не вижу очевидного способа его дальнейшей настройки с использованием индексов. Но у него, по крайней мере, есть потенциальное преимущество перед вашим текущим запросом, заключающееся в том, что он не включает поведение N^2 с коррелированными подзапросами в предложении select.

Вы можете использовать LEFT JOIN и условную агрегацию следующим образом:

select 
    S.siteid,
    COUNT(CASE WHEN SI.CountUniqueRoleAssignments > 0 THEN 1 END) as CountUniquePermissions,
    COUNT(CASE WHEN SI.Modified<DATEADD (day, -30 ,GETDATE()) THEN 1 END ) as CountNotModified30Days
from sites S
LEFT JOIN ScannedItems SI ON SI.siteid = S.siteid
GROUP BY S.SITEID

При любом вопросе производительности вы должны протестировать код SQL на своих данных. Для этого запроса:

select s.*,
       (select COUNT(*)
        from ScannedItems si
        where si.siteid = s.siteid and si.CountUniqueRoleAssignments > 0
       ) as CountUniquePermissions,
       (select COUNT(*)
        from ScannedItems si
        where si.siteid = s.siteid and si.Modified < DATEADD(day, -30 ,GETDATE())
       ) as CountNotModified30Days
from sites s;

Я бы рекомендовал два индекса:

• ScannedItems(siteid, CountUniqueRoleAssignments)
• ScannedItems(siteid, Modified).

Я ожидаю, что с этими двумя индексами это будет быстрее, чем любая альтернатива, использующая агрегацию. Почему? Агрегация — довольно дорогая операция. Коррелированные подзапросы могут использовать для обработки только индексы.

Это сканирование двух индексов (по сравнению с одной агрегацией), поэтому не гарантируется на 100%, будет ли это быстрее, поэтому вам следует протестировать его. Однако, если у вас есть фильтр в таблице sites, подход коррелированного подзапроса часто намного быстрее, потому что для расчета используются только сайты, возвращаемые запросом.