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

Asked
Viewd16941

8

Выполнение следующего простого запроса занимает очень много времени (несколько минут).

У меня есть индекс:

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

Статистика выглядит следующим образом:

  • логических чтений 1827978
  • физические чтения 1113
  • читать вперед 1806459

Взяв тот же самый запрос и переформатировав его следующим образом, я получил следующую статистику:

  • логических чтений 36
  • физических чтений 0
  • читать вперед 0

Для выполнения требуется 31 мс.

 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 1
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 2
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]
/* AND SO ON TO 9 */
 

Как мне создать индекс, который быстро выполняет группировку?

  • У вас есть индекс по SourceSystemKey?Если нет, я думаю, вы можете вызвать полное сканирование таблицы.

    heferav04 ноября 2009, 12:41
  • Что показывает шоу-план?и какие значения может принимать SourceSystemKey?

    mmmmmm04 ноября 2009, 13:07

6 ответов

1

Попробуйте указать 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.

  • Имеет ли значение добавление OPTION (HASH GROUP) или OPTION (ORDER GROUP) в конце запроса?

    Andomar04 ноября 2009, 13:05
  • Я реструктурировал запрос, чтобы запросить исходные системы, а затем выполнил внутренний запрос для каждой из максимальных дат.

    Это идеально использует индекс и занимает около 7 мс. Намного быстрее, чем группировать по, а также быстрее, чем объединять.

    выбрать SourceSystems.SourceSystemKey, (выберите max (AsAtDateKey) из fctWMAUA где fctWMAUA.SourceSystemKey = SourceSystems.SourceSystemKey группа по fctWMAUA.SourceSystemKey) MaxData из SourceSystems

    Craig09 ноября 2009, 08:59
  • если посмотреть на план, это имеет смысл. Этот первоначальный поиск найдет все записи.Существует всего девять исходных систем, и он ищет их.

    Craig04 ноября 2009, 12:46
  • Привет, Андомар! Хорошее предложение.Хеш-группа сократила его примерно до пятнадцати секунд, что приемлемо, если я кэширую результаты. По-прежнему странно, что я могу получить 32 мс из объединенной версии и ничего близкого к группе по версии.Версия union выполняет поиск и вершину (1) для каждого из запросов, что очень быстро.Индекс не может это дублировать.

    Craig05 ноября 2009, 04:22
  • Пробовал все ваши предложения. Все еще очень медленно.Союз по-прежнему быстр.

      | --Stream Aggregate (GROUP BY: ([t0]. [SourceSystemKey]) DEFINE: ([Expr1003] = MAX ([partialagg1004])))
           | --Параллелизм (Собрать потоки, ЗАКАЗАТЬ: ([t0]. [SourceSystemKey] ASC))
                | --Stream Aggregate (GROUP BY: ([t0]. [SourceSystemKey]) DEFINE: ([partialagg1004] = MAX ([KITE]. [Dbo]. [FctWMAUA]. [AsAtDateKey] как [t0]. [AsAtDateKey])))
                     | --Поиск индекса (ОБЪЕКТ: ([KITE]. [Dbo]. [FctWMAUA]. [IX_AsAtDateSourceSystem] AS [t0]), SEEK: ([t0]. [SourceSystemKey]> = (1) AND [t0].[SourceSystemKey] <= (9)) ЗАКАЗАНО ВПЕРЕД)
    
    Craig04 ноября 2009, 12:39
  • Я также переупорядочиваю поля в индексе, и он не меняется.

    Craig04 ноября 2009, 12:42
0

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

1

Используйте 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», которые лучше используются в отсортированных индексах.

2

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

 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-сервер осознает это, он сможет свободно использовать еще несколько интеллектуальных поисков по индексу для определения максимальных значений (что и происходит во втором запросе)

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

0
  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
 

или

  WHERE SourceSystemKey >= 1 AND SourceSystemKey <= 9
 

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

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

С уважением.

  • @ j.a.estevan: SQL Server требует GROUP BY, прежде чем он позволяет использовать агрегаты, такие как MAX ()

    Andomar04 ноября 2009, 15:29
  • В этом сценарии это не требуется.Как правило, это не требуется, если вам не нужно группировать данные.Просто попробуйте (например):

    выберите max (object_id) из sys.tables, где имя, например «% A%»

    Это отлично работает в SQL Server 2005.

    j.a.estevan04 ноября 2009, 15:40
  • Андомар: Я говорил о GROUP BY, потому что если вы поместите «WHERE SourceSystemKey = 3», я не вижу смысла в «GROUP BY SourceSystemKey», потому что существует только SourceSystemKey.Группировать нечего, вы ищете абсолютное значение MAX, которое проходит фильтр WHERE.Но в любом случае оптимизатор знает об этом, и это не должно быть проблемой.(edit: Говоря о второй команде. В первом случае GROUP BY в порядке, очевидно)

    j.a.estevan04 ноября 2009, 14:15
  • Я попробовал что-то среднее, но ничего не изменилось. Он использует индекс, и первоначальный поиск по индексу возвращает 665 миллионов строк.

    Используя объединение, он ищет одну строку (верхнюю 1) для каждого правильно упорядоченного максимума, и это очень быстро.

    Без объединения он ищет 665 миллионов строк и выполняет итерацию партии. Это безумие.

    Оба запроса определенно используют одни и те же индексы в плане.

    Craig04 ноября 2009, 12:41
  • Вы правы, группа по не требуется в объединенной версии запроса.Я копировал / вставлял запрос. Однако это не влияет на план запроса.

    Craig05 ноября 2009, 04:14
  • Что вы имеете в виду, говоря «Вам не нужно группировать по фиксированному полю»?Он ищет максимальное свидание.

    Andomar04 ноября 2009, 11:56
  • @ j.a.estevan: Спасибо, это избавит меня от лишнего набора текста!

    Andomar05 ноября 2009, 09:52
3

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

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]