Замедление при индексном представлении для SQL 2005
Скажите, что у меня есть очень длинная таблица (~35 миллионов строк) названный TimeCard только с 5 столбцами (tableID, CompanyID, UserID, ProjectID, DailyHoursWorked, entryDate). Это - довольно прямая таблица, которая записывает обработанные часы сотрудников в день на проект на компанию.
Я теперь должен генерировать отчет узнать общие обработанные часы сотрудников в месяц на проект для любой данной компании. Вместо того, чтобы выполнить агрегирование, необходимое, когда отчет работает, я хочу создать подобную таблице структуру данных, которые уже имеют все данные Компании/Проекта/Пользователя, агрегированные месяцем, поэтому когда отчет работает, я могу просто запросить ту структуру данных непосредственно, не выполняя агрегирования во время выполнения, так как ~35million записи может занять несколько минут.
Таким образом, у меня есть 2 различных пути. Каждый составляет дополнительную физическую таблицу с (CompanyID, UserID, ProjectID, MonthlyHoursWorked, Месяц) как мои столбцы и просто использует триггер за таблицей TimeCard для изменения значений за дополнительной таблицей. Или я могу создать Индексное представление. Таким образом, я попробовал обоих. Я сначала попробовал индексное представление следующим кодом:
CREATE VIEW [dbo].[vw_myView] WITH SCHEMABINDING AS
SELECT
JobID,
ProjectID,
Sum(DailyHoursWorked) AS MonthTotal,
DATEADD( Month, DATEDIFF( Month, 0, entryDate), 0 ) AS entryMonth,
CompanyID,
COUNT_BIG(*) AS Counter
FROM
dbo.TimeCard
Group By DATEADD( Month, DATEDIFF( Month, 0, entryDate ), 0 ), JobID, ProjectID, CompanyID
Go
CREATE UNIQUE CLUSTERED INDEX [IX_someIndex] ON [dbo].[vw_myView]
(
[CompanyID] ASC,
[entryMonth] ASC,
[UserID] ASC,
[ProjectID] ASC
)
Индексное представление создается правильно и всего с ~5 миллионами общего количества строк.
Однако каждый раз, если я очищаю кэш SQL и выполняю следующий запрос: *выберите * из vw_myView, где companyID = 1*, требуется почти 3 минуты. Если я иду с дополнительным маршрутом таблицы, как я упомянул выше с моим очищенным кэшем, требуется приблизительно 4 секунды.
Мои вопросы, действительно ли Индексное представление является плохим выбором для этого конкретного сценария? В особенности мне интересно знать, получает ли все индексное представление re-calculated/re-aggregated каждый раз, когда базовая таблица (TimeCard) изменяется или когда запрос выполняется против него?
Спасибо!
6 ответов
Я не думаю, что вам нужно индексированное представление (я не говорю, что индексированное представление - плохая / хорошая идея). Я думаю, вам нужен индекс в столбцах "CompanyID" и "EntryDate" ". После этого следует использовать условие where "WHERE CompanyID = @CompanyID AND EntryDate> = @StartDate AND EntryDate <= @EndDate".
Если таблица обрабатывается в первую очередь с помощью EntryDate, вы можете использовать кластерный индекс в столбце EntryDate.
Я думаю, что после этого оператор select будет работать намного быстрее, чем сейчас.
Я бы не использовал для этого представление. Я думаю, что таблица, заполненная триггером, - это правильный путь. Но не забудьте настроить итоги обновлений и удалений, а также вставок.
Вы рассматривали возможность разбиения таблицы на разделы. Вы можете подумать о комбинации списка и хеш-таблицы разделения.
Если вы не используете редакцию Enterprise или Developer , то вам необходимо использовать ] с (noexpand) подсказкой:
select *
from vw_myView with (noexpand)
where companyID = 1
При изменении базовых данных представление обновит только строки, относящиеся к измененным данным, а не всю таблицу. Это может отрицательно повлиять на базу данных OLTP с высокой степенью вставки, но при умеренном использовании не должно создавать проблем с производительностью.
Совет от Microsoft :
В качестве общей рекомендации любые модификации или обновления представления или базовых таблиц, лежащих в основе представления {{1} } следует выполнять пакетами, если возможно, а не одноэлементными операциями. Это может уменьшить некоторые накладные расходы на обслуживание представления.
Ну, идея индексированного представления определенно хороша, и если вы можете создать кластерный индекс на нем - идеально. Это должно быть быстро - намного лучше, чем 3 минуты на запрос!
С другой стороны: если эти фрагменты информации только когда-либо обновляются, например, один раз в месяц или один раз в неделю (или даже каждую ночь), может быть лучше просто поместить их в отдельную таблицу DailyTimeCard , которая заполняется / обновляется, например, пакет SSIS регулярно.
Я бы также не рекомендовал использовать триггеры для постоянного обновления такой таблицы фактов - если вам действительно нужно иметь самые свежие данные каждую секунду дня, то оставайтесь с индексированный просмотр.
Но ваше индексированное представление делает довольно тяжелую работу - суммирует, группирует по и так далее. Постоянное поддержание этого в актуальном состоянии, пока ваша базовая таблица TimeCard изменяется и обновляется, вызовет некоторую нагрузку на вашу систему - трудно сказать, сколько, - но это может быть весьма заметно.
Если вы найдете способ извлечь нужную информацию - сгруппировать и суммировать один раз, а затем сохранить эти агрегированные данные в отдельной таблице фактов - у вас должны быть и быстрые, и быстрые запросы к таблице DailyTimeCard . , а остальная часть вашей системы должна быть меньше обременена постоянным обновлением индексированного представления.
Может быть, это не то решение, которое вы ищете, но просто подумайте об этом. Это может сработать - а может и не сработать!
Я думаю, вы на правильном пути, используя просмотр индекса. Однако если вы поместили индексы в запрашиваемую таблицу, TimeCard для ваших агрегированных столбцов. Вам необходимо создать индекс JobID, ProjectID, entryDate, CompanyID (1 индекс). Если вы используете 1 индекс для каждого столбца, это НЕ решит ваших проблем, потому что запрос должен будет использовать все 4 индекса вместе.
Я думаю, что использование триггера будет медленным, но по-другому. Это ускорит ваш запрос, но замедлит каждую вставку в TimeCard . Если вы все же решите использовать триггер, я бы обязательно проиндексировал эту таблицу или тоже мог бы быть медленным, не на 3 минуты медленным, но все же медленным для сортировки и возврата данных.