как структурировать индекс для группы в Sql Server

Попробуйте указать SQL Server использовать индекс:

...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...

Убедитесь, что статистика для таблицы актуальна:

UPDATE STATISTICS [fctWMAUA]

Чтобы получить более точные ответы, включите план показа для обоих запросов:

SET SHOWPLAN_TEXT ON

и добавьте результаты к вашему вопросу.

Вы также можете написать запрос без GROUP BY. Например, вы можете использовать эксклюзивное LEFT JOIN, исключая строки с более старыми датами:

select cur.SourceSystemKey, cur.date
from fctWMAUA cur
left join fctWMAUA next
    on next.SourceSystemKey = next.SourceSystemKey
    and next.date > cur.date
where next.SourceSystemKey is null
and cur.SourceSystemKey in (1,2,3,4,5,6,7,8,9)

Это может быть на удивление быстро, но я не думаю, что это сможет превзойти UNION.

Трудно сказать, не глядя на план выполнения, однако вы можете попробовать следующее:

SELECT * FROM
(
    SELECT MAX(t0.AsAtDateKey) AS [Date], t0.SourceSystemKey AS SourceSystem
    FROM fctWMAUA (NOLOCK) AS t0
    GROUP BY t0.SourceSystemKey
)
WHERE SourceSystem in (1,2,3,4,5,6,7,8,9)

Трудно сказать, не глядя на план выполнения, но я думаю, что происходит то, что SQL-сервер недостаточно умен, чтобы понять, что указанное предложение WHERE отфильтровывает группы и не оказывает никакого влияния на записи, включенные для каждого группа. Как только SQL-сервер понимает это, он может использовать более интеллектуальный поиск по индексу для определения максимальных значений (что и происходит во втором запросе).

Это всего лишь теория, но, возможно, стоит попробовать.

Я обнаружил, что лучшим решением является следующее. Он имитирует объединенную версию запроса и выполняется очень быстро.

40 логических операций чтения и время выполнения 3 мс.

SELECT [t3].[value]
FROM [dimSourceSystem] AS [t0]
OUTER APPLY (
    SELECT MAX([t2].[value]) AS [value]
    FROM (
        SELECT [t1].[AsAtDateKey] AS [value], [t1].[SourceSystemKey]
        FROM [fctWMAUA] AS [t1]
        ) AS [t2]
    WHERE [t2].[SourceSystemKey] = ([t0].[SourceSystemKey])
    ) AS [t3]

Используйте HAVING вместо WHERE, чтобы фильтрация происходила ПОСЛЕ группировки:

SELECT MAX(AsAtDateKey) AS [Date], SourceSystemKey AS SourceSystem
FROM fctWMAUA (NOLOCK)
GROUP BY SourceSystemKey
HAVING SourceSystemKey in (1,2,3,4,5,6,7,8,9)

Мне также не особенно нравится предложение IN, особенно когда его можно заменить на «‹10» или «Между 1 и 9», которые лучше используются в отсортированных индексах.

 WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]

Вам не нужно группировать по фиксированному полю.

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

 WHERE SourceSystemKey in (1,2,3,4,5,6,7,8,9)

для чего-то вроде

 WHERE SourceSystemKey BETWEEN 1 AND 9

or

 WHERE SourceSystemKey >= 1 AND SourceSystemKey <= 9

если SourceSystemKey является целым числом. Но я не думаю, что это вызовет большие изменения.

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

С Уважением.

Вы пытались создать другой индекс только для столбца SourceSystemKey? Большое количество логических чтений, когда вы используете этот столбец в предложении where, заставляет меня думать, что он выполняет сканирование индекса/таблицы. Не могли бы вы запустить план выполнения и посмотреть, так ли это? План выполнения также может содержать предложение по индексу.